Charts: Adding context

Now, I’ve explained how to create various simple charts, but you may have noticed that there’s something all of my examples were missing.

Simple pie chartTake for example, our basic pie chart. Imagine coming back to it a year or more after you made it. Do you remember what it’s about? How about when it was made? If so, you have a better memory (or make a lot fewer graphs) than I do! It really needs labels or a title or both to give it some sort of context. Without some sort of explanatory text, a graph is really just a picture (and not necessarily even a pretty one at that).

Most spreadsheet programs will allow you to add titles and labels both when the graph is first created, and later. If you have row/column labels next to the numbers you included in the graph, the program might even detect them and (try to) add them automatically for you. If it does try and manages to mess it up however, you can always fix it using the steps below.

Labeled bar chartStart by copying the table below and pasting it into your favorite program, then making a bar chart. Then follow the instructions for your program to turn your original graph into something that looks like this one, which was made in Excel 2007.

Allen 16
Beth 8
Cindy 10
Doug 1

To make this chart out of the default one created with this data, I took the following steps:

  1. Changed the item labels from “1 2 3 4” to the names.
  2. Added a “Summer 2010 Reading Program” title at the top.
  3. Added a “Books Read” axis label at the bottom.
  4. Removed the legend box from the right side.

A quick note before jumping into the program-specific instructions. When I tell you to click of right-click on “the graph” but don’t specify a particular part of the graph, I’m talking about the whitespace surrounding the part with the lines and the bars. Like pointing at a piece of paper and saying “This!”, if you’re touching something in the middle you might be talking about a word, sentence, or paragraph, but if you’re touching where there’s no text, people are more likely to assume you’re talking about the whole page. Programs are written by people, and so use some of the same assumptions.

In short, if you click on something and the menu I describe doesn’t pop up, try moving your mouse closer to the edge of the graph, into the blank “margin” area, and trying again.


Changing Item Labels in Excel 97:
If you haven’t done anything else to the chart, the easiest way to change the item labels might be to just delete the whole graph and then select the labels AND the numbers, and make a new graph. If you can’t do that, however, here’s how you change from the automatically generated labels to the ones you want.

  1. Right-click on the graph.
  2. Click on “Source Data…”
  3. In the window that appears, click on the “Series” tab.
  4. Near the bottom, find the textbox with the label “Category (X) axis labels. The textbox also has a graphic.button at the right end. It’s asking you to tell it what cells your item labels are in. You could type in the cells, but I recommend clicking on the button. If you look really closely at the graphic, you’ll see that it looks like a miniature spreadsheet with one cell highlighted, and a red arrow pointing to the highlighted cell. Click on the button.
  5. The window will now be much smaller, and you can click on the worksheet behind it. Do so, and highlight the four names. Note how text appears in the textbox. Mine says =Sheet1!$A$1:$A$4”, but yours will depend on where you pasted the values from the table I provided.
  6. Either click anywhere in the textbox and hit “Enter”, or click on the graphic/button at the end of the textbox. The pop-up window will go back to looking the way it did before.
  7. Click on the “OK” button. It will now go back to the previous window. Note how the Axis Labels box now has “Allen Beth Cindy Doug” instead of “1 2 3 4”.

Adding a Title/Label in Excel 97:
Almost the same instructions work for both the title at the top of the graph, and the labels along the side or bottom.

  1. Click on the graph.
  2. Click “Chart Options…”
  3. In the “Titles” tab, the first textbox is labeled “Chart title”. Enter the title for the graph in that textbox. It will appear at the top of the graph.
  4. Below that are two more textboxes, labeled “Category (X) axis:” and “Value (Y) axis:”. The X and Y are only correct if you’re working with a column chart, so pay more attention to the fact that they’re labeled “Category” and “Value”. If you want a label that applies to all of the items (in this example that could be a label such as “Student Names”, although I haven’t used one), then you want to say what category the items fit into. If, on the other hand, you want to say what the values represent (Books read? Feet run? Inches grown?), then, logically, the values are what you want to label. Enter the label(s) in the appropriate textbox(es).
  5. Click “OK”.

