Forum Discussion
How to maintain chart formatting when altering the data range each month?
I'm working with numerous charts that are formatted with part column (# data) and part line (% data). Each month, new data is entered into the source spreadsheet. When selecting the appropriate data to update the charts, if not in consecutive cells, the formatting is lost and reverts back to either all colums or all lines. I want to maintain the combination of columns and lines in these charts when I select the data for a rolling 12-month period.
Suggestions? Thank you.
Hello,
what do you mean with "if not in consecutive cells"? Do you want to plot from a start month, then leave a gap?
When you select the chart, the data is highlighted. You can drag the sizing handles of the highlights to change what data is charted.
4 Replies
I know it's an old question, but it wasn't really answered.
There's an obscure and poorly documented setting in Excel called Properties Follow Chart Data Points. It means if you apply custom formatting to your chart, the custom formatting is linked to the cells containing the plotted data and not to the points and series in the charts. Confusing, I know.
If you turn off this setting, you can change the chart source data, and the customization will stick.
Go to File > Options > Advanced, and scroll halfway down to the Chart section. Then Uncheck the box for Properties Follow Chart Data Point for Current Workbook.
Then go check the box, because some things work better that way.
I wrote an article about this setting: https://peltiertech.com/properties-follow-chart-data-point/.
Hello,
what do you mean with "if not in consecutive cells"? Do you want to plot from a start month, then leave a gap?
When you select the chart, the data is highlighted. You can drag the sizing handles of the highlights to change what data is charted.
That's perfect - thanks so much!
Just to play with this - bit more dynamic variant using known pattern with OFFSET.
1) Assuming end of the rolling year shifted back from last date in the table on number of months defdined in D6
2) Name this cell as Shift
3) In Name Manager add definitions for our dates (X) axis and series as following
OFFSET defines the range for each of above
- starting from cell $B$7
- on 12 rows back from last date plus Shift
- which column to take (-1 = dates, 0 = Total Encounters, 1= next column # of times, etc.)
- 12 is height of the range (12 months)
- 1 is the width of the range (one column)
4) Select Data for the chart
and edit each of series and axis substituting cells range on defined before names, for example for Total Encounters
Before:
After:
Now chart shifts automatically if you add/remove date and/or change Shift cell value. File is attached
