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

   

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