Create an interactive dashboard in Google Sheets
It’s great to see that G-Suite has released three Google Sheets new features to improve data display and reporting. The latest round of updates was announced at Google Cloud Next 2019 and the gradual rollout started this summer. With these three new tools, Sheets moves a step closer to offering a full dashboard-type experience.
What’s new?
Slicers
Slicers are a user-friendly new filtering tool for pivot tables and charts. They allow you to “slice” through the data, selecting what you do and don’t want to include in your results. A slicer functions like a checkbox. You can check or uncheck items and it filters the results. But unlike filter view or drop-down menus, slicers are designed for collaboration. The slicer appears as a neat, user-friendly button on the screen – perfect for Google Sheets users who want to create a dashboard or share reports with others.
What can I do with a slicer?
- Filter data in a Pivot Table or Chart by condition or values
- Provide a standalone, user-friendly button to filter data
- Improve the appearance of your dashboards and reports – ideal for collaboration
- Watch the data update dynamically – with one click
How do I add a slicer?
- Open your Google Sheets spreadsheet
- Create a pivot table or chart from your data.
- In the Google Sheets menu, select Data > Slicer
- Select which column you want to filter your results by.
- In the slicer, select if you want to filter by condition or values.
Scorecard Charts
What’s a scorecard chart?
A scorecard chart zooms in on one metric in your spreadsheet – and displays it visually in a “scorecard” format. This is an essential component of a good dashboard, providing at-a-glance information on key stats to the user. This is really useful for pulling out important pieces of data for analysis. It’s ideal for KPI reports, as you can select values such as Total Revenue to be displayed in this way. Check out our blog post for more ideas on how to manage and visualize your marketing KPIs with a dashboard in Google Sheets.
How do I add a scorecard chart?
- Open a spreadsheet in Google Sheets
- In the menu, click Insert > Chart
- On the right hand side, click the Chart type dropdown menu
- Scroll down, and in the last section, called “Other”, you will see an image of a scorecard chart.
There are three options. Your scorecard chart can display data:
- From one cell
- From multiple cells
- To show comparisons between two ranges of data
How to display data from one cell:
- On the scorecard chart click More > three dots > Edit chart.
- On the Setup tab, under key value click More > three dots > Edit.
- Click the cell you want to display data from.
How to display information about a group of cells:
This is for when you want to display data obtained from multiple cells, such as a sum or an average. Here’s how to do it.
- On the scorecard chart click More > three dots > Edit chart.
- On the Setup tab, under key value click More > three dots > Edit.
- Select a group of cells and click OK.
- In the setup tab, click Aggregate. Then choose an option.
How to display comparisons:
This is for when you want to compare data to show differences, such as changes over time, or a percentage difference. You choose a “baseline value” and the comparison displayed will be the difference from this value.
- On the scorecard chart click More > three dots > Edit chart.
- On the Setup tab, under Baseline value, click More > three dots > Edit. Enter your baseline value.
- Select a cell or range of cells to compare your key value to and click OK.
- To display the comparison as a percentage, select Customize > Baseline value.
- Under Show comparison to key value as, choose percentage from the dropdrown menu.
Themes in Google Sheets
This update allows you to customize the entire look and feel of your spreadsheet, with different colours and fonts. The theme is applied to everything, including charts, pivot tables and cells. Ready-made themes include “forest”and “retro” but you can also customize the theme yourself.
How do I apply a theme to my spreadsheet?
Open your Google Sheet.
In the menu bar, select Format > Theme
Good luck!
Have fun experimenting with your own dashboard! Google is constantly innovating and improving G Sheets so we’ll keep you updated with the next releases of new Google Sheets features.
