Lesson 17: Working with Charts

/en/excel2010/using-templates/content/

Introduction

Lesson 17

A chart is a tool you can use in Excel to communicate data graphically. Charts allow your audience to see the meaning behind the numbers, and they make showing comparisons and trends much easier. In this lesson, you'll learn how to insert charts and modify them so they communicate information effectively.

Charts

Excel workbooks can contain a lot of data, and this data can often be difficult to interpret. For example, where are the highest and lowest values? Are the numbers increasing or decreasing?

The answers to questions like these can become much clearer when data is represented as a chart. Excel has various types of charts, so you can choose one that most effectively represents your data.


Types of charts

Click the arrows in the slideshow below to view examples of some of the types of charts available in Excel.

  • Slide 1

    Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.

  • Slide 1

    Column charts use vertical bars to represent data. They can work with many different types of data, but they're most frequently used for comparing information.

  • Slide 1

    Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.

  • Slide 1

    Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it's easy to see which values make up the percentage of a whole.

  • Slide 1

    Bar charts work just like column charts, but they use horizontal instead of vertical bars.

  • Slide 1

    Area charts are similar to line charts, except the areas under the lines are filled in.

  • Slide 1

    Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time.

  • Slide 1

Identifying the parts of a chart

Click the buttons in the interactive below to learn about the different parts of a chart.

labeled graphic

Horizontal Axis

The horizontal axis, also known as the x axis, is the horizontal part of the chart.

In this example, the horizontal axis identifies the categories in the chart, so it is also called the category axis. However, in a bar chart, the vertical axis would be the category axis.

Legend

The legend identifies which data series each color on the chart represents. For many charts it is crucial, but for some charts it may not be necessary and can be deleted.

In this example, the legend allows viewers to identify the different book genres in the chart.

Data Series

The data series consists of the related data points in a chart. If there are multiple data series in the chart, each will have a different color or style. Pie charts can only have one data series.

In this example, the green columns represent the Romance data series.

Title

The title should clearly describe what the chart is illustrating.

Vertical Axis

The vertical axis, also known as the y axis, is the vertical part of the chart.

In this example, a column chart, the vertical axis measures the height—or value—of the columns, so it is also called the value axis. However, in a bar chart, the horizontal axis would be the value axis.

To create a chart:

  1. Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart.
    Selecting cellsSelecting cells
  2. Click the Insert tab.
  3. In the Charts group, select the desired chart category (Column, for example).
    Selecting the Column categorySelecting the Column category
  4. Select the desired chart type from the drop-down menu (Clustered Column, for example).
    Selecting a chart typeSelecting a chart type
  5. The chart will appear in the worksheet.
    The new chartThe new chart

Chart tools

Once you insert a chart, a set of chart tools arranged into three tabs will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs to modify your chart.

The Design, Layout and Format tabsThe Design, Layout and Format tabs

To change chart type:

  1. From the Design tab, click the Change Chart Type command. A dialog box appears.
    The Change Chart Type commandThe Change Chart Type command
  2. Select the desired chart type, then click OK.
    The Change Chart Type commandSelecting a chart type

To switch row and column data:

Sometimes when you create a chart, the data may not be grouped the way you want. In the clustered column chart below, the Book Sales statistics are grouped by Fiction and Non-Fiction, with a column for each year. However, you can also switch the row and column data so the chart will group the statistics by year, with columns for Fiction and Non-Fiction. In both cases, the chart contains the same data—it's just organized differently.

Book Sales, grouped by Fiction/Non-FictionBook Sales, grouped by Fiction/Non-Fiction
  1. Select the chart.
  2. From the Design tab, select the Switch Row/Column command.
    The Switch Row/Column commandThe Switch Row/Column command
  3. The chart will readjust.
    Book sales, grouped by yearBook sales, grouped by year

To change chart layout:

  1. Select the Design tab.
  2. Click the More drop-down arrow in the Chart Layouts group to see all of the available layouts.
    Viewing all of the chart layoutsViewing all of the chart layouts
  3. Select the desired layout.
    Selecting a chart layoutSelecting a chart layout
  4. The chart will update to reflect the new layout.
The updated layoutThe updated layout

Some layouts include chart titles, axes, or legend labels. To change them, place the insertion point in the text and begin typing.

To change chart style:

  1. Select the Design tab.
  2. Click the More drop-down arrow in the Chart Styles group to see all of the available styles.
    The Change Chart Type commandViewing all of the Chart Styles
  3. Select the desired style.
    Selecting a chart styleSelecting a chart style
  4. The chart will update to reflect the new style.
The updated chartThe updated chart

To move the chart to a different worksheet:

  1. Select the Design tab.
  2. Click the Move Chart command. A dialog box appears. The current location of the chart is selected.
    The Move Chart commandThe Move Chart command
  3. Select the desired location for the chart (choose an existing worksheet, or select New Sheet and name it).
    Selecting a different worksheet for the chartSelecting a different worksheet for the chart
  4. Click OK. The chart will appear in the new location.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the new data. To fix this, you can adjust the data range. Simply click the chart, and it will highlight the data range in your spreadsheet. You can then click and drag the handle in the lower-right corner to change the data range.

Screenshot of adjusting a data range

If you frequently add more data to your spreadsheet, it may become tedious to update the data range. Luckily, there is an easier way. Simply format your source data as a table, then create a chart based on that table. When you add more data below the table, it will automatically be included in both the table and the chart, keeping everything consistent and up to date.

Watch the video below to learn how to use tables to keep charts up to date.

Challenge!

  1. Open an existing Excel 2010 workbook. If you want, you can use this example.
  2. Use worksheet data to create a chart.
  3. Change the chart layout.
  4. Apply a chart style.
  5. Move the chart to a different worksheet.

/en/excel2010/working-with-sparklines/content/