What Is Dashboard In Google Sheets?
A dashboard in Google Sheets is a business analysis tool that takes complex data from different sources and translates it into visually appealing plots and tables. We can use it to identify common patterns and trends to make informed decisions.
Users can utilize the Google Sheets dashboards to track a project’s status, compare different tasks’ progress and monitor key performance indicators as part of project management.
For example, the source dataset contains a firm’s monthly inventory level data.
We must make the given data visually more appealing, which we can achieve by building a dashboard in Google Sheets based on the source data.
While we can use the Google Sheets dashboard templates, we have created one from scratch.
We open a new sheet and access the Chart option from the Insert tab to open the Setup tab within the Chart editor window.
Next, we insert two chart types, the Scorecard and Pie, from the Chart editor pane. The first one is to show the total monthly inventory level, which is the sum of all the values in the range B2:B7 of the source dataset. On the other hand, the Pie chart is the graphical representation of the month-wise inventory level data based on the data in the cell range A2:B7 of the source dataset.
Furthermore, the Chart editor pane has two tabs, Setup and Customize. While the Setup tab gives us the options to insert the required charts, the Customize tab contains options to modify the inserted plots according to our requirements.
Thus, the above example shows that building a dashboard in Google Sheets makes analyzing the source dataset more practical and effective.
Table of contents
Key Takeaways
- A dashboard in Google Sheets is a powerful analysis tool that helps visually represent data in a single sheet based on the data fetched and imported from various sources.
- Typically, we can use tools such as Pivot Tables, Charts, Sparklines, Slicers, and Data Validation to build interactive and dynamic dashboards in Google Sheets.
- The Google Sheets dashboards are useful for measuring metrics such as customer KPIs, financial statistics, sales trends, web analytics, and marketing statistics.
- While there are dashboard templates available in Google Sheets, we can create customized dashboards from scratch.
Types Of Dashboard In Google Sheets
The different dynamic and interactive dashboard in Google Sheets types are as follows:
- Analytics dashboards – They help in determining patterns by manipulating massive historical datasets.
- Operational dashboards – They help monitor operational processes.
- Strategic dashboards – These highlight high-level macro metrics, which are crucial for drawing strategic decisions.
- Tactical dashboards – These dashboards help us track a firm’s performance.
Common Google Sheets Tools For Creating Dashboard
The common tools for creating an interactive dashboard in Google Sheets are the following:
- Pivot Tables
Pivot tables help us summarize massive datasets.
- Lookup Formulas
We can use inbuilt functions such as VLOOKUP, and create user-defined multiple conditions-based lookup formulas to make the source dataset more presentable.
- Slicers
Slicers help us visually depict selected data while keeping the sliced-away data hidden.
- Charts
Charts are useful for interpreting the data trends visually, which helps in quick analysis.
- Sparklines
Sparklines are useful graphs for representing the time-based improvement in a metric.
How To Create Dashboard In Google Sheets?
The steps to create a Google Sheets dashboard are as follows:
- Open Google Sheets.
- Update the source dataset, based on which we must develop a dashboard in one sheet tab.
- Add a new sheet tab where we wish to display the dashboard content.
- Insert the required pivot tables based on the source dataset in the second sheet tab.
- Using the Chart option in the Insert tab, create the required charts based on the pivot tables or the source dataset in the second sheet tab. Use the options and features in the Chart editor pane, which opens upon choosing the Chart option, to create the desired plots.
- We can use other tools, such as Sparklines and Slicers, to enhance the dashboard’s appearance and effectiveness.
For example, the source dataset shows a company’s monthly invoice data.
The requirement is to create a dashboard based on the source dataset. The dashboard must show the total order cost scorecard, the monthly order cost trend, and the order quantities and costs based on the ascending order of the product categories.
Though we can make use of the Google Sheets dashboard templates, we shall see how to develop a dashboard from scratch in Google Sheets.
Step 1: Add a new sheet to the sheet containing the source dataset and choose Insert – Chart.
Step 2: The Setup tab in the Chart editor window pops open, where we must select the plot type using the Chart type field dropdown options in the Setup tab. We shall click the Scorecard chart type to select it.
Step 3: Click the icon under the Data range field to open the window to update the required data range.
We shall choose the required range containing the order cost data from the source dataset in the first sheet and click OK.
Next, please scroll down the Chart editor pane to check the Aggregate option, as we must show the total of the order cost values in the chosen range.
Step 4: Click the Customize tab to open it. Here, we can click the Key Value section to set the required format settings for the displayed total order cost data.
Further, we shall set the Scale factor and update the required Suffix to make the displayed data more presentable.
Step 5: Click the Chart & axis titles section to expand it. Here, we can update the required chart title and its format, as depicted below.
Next, close the Chart editor pane.
Step 6: Resize the inserted Scorecard chart and place it in the required place in the sheet.
Next, select Insert – Chart.
Step 7: Once the Setup tab in the Chart editor pane opens, choose the Line chart, which is similar to Excel Line chart, in the Chart type field.
Next, click the icon under the Data range field to update the required data range in the Select a data range window, which opens on clicking the icon.
We need to show the monthly order cost values, with the months and the order costs in two non-adjacent columns. So, the cursor should be in the first field in the Select a data range window and select the range containing the months in the source dataset.
After that, click the Add another range option to update the range containing the order costs in the source dataset. Click OK.
We will see the following plot.
Next, we need the monthly order cost. So, we shall select the Aggregate option, which we can see by scrolling down the Chart editor window.
Step 8: Click the Customize tab to open it. Here, set the chart title, axis titles, and their formats using the options under the Chart & axis titles section.
Step 9: Click the Series section to expand it and choose the options under it to display the series in the plot according to our requirements.
Close the Chart editor window.
Step 10: With the inserted chart selected, resize it and place the graph in the required position in the sheet.
Next, select Insert – Chart.
Step 11: Choose the Table chart as the required chart type.
Next, update the required ranges in the Data range field.
We must show the product categories, and the corresponding quantities and order costs. So, we shall choose their ranges from the source dataset.
Click OK to view the Table chart.
Next, please scroll down the Chart editor pane to choose the Aggregate option since we must display the total quantities and order costs for each product category.
Step 12: Click the Customize tab and then the Table section to access the options under it.
Select the Ascending option to sort the chosen column, Product Category.
Resize the Table chart and place it in the desired location on the sheet.
Step 13: Choose View – Show – Unselect the Gridlines option to view the sheet without gridlines.
Finally, the dashboard with the required elements will appear as depicted below.
Now, any changes made to the source dataset will be reflected in the dashboard, which helps in interactively analyzing the data.
Examples
We shall see illustrations to understand the different ways of developing interactive dashboards in Google Sheets.
Example #1
The source dataset holds the date-wise sales-generated data of representatives at a firm and their customer ratings.
We must create a sales dashboard in Google Sheets that shows the total sales generated value. Also, the dashboard must include graphical representations of the sales generated and the highest customer rating data per sales representative.
Then, here is how to develop the required sales dashboard in Google Sheets.
Step 1: Add a new sheet to the existing sheet, and click the top-left corner of the workspace to choose all the cells in the sheet. Next, click the Fill color icon in the ribbon to set the required color in all the cells.
Step 2: Choose Insert – Chart.
Step 3: The Setup tab in the Chart editor window will open, where we must choose the Scorecard chart as the chart type.
Next, set the required data range in the Data range field by clicking the icon under the field and selecting the required data range or ranges from the source dataset.
Click OK.
Step 4: Scroll down the Chart editor window and select the Aggregate option, as we must display the total sales generated, which is the sum of all the values in the chosen range.
Step 5: Open the Customize tab in the Chart editor, where we must click the Key Value section to open it. Here, we can choose the key value settings to match our requirements.
Next, click the Chart & axis titles section to open it and use the options to set the required title and format settings.
Close the Chart editor. Next, resize the inserted Scorecard chart and place it in the required location in the sheet.
Step 6: Select Insert – Chart.
Once the Setup tab in the Chart editor window opens, set the Chart type field as the Column chart, which is similar Excel Column chart.
Next, update the Data range field using the icon under the field.
Since we must show the sales generated values for the representatives. So, we shall choose the corresponding column ranges from the source dataset to update in the Select a data range window.
Next, select the Aggregate option in the Setup tab to view the total sales generated by each representative graphically.
Step 7: Open the Customize tab to update the series color by using the options under the Series section.
Next, click the Chart & axis titles section to use the options under it and set the required chart title and axis titles.
Next, close the Chart editor pane, resize the chart and place it in the required location in the sheet.
Step 8: Choose Insert – Chart.
Step 9: We shall select the Column chart again and choose the data range from the source dataset to view the sales representatives and their highest customer ratings graphically.
Please note that the graph must show the highest customer rating of each representative. So, set the field against the Customer Rating series as Max.
Now, the plot will show the maximum rating each sales representative received.
Step 10: Click the Customize tab to open it. Here, we click the Key value section to use its options to set the required settings for the data series.
Please scroll down the window to choose the Data labels option and its formatting settings to view the data labels for the bars in the chart.
Step 11: Click the Chart & axis titles section options to set the required chart title and axis titles in the desired format.
Close the Chart editor window, resize and place the chart in the required location in the sheet.
Step 12: Select View – Show – Unselect the Gridlines option.
Thus, the required sales dashboard will appear as depicted below.
Example #2
The following dataset shows the date-wise total sales data for each product in the respective branch offices of a firm.
We shall build a sales dashboard for the given source dataset.
Step 1: Select the Insert tab – Pivot table.
The Create pivot table window will appear, where we shall choose the New sheet option to view the pivot table in a new sheet.
Step 2: A new sheet appears with the Pivot table editor pane open.
Drag the required columns under Search to place them under the specific Rows, Columns, and Values fields.
We now have a pivot table to show the branch offices and their total sales data.
Step 3: Select the range A1:B6 and use Ctrl + C shortcut to copy the pivot table. Next, select cell D1 and using Ctrl + V, paste the copied pivot table in the chosen cell.
Next, click the Edit icon to modify the copied pivot table.
Once the Pivot table editor pane opens, drag the columns under Search to the required pivot table fields.
Thus, we now have another pivot table showing the product-wise quantities at the branch offices. Next, we will place the pivot tables at the required positions in the Sheet. For that, we must select the pivot table range, and by using Ctrl + X, we can cut the pivot table. Next, choose the target cell, and by using Ctrl + V, we can paste the cut pivot table in the chosen cell.
Step 4: Select a cell in the first pivot table and choose Insert – Chart.
Google Sheets inserts a Pie chart by default, which is similar to Excel Pie chart.
Next, click the Customize tab in the Chart editor to set the desired chart title and its format using the options in the Chart & axis titles section.
Close the Chart editor pane. Next resize and place the chart in the desired location in the Sheet.
Step 5: Select cell I5 and enter the term “Trend”. Next, choose the range I4:I5 and select the Merge cells option in the ribbon – Merge all.
Next, center align the text using the Middle align option in the ribbon.
Next, choose the second pivot table and, using the Borders option, set the required border settings.
Step 6: Choose cell I6, enter the SPARKLINE function, and press Enter.
=SPARKLINE(E6:H6,{“chart type”,”line”})
Next, using the fill handle, feed the formula into the remaining cells I7:I9.
Select cells I6:I9 and set the required trend lines color using the Text color option in the ribbon.
So, now we have the trends for the second pivot table data using the Sparkline function.
Step 7: Choose a cell in the first pivot table and select the Data tab – Add a slicer option.
A slicer gets inserted with the Slicer pane open on the right of the workspace.
The Slicer pane shows the first pivot table range as the chosen data range in the first field.
Next, use the Column field dropdown list to choose the required column name to filter the dashboard data. For instance, we shall choose the Date column in this example.
Finally, close the Slicer pane and with the slicer selected, drag and place the slicer at the required location in Sheet.
Thus, our final dashboard appears as shown below.
Now, we can click the All dropdown option in the slicer to select or unselect the required dates. For example, we will unselect the first date value and click OK.
Thus, the data displayed in the dashboard changes according to the new slicer settings.
Important Things To Note
- Ensure the source dataset is accurate and in the required format before creating a dashboard in Google Sheets.
- Ensure to choose the right chart type in the Chart editor pane to represent the required data we aim to display graphically in a dashboard.
- Choose appropriate themes and color codes to ensure the dashboard appears professional and visually more appealing.
Frequently Asked Questions (FAQs)
You can create a KPI dashboard in Google Sheets using the following steps, explained with an illustration.
The source dataset shows a website’s monthly traffic, bounce rate, unique visitors, leads and customer data.
Here is how we can build a KPI dashboard using the source dataset in Google Sheets.
Step 1: Open a new sheet in the same file as the source dataset sheet. Choose cell A1, update the dashboard heading, and set the desired format setting using the options in the ribbon.
Step 2: Select Insert – Chart.
Step 3: The Chart editor pane will appear, where we must select the required chart in the Setup tab.
Next, using the icon under the Data range field, choose the required Month and Traffic data range from the source dataset.
Click OK to view the required Column chart to display the monthly traffic trend graphically.
Step 4: Click the Customization tab to set the chart and axis titles using the options under the Chart & axis titles section.
Next, click the Series option to expand the section and use the options in it to set the required settings for the data series in the chart area.
Next, close the Chart editor window. Now, with the chart remaining selected, we can resize and drag it to place it in the required location in the sheet.
Step 5: Select Insert – Chart.
Step 6: Set the chart type in the Setup tab in the Chart editor as the Line chart.
Next, click the icon under the Data range field to choose the required Month and Bounce Rate ranges from the source dataset in the Select a data range window.
Click OK to view the monthly bounce rate trend plot.
Finally, close the Chart editor pane. Next, as the second plot is chosen, we can resize it and place it in the required location in the sheet.
Thus, the final KPI dashboard appears as depicted below.
The limitations of building dynamic dashboard in Google Sheets are as follows:
• Data consolidation and merging data from different sources is challenging.
• Google Sheets has limited rows and columns. So, when the data to display in a dashboard increases over time, it becomes a challenge to incorporate the new data into the dashboard for real-time data visualization and review.
• Checking a Google Sheets dashboard on mobile is tedious, as we might have to scroll vertically and horizontally to view the dashboard data.
• Since we share the Google Sheets document, a dashboard can always have data integrity issues and security risks.
The benefits of creating dynamic dashboard in Google Sheets are as follows:
• Enhanced team-sharing capabilities
• Developing data visualizations is more straightforward.
• An inexpensive option
• Integration with third-party applications is possible.
Download Template
This article must be helpful to understand Dashboard In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Dashboard In Google Sheets. Here we explain how to create a dashboard in Google Sheets with examples and points to remember. You can learn more from the following articles –
Leave a Reply