Sheets, data, and charts ... Oh my! If you are just tuning in, this is the second post in our Google Sheets series. If you missed the first post, we covered how to Sort, Filter, Merge Cells and Freeze Rows and Columns. We continue this series with additional information to help you leverage the features of Google Sheets.
We invite you to explore Dashboards along with us:
- Understanding Dashboards
- Data Examples
- Use Case
- Using Dashboards
- Creating a Dashboard
- Inserting a Chart
- Chart Recommendations
- Chart Previews
- Built-in Chart Tutorials
- Interpreting the Data
Understanding Dashboards In Google Sheets
Google Sheets allows you to track and manipulate data, and then graphically depict related, meaningful data to show trends, comparisons or representations of a whole. A dashboard of charts is useful for analyzing various sets of data on your own, or you may want to share your dashboard with your colleagues so they can help you discover underlying trends within related data. It is often easier to draw conclusions from information displayed graphically, rather than attempting to digest multiple lists of text and numbers.
Use Case
Using the features of Google Sheets, it becomes easier to depict desired information of data sets. For instance, a dashboard of sales information could help you examine various trends at a glance.
- When did we meet our revenue goals?
- Why have we exceeded our goals drastically in certain months?
- What products had the highest total sales when we exceeded our monthly revenue goals?
- How does our month to month revenue compare to last year?
Using Dashboards In Google Sheets
After reviewing how to insert charts into the dashboard tab of our spreadsheet, we will be able to answer the questions outlined above using the sample sales dashboard that we’ve created in Google Sheets.
For this example we will be manipulating the following sample data sets:
The dashboard will contain these lists of data in the form of charts. Google Sheets offers several basic chart type options, including Line, Area, Bar, Column, Scatter, Pie and Table. Advanced chart types are available as well, such as Motion, Timeline, Sparklines, Gauges, Radar, GeoMap, Combo, Organizational, Bubble, Candlestick and Tree Map.
Let’s review how to insert one of the charts on our Dashboard tab, and also take a look at some of the unique features of Google Sheets along the way.
The following are steps for inserting a chart in Google Sheets:
1. Open the Dashboard tab of your spreadsheet and click in the area where you would like to place the chart.
2. Click on the Insert Chart icon on the menu bar, or...
2a. Select Insert from the menu items.
3. Choose Chart from the drop-down list and the Chart Editor window opens to the Start tab.
4. While on the start tab, Highlight the range data you would like to include in your chart and choose OK.
The selected range, A1:E5, within the sheet named “Quarterly Product Sales” is displayed in the “Data” field highlighted below. You can click on “Select ranges” to add additional ranges of data for your chart.
5. Determine what chart will be the most effective in displaying your data.
Recommended Charts:
An advantage when using Google Sheets to create your dashboards is the 'Recommended Charts' feature.
For example, after selecting the Quarterly Product Sales range of data below, Google Sheets has shown which charts are recommended for visually representing the selected data. This feature will save you time searching for a chart that will accurately represent your data.
Preview Charts:
Also, the automatic preview in Google Sheets allows you to test out different chart types and take a sneak peek at how your data will display before actually inserting a chart into your spreadsheet.
If one of the recommended chart types are not desired, select “More” to move from the Start tab of the Chart Editor window to the Charts tab.
On the Charts tab, all chart types, and a visual for each, are listed. The visuals that are shown in color indicate the chart types that will function properly with the layout of your selected data.
Those charts displayed in grayscale indicate that your selection of data is not in the correct layout, or does not include the required details for producing that particular chart.
Built-in Tutorials:
Luckily, you are not left in the dark as to why a particular chart is not an option. Google Sheets provides a description of what format is required.
For example, after selecting the Pie chart type from the list shown below, a notification displays in the chart preview pane explaining that the selected data is not in the required format for the Pie chart. Additionally, details on how to format the data for the Pie chart, and the opportunity to try a live example are provided as well.
Steps for Inserting a Chart (continued):
The recommended Bar chart is sufficient for displaying Quarterly Product Sales data. So, we can move on to customizing the Bar chart.
6. Select the Customize tab on the Chart Editor window.
On the Customize tab, we have the opportunity to adjust the Bar chart’s appearance to meet our personal preferences.
Items available for customization are as follows:
- Add a Chart Title with the font size and color of your choice
- Select the desired placement, font and font size of the legend
- Choose a background color
- Add axis titles
- Adjust the font, size and min/max values of axis labels
- Customize major and minor gridlines and the color of each
- Customize the number formats or select “From Data” to keep the selected data’s formatting
- Customize the bars with a unique color, or utilize the default color scheme
7. After customizing your chart, select Insert at the bottom of the Chart Editor window.
After choosing insert, you can continue to make edits to your chart by selecting the Quick Edit Mode icon located in the top, left corner of your chart. Quick edit mode allows you to click on areas of the chart and make any necessary formatting and layout changes.
Or, selecting the Advanced Edit option from the drop-down arrow in the top, right corner of your chart will take you back to the Customize tab of the Chart Editor window to make further changes.
After repeating the steps for inserting charts for each of our data sets, we can merge cells in Row 1 of our dashboard spreadsheet and give the dashboard a title (“Sales Performance Dashboard” in the example below).
Next, click on each chart to move or resize, and organize them on the dashboard sheet, which is comprised of the following Google Sheets chart types:
- Line Chart - Revenue: 2012 vs. 2011
- Combo Chart - 2012 Revenue vs. Goal
- Bar Chart - 2012 Quarterly Product Sales
- Column Chart - 2012 Client Meetings
Finally, we can quickly identify trends in the sales data, and answer the questions we had pondered at the beginning of this tutorial, before we had created the dashboard.
Q. When did we meet our revenue goals?
A. We can see on our Combo chart that in 2012, we reached or exceeded our monthly revenue goals in January, February, April, May, June, October, November and December.
Q. Why have we exceeded our goals drastically in certain months?
A. We can see on our Combo chart that the most significant difference in our projected goal versus our actual revenue occurred in April. This occurred because Joan and Fred had the highest amount of success in meeting with prospective clients during the month of April which is clearly shown in our Column chart.
Q. What products had the highest total sales when we exceeded our monthly revenue goals?
A. We can see on the Bar chart that during the second quarter (April - June), Products A, B and D reached their highest sales for the year.
Q. How does our month to month revenue compare to last year?
A. We can see on our Line chart that 2012 revenue was relatively similar to that in 2011, as both lines have peaked and dropped in the same months.
In this post, we’ve covered a few of the basic chart types available in Google Sheets. There are plenty of great features and functions available in Google Sheets to allow you to create visually appealing and compelling dashboards of your own.
If you have any questions about how to use these functionalities, we invite you to chat with one of our experts during Blog Office Hours, every Thursday from 11-12 est. Additionally, if you would like to schedule a Google Spreadsheets training for your organization, please visit the Training section of our website. Stay tuned for the next installment of our Google Spreadsheet Series, “How to: Maintain Data Integrity in Google Sheets”.