Click here to go back to the main course page, so that you can see the other dashboards that you can build, as well as to get your dashboard formulas cheat sheet.
In this lesson, I am going to show you how to build a customer survey dashboard in Google Sheets (Video is below). In this particular dashboard, our raw data will be responses that were generated with Google Forms. Google Forms responses are great to use for building dashboards because the dashboard will update automatically as new responses come into the response sheet.
In this dashboard we will focus on using the COUNTIF function in several different ways to analyze data, which is very important for being able to build dashboard and work with data professionally. The data visualizations in this dashboard are kept simple.
After downloading the raw data that is linked below, scroll down and begin the video to start building the customer survey dashboard. Timestamps for each chapter are also shown below, so that you can easily find your place if you need to take a break.
Get the ultimate Google Sheets cheat sheet
Download the raw data
Download these CSV (Spreadsheet) files, so that they will be readily available for you to use / import when you are building the dashboards.
Google Submission Form (Not needed / For reference only)
View a copy of the completed dashboard
A finished dashboard example is linked below, for your reference. Use this template to help you view the formulas and formatting, while building your version as you follow along with the course.
To make your own copy of the dashboard examples, do the following:
- Click "File" on the top toolbar while the sheet is open
- Click "Make a copy"
- Rename the sheet if you want
- Click "Okay"
Your completed dashboard will look like the image below, after you complete the final "Revamp" lesson.
How to build the customer survey dashboard
Follow along with the video below to build the customer survey dashboard!
Timestamps for each chapter / lesson:
0:00:00 1 Survey Dashboard Overview
0:02:10 2 Google Forms Intro
0:07:01 3 Optional How to create Google Forms
0:08:27 4 Importing the Customer Survey Data
0:13:09 5 Create and format new tabs
0:16:01 6 Using the UNIQUE formula
0:18:49 7 SORT function and nested functions
0:19:38 8 Copy formula to right with autofill
0:21:11 9 Add columns for totals
0:26:01 10 Use COUNTIF to calculate totals
0:29:42 11 Copy the COUNTIF formula down the column
0:30:40 12 Copy COUNTIF formula to the right
0:35:48 13 Simple Rating COUNTIF plus COUNTIF (Multiple Criteria)
0:44:21 14 Product type breakdown: COUNTIF Wildcard
0:50:48 15 Using MIN and MAX to find first and last response
0:54:45 16 NOTE about ending Enter and format headers for new formulas
0:58:38 17 Count number of responses: Count if not blank
1:01:06 18 Count number of responses after date
1:02:50 19 Count responses between dates: COUNTIFS Multiple Criteria
1:09:31 20 Change the tab color
1:10:55 21 Dashboard tab background formatting
1:13:21 22 Insert a pie chart
1:16:02 23 Chart Creation: Connecting Data- Method #2
1:17:56 24 Customizing Charts
1:21:53 25 Optional: Select custom colors
1:23:33 26 Create (Duplicate) more pie charts
1:29:23 27 Add a logo, and a note on simplicity
1:32:04 28 Hiding tabs, protecting, and sharing
1:37:46 29 Survey Dashboard Revamp
1:46:07 30 Optional: Copying and pasting values only (Survey Example)
