How to Create a Dashboard in Google Sheets?

Google Sheets is an indispensable tool for data presentation and analysis. It facilitates the creation of dashboards that are both intuitive and insightful.

Developing a dashboard enables you to track progress, analyze trends, and make prompt decisions.

Assume you run a medium-sized business that provides web design and development services. You decide to create a dashboard in Google Sheets for the analysis of your clients’ data. Consequently, visualize trends, and compare performance across different clients. You utilize the dashboard to track customer leads, website visits, lead conversion rates, and customer satisfaction.

A dashboard in Google Sheets is beneficial in providing insights into your clients’ data. You can identify customer trends and understand customer behavior with ease. It also helps in decision-making based on the data you have collected. Thus, you can improve your offerings and customer experience.

This guide will show you how to create a dashboard in Google Sheets. Furthermore, you will learn how to use it for data analysis.

Table of Contents:

1. What is a Dashboard in Google Sheets?
2. Best Charts for Google Sheets Dashboard
3. How to Create Intuitive Charts for Dashboards in Google Sheets?
4. How to Create a Dashboard in Google Sheets?
5. Wrap Up

What is a Dashboard in Google Sheets?

A dashboard in Google Sheets is a graphic display of data in graphs, tables, and charts. It is an excellent data analysis and visualization tool.

You can use it to spot trends and insights in your data. Also, it makes it simple for you to share insights with your team or clientele.

A dashboard in Google Sheets facilitates the visualization and organization of data from multiple sources. You can use it to track customer leads, website visits, lead conversion rates, customer satisfaction, and other metrics.

Additionally, incorporating a Tornado graph in Google Sheets can enhance your dashboard by providing a clear visual representation of variable impacts and sensitivity analysis.

Best Charts for Google Sheets Dashboard

When creating a dashboard in Google Sheets, choosing the right chart to represent your data is crucial. Different charts have distinct advantages and disadvantages.

Here are some of the best charts for the Google Sheets dashboard:

• Radar Chart

A Radar Chart, also referred to as a Spider Chart, a multivariate analysis tool is a useful graphical representation of multivariate data. It allows you to compare several variables at once, offering a clear and easy-to-read visualization of the data.

The most potent characteristic of a Radar Chart is the ability to compare various data sets in a single visualization. You can use it, for instance, to compare the effectiveness of two teams. Additionally, you can compare the success of various items across several marketplaces.

A Radar Chart is also effective for conveying trends. Displaying the data in a circular format makes it easy to spot changes in the data over time.

Example

Here is the yearly sales data for different product categories.

Months	Garments	Electronics	Cosmetics
Jan	13147	14254	18388
Feb	9047	19986	18692
Mar	13493	19425	10639
Apr	10260	19216	12218
May	12127	16674	14936
Jun	11048	16908	10915
Jul	5435	16388	19854
Aug	12624	19638	11609
Sep	7768	15327	18657
Oct	9459	16176	15701
Nov	14201	18804	11120
Dec	13790	16587	12802

Below is the Radar Chart visualization of the data.

• Stacked Column Chart

One of Google Sheets’ most versatile chart types is the Stacked Column Chart. It makes it easy to compare the cumulative values of multiple data sets.

The Stacked Column Chart or a Clustered Stacked Bar graph creates a bar chart wherein individual bars display individual data sets. A series of discrete sub-bars then represent the bar values. This facilitates evaluating how the datasets’ values stack up against the whole.

Moreover, the Stacked Column Chart allows color coding, which aids in highlighting specific data values. This makes it simple to spot the critical values in the data.

Example

Below is quarterly traffic data for various social media platforms.

Quarters	Facebook	Direct	Instagram	Twitter	Google Ads
Q1	11381	20748	17861	27417	19635
Q2	23731	24678	16610	16810	25242
Q3	14378	10842	20971	13941	11412
Q4	21278	25272	22648	21622	28464

Here is the visualization of the traffic data.

• Sankey Chart

A Sankey Chart is ideal if you’re looking for a solution to make your dashboard interactive. This flowchart clarifies how a system or procedure comes into being by displaying the interdependencies between parts. You can use it to show the flow of anything from energy to money to materials.

You can use a Sankey Chart to monitor the development of a project. It can demonstrate how resources are distributed, tasks are accomplished, and progress is made.

Example

Let’s visualize this online sales data on a Sankey Chart.

