Show All

Hide All

You can choose different options in an Excel chart to emphasize your data in different ways. For example, when you're measuring sales revenue along with number of units sold, you can add a second axis to measure a portion of values differently from another portion. Here are some examples of the different ways you can measure and display data in Excel charts.

Compare and emphasize data

By default, Excel plots whatever you have fewer of—rows or columns—as the data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.). In the following example, Excel plots the sales years (the rows) as the data series, which emphasizes the comparison of sales by country.

You can change the default option and have the country data (the columns) used as the data series instead, so that the chart emphasizes a comparison of sales by year.

Add a second value axis to measure data

A secondary value axis is useful when the average values for different data series vary widely, or when you are plotting different types of data in one chart. For example, to show both the number of homes sold each week and the average sales price per week, plot the number along one axis and the average price along the second axis.

 Tip   To help distinguish the secondary axis, you can change the chart type for just one data series. For example, in the previous bar chart example, the data series on the secondary axis is shown as a line.

Reverse the order of categories or values

Excel plots categories and values in the order in which they appear on your worksheet. If you want them in a different order, you can reverse them.

In the following example, categories are plotted in the order in which they appear on the worksheet—April, March, February, and January.

If you reverse the order, the months appear in chronological order as shown in the following chart.

 Note    You could also reverse the order of the values so that the scale shows 0 at the top and 800 at the bottom.

Change the order of data series

Excel plots data series in the order in which the data appears on your worksheet. You can change the order of the series to make your chart more effective. In the following example, a 3-D column chart shows sales for each state, plotted in the order in which they appear on the worksheet.

To make the data series for AK and OR visible—without reorganizing the worksheet data itself—you can simply change the order in which the data series are shown.

Control how empty cells are plotted

On the Chart tab in the Options dialog box (Tools menu, Options command), Excel provides three ways to plot an empty cell (or data point (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.)) in a data range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.):

• Plot nothing for that data point  When you choose Not plotted (leave gaps), Excel leaves gaps in the line for all empty worksheet cells in a data series, making the line segmented.
• Plot the data point as a zero value  When you choose Zero, Excel treats blank cells as zeros, so that the line drops to zero for zero-value data points.
• Interpolate the data point  When you choose Interpolated, Excel interpolates data points that represent blank cells, and fills in the gaps with connecting lines.

By default, Excel does not plot a data point for an empty cell, which causes a break in a data series. For example, in the following chart, there is no fuel expense data for December, so no data point is shown.

However, you could have Excel interpolate, or estimate, the data point. In the following chart, Excel averages the data for November and January to estimate the missing December data.

If the empty cell actually reflects a zero value, you could also have Excel plot a zero for that data point, as shown in the following example.