Excel Video 99 covers how to adjust your chart to handle gaps in your data, hidden data, and filtered data. If for some reason you’re missing some data that you want to include in your chart, the Hidden and Empty Cells Window (accessed from Select Data Source) allows you to have gaps in your chart, put zeroes in place of the missing data, or, if you have a line chart, you can have Excel draw a straight line between data points where the data is missing.
You’ll see in the video that having Excel draw straight lines can be misleading, so you need to be careful how you use that feature. It may be more appropriate to have Excel draw straight lines when the missing data represents one day instead of one year.
You may also find it helpful to temporarily remove data from a chart by hiding the relevant rows or columns. In other situations, you may want to show data in a chart you don’t have room for in your spreadsheet. The Hidden and Empty Cells Window will help you solve either situation. The hidden cells logic also works with filtered cells. If you’re filtering data in a table, you can easily choose whether or not to have your chart reflect the filtering.
