Spreadsheets are amazingly powerful tools for creating, storing, manipulating, and analyzing numeric information. However, not everyone can look at a column of numbers and gain insight into the underlying process or information that those numbers are abstracted from. For that reason, spreadsheet programs, including Google Sheets, have included graphical charting functions almost from their very earliest incarnations back in the Lotus 1-2-3 days, even though technically charts have little to do with a spreadsheet’s core functionality of analyzing information.
Google Sheets, Google’s free cloud-based spreadsheet solution, also includes charting components which are simple to use but quite powerful. In this tutorial article, I will show you how to add charts to your Google Sheets, how to edit the legend that Google Sheets assigns to your charts, and how to edit some other chart features.
Working with charts is relatively simple. You just need to have a set of data to refer to, design a chart in the built-in charting tool within Sheets, set the legend so it is easily understandable, and insert it into the spreadsheet. You can create your own data to follow this tutorial, or you can create a new sheet and use the data I use for my examples. For the example chart, we’ll use a simple little sheet with a list of a household’s expense category, and the monthly budget for each expense. Create a sheet with two headings, “Expense” and “Monthly”, and add the following information to the sheet:
Adding a chart to Google Sheets
To create a chart, we first need to specify a data set for the chart to be based on. We begin by selecting a data range and working from there. In the example in the images, the data range is A1 to B7, or ‘A1:B7’ in spreadsheet notation.
- Open the sheet you want to create a chart within.
- Identify the data range you want to use and highlight it within the sheet.
- Select Insert from the top menu and Chart. The chart editor will open up on the right side of your screen, and the chart will appear in the sheet.
- The first line of the chart editor is titled “Chart type”. Select a chart type from the dropdown. Sheets will suggest a few chart types that are suited for the type of data you’ve provided, but you can choose whatever type you wish.
- You can customize the data elements used in the chart; these controls appear below the Chart type selection.
- Select the Customize tab in the chart editor to see the formatting controls. Play with these to learn how to modify your chart. The chart will change as you make changes in the dialog.
- When you have finished modifying the chart, click the X in the upper right of the chart editor to control it.
- Drag the chart to where you want it in your Sheet.
Deciding what chart type to use
Different chart types lend themselves well to displaying different data types. Not all chart types will work with all data, so it may be a case of experimenting as you go along. The chart editor has a suggestions section which indicates the chart type that the software thinks would be appropriate, and you can start from there if you really don’t know what kind of chart to deploy.
Each type of standard chart has an associated type of information that it is best-suited for displaying, depending on what the visualization is intended to accomplish. For example, in the case of our monthly household expenses, a pie chart is a very powerful way to demonstrate that our mortgage payment is dominating our monthly expenses because it makes that visual element very large on the sheet.
Edit the chart legend in Google Sheets
Once you’ve created a chart, it’s likely that you’ll want to change the legend. The chart legend is the colored box and text that tells the reader what each color on the chart represents. On the current chart, it is labeled “Monthly”. Google Sheets does its best to figure out a label by default, but it often ends up being something helpful like “Monthly” – technically accurate, but not very illuminating to anyone looking at the chart.
Editing the chart legend in Google Sheets is done from within the chart creation window or from within the sheet. Once you’ve created your chart, you can bring back the chart editor by right-clicking anywhere on the chart itself and selecting any menu item; this will open the chart editor and take you to the specific editing area. You can edit the legend in a number of ways. You can change whether it displays at all (in many charts it simply isn’t needed), or specify its position within the chart. You can also change the font, font size, formatting, and text color of the legend.
- Right click the chart and select ‘Legend’.
- Modify the side it is displayed, the font type, size and color as you see fit.
- The chart will update as you make changes within the editor.
Changing the legend text in Google Sheets
One feature that many users wish they could have is the ability to change the text displayed for the legend. On our example sheet, for example, the legend “Monthly” isn’t really all that useful or descriptive. The only way to change the legend text is to rename the data column, and the legend will also change. For example, we could replace the “Monthly” text in column A2 with “June 2018”, or “Estimated Monthly Amount”, and then our chart would show that text instead, which would be more useful. So this works, and the only time it’s problematic is if you want the spreadsheet row or column to have a different label than that displayed on the chart.
Editing other chart elements
There are many chart elements that you can edit within Google Sheets. The easiest way to access chart settings is to right-click within the chart to pull up the chart editing context menu.
Under “Chart area” you can choose between resizing the chart area (which lets you increase or decrease the size of the chart display within the chart frame) or fitting the chart area to the available chart frame. (You can change the chart frame by clicking anywhere within the chart, then clicking and dragging on the resizing frame.)
Most of the elements on the context menu just take you to the appropriate section of the Chart Editor, but it’s a very useful shortcut for the commonly selected tasks. Using the context menu, you can change the chart style, change the chart and axis titles and subtitles, choose which data series the chart displays, change the legend, change the labels on the X and Y axis, set the gridlines, or reset the data range the chart draws from.
You might also be interested in checking out this TechJunkie article on how to build graphs in Google Sheets.
Got any Google Sheets chart tips you would like to share? Tell us about them below!