Excel 2013 & 2016: Creating charts

Updated 2015.03.10

Objectives:

·        Learn how to create charts in Excel.

·        Learn how to customize charts after creating them

Charts

To create a chart in Excel, you must carry out the following steps in order. Here is a copy of the file used in the examples.

Decide what you want your chart to look like.

Decide which numbers will be plotted, which labels will go on the x-axis, which numbers will go on the y-axis, whether you will have labels on both the horizontal and vertical axis, whether you will have a legend, etc. In this case, I want to create a chart that looks like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image001.jpg

1.    Select the data

Select the data to be charted before doing anything else. The selection must include not only the numbers to be plotted, but any labels for the x-axis and y-axis. Data selections do not need to be adjacent, but if you are selecting columns, all columns must be the same height, and if you are selecting rows, all rows must be the same width. If you could place them together, they would have to form a rectangle.

 

NOTE: For each row or column that you select, it must be a continuous selection. You cannot stop halfway down a column of numbers and do a <ctrl>+<click> and continue. Excel will interpret this as two ranges of numbers to be plotted rather than one and your chart will not look anything like what you want it to look like. In this case, your selection must be the following.

 

NOTE: I want the words "Wins" and "Losses" in the legend, so I must include them in my selection (row 2).

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image002.jpg

2.    Select the desired chart type

·     Click on the Insert tab.

·     In the Charts group, click on the desired chart type. I have selected a Column chart.

·     From the drop-down menu, click on the desired sub-type. I have selected a clustered column chart for the chart sub-type.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image003.jpg

 

Excel will create a chart for you. All you really need to complete the chart is to add a title and any necessary labels for the x-axis and y-axis.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image004.jpg

3.    Verify that your data has been correctly selected

In the above example, Excel interpreted my data correctly, but it doesn't always do so (it depends on whether the rows are taller than the columns are wide—or not). If your data doesn't look right, first verify that the data range is correct. If the related data (all of the points that make up the same line in a line chart, e.g.) are in columns, but Excel is taking all of the numbers on a row and making them into a line (or vice-versa), go to the Chart Tools and click on the Design tab. Then click on the Switch Row/Column button:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image005.jpg

4.    Add titles and a legend

With the chart selected, the words Chart Tools should appear above the Design and Format tabs, and as long as you have the chart selected, these tools will be visible:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image006.jpg

Some things that need to be done are: add titles/labels to the chart, the x-axis, and the y-axis.

Add a title

·     Click on the Design tab.

·     In the Chart Layouts group, click on the Add Chart Element button.

·     Click on Chart Title.

·     Click on the desired location for your chart title. In the example below, the Above Chart position has been selected.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image007.jpg

·       The following will appear above your chart:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image008.jpg 

·       Click on the formula bar and enter your title. When you press the Enter key, your title will replace the "Chart Title" text.

Add an x-axis (horizontal axis) label

·       Click on the Design tab.

·       In the Chart Layouts group, click on the Add Chart Element button.

·       Click on the Axis Titles menu item.

·       Click on the Primary Horizontal menu item.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image009.jpg

·     The following will appear below your x-axis:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image010.jpg

·       Click on the formula bar and enter your x-axis title. When you press the Enter key, your title will replace the "Axis Title" text.

Add a y-axis (vertical axis) label

·       Click on the Design tab.

·       In the Chart Layouts group, click on the Add Chart Element button.

·       Click on the Axis Titles menu item.

·       Click on the Primary Vertical menu item.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image011.jpg

·     The following will appear to the left of your y-axis:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image012.jpg

·       Click on the formula bar and enter your title. When you press the Enter key, your title will replace the "Axis Title" text.

Move the legend

·       Click on the Design tab.

·       In the Chart Layouts group, click on the Add Chart Element button.

·       Click on the Legend menu item.

·       Click on the desired location. In the example below, the Right menu option has been selected.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image013.jpg

·    Excel will position the legend where you requested it.

·    NOTE: You can also click and drag the legend. However, you will have to resize the chart on your own to accommodate the legend’s new location. If you use the method described above, Excel will resize the chart to accommodate the legend’s new location.

5.    Other Formatting

Modify the grid lines

·     By default, Excel will place "major" grid lines that extend horizontally from the y-axis. If you want to remove them, or add minor grid lines, or add vertical grid lines, click on the Design tab.

·     In the Chart Layouts group, click on the Add Chart Element button.

·     Click on the Gridlines menu item.

·     Click on the grid lines option that you want. In the example below, Primary Minor Horizontal gridlines has been selected.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image014.jpg

