eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
In some ways Google Spreadsheets goes beyond Microsoft Excel and can actually extend the enterprise user’s capabilities. Google Spreadsheets can help users in the enterprise with collaboration, visualization and analytics. Here are some examples:
1. Collaboration
Google Spreadsheets allows multiple users to work together in real time on a single spreadsheet report. Even if you’re not ready to replace Excel, you will benefit from uploading your existing spreadsheet to Google Spreadsheets, then inviting people to a collaborative session. When finished, you can simply save your spreadsheet back to Excel or keep it live on Google Docs.
2. Visualization
Google has built some interesting data visualization tools into its free Spreadsheets application. These tools, known as gadgets, can be added to a Google spreadsheet as needed. For example you can inset an animated “motion chart” into your spreadsheet to show how data has changed over time.
3. Data analytics
The analytical features in Google Spreadsheets are extremely powerful, enabling numbers to tell stories that might otherwise have never been found. By being able to “slice and dice” data, you can see your numbers in a new light and gain important insights.
Pivot tables are a great case in point for the power of analytics. By inserting a pivot table into your spreadsheet, you can instantly and dynamically compare classes or information such as expenses over time, sales by geography and so forth. Or you can quickly identify your top 10 (or bottom 10) products, vendors or salespeople.
Step-by-Step Guide to Using Analytics in a Google Spreadsheet
Step-by-step guide to using analytics in a Google spreadsheet
The following are four steps you need to follow in order to use Google Spreadsheets for data analysis.
Step No. 1: Choose your spreadsheet
If you are already using Google Spreadsheets, you are one step way from getting started with using it for analytics. If you use other spreadsheet applications, just log in to your Docs.google.com account (if you don’t have one, it is very easy to create it) and select “upload.” Then, select your spreadsheet from your desktop.
Step No. 2: Ensure your data is formatted correctly for analysis
To use a spreadsheet for analytics, it’s important to have a very simple layout. In other words, be sure to have a first row that contains the headers for each column. Below the first row, be sure to clearly list all the pertinent information and data. For example, under the “Product” header (see screenshot below), you should have a long list of product names or SKU numbers. Then, under the “Date” header, you should have dates and so forth.
Step No. 3: Insert the analytics component
In Google Spreadsheets, select “Insert” and then “Gadgets.” Gadgets are the way Google delivers advanced functionality in their spreadsheet and other applications. On the left side of the new window that comes up, chose “Tables.” Now you will see “Analytics for Google spreadsheet.” Click on “Add to spreadsheet.”
Create Your First Analytical Report
Step No. 4: Create your first analytical report
Now it’s time to create your first analytical report. Using your mouse, select the data range you want to include in your analytical report. Then click the “apply and close” option. See the screenshot example below.
From the “new reports” screen, select the data you want to represent in your rows and columns. Use the dropdown selection box and click on “Finish.”
At this point, you have your report ready. Here’s an example of a ready report:
The Report Tool is Full of Features
The report tool is full of features and functions that will let you get more out of your data, such as filtering your report using the filter pane to make reports more specific. See the screenshot below.
Or you can select and sort your top (or worst) data, as follows:
To learn more about pivot tables for Google Spreadsheets, a short tutorial video can be found online.
Google Spreadsheets’ analytics capability, like its other value-added components, can instantly enhance your use of business, organizational or personal information. Best of all, these features are no further than your Web browser. Give one a try and see if it doesn’t make your daily tasks a lot easier and more productive.