Pivot table keeps adding subtotal by year when table refreshed

Question

Pivot table keeps adding subtotal by year when table refreshed

Anonymous

Hello community,

I have some pivot tables, the results of which feed into another table simply by referencing the cell.

My issue is that when I refresh the data, despite having set the underlying pivot tables to NOT show subtotals, some of the tables update with the year subtotalled. This has the knock on effect of throwing out my referenced cells in another table.

I am not asking for advice on how to do this more dynamically, as I'm happy with how it works currently, I want to know how to stop it from adding in the unwanted subtotals on refresh please.

I have already set my default layout to not show subtotals, tabular format etc.

Thank you in advance of any help you can offer,

Suzi

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

Answer accepted by question author

0 additional answers

Answer 1

Dear Suzi S,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

Based on your description, we understand that you want to prevent subtotals in certain columns of a pivot table.

I noticed you have already set my default layout to not show subtotals, tabular format etc. I would like to comfirm whether you tried the following settings, if not, try this:

File > Options > Data > “Disable automatic grouping of Data/Time columns in PivotTables."

If you still encounter this situation, could you please share the screenshot of your sample data with us so that we can conduct more specific tests? Thank you for your understanding.

Sincerely,

Tin | Microsoft Community Moderator