How do I hide part of a data series in a chart?
We have a chart with 2 data series - Budget & Actual. We want to show all 12
months of the budget, but only the year-to-date months of the actual. The problem is the data cells for the future actual amounts have formulas in them so the chart use the result and plots the line. We do not want to delete the data (which will require manual input each month, we just want the actual data series line to not show for the future periods. We tried using IF statements with 0, "", " " and an alpha character, but these have not worked. -- Jeff |
How do I hide part of a data series in a chart?
Hi Jeff:
You can set up your actual data as a dynamic named range series, using the OFFSET function. Many MVPs give tutorials how to do this on their websites, see for instance: http://www.tushar-mehta.com/excel/ne...rts/index.html http://www.stfx.ca/people/bliengme/e...ps.htm#Dynamic http://peltiertech.com/Excel/Charts/Dynamics.html http://www.contextures.com/xlNames02.html GL, Henk "Jeff Bremer" wrote: We have a chart with 2 data series - Budget & Actual. We want to show all 12 months of the budget, but only the year-to-date months of the actual. The problem is the data cells for the future actual amounts have formulas in them so the chart use the result and plots the line. We do not want to delete the data (which will require manual input each month, we just want the actual data series line to not show for the future periods. We tried using IF statements with 0, "", " " and an alpha character, but these have not worked. -- Jeff |
All times are GMT +1. The time now is 09:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com