Creating Charts


Cells in Excel are like a table in MS Word; they contain text information—numbers and words. When you have many cells together with related information, organized into rows and columns, this can be called a table. The same information, however, can be expressed as a chart. A chart shows in pictures what a table shows with words and numbers.

When you create a chart, you must remember that a chart must tell a story. It must send a clear message to the audience. Someone should be able to look at the chart and immediately understand what you are trying to say. Therefore, the information must be presented in a way that gives your message simply and clearly.

Take a look at the two charts below. They both show the exact same data. The first chart, however, in not well-presented. It does not organize the data in a way that makes a meaning clear. By looking at the first chart, can you understand anything?

Now look at the second chart. It shows the exact same information—but this time, it shows the data in a way that clearly shows you the point.

The message is clear: generally, or at least in this group, men are taller than women.

In order to make the message clear, you must decide how the data is ordered, what type of chart is used, and how it is displayed.

Making the Chart

Here's how to make a chart using Excel 2016:

First, select all the text in the chart, including the header labels.

Then, look at the "Insert" tab:

See the "Charts" area towards the right, the one with the large button titled "Recommended Charts" folowed by an array of small chart buttons? Click on "Column."—the small button at the top left of the array of small buttons. You will see these choices:

In this case, just select the first choice in the "3-D Column" section; that chart should be a "Clustered Column" chart. You will see an inline chart appear, lke this:

That's it! Now you can modify it however you like.

Organizing the Data

Notice that the data in the chart is not really organized; all the data is mixed up, and not in any particular order. In order to clean things up, we should sort the chart data.

Fortunately, the table data is still connected to the chart, so that any change we make in the cells will be reflected in the chart. So, let's go back and select all the data in the table:

Be sure to select all the data, with the header label cells.

Once you have selected the data, go to the DATA tab and select "Sort...".

In the "Sort" dialog box, you may note that the button at top right, labeled "My data has headers," is checked. If you look back at the table cells, you will see that this has caused the Name and Height label cells to be deselected. You should check this status every time you sort; if you did not select the headers originally, checking the box will remove the first line of data. If you selected the headers but the box is not checked, then the header text will be sorted to the middle of the table.

It is best to select the header cells before sorting, and then check the box. Why? Because if Excel knows that those are header cells, it will put the labels in the Sort by menu seen in the dialog box below:

Your finished table should look like this:

Formatting the Chart

With the chart selected, look at the Ribbon: you will see a new tab, Chart Tools, appear. In the Design part, you can quickly choose from some basic chart themes:

In addition, in the Format tab, you can control the appearance of much more within the chart. You can also use the Format Cells dialog box (Control + 1) to format the chart.

However, there is an even better way: use the Format panes.

To make the Format pane appear, right-click on any part of the chart. At the bottom of that pop-up menu, you should see a menu option for "Format":

Once you open up that pane, you can format any part of the chart that you select, including any axis, the chart background, or the "walls," which is the area behind the bars or other chart indicators:

Let's return to the chart that we made:

Notice that the chart does not yet clearly show that there is a difference in gender. You would have to carefully read each name at the bottom to see the distinction. Let's change the color of some columns to clear that up. First, click on the columns in the chart:

Notice the little resize handles at each end of each column. Now, left click once on one of the columns—in this case, Wanda's column:

Note that now only Wanda's column is selected. Now, go to Chart Tools: Format, go to the Shape Fill menu, and select a pink color (or whatever gender-appropriate color you desire):

Once the bar changes color, click on the next bar; if you do so without any different action in between, that bar should also be individually selected, and then you can Redo (Control + Y) the coloring:

To change all the other desired colors, just click on the next one and continue to do a Control-Y shortcut, and they will turn color one by one.

I then made some other changes, and came out with this:

Here, you can see the "message" of the chart clearly: men are usually taller than women.

You can see that I made lots of other changes as well. Usually, you just right-click on whatever you want to change, choose the correct option from the pop-up menu, and make your changes. You can also effectively use the Ribbon tabs to make easy changes.

After you are through, you can select the whole chart, copy it, then go to PowerPoint or Word, and then paste—and your chart is in that document! You can even keep changing the chart and the data after you pasted it.

Now you know how to create charts in Excel.

There is a lot more in that program, and much of it you can learn by exploring and trying things out. Go ahead and give it a try!

Previous Chapter Chapter Practice Unit Main Page