Store	Category	Items	Brand	Units Sold
Online Store	Electronics	Mobile	Samsung	39
Online Store	Electronics	Tablet	Samsung	73
Online Store	Electronics	Laptop	Dell	156
Online Store	Garments	Jeans	Levi’s	46
Online Store	Garments	T-Shirt	H&M	104
Online Store	Garments	Jackets	Puma	41
Online Store	Furniture	Sofa	IKEA	73
Online Store	furniture	Chair	Kartell	46
Online Store	furniture	Desk	Stickley	43

Below is the Sankey Chart visualization of the data.

• Waterfall Chart

A Waterfall Chart displays the cumulative effect of positive and negative values in a sequence over time. It is also known as a Bridge Chart.

You can also use a Waterfall Chart to observe how modifications affect a starting value. Additionally, it is useful for monitoring cumulative change over time.

This chart can show items such as;

• The impact of profits and losses on financials.
• The impact of multiple expenses on a budget.
• The cumulative effect of inventory changes.
• Changes in customer satisfaction.

Example

Here is the company income data.

Stacks	Bridges	Amount
Sales	Mobiles	20025
Sales	Tablets	9746
Sales	PCs	7095
Services	Mobiles	3959
Services	Tablets	6650
Services	PCs	9077
Total Revenue	Mobiles	23984
Total Revenue	Tablets	16396
Total Revenue	PCs	16172
Marketing	Mobiles	-4630
Marketing	Tablets	-2360
Marketing	PCs	-2650
Transportation	Mobiles	-2150
Transportation	Tablets	-935
Transportation	PCs	-965
Salaries	Mobiles	-3360
Salaries	Tablets	-2340
Salaries	PCs	-1130
Wages	Mobiles	-3510
Wages	Tablets	-2390
Wages	PCs	-930
Operating Cost	Mobiles	-640
Operating Cost	Tablets	-3700
Operating Cost	PCs	-1240
Taxes	Mobiles	-4180
Taxes	Tablets	-1800
Taxes	PCs	-5460
Net Income	Mobiles	5514
Net Income	Tablets	2871
Net Income	PCs	3797

Below is the Waterfall Chart visualization of the income data.

• Sentiment Trend Chart

This chart allows you to visualize changes in sentiment over time.

You can use it for different purposes, such as:

• Tracking customer feedback.
• Analyzing marketing campaigns.
• Measuring employee engagement.

A Sentiment Trend Chart compares key data points. It utilizes a combination of line and bar graphs to show the differences. Green-colored bars signify growth, while red bars signify a decline in sentiment.

The line chart provides an overview of the overall pattern and trend of the variables under study. This makes it easy to identify changes or discrepancies.

Example

Assume this is the income and expense data of your company.

Months	Income	Expenses
January	40134	8919
February	21757	10038
March	26950	8595
April	48140	10793
May	46486	9584
June	40333	9042

Below is the Sentiment Trend Chart visualization of the data.

How to Create Intuitive Charts for Dashboards in Google Sheets?

It can be daunting to create intuitive dashboards in Google Sheets.

ChartExpo is the perfect platform for creating your Google Sheets dashboard without much technical know-how.

ChartExpo is a Google Sheets add-on that simplifies the creation of insightful charts and dashboards.

You can create a dashboard using a variety of charts and graphs available on ChartExpo. Its user-friendly interface enables you to create insightful charts even if you have no prior coding knowledge.

Furthermore, you can customize the charts by changing properties such as colors, labels, and fonts.

How to Install ChartExpo in Google Sheets?

1. To start using ChartExpo, you can download it directly from the Google Sheets program. To do this, select Extensions from the top toolbar.
2. A menu will appear. Click the Get add-ons option.
3. Search for ChartExpo, and click on Charts, Graphs & Visualizations by ChartExpo when it appears in the results.
4. Click the Install button. You will have to confirm your Google account and accept some permissions.

ChartExpo charts are available both in Google Sheets and Microsoft Excel. Please use the following CTA’s to install the tool of your choice and create beautiful visualizations in a few clicks in your favorite tool.

Example

Suppose you have online store sales data. Let’s map this on the Sankey Chart.

Store	Category	Items	Brand	Units Sold
Online Store	Electronics	Mobile	Samsung	39
Online Store	Electronics	Tablet	Samsung	73
Online Store	Electronics	Laptop	Dell	156
Online Store	Garments	Jeans	Levi’s	46
Online Store	Garments	T-Shirt	H&M	104
Online Store	Garments	Jackets	Puma	41
Online Store	Furniture	Sofa	IKEA	73
Online Store	furniture	Chair	Kartell	46
Online Store	furniture	Desk	Stickley	43

