Google Sheets is not just a simple spreadsheet application. It can be used to create custom interactive dashboards to analyze personal and business data in the best possible way. With loads of advanced features, one can create a dynamic data dashboard in Google Sheets that changes automatically as data is changed over a period of time. In this tutorial, we'll make one through a step-by-step method and will show how you can use the same process for your use case. Feel free to add more and customize it to meet your requirements. Let's get started and master creating custom dashboards in Google Sheets.
📷 Credit: DALL·E 3
In this guide, we'll see the data source as either external or manually entered to accommodate both scenarios. The latter case is more reliable as the external source is dependent on the Internet.
If you are going to use custom dashboards for your critical business data, make sure you make a copy of the same in a separate Google account to avoid any mishap. So, let's create some interactive dashboards.
Step 1: Plan Your Dashboard
Quite obviously, we first need to plan and outline the custom dashboard we're looking for. For that, the following answers and pointers need to be addressed.
Define your objectives: The goals of your dashboard need to be defined at this stage. You have to figure out the metrics you want to track and analyze.
Identify your data sources: Decide if the data used in the dashboard will be imported manually or pulled from an external Google Sheets file.
Plan your layout: Last but not least, plan the layout of your dashboard. Create a rough sketch of how data tables, charts, and filters will be arranged in the sheet.
Step 2: Prepare Your Data
It's time to prepare the dataset you'll be using for the custom dashboard. A properly organized and well-formatted dataset is easy to work with and speeds up the entire process. Let's see all the important steps related to data preparation.
1. Organize Data: First of all, make sure you are arranging the data in table format with rows and columns. Create appropriate headers for each column. Here's a sample dataset in the form of a table we'll be using in this tutorial.
📷 Raw and unformatted dataset in a Google Sheets file
This is an example of manually entering the data in a Google Sheets file. But, what if the data source is another Google Sheets file?
2. Import Data: If the data source is external, one can use the IMPORTDATA, IMPORTRANGE, or IMPORTXML functions to quickly fetch a large dataset—automatically.
In this example, we're importing data from a different Google Sheets file. The function's first argument is the URL or path to the file and the second argument is the cell range of the dataset.
3. Clean Your Data: If your data requires unique and distinct entries without any duplicacy, you must clean your data. To do that, select the data range to work upon, and select the Data → Data cleanup → Remove duplicates option from the main menu.
4. Validate Data: After cleaning the data, you can add validation checks on the dataset to ensure incorrect entries do not seep in corrupting it. In our example, data validation checks include cells with only number entries, cells with only month names, and cells with four directions names. Here's how to add these validation checks.
📷 Number range validation check in Google Sheets
For the Sales and Target columns, select the entire data range and go to the Data → Data validation option. Thereafter, click the Add rule button to create a new validation check.
For Criteria, select the Is between option from the dropdown menu. In our example, we'll enforce a number range between 1 and 10000. Add both the numbers as shown in the image above.
Thereafter, create a custom help message and select the Reject the input option to ensure no wrong entries are accepted in these cells.
Step 3: Add Interactive Elements
Now it's time to add some interactive and dynamic elements to our custom dashboard. For example, dropdown menus for data input, slicers to filter data easily, and dynamic formulas to calculate on the fly against the ever-changing data.
Create Dropdown Menus
Here's how you can create data entry dropdown menus to avoid incorrect input.
Start with selecting the cells where you want the dropdown menu. In our example, I'm adding dropdowns to the Region column.
After selection, go to the Data → Data validation → Add rule option.
📷 Manually enter all the valid options for the dropdown menu
For the Criteria option, select the Dropdown entry and manually enter all the valid options. For example, in our case, the entries will be, viz., East, West, North, and South.
And here's how the dropdown menus look as shown in the image below. It'll ensure incorrect entries are not accepted in the respective cells in the data table.
📷 Add data validation dropdowns for cells
In this example, I've created data validation dropdown menus for the Month column as well.
Use Slicers
In Google Sheets, slicers are interactive visual elements one can use to quickly filter data based on different conditions. Let's add some to our sample data.
Start with selecting the data range to work upon and go to the Data → Add a slicer option.
📷 Slicers in Google Sheets help you filter data easily
Select the data range or column on which the slicer will work. Customize its appearance as per your dashboard's theme.
Add Searchable Filters
Although slicers are a great option to filter data, searchable filters are more user-friendly, if created correctly. You can create a search box and use it just like a search engine. Here's how to do it.
📷 Searchable filters work with dynamic data and are easy to use
To create a searchable filter, create a text box for the search. Customize its appearance as per your preferences. Make sure there is enough space below it to accommodate the entire data set.
In the top and leftmost column right below the search box (G3 in our example shown above), add the filter formula. Here's what I used in this example to filter data based on sales figures.
=FILTER(A2:D4,ISNUMBER(SEARCH(H2,C2:C4)))
We start with the =FILTER() function. Its first argument is the entire data source range. You can select this range with the help of the mouse.
In the second argument, use the ISNUMBER() function. Its first argument SEARCH() function.
The search function's first argument is the search text box cell address, and the second argument contains the entire range of the data of the column we're targeting. In this example, we select the entire data in the Sales column.
Step 4: Create Advanced Visualizations
Next, we'll add some advanced data visualizations to our dashboard. It'll add more depth and information making it easy to analyze and study the trends and shortcomings from a business perspective.
Custom Combination Charts
First, we'll add a 'Combo Chart' to our dashboard. Here's how to do it. First of all, select the entire data set including the headers as well. Thereafter, go to the Insert → Chart option.
📷 Combo charts give you multiple data visualizations in one place
Choose the Combo Chart type in the editor and make the necessary customizations as per your needs. Finally, drag and place the chart at the location you want it to be.
Interactive KPI Data
Key performance indicators are the best way to quickly analyze critical business information. And that's exactly what you must add to your data. In our example, I've created an additional Status column.
📷 Use KPIs for better data visualization
Select the call right below the status header and add the following formula to it. Make changes in this formula according to your data.
Note the cell data range I've used in this formula. If sales exceed the target, 'On Track' is filled in the cell otherwise you get the 'Behind text.
Next, we'll add conditional formatting to these cells. If sales exceed the target, the respective status cell will be green, else it'll be red. To do so, first of all, select the entire status column data range.
Right-click and choose the Conditional formatting option from the context menu. In the Format rules attribute, choose the Text contains option from the dropdown menu and add the respective text value creating two such rules for both 'On Track' and 'Behind' text entries. Select the background and text colors for each rule.
Trend Analysis with Sparklines
Generally, charts occupy a lot of space in the spreadsheet. If you have tight space and want to show trends in a limited space. Use the SPARKLINE to display such trends within a single cell.
📷 Use sparkline charts to show trends within a single cell
Here, in our example, I'm using it for the Sales column data.
=SPARKLINE(C2:C4, {"charttype", "bar"})
As you can see, its usage is dead simple as you can quickly display up to 4 different types of trend charts in a small space. You can use it for selective columns in your custom dashboard.
Step 5: Use Pivot Tables for Advanced Insights
Now here we have a small dataset, but, if you are working on a large dataset, getting advanced insights out of it and that too in a limited space can be challenging.
And, that's where pivot tables come into the picture. They are use to use and enables you to create advanced data indight tables in no time. Let's create one for our sample data.
📷 Pivot table for month-wise total sales
Start by selecting one of the cells in the dataset. Then go to the Insert → Pivot table option. You can either create this pivot table in the same sheet or use a new sheet in the same file.
If the pivot is going to have a good number of columns, I'll recommend using a separate sheet for the same. So, choose the correct option accordingly.
I've selected Region for the rows and Months for the columns. Also, Sales have been selected for the values on which the calculation will be done. Custom filters can also be added to the table for complex and advanced data visualizations.
Step 6: Add Automation and Alerts
Another element that can be added to your custom dashboard is an automated alert for a specific condition. For example, you can use Google Apps Script to automatically send email alerts for low sales. Here's a sample script.
// Script to send email alerts for low sales:
function sendAlert() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
var range = sheet.getRange("C2:C4");
var data = range.getValues();
data.forEach(function(row, index) {
if (row[0] < 4000) {
MailApp.sendEmail("your@email.com", "Low Sales Alert", "Region: " + sheet.getRange(index -1, 1).getValue());
}
});
}
In this example, I'm fetching the sales values from the 3rd column and analyzing if any value is less than 4000 or not. If so, an email is sent alerting you about it. Feel free to enhance and customize it.
Conclusion
Interactive dashboards in Google Sheets are powerful tools for visualizing and analyzing data. By adding advanced features, automation, and dynamic elements, you can build dashboards that are not only functional but also visually striking.
Experiment with layouts, charts, and filters to make your dashboard truly impactful.
Image Credit: All screenshots and image alterations by Rajeev Edmonds/FreshTechTips
