What is A Gauge Chart in Google Sheets?
A gauge chart in Google Sheets is a graph that showcases a data point, comparing it to a predefined range of values. This visual representation is usually used to show progress toward a set goal. It can also be used to compare the performance against a target. Its appearance is like a speedometer. Generally, we can use a gauge chart to visualize the following:
- Performance of a Key Performance Indicator (KPI)
- Project completion
- Progress toward a target in sales
In the simple example below, we use a gauge chart to show numeric values within a range. It is used to measure progress toward a goal. The needle or pointer shows the current value. The minimum value is 0 and the maximum is 100 as it is a percentage.
Key Takeaways
- Gauge charts are used to provide a visual representation of a KPI or metric’s current value. They help check whether it is within an acceptable range.
- The gauge chart is circular and has a range starting from a minimum value and ending with a maximum value.
- A needle indicates the current value. The range is divided into parts, which can be shown with different colors to visually show where our actual value stands within the target.
- The Max, Min, and Target values can be tweaked to make the chart clearer to the user.
- The Gauge chart in Google Sheets measures web traffic, sales performance, employee performance, and project progress.
How to Create Gauge Chart in Google Sheets?
A gauge chart in Google Sheets is very helpful for visualizing how close a project is to completion or how close we are to a target. The following step-by-step guide shows how to create a gauge chart in Google Sheets.
Step 1: First, we enter the following data for the different departments to study their performance in an organization.
Step 2: Let’s create the gauge chart. To create a gauge chart for the different departments, highlight the cell range A1:B5. Then, click the Insert tab in the main menu and then click on Chart.
Step 3: In the Chart editor on the right side, click the dropdown menu for the Chart type. Scroll down and select the Gauge chart. The gauge chart will be created.
Step 4: You can modify several chart features by going to the Customize tab in the Chart editor. Here, you can change the features under Chart Style, Chart Axis, and titles.
Examples
As explained earlier, Gauge charts visualize the present value of a KPI to check whether it is within an acceptable range. Thus, you can quickly visualize whether the value of a KPI or a metric is within the acceptable range. Here, in this section, we will learn how to make different gauge charts to visualize progress. The Gauge chart in Google Sheets includes a single gauge chart and a multiple one.
Example #1 – Create a Gauge Chart in Google Sheets with Single Value
To create a gauge chart in Google Sheets for a single value, we have the performance percentage of a class’s students. Let’s look at how to prepare the chart.
Step 1: First, set up your data. We need two values: the actual value, which indicates the class’s average performance, and the target range in percentage.
Step 2: Select the data. Now, go to the menu bar and click on Insert > Chart.
Step 3: In the Chart Editor that appears, go to the Setup tab. Choose the chart type as “Gauge chart.”
Step 4: To modify the colors of the gauge chart, click the Customize tab within the Chart editor. Then, click the dropdown arrow next to Gauge.
Step 5: Now, specify the colors to use for various maximum and minimum values on the gauge chart.
You get a chart as shown below.
This shows that the students’ performance in the class is good but not good enough to be in the optimal range, thereby offering food for thought on teaching improvements!
Example #2 – Create a Gauge Chart in Google Sheets with Multiple Values
To create a gauge chart with multiple values, we use the budget allocated to different departments in an organization as an example.
Step 1: We set up the table as shown below. We have two values: the amount used from the budget and the total budget.
Step 2: Our next step is to insert the gauge chart. Select your data. Here, we select the range A1:B4. Next, go to the menu at the top. Now, go to the Insert menu and choose Chart.
Step 3: In the Chart Editor pane, under the Setup tab, scroll down and select Gauge Chart under Chart Type drop-down.
Step 4: You can see the gauge charts as shown below. However, we have to set the maximum value. Adjust chart settings by going to the Customize tab. Here, under Gauge, set the required minimum and maximum value.
Therefore, you can also adjust the target values and the color scheme to make the chart more attractive and understandable.
Example #3
In this example, we will show you how to find a salon’s customer satisfaction value using a gauge chart. We have reviews from male and female customers. We find the ratio and find which customer is more satisfied—male or female.
Step 1: We have written the values in a table. In the B3 and C3, we calculate the percentage of positive reviews to get a clear picture.
For the percentage of men who gave a positive rating, use the following formula in cell B3.
=(B2/B4)*100
For the percentage of women who gave a positive rating, use the following formula in cell C3.
=(C2/B4)*100
Step 2: Select cells B3 and C3. Next, go to the menu at the top and click Insert > Chart. Under Chart Type, choose the Gauge chart. See below image for the gauge charts.
Step 3: Let’s add different color schemes for the gauge range to visually identify which side needs improvement.
Specify the first range as 0 for the Min value and 50 for the max value and choose your required color.
Now, observe the gauge charts. It gives you a pretty good picture, right?
Important Things to Note
- The uses of a gauge chart are mainly as follows:
- Showing the progress toward a set goal
- Visualizing percentages
- Displaying key performance indicators
- While viewing graphs with a target, you can set the target or goal by adjusting the Max value under the Customize tab in the “Gauge Range” and adjusting its value.
- Also, labels can be added to the gauge chart by customizing under the Customize tab, in the Chart & axis titles for Google gauge chart customization.
- The gauge charts are designed for positive values. Therefore, both the Min and Max values should be non-negative.
Frequently Asked Questions (FAQs)
To modify it for an existing gauge chart, follow these steps:
1. First, click on the chart and select it.
2. Use the handles on the corners to resize the chart.
3. The chart can be clicked and dragged to reposition it within the spreadsheet.
4. For very accurate sizing based on your requirement, right-click the chart select “Chart Area > Resize Chart Area” and enter the required dimensions.
If the actual value from which we derive the graph is beyond the max or min value, the gauge chart displays, but the representation may not be accurate and meaningful. Always ensure that the max and min values are right for the data to display an accurate chart for making meaningful decisions.
One uses Gauge charts across different fields.:
1. It can be used in project management for visualizing project completion percentages.
2. It is used in sales for tracking progress toward targets.
3. A gauge chart is used for measuring customer satisfaction with Net Promoter Scores (NPS)
4. It can be used in finance to show budget utilization.
5. In Human Resources, it helps visualize employee performance.
Download Template
This article must help understand Gauge Chart in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is a Gauge Chart in Google Sheets. We learn how to create it to indicate KPIs & other metrics with examples & working template. You can learn more from the following articles. –
Leave a Reply