Deleting graph elements in Excel 97:
Sometimes Excel does something helpful for you that you didn’t want it to do. Sometimes you added an element (title, label, whatever) then decided you didn’t like it. So you might want to delete part of your graph. Fortunately, deleting is easy, and pretty much the same for everything.

  1. Click on the element you want to get rid of. It should now be surrounded by a line and eight little black boxes (one at each corner, and one in the middle of each side).
  2. Hit your “delete” key.

Changing Item Labels in Excel 2007:
If you haven’t done anything else to the chart, the easiest way to change the item labels might be to just delete the whole graph and then select the labels AND the numbers, and make a new graph. If you can’t do that, however, here’s how you change from the automatically generated labels to the ones you want.

  1. Click on the existing labels. A box should appear around them.
  2. Right-click anywhere in the box.
  3. In the menu that appears, click on “Select Data”.
  4. In the window that pops up, the box on the right-hand side is labeled “Horizontal (Category) Axis Labels”, and there is an “Edit” button immediately below that. Click on the “Edit” button.
  5. In the new window that appears, you have a textbox with a button at the end, followed by the greyed-out words “Select Range”. It’s asking you to tell it what cells your item labels are in. You could type in the cells, but I recommend clicking on the button. If you look really closely at the button, it looks like a miniature spreadsheet with one cell highlighted, and a red arrow pointing to the highlighted cell. Click on the button.
  6. The window will now be somewhat faded and smaller, and you can click on the worksheet behind it. Do so, and highlight the four names. Note how text appears in the textbox. Mine says “=Sheet1!$A$2:$A$5”, but yours will depend on where you pasted the values.
  7. Either click anywhere in the textbox and hit “Enter”, or click on the button at the end of the textbox. The pop-up window will go back to looking the way it did before.
  8. Click on the “OK” button. It will now go back to the previous window. Note how the Axis Labels box now has “Allen Beth Cindy Doug” instead of “1 2 3 4”.
  9. Click on the “OK” button.

Adding a Title/Label in Excel 2007:
The same instructions work for both the title at the top of the graph, and the labels along the side or bottom.

  1. Click on the graph.
  2. Look at the very top of the screen, above the usual menu headers. You’ll see a new menu header called “Chart Tools”. If it’s not there, try clicking on the graph again.
  3. Right under the “Chart Tools” header there are three new sub-menu headers: Design, Layout, and Format. Click on Layout.
  4. In the “Labels” group, click on either “Chart Title”(for a title at the top of the graph) or “Axis Title” (for a title at the bottom or side of the graph). If you are doing an axis title, you will then need to select “Horizontal” for one at the bottom, or Vertical for one at the side.
  5. In the menu that appears, click on any option except “None”.
  6. There should now be a box with the words “Chart Title” (or “Axis Title”) on your graph. If you just type, it will replace the words “Chart Title” with what you type. Or you can click inside the box, highlight the words you don’t want, and then type text you do want.

Deleting graph elements in Excel 2007:
Sometimes Excel does something helpful for you that you didn’t want it to do. Sometimes you added an element (title, label, whatever) then decided you didn’t like it. So you might want to delete part of your graph. Fortunately, deleting is easy, and pretty much the same for everything.

  1. Click on the graph.
  2. Click on the element you want to get rid of.
  3. Hit your “delete” key.

Changing Item Labels in Google Spreadsheet:
If you haven’t done anything else to the chart, the easiest way to change the item labels might be to just delete the whole graph and then select the labels AND the numbers, and make a new graph. If you can’t do that, however, here’s how you add item labels.

  1. Click on the graph.
  2. Click on the downward-pointing triangle near the upper left corner.
  3. Click “Edit Chart…” This will re-open the Chart Editor.
  4. In the Start tab, there’s a textbox labeled “Data – Select range…” Click on “Select Range”.
  5. A new window opens, labeled “What data?” If you click on the worksheet it will show the selected range in the textbox. In the worksheet, select the numbers AND labels. In my case the textbox now says “Sheet1!A1:A2”, but yours might say something different.
  6. Click “OK”.
  7. Back in the Chart Editor, underneath the Data textbox, you will note that it automatically has “Use column A as labels” checked, and greyed out. Because column A has words in it, not numbers, it knows it must be labels. If column A had numbers you would have the option to either check or uncheck it.
  8. Click “Update”.

