.png)
How to Make Gauge Charts in Google Sheets
Learn how to create gauge charts in Google Sheets, useful for comparing a value to a range of other possible values
Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.
Gauge charts are a type of chart that visualizes a set of data and how it compares against a range of possible values. In most gauges, such as in fuel gauges, there is an acceptable range of values indicated by a green or white color. If the value drops to the red-colored range, then you should go to the nearest gas station to refuel your car. The gauge chart works in a similar way.
Gauge charts are best used to visualize the current value of a KPI or a metric and whether it is within an acceptable range or not. A gauge chart quickly visualizes whether the value of a KPI or a metric is within the acceptable range.
In this tutorial, we will learn how to make gauge charts in Google Sheets. We will encounter an example of a single gauge chart and a multiple gauge chart.
How to format the data for the gauge chart
The data format is straightforward:
- First column: label or title of the gauge chart, which appears near the center of a gauge chart
- Second column: the value of the gauge chart
Google Sheets generates a single gauge chart for each row. We cannot have several needles inside a gauge chart; Google Sheets will set a gauge chart for each row, in the case of selecting several rows of data.
How to make a gauge chart
Step 1: Select all the data to be converted to gauge charts, then click Insert in the main menu, then select Chart from the drop-down menu.
Step 2: Go to the Chart editor sidebar on the right side, then click the drop-down menu under the Chart type label. Select the Gauge chart option, classified under the Other label.
Here is the gauge chart:
In the next sections we will show you how to improve on the basic chart..
How to change the gauge range
The first thing you can change is the gauge range, the values from the lower end to the upper end. As you can see, the values can be a little lopsided, especially for Customer satisfaction (which is only displayed as Customer in the original chart). We can change it:
Step 1: On the Chart editor sidebar located on the right side of Google Sheets, click the Customize tab. Click the Gauge option.
Step 2: The first label is Gauge range, with two textboxes underneath it, one for the minimum value (labeled as Min) and the maximum value (labeled as Max). For our customer satisfaction gauge chart, the range should be from 0% to 100%
We set the maximum value to 1 because the data that we used for the value is in decimal form but converted to a percentage by clicking this option in the main toolbar:
The gauge chart:
How to add colors within the gauge range
You probably wanted to mark the range of acceptable values in green, the danger values in red, and those in between as yellow. For our case, we want to mark the values as follows:
- Green values: 80% to 100%
- Yellow values: 60% to 80%
- Red values: 0% to 60%
Using these values, here are the steps:
Step 1: On the Chart editor sidebar located on the right side of Google Sheets, click the Customize tab. Click the Gauge option.
Step 2: Below the Gauge range label, there are three Range color labels with color palettes underneath them. One for each color: red, yellow, and green. Let us set first the range for the color red. On the Min and Max textboxes, add 0 and 0.6, respectively.
The chart automatically updates after adding the ranges.
Step 3: Repeat the same steps for the yellow range and the green range.
The resulting gauge chart:
As you can see, you can easily shuffle the ranges of these colors. If you want to use the following range:
- Red values: 80% to 100%
- Yellow values: 60% to 80%
- Green values: 0% to 60%
Your gauge chart will look as follows:
How to replace the gauge chart label with the chart title
Finally, we want to replace the gauge chart label in the center with a chart title on top of it. Unfortunately we cannot change the font size of the label. However, we can opt to replace it with the chart title. Here are the steps:
Step 1: Select the Setup tab of the Chart editor. Scroll down to find the Label option, and then click the three dots besides the cell containing the label of the chart. An option to Remove the label will appear. Click it.
Step 2: Click the Customize tab of the Chart editor sidebar, then click Chart & axis titles. A drop-down box appears first; by default, Chart title is selected. If not, click on it to select Chart title. Afterwards, type the chart title in the textbox below it (In our example, the title is “Customer Satisfaction”).
With additional formatting, we get the following gauge chart:
How to make gauge charts for several values
Step 1: Select all the data to be converted to gauge charts, then click Insert in the main menu, then select Chart from the drop-down menu.
Step 2: Go to the Chart editor sidebar on the right side, then click the drop-down menu under the Chart type label. Select the Gauge chart option, classified under the Other label.
Here is the gauge chart, with few modifications:
The rows should have related data, because you can only specify a single set of values for the gauge range and the color ranges. Following the same steps as with a single gauge chart, here is the result:
Sample sheet
Here is the sample sheet for the gauge charts we made in this tutorial.
