Excel keeps changing the Pivot Table row Nr. labeling

Hi Guys,

After an extensive search on the web I could not find a solution to my problem, which actually makes this my first question on this forum. Any help will be greatly appreciated.

Excel 2010 has two ways of reporting/labelling/filtering my Pivot Table rows:

1st way (desired): After applying a column header filter the pivot table data, the row numbering remains sequential (1,2,3..) I have formulas which refer to this row numbers, and I want to reference cells as such (see picture 2)

Problem: Sometimes (seemingly random) excel changes this way of reporting/labelling/filtering to the following:

2nd way: (non desired): After applying a column header filter the pivot table presents the data with fixed row numbering, so every row entry has a fixed row number (see picture 1). If I want to use this reference this cells I can use VBA to loop over the visible cells only, but this greatly reduces performance of my worksheet as it needs to loop over all 150.000 cells in that column. This is not an option for me.

I have saved all my previous files so I have compared the differences between the a pivot table using the 1st and 2nd style. The only difference I could find is the way of column filtering; The 2nd way does not include label filtering, only one type of filtering (depending on the type set in the query: text, label, date etc, see picture 4). The 1st way includes 2 ways of filtering (label/text/data and value, see picture 3 and 5).

I want to find a way to prevent this change of reporting style which leads to different row numbering. I have yet to find an answer to this on google. Even a simple description on how Pivot Tables handle their filtering and row numbering would be helpfull. I think it has something to do with the handshake of the basic filtering function (which is a not a pivot table function, which is applied later) with the pivot table. Or it has something to do with the file type set in the power query settings. This still remains elusive to me.

Pictures of the row numbering (desired vs non-desired) and 2 ways of filtering are found in the attachments of this post.

Kind regards,

Frank