Adding a Title/Label in Google Spreadsheet:
The same instructions work for both the title at the top of the graph, and the labels along the side or bottom.

  1. Click on the graph.
  2. Click on the downward-pointing triangle near the upper left corner.
  3. Click “Edit Chart…” This will re-open the Chart Editor.
  4. Click on the “Customize” tab.
  5. In the Customize tab, the first textbox is labeled “Chart: Title” Type the title in the textbox. (You can also name your chart something other than “Chart1” by clicking on the “Name” link.
  6. Further down there’s a textbox labeled “Axis”, with the words “Vertical” and “Horizontal” next to it. To get a label along the bottom click on “Horizontal” and enter the label in the textbox.
  7. Click “Update”.

Deleting the legend in Google Spreadsheet:
Like most spreadsheet programs, Google automatically includes a legend when you first create the graph, even if there’s only one type of item. To delete it, do the following.

  1. Click on the graph.
  2. Click on the downward-pointing triangle near the upper left corner.
  3. Click “Edit Chart…” This will re-open the Chart Editor.
  4. Click on the “Customize” tab.
  5. In the Customize tab, , beneath the “Title” textbox there’s a set of radio buttons labeled “Layout: Legend”. Click on the option labeled “None”.
  6. Click “Update”.

Changing Item Labels in Open Office:
If you haven’t done anything else to the chart, the easiest way to change the item labels might be to just delete the whole graph and then select the labels AND the numbers, and make a new graph. If you can’t do that, however, here’s how you change from the automatically generated labels to the ones you want.

  1. Click on the existing labels. Little green boxes should appear above and below them.
  2. Right-click anywhere along the axis (between the two little green boxes).
  3. In the menu that appears, click on “Data Ranges”.
  4. In the window that pops up there’s a text box labeled “Data Range”, and in that text box is your current data range. Mine says “$Sheet1.$B$1:$B$4”, which means it’s located in worksheet 1, in column B, rows 1 through 4. Yours will be different if you pasted it in a different space. Since the labels we want are actually in the column immediately to the left of the current data range, you need to expand the range one column to the left. In my case I would change it to “$Sheet1.$A$1:$B$4” — change the first “B” to an “A”, and otherwise leave it alone. That changes it to mean worksheet 1, columns A through B, rows 1 through 4.
  5. Further down in the same window, make sure the box labeled “First column as labels” is checked.
  6. Click “OK”.

Adding a Title/Label in Open Office:
The same instructions work for both the title at the top of the graph, and the labels along the side or bottom.

  1. Right-click on the graph.
  2. Click on “Insert Titles”.
  3. In the window that opens up, there are a number of labeled textboxes. The first one is “Title”. Click in that and enter whatever chart title you want. It will appear at the top of the graph.
  4. Further down in the Axes section, there are “X axis” and “Y axis” textboxes. If you are doing a horizontal bar chart, in order to get a label along the bottom you want to click in the “Y axis” box. If you were doing a column chart and you wanted a label at the bottom you’d want the “X axis” box. Confused? Don’t worry, so am I, but that’s the way it is. For some reason they’ve chosen to label a horizontal bar chart as if it were a column chart turned on its side.
  5. Enter the axis label in the selected textbox.

Deleting graph elements in Open Office:
Sometimes Open Office does something helpful for you that you didn’t want it to do. Sometimes you added an element (title, label, whatever) then decided you didn’t like it. So you might want to delete part of your graph. Fortunately, deleting is easy, and pretty much the same for everything.

  1. Click on the graph.
  2. Click on the element you want to get rid of.
  3. Hit your “delete” key.