Microsoft Office Tutorials and References
In Depth Information
Changing the Way Excel Plots Blank Values
Chart Types
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 (Figure 19-20), 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 again 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 hidden cells 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.
Chart Types
Although there’s a lot to be said for simple column charts—they can illuminate
trends in almost any spreadsheet—there’s nothing quite as impressive as successfully
pulling off the exotic bubble chart. This section offers a “greatest hits” tour of the
most popular charts that Excel offers.
Search JabSto ::




Custom Search