When a header title in the source data is changed, can a pivot table automatically select the changed header from the field list and insert into specific pivot table area, without using macros?

Question

When a header title in the source data is changed, can a pivot table automatically select the changed header from the field list and insert into specific pivot table area, without using macros?

Anonymous

Hello

I have data with month headers similar to what you see below. Every month, the old month is omitted and new month is added. And the no of columns remain the same.

This month's data:

Part	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24
A	1	11	21	31	41
B	2	12	22	32	42
C	3	13	23	33	43
D	4	14	24	34	44

Next month's data

Part	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24
A	1	11	21	31	41
B	2	12	22	32	42
C	3	13	23	33	43
D	4	14	24	34	44

I inserted a pivot table and dragged all months into Values area.

Now is it possible that when the month headers change next month in the source data, the pivot table automatically selects the newly added month and insert it into value field area? Is it possible to do this without using any macros?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

This is easily achieved with Power Query. Unpivot the 6 column data table to three columns. One for Part, one for the Value and one for Month (based on a real date value). Load the flattened data directly into a Pivot Table and format the month labels to your liking.

The flattened data would look like this in PQ:

And the Pivot Table will come out like this:

Next month, dump the new data in the same Excel table and refresh the query.

Answer 2

No, and even using macros it's not easy to accomplish.

The usual workaround to such tasks is to prepare the data with headings like this:

Month0, Month1, Month2, Month3, Month4

Now you can run your analysis in your file without issues, and in the final report write a simple macro and overwrite the headings in the pivot table report with the month names.

Andreas.

Share via

When a header title in the source data is changed, can a pivot table automatically select the changed header from the field list and insert into specific pivot table area, without using macros?

2 answers