Microsoft Excel —

Changing the Order of Data Series

If a table has more than one series, Excel charts it in the order it appears on your worksheet (from left to right if your series are arranged in columns, or from top to bottom if they're arranged in rows). In a basic line chart, it doesn't matter which series Excel charts first the end result is still the same. But in some charts, it does make a difference. One example is a stacked chart, in which Excel plots each new series on top of the previous one. Another example is a 3-D chart, where Excel plots each data series behind the previous one.

You can easily change your data series' order. Select your chart, and then choose Chart Tools : Design > Data > Select Data. Now select one of the series in the Legend Entries (Series) list, and then click the up or down arrow buttons to move it. Excel plots the series from top to bottom.

Changing the Way Excel Plots Blank Values

When Excel creates a chart, its standard operating procedure is to ignore all empty cells. The value of 0 doesn't count as an empty cell and neither does text (Excel plots any cells that contains text as a 0).

So what's the difference between an ignored cell and a cell that contains the number 0? In some types of charts, there's no difference. In a bar or pie chart, the result is the same you don't see a bar or a pie slice for that data. However, in some charts, there is a difference. In a line chart a 0 value is plotted on the chart, but an empty cell causes a break in the line. In other words, the line stops just before the missing data, and then starts at the next point. This broken line indicates missing information.

If you don't like this behavior (perhaps because your empty cells really do represent 0 values), you can change it. Select your chart, and then choose Chart Tools : Design > Data > Select Data to get to the Select Data Source dialog box. Then, click the Hidden and Empty Cells button, which pops open a dialog box with three choices:

Gaps
Excel leaves a gap where the information should be. In a line chart, this breaks the line (making it segmented). This option is the standard choice.

Zero
Excel treats all blank cells as though they contain the number 0.

Span with line
Excel treats all blank cells as missing information and tries to guess what should go in between. If a line chart goes from 10 to 20 with a blank cell in between, Excel interpolates the data point 15 and plots it.

You can also switch on or off the "Show data in hidden rows and columns" setting to tell Excel whether it should include cells that are hidden when creating a chart. This setting determines how Excel deals with data when you use filtering in a table, or when you explicitly hide rows or columns using the Home > Cells > Format > Hide & Unhide menu. Ordinarily, Excel treats these missing values just like blank values, and ignores them.