• To get started with ChartExpo, install ChartExpo in Google Sheets.
• Go to Extensions > Charts, Graphs & Visualizations by ChartExpo > Open.

• To access the charts library, click the Add new chart button.

• Once ChartExpo is loaded. Click on “Sankey Chart”.

• Put the data table in the sheet first. Then, choose the Sheet name from the drop-down in the ChartExpo section.
• Select the metric column, metric is the numeric column in your data sheet (in this example, it is the Unit Sold).
• Select the columns of the dimension, dimension is the categorical column in your datasheet that contains text information  (in this case, it is the Store, Category, Items, & Brand).
• Click the Create chart button to complete the process.

• The Sankey Chart will look as follows.

• If you want to have the title of chart, click on Edit Chart, as shown in the above image.
• To change the title of the chart, click on the pencil icon that is available very next to Chart Header.
• It will open the properties dialog. Under the Text section, you can add a heading in Line 1 and enable the Show option. Give the appropriate title of your chart and click on Apply button.

• To add colors, click the pencil symbol at the top of the nodes and extend the “Node” property. Pick the desired color, and hit the “Apply” button to save all modifications.
• Click the “Save” button to make the changes made to the chart permanent.

• Your final chart will look like the as below.

Insights

• Level 1 (Store) has a total of 621 orders.
• Out of the categories in Level 2, Electronics make up the largest portion with 43% (268 orders). On the other hand, Furniture has the least with 26% (162 orders).
• At Level 3 (Items), Laptops and T-shirts are the main contributors. They make up 25% (156 orders) and 17% (104 orders), respectively.
• At Level 4 (Brand), Dell and Samsung are the biggest sellers. They account for 25% (156 orders) and 18% (112 orders) of the total.

Improve Sankey Chart Insights by Creating a Dashboard in Google Sheets:

1. Open your Google Sheets Application.
2. Install ChartExpo Add-in for Google Sheets from Google Workspace Marketplace.
3. Select the Sankey Chart from the list of charts.
4. Fill in the necessary fields.
5. Click on the “Create Chart” button.
6. Customize your chart properties to add header, axis, legends, and other required information.
7. Export your chart and share it with your audience.

The following video will help you to create a Sankey Chart in Google Sheets.

How to Create a Dashboard in Google Sheets?

Follow these steps to create a dashboard in Google Sheets.

• Individually create charts in different sheets, as we have done above.
• Click the “Add chart to sheet” button to add the chart image to the respective sheet.

• Copy and paste the image into one sheet to create a dashboard in Google Sheets.
• This will be the appearance of the final dashboard.

FAQs

What is a dashboard in Google Sheets?

A dashboard in Google Sheets visualizes data from a spreadsheet or spreadsheets in an interactive format. It’s a wonderful tool for sifting through data and laying it out so anyone can understand. Dashboards are useful for spotting patterns, correlations, and outliers.

How do I create a budget dashboard in Google Sheets?

Use ChartExpo to create a dashboard in Google Sheets. This potent tool facilitates the creation of insightful charts for your dashboard. It has a wide variety of charts you can use, such as the Radar Chart and Sankey Chart.

How do I create a project management dashboard in Google Sheets?

You can create a project management dashboard in Google Sheets with ChartExpo. Here, you can use a Sankey Chart to monitor a project’s development. It can demonstrate how resources are distributed, tasks are accomplished, and progress is made.

What are the best charts for Google Sheets dashboard?

The best charts to use in Google Sheets dashboards include;

• Radar Chart.
• Stacked Column Chart.
• Sankey Chart.
• Waterfall Chart.
• Sentiment Trend Chart.

Wrap Up

You can build a dashboard in Google Sheets for the analysis of your business’s data. It can help you identify trends and insights in the data swiftly. Then make decisions based on these insights and improve your offerings and customer experience.

A dashboard is crucial for tracking critical business operation KPIs and metrics. This helps with improving the efficiency of your business operations. Additionally, it facilitates communication of insights to your team and clients.

How do you create a dashboard in Google Sheets?

This is where charts come into play.

Charts are potent data visualization tools. However, creating charts can be challenging.

ChartExpo comes to the rescue.

It’s a Google Sheets add-on that streamlines the process of making graphs and dashboards. You can create impressive data visualizations using a wide variety of charts available.

The best part?

All the charts you create will be available right away within your dashboard. This way, you can easily access insights and use them to your advantage.