Last x Months charts with dynamic label for month and year

**CariocaExcel** · 07-16-2019, 01:23 PM

Guys,

I have been working a series of chart and I recently I stumble upon a chart that saves some space on the month and year; Months will start with the first letter and the year will show only one time under the letters of each month.

Trying to apply this to my reality was a little bit more complicated than I expected, since I can´t manage to have Year and Month to be dynamic along with rest of the data.

Does anyone here have clue to set a dynamic named range for the XValues to accommodate this? My chart uses the last 12 months and the formula is pretty simple:

Formula:

Please Login or Register  to view this content.

This is the chart example that I intend to use:
Chart_Month_Year.JPG

Appreciate all help!
CariocaExcel

**CK76** · 07-16-2019, 01:46 PM

Without knowing how your data is organized. Hard to help you.

I'd recommend uploading sample workbook with desensitized data.

Though I'd strongly recommend using 3 letter code for month. With single letter, there's nothing that differentiates March from May, except for the position of data. This is poor practice in any sort of data visualization/analysis.

If 3 letter code can be used... you can use Pivot Table combined with Excel table to make it more dynamic.

Sample attached.