Learn how to create scorecard charts in Google Sheets, useful for summarizing a single metric
Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.
Scorecard charts in Google Sheets display the summary of a single metric. They are commonly used to highlight key performance indicators (KPIs) together with a change in their value over a specific baseline value.
Consider the data set below, which shows the monthly net profit/loss for January to December.
How to Add a Scorecard Chart in Google Sheets?
Step 1. Click an empty cell outside your dataset.
Step 2. Go to Insert and select Chart. An empty chart and the Chart editor located on the right side of your Google Sheet will appear.
Step 3. On the Setup tab, click the down arrow under Chart type. Hover your mouse on an example image to see the chart type. Scroll down and select the Scorecard chart under Other.
The scorecard chart will still be empty. To add a value on your scorecard chart, you can choose to show data from one cell or a summary from multiple cells.
Showing data from one cell
Suppose we want to display a value from one cell, say the total net profit/loss from January to December based on our sample dataset above. To do that,
Step 1. Open the chart editor by double clicking on the scorecard chart. You may also click on the vertical ellipsis icon at the upper right corner of your scorecard chart. Select Edit chart.
Step 2. On the Setup tab, you can manually change the value on the data range to your desired cell. Press Enter, then the key value will automatically change, and the scorecard chart will display the key value.
You can also change the key value by selecting the Select data range icon located at the bottom right of the data range option.
The Select a data range menu will open. Click on the rectangle located at the top. Then, manually type the cell location you wish to show or select the cell on your dataset. Click OK. The Scorecard chart then displays the value of the selected cell.
Note: You can change the size of the scorecard chart by dragging on the four corners.
Showing data from multiple cells
Scorecard charts can also summarize data from multiple cells. It could be the average, count, sum, maximum, median, minimum, and sum. Here are the steps:
Step 1. Click the rectangle located at the top of the Select a data range menu. Select the multiple cells you wish to summarize on your data set. Press OK.
Step 2. Check the Aggregate button on the Chart Editor under the Setup tab. Click the up arrow menu, then choose among the different measures.
Step 3. You now have an updated scorecard chart.
How to Show Comparisons on Scorecard Charts?
Scorecards can also be used to show the comparison between two data or ranges of data. Here, the value you want to highlight, called the key value, will be compared to a specific baseline value.
Step 1: Under Key Value, select the Baseline Value. Then, click on the Select data range menu icon.
Step 2: On the Select a data range menu, type or select the cells you wish to compare.
Step 3: If needed,change the aggregate measure by clicking on measure at the left of the baseline value. Choose among the available measures. For better analysis, keep in mind that the measures must be the same when comparing two datasets.
Step 5: You now have a scorecard chart with a comparison!
The above scorecard chart shows the average net profit for Quarter 3 and it is smaller by $24, 921.33 than the average net profit in Quarter 4.
Formatting the Comparison
The comparison between the key value and the baseline value can be presented as an absolute change or percentage change. To change the comparison presentation, go to the Customize tab on the Chart Editor. You can also change other formats such as font style, font size, and font colors here.
Switching the Key Value and the Baseline Value
The key value or specific metric you want to focus on may sometimes interchange with your baseline value while doing your analysis. In Google Sheets, the data cell or cells are stored in your data range once you make a scorecard. You can switch the key value and the baseline value by just selecting the range under their respective options.
The above scorecard chart shows the average net profit for Quarter 4 and that is higher by $24,921.33 than the net profit in Quarter 3.
How to Customize the Scorecard Chart?
For better presentation, it is beneficial to add titles to your scorecard chart. To add titles, go to the Customize tab on the Chart editor. Under Chart & axis titles, click on the first up arrow menu, then choose among the two options.
Other title formatting options such as font style, font size, and color can be changed here.
One great feature of scorecard charts is that they are automated. Once you change the value of the cells linked to your scorecard, the values in your scorecard chart will automatically update.
Sample Sheet for Creating Scorecard Charts
You can try creating and customizing the above scorecard charts using this sample sheet.