Charts: not plotting zero series

[travis]

I am building a retirement projection spreadsheet which shows income
over a long period.

Some of the series that I may want to chart for some scenarios are
empty in other scenarios.

For instance, I would chart the salaries of both spouses during the
projection period, but if one partner doesn't work the salary series
for that person will contain only zero values.

There are lots of different forms of income to chart including work and
business income, rent, dividends, bank interest, social security,
pensions, annuities etc. For the majority of scenarios most forms of
income will be zero for the duration of the projection.

To reduce clutter, rather than having a chart with twenty series, 15 of
them empty, I want to chart only the five series that at some point
have income in them. THis doesn't necessarily mean just in the first
year, for instance at the start of the scenario there will be no age
pension and at the end of the scenario there will be no salary.

How do I do that?

Travis

 

[Jon Peltier]

Set up your time series in rows, so each row is a different income stream
and the top row has your dates. At the beginning or end of the data put the
total for that row into a column. Select the whole range and go to the Data
menu > Filter > Autofilter. Now you can filter the rows by the total column,
showing only rows with a total > 0. When you make the chart, include all
data (before hiding zero rows); by default, hidden data isn't charted, so
autofiltering is a pretty handy way to hide certain series from the chart.

- Jon