a self compressing chart

[tdhlmn]

I am collecting daily data readings for water and electric use: my columns are, from left to right, date: meter reading: daily usage(calc'd): mnth avg to date (calc'd).
I then graphed the data, no problem till into the 2nd or 3rd month with 90 rows of data. solution: hide all but the last day of the month - YEA ----uh bummer - the date is automatically adjusted by excel for major or minor divisions with no respect to hidden rows.....OK - added column to convert date to text - YEA this works.

So any thoughts on how to have the chart automatically compress the month but keep the last month on a daily reading?

 

[Andrew Poulsom]

Welcome to MrExcel.

Right click the x axis and choose Format Axis. Change the axis type from Date to Text (or Category).

For automation you can use AutoFilter. In E2 enter:

=OR(TEXT(A2,"mmyyyy")=TEXT(MAX(A:A),"mmyyyy"),A2=DATE(YEAR(A2),MONTH(A2)+1,0))

and copy down. Then AutoFilter that column for TRUE.

 

[tdhlmn]

Thank You, some times hard to see the forest for the trees, thanks for the fresh perspective.