Having a dynamic dashboard helps streamline your data management, analysis, and retrieval. 

It drives data-driven decision-making forward by refining large data sets into actionable insights. 

The question is, how do you make one with Google Sheets? 

Continue reading to learn everything you need to know about dynamic Google Sheets dashboards. 

Table of Contents

• What is a dynamic Google Sheets dashboard?
• Benefits of creating dynamic Google Sheets dashboards
• Important elements to know before building dynamic dashboards in Google Sheets
• Steps to create a dynamic Google Sheets dashboard
• Limitations of dynamic dashboards in Google Sheets
• Supercharge your dynamic dashboards with Polymer

What is a dynamic Google Sheets dashboard?

A dynamic dashboard in Google Sheets automatically updates whenever you add changes. 

Suppose you have a dashboard that visualizes a data table into a dynamic graph. Whenever you add or change something on the data table, Google Sheets automatically updates the data visualization on the graph without further action. 

Dynamic Google Sheets dashboards may also use functions and formulas to perform advanced data processes. 

For example, your dashboard sheet may include a simple formula that calculates the sum of a data range in another sheet. This allows you to create a clean and automated Google Sheets dashboard while keeping raw data hidden. 

Benefits of creating dynamic Google Sheets dashboards

Here are some of the benefits of creating dynamic dashboards with Google Sheets: 

• Generate data visualizations easily. Google Sheets features easy-to-use data visualization tools to help create dynamic graphs—no formulas and functions necessary. Just focus on the raw data and let Google Sheets do the rest. 
• Built-in data validation method. Google Sheets lets you create data validation rules to prevent data entry errors. Data validation works by restricting data inputs based on a list of allowed values. 
• Integrations with third-party tools. With the right integrations, Google Sheets can automatically collect data from third-party sources and update your dynamic dashboard. Google Sheets is widely supported by countless data management, marketing, and accounting tools for businesses. 
• Team sharing options. Companies can use Google Sheets’ sharing features to collaborate on dynamic dashboards in real-time. Owners can also assign user permissions to control who can view, edit, or comment on dashboard data. 
• Save money. Google Sheets is a free tool that only requires an active Google account to use. This is perfect for startups and small businesses that need a data dashboard without the budget for an enterprise data management solution. 

Important elements to know before building dynamic dashboards in Google Sheets

Before you create your first dynamic dashboard on Google Sheets, here are six important elements you should know about:

1. Pivot tables

A pivot table summarizes large data sets to help you organize information, recognize patterns, and form data-driven decisions. It’s an important element to master, especially if you want to learn how to create a sales dashboard in Google Sheets.

For example, your Google Sheets dynamic dashboard may contain a pivot table that identifies your top-selling agents. Or, it can determine your most lucrative markets based on the average value of sales and total sales volume. 

Pivot tables are a must-have for any dynamic dashboard in Google Sheets. It provides a detailed look at huge amounts of data and highlights key insights. 

2. Conditional formatting

Conditional formatting automatically changes the appearance of cells based on their values. It depends on Boolean rules (true or false) or gradient rules (based on each cell’s value). 

With a Boolean rule, conditional formatting is applied based on specific criteria. This may include pre-defined condition types or custom formulas. 

Gradient rules, on the other hand, change cells to a spectrum of colors based on a corresponding range of values. It uses defined interpolation points, including a starting (minimum point), middle point, and final (maximum point). 

While it doesn’t affect data values, conditional formatting makes data-rich dashboards more readable.  

For instance, conditional formatting can be used to highlight products in a certain category. Simply create a Boolean conditional rule that changes the row that contains specific category keywords.

On Google Sheets, select the rows you want to use conditional formatting on. Click ‘Format’ and select ‘Conditional formatting.’ 

IMAGE

Image Source: Sheets.Google.com

Use the rule editor panel on the right to configure your condition. 

In this case, you need to change the color of an entire row if a category keyword is found in it—like “Accessories.” 

Select ‘Custom formula is’ under “Format rules” and enter the following:

=$C2=”Accessories”

In this formula, cell C2 is referenced to indicate the first row wherein the condition is tested. We added the dollar sign ($) to make it an absolute reference, which designates the cell as a constant variable in your formula. 

Under “Formatting style,” you can choose a cell fill color or text formatting styles like bold, italic, or underline. 

You should immediately see how your conditional formatting works in your spreadsheet.

3. Charts

Charts pertain to data visualization tools baked into Google Sheets. They use a specified data range within the spreadsheet, including data from another sheet, to render different chart types, like:

• Pie chart
• Line chart
• Area chart 
• Column graph
• Bar graph
• Map chart
• Scatter plot
• Gauge chart
• Radar chart

