How awesome does it feel to have your data transformed into visuals that can inform your decision-making? Google sheets is a tool that can be used to refine your data and turn it into a dashboard.

Want to transform the raw data you have into an insightful dashboard in no time, then this tutorial is the right guide for you.

Introduction

Google sheets is a web-based application that is used to organize, create and edit spreadsheets. It helps provide insights from real-time data. By real-time data, we mean data that is delivered as it is gathered. Google sheets can be used for data visualization, cloud collaboration, google product integration, and also spreadsheet formatting.

With google sheets, data can be turned into insights immediately after it is collected. Google sheets allow many users to interact with the spreadsheet at the same time while it keeps track of the changes made by each user.

Is Google sheets the same as Microsoft excel?

Google sheets and Microsoft Excel are both spreadsheets applications that have almost the same interface and are both used for the calculation and storage of databases.

Although both platforms have some similarities, they also have their peculiarities. Here are the differences between both platforms:

1. Google sheets is offered by Google which makes it free and Microsoft Excel is offered by Microsoft Corporation which requires a subscription payment
2. Google sheets allows many users to interact with the spreadsheet at a go while Microsoft Excel only allows a user to interact with the spreadsheet at a time unless you are using the Microsoft 365 platform
3. Google sheets enables users from different locations to access the spreadsheet data while data on Microsoft excel can only be accessed by the person that has the device on which the spreadsheet is saved unless you are using the Microsoft 365 platform.
4. Google sheets allows for collaboration while Microsoft excel does not.
5. Microsoft Excel has more in-built features for data visualization and data analysis while google sheets has limited features for data visualization and data analysis.
6. Google sheets can process real-time data while Microsoft excel can not

Requirements

The tool and material necessary for this tutorial include:

1. A database (Here is the data)
2. Google sheets app (all you need do is to open Google sheets in your browser)
3. A Gmail account

Steps to create a dashboard

Before you create a dashboard, you need to take note of these:

1. The kind of data you want to visualize,
2. The problems you want to solve with your dashboard, and
3. The audience that will view your dashboard

In this article, we are going to help Tiara Cookies make better decisions by creating a dashboard using her sales data. Tiara Cookies is an online cookies store.

Now, let’s set it into action.

Step 1: Prepare the data

Open the google sheet containing the data. Study the data to understand what each column represents.

The image above shows a portion of the data.

Step 2: Prepare the dashboard area

1. Create a new sheet and rename it to Dashboard
2. On this new sheet click on the View tab
3. Click on Show to unselect Gridlines
4. Click on Format
5. Click on Theme to select any theme of your choice
6. Give the dashboard a title, (Tiara Cookies Sales Dashboard)

Step 3: Fill the dashboard with visuals

Create the necessary visuals that will populate the dashboard.

1. A pie chart that will summarize the number of cookies bought by both male and female
   1. Hold the Ctrl key to select the columns (Gender and Amount of cookies bought) that will be included in the chart.
   2. Click on the Insert tab and select Chart
   3. A visual will appear having three dots at the upper right-hand side
   4. Click on the three dots to select Edit
   5. A chart editor appears on the right-hand side
   6. Check the aggregate box
   7. Click on Chart type to choose Pie chart from the dropdown list
   8. Click on Customize
   9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Amount of cookies bought by gender),
   10. You can as well customize the chart more to your taste by changing the colors, font types, font sizes, and so on
   11. Copy the chart and paste it onto the Dashboard sheet

ii. A scorecard showing the total amounts of cookies sold for the year

1. Select the Amount of cookies bought column
2. Click on the Insert tab and select Chart
3. A visual will appear having three dots at the upper right-hand side
4. Click on the three dots to select Edit
5. A chart editor appears on the right-hand side
6. Check the aggregate box
7. Choose the Scorecard chart from the Chart type
8. Click on Customize
9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Total amount of cookies sold for the year),
10. You can as well customize the visual better by changing the colors, font types, font sizes, and so on
11. Copy the chart and paste it onto the Dashboard sheet

iii. A column chart showing the cookies' popularity

1. Hold the Ctrl key to select the columns (Cookies sold and Amount of cookies bought) that will be included in the chart.
2. Click on the Insert tab and select Chart
3. A visual will appear having three dots at the upper right-hand side
4. Click on the three dots to select Edit
5. A chart editor appears on the right-hand side
6. Check the aggregate box
7. Click on Chart type to choose Column chart from the dropdown list
8. Click on Customize
9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Popularity of cookies sold),
10. You can as well customize the visual better by changing the colors, font types, font sizes, and so on
11. Copy the chart and paste it onto the Dashboard sheet

iv. A column chart displaying how many cookies bought by age group

1. Hold the Ctrl key to select the columns (Age group and Amount of cookies bought) that will be included in the chart.
2. Click on the Insert tab and select Chart
3. A visual will appear having three dots at the upper right-hand side
4. Click on the three dots to select Edit
5. A chart editor appears on the right-hand side
6. Check the aggregate box
7. Click on Chart type to choose Column chart from the dropdown list
8. Click on Customize
9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Amount of cookies bought by age group),
10. You can as well customize the visual better by changing the colors, font types, font sizes, and so on
11. Copy the chart and paste it onto the Dashboard sheet

v. A line chart showing the amount of cookies bought across the year

1. Hold the Ctrl key to select the columns (Month and Amount of cookies bought) that will be included in the chart.
2. Click on the Insert tab and select Chart
3. A visual will appear having three dots at the upper right-hand side
4. Click on the three dots to select Edit
5. A chart editor appears on the right-hand side
6. Check the aggregate box
7. Click on Chart type to choose Line chart from the dropdown list
8. Click on Customize
9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Total amount of cookies bought across the year),
10. You can as well customize the visual better by changing the colors, font types, font sizes and so on
11. Copy the chart and paste it onto the Dashboard sheet

vi. A donut chart displaying the amount of cookies bought in each region

1. Hold the Ctrl key to select the columns (Region and Amount of cookies bought) that will be included in the chart.
2. Click on the Insert tab and select Chart
3. A visual will appear having three dots at the upper right-hand side
4. Click on the three dots to select Edit
5. A chart editor appears on the right-hand side
6. Check the aggregate box
7. Click on Chart type to choose Donut chart from the dropdown list
8. Click on Customize
9. A list of options containing Chart and axis titles appears, click on this to input the title of the chart (Total amount of cookies bought in each region),
10. You can as well customize the visual better by changing the colors, font types, font sizes and so on
11. Copy the chart and paste it onto the Dashboard sheet

Step 4: Arrange the charts in the dashboard

Creatively arrange the chart tiles to fit in the dashboard.

Insights gathered from the data

From the visualizations, one can make the following remarks:

1. Macadamia has the highest popularity among the cookies with a total of 65.
2. Females (64.4%) patronize the cookie store more than males (35.6%)
3. July has the highest record for cookies sales with a total of 41
4. Customers between the “10-19” age group frequent the store more than others
5. The South region (29.9%) records the highest sales, followed by the East (28.2%), the West (23.7%), and the North (18.1%)
6. The total amount of cookies sold for the year is 177

We do hope that you found this blog exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!