Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 59 Hack Chart Data So That Blank Cells Are Not Plotted

Excel treats blank cells as having a value of
0, which can result in some ugly charts. Your chart can suddenly drop
off, leaving you with a chart that no longer accurately paints the
picture you are trying to convey. With the hacks in this section, you
can ensure that blank cells are not plotted.

You
can prevent blank cells from being plotted in two very easy ways: by
hiding rows or columns, and by having cells return a value of #N/A.

Hiding Rows or Columns

Set
up some data as shown in Figure 5-40, create a line
chart, and see what it looks like if you have 0 values plotted.
Highlight the range A1:B12, then open the Chart Wizard and set up a
line chart as an object in the current worksheet.

Figure 5-40. Data with line chart plotting 0 values

The chart in Figure 5-40 is plotting sales figures
by month. This means that if the current month is April, the chart
will plot eight months of empty cells, as the May through December
figures are not yet available.

To
avoid this, simply hide rows 5:12 (May:Dec). Excel will not plot
hidden rows, and thereby will produce the result shown in Figure 5-41. To hide these rows, select them, and then
select Format Rows Hide.

Figure 5-41. Data with rows 5 through 12 hidden, and a chart plotting January through April figures only