Using charts is the easiest way to construct a dynamic dashboard in Google Sheets. 

The visual interface lets you define data ranges, set rules, add labels, and customize the chart’s appearance without writing a single formula. 

4. LOOKUP formulas

The LOOKUP is a data function that searches for a “key” and pulls the value of a matching cell within the specified search row or column. It is used to retrieve values from data ranges, which is essential to building dynamic dashboards in Google Sheets. 

A widely-used variation of the LOOKUP function is VLOOKUP, which only scans data from left to right. The VLOOKUP is also only useful on vertically-arranged data sets, whereas LOOKUP has more flexibility and can scan data in any direction.

However, the LOOKUP function requires data to be strictly sorted in ascending order. As such, you need to learn how to organize data in Google Sheets if you wish to use LOOKUP queries in your dashboard. 

5. SPARKLINE

The SPARKLINE function creates a small chart that can be contained in one cell.

SPARKLINE uses line charts by default. But it can also use different chart types and customization options. 

You can use SPARKLINE charts to give viewers a glimpse of data trends—perfect if you’re learning how to create a KPI dashboard in Google Sheets.

They’re also good alternatives to full-sized data visualizations if you want to create a minimal, easy-to-read dashboard. 

6. Data validation

Data validation lets you create a pre-defined set of acceptable inputs to a data set. You can also This prevents invalid data values, which lead to errors or inaccurate calculations in your dynamic dashboard.  

Steps to create a dynamic Google Sheets dashboard

Now that you understand the components that go into Google Sheets dashboards, follow these steps to build one:

Step 1: Load or import your data set

Add your data set to Google Sheets through manual entry, copy-pasting, or data imports from third-party platforms. 

To keep your dynamic dashboard clean and presentable, load your data set into a designated sheet. Be sure to include the headers to simplify data range references later. 

Data sheets serve several uses in a Google Sheets dynamic dashboard. Apart from providing data ranges for formulas and charts, it can also define categories for data validation. 

Read our guide on How to Import Data into Google Sheets for more details. 

Step 2: Create your tables and charts

With your data sheet ready, it’s time to create your dynamic data tables and visualizations. You may also need to use LOOKUP formulas to pull values from specific data ranges.

Here are quick walkthroughs on how to build pivot tables and charts—as well as how to use the SPARKLINE and LOOKUP functions. 

How to create pivot tables

To create a pivot table, click ‘Insert’ and select ‘Pivot table.’ 

On “Create pivot table,” specify the data range you want to use and choose whether to insert your pivot table into a new or existing sheet.

In the pivot editor table, feel free to add rows, columns, values, and filters based on your selected data range. 

For your reference, check out this pivot table template from the Google Workspace Learning Center.  

How to create charts

To create a chart, click ‘Insert’ and select ‘Chart.’ 

Use the chart editor to the left to configure your chart. Choose a chart type, select your data range, tweak addition options, and customize its appearance. 

Feel free to reposition and resize your chart as you see fit. 

How to use the SPARKLINE function

The SPARKLINE function follows the syntax:

=SPARKLINE(data, [options])

For example, here’s a simple SPARKLINE chart using the prices of products in the range. 

The output is a simple line chart that reflects changes in values over the range. 

You can customize the appearance of SPARKLINE charts using optional parameters. 

Suppose you need to create a column graph instead of a line chart. Simply add the option charttype using the following syntax:

=SPARKLINE(B10:B15, {“charttype”,”column”})

To change the chart’s color, assign a new font color from the main toolbar. 

When creating tables and charts, you might need to use LOOKUP formulas.

How to use the LOOKUP function

The basic LOOKUP syntax is as follows:

=LOOKUP(search_key,search_column,result_column)

In the function above, search_key represents the value attached to the cell you’re looking for. 

Next, search_column should be the range where the search key can be found. Lastly, result_column points to the range containing the value to be returned. 

For example, the table below includes a list of blog posts, their writers, and their content pillars. 

Let’s say we need to create a LOOKUP function that identifies the writer who created “How to Write Blog Posts.” 

For this, we need to type “=LOOKUP(A11,A2:A6,B2:B6)” into the cell or formula bar. 

A2 is the search key that corresponds to the value we’re looking for in another column (How to Write Blog Posts). 

A2:A6 is the search column where the search key can be found. Lastly, B2:B6 is the result column which the output value will be taken from. 

Here’s the return value of the LOOKUP function demonstrated above:

Just remember that the LOOKUP function only works reliably on organized data sets, which are sorted from least to greatest. 

If your data is not sorted, use the VLOOKUP function instead. 

How to use the VLOOKUP function

The syntax of the VLOOKUP function is as follows: 

=VLOOKUP(search_key,range,index,[is_sorted])

Similar to the LOOKUP function, search_key in VLOOKUP also reflects the value that corresponds to the data you want to pull. 

The range parameter specifies the data range where the search_key and return value can be found. Index, on the other hand, identifies the specific column with the return value. 

Finally, is_sorted is an optional value that can be set to “true” or “false.”

If this is set to “true,” Google Sheets will return the value for the search key’s approximate match, which is likely to be incorrect especially if your data isn’t organized. As such, it’s recommended to set this to “false,” which pulls data from your search key’s exact match. 

In the example below, we used VLOOKUP to check the price of a MacBook Pro. 

Here’s a quick rundown of the parameters we used: 

• Search key: MacBook Pro (A16)
• Range: B2:D6 (the data range we’ll check)
• Index: 3 (since we want values under the third column of the range)
• Is sorted: False (since we want the exact match)

 The result shows the correct return value associated with the search key. 

Pulling data from your data sheet

Since you keep your data ranges in a separate sheet, it’s important to learn how to refer to those ranges when building your dashboard. 

The key is to prefix references with your data sheet’s name followed by an exclamation point. 

For example, if you want to use the VLOOKUP function and the search_key is found in “Data Sheet,” use the following formula:

=VLOOKUP(‘Data Sheet’!A2,’Data Sheet’!A:E,5,false)

Moving over to your data sheet, you’ll see that the function looks for “Alexandra” (A2) as the search key while scanning the range A:E. Since the index value is 5, the function pulls the return value from the fifth column (Major).

Step 4: Validate your data

A dynamic dashboard without data validation is prone to errors. Luckily, setting data validation rules in Google Sheets is relatively easy.

To use data validation in Google Sheets, select the cells you want to constrain, click ‘Data,’ and select ‘Data validation.’ 

On the “Data validation rules” panel, click ‘Add rule.’ 

Under the “Criteria” drop-down menu, choose the validation rules to use in evaluating the allowed values. 

A simple method is to select ‘Dropdown’ and manually enter the allowed values one by one. You can also define a range of acceptable numbers, dates, required keywords, input types (email, URL, etc.), and more. 

Let’s say you want to constrain data inputs within a scale of 1-10. 

Under “Criteria,” select ‘Is between’ and enter “1” and “10” in the “Value or formula” fields below. 

At the bottom, choose whether to show a warning or reject the input if the user enters an invalid value. This includes numbers that don’t fall between 1-10. 

If you choose to reject the input, Google Sheets will display a notification and delete the value if it’s invalid. If you chose to show a warning, the value is retained—but an “invalid” warning will appear.

Step 5: Consolidate your charts in one sheet to build your dashboard

With your tables, charts, and functions ready, it’s time to build your dynamic dashboard. 

Learning the basics of how to create a Google Sheets dashboard is a good start. This time, you just need more emphasis on presentation and organization.

Remember, your dashboard should only display presentable, readable data. This includes your data visualizations and LOOKUP formulas. 

For ideas and inspiration, check out these 10 free Google Sheets dashboard templates.

Since your data ranges are tucked away in your data sheet, there’s no reason to edit your dashboard after building it. As such, it’s a good idea to protect the entire dashboard sheet from further changes. 

To do this, right-click your dashboard sheet and select ‘Protect sheet.’ Click ‘Set permissions’ on the right panel to continue.

One way to protect your dashboard is to restrict editing permissions only to you. Alternatively, you may choose to display a warning whenever someone tries to edit the sheet. 

Step 6: Publish your dynamic dashboard

Finally, it’s time to publish your dynamic dashboard.  

Simply use the built-in sharing feature to invite your team, copy your dashboard’s URL, and set access permissions. 

From your spreadsheet, click ‘Share’ in the upper-right corner. 

Enter the email addresses of the users you want to invite to your dashboard. 

Alternatively, expand the ‘Restricted’ drop-down menu and select ‘Anyone with link.’ Click ‘Viewer’ to specify user access as viewers, commenters, or editors, then click ‘Copy link.’ 

With the link in your clipboard, share it with your team or client in any way you prefer (email, social media, etc.). 

Supercharge your dynamic dashboards with Polymer

The Google Sheets dashboard certainly has its merits. However, it also has its severe downsides.

For example, Google Sheets dashboards have limited advanced analysis capabilities; the visualizations look old and clunky, etc.

With Polymer, you can create stunning graphs, charts, reports, and interactive visualizations out of your Google Sheets data. Our advanced BI features allow you to automatically generate data tags, get real-time updates, and create interactive pivot tables.

Also, Polymer integrates with a host of apps and services, including Facebook, Google Drive, Zendesk, Jira, and Google Ads.

Discover what Polymer can do for you by starting a free trial. 

‍