Select your chart location

·     You have a choice of putting the new chart on a sheet by itself, or as an "object" in the current sheet. A chart will almost always look better if you put it on a separate sheet. By default, Excel puts charts on the same sheet as the data that it is based on. To change the chart’s location:

·     Click on the Design tab.

·     In the Location "group" click on the Move Chart button:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image015.jpg

·     Excel will display the following Move Chart dialog box.

·     Click on the New Sheet button, and enter the desired sheet name in the text box.

·     Then click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image016.jpg

Customize your chart

Customizing (formatting) Excel charts is fairly simple. It works the same way that formatting the spreadsheet works: First you select the item you want to format (in a chart, however, "handles" –little circles -- will appear around your selection), then you right-click to invoke a popup menu that almost always has the formatting options that you want on it.

To change this in your chart:

Do this:

Font attributes for any part of the chart

Select the part you want to change, then click on the Home tab. Choose font name, font size, font attribute, or font color from the ribbon.

Format x-axis labels

Right-click on the x-axis. Choose Format Axis from the popup menu. Then choose the desired formatting tab and attributes.

Format y-axis labels

Right-click on the y-axis. Choose Format Axis from the popup menu. Then choose the desired formatting tab and attributes.

Move a title or legend

Select the title or legend. Handles will appear. Move the tip of the cursor to the edge of the selection box (it will https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image017.jpgturn into a four-headed arrow), then drag the box.

Delete a title or legend

Select the title or legend, then press Delete key. The Backspace key also works.

Format the background

Right-click anywhere on the background. From the popup menu, click on Format Plot Area. The following window will appear on the right side of your chart:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image018.jpg

In the Fill group, select the desired changes, then click on the Close button.

Format the bars/lines

Right-click on a bar/line, then:

·     Click on Format Data Series. The following window will appear on the right side of your chart:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image019.jpg

·     Make the appropriate changes.

·     Click on the Close button.

Print a chart that is on a page with other data.

If you want to print a chart without the surrounding data, click on the chart. Then:

·     Click on the File tab.

·     Click on Print.

·     Click on the Print button.

What if Excel misinterprets your data?

We will look at a different set of data for this.

Select the data

Go to the Media Usage worksheet in the sample file. Then select the following data (the years in column A, including the word "Year" and the magazine and book numbers in columns E and F, including the words "Magazines" and "Books"):

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image020.jpg

Select the desired chart type

·     Click on the Insert tab.

·     In the Charts group, click on the desired chart type: Column.

·     From the drop-down menu, click on the desired sub-type: Clustered Column.

This is what Excel gives you:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image021.gif

Excel is trying to plot the years. We do NOT want the years plotted – we want them at the bottom of the chart, along the x-axis. To get Excel to do this:

·     Make sure the chart is selected. The Chart Tools will appear at the top of the worksheet.

·     Click on the Design tab.

·     In the Data group, click on the Select Data button.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image022.jpg

 

·       The Select Data Source dialog box will appear. Since we do NOT want the years plotted, click on Year in the Legend Entries list box. Then click on the Remove button.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image023.jpg

 

·       This will remove the years from the plot area, and yourchart now looks like this (your colors will probably be different). The bars representing the years are gone, but the labels on the x-axis are still just numbered 1 through 12. We want the years along the x-axis.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image024.jpg

 

·       To tell Excel that we want the years (column A) to appear along the x-axis, we need to go to the Horizontal (Category) Axis Labels area and click on the Edit button:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image025.jpg

 

·       The Axis Labels dialog box will appear. Drag the mouse over the years that you want to appear on the x-axis and click on the OK button. NOTE: Do NOT include the word "Year" (cell A2) in your selection.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image026.jpg

 

·       Your chart will now look like this. You will still need to either reduce the size of the numbers on the x-axis or rotate them so they no longer run together (or just move the chart to a separate page all by itself), plus do any other formatting (e.g. titles, etc.). You may also want to do something about the ugly default colors. This is easy to do.

 

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image027.gif

Changing the default colors of your bars.

·       With the chart selected, click on the Chart Styles button (paintbrush).

·       Click on the Color tab.

·       Click on the desired color scheme.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image028.jpg

 

Changing the color of a series of bars to a specific color

·       Click on one of the bars in the series you wish to change. Handles should appear around all of the bars in that series. I have clicked on the Books series below.

·       Click on the Home tab.

·       In the Font group, click on the Fill Color button (the paint bucket).

·       Click on the desired color. Below I have clicked on Red and the bars have changed to red.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/006-Charts_files/image029.jpg