Excel pivot table: display rows filtered, but keep total for all rows (unfiltered)

Question

I have a pivot table where I filter the data by month. The thing is I need a total column for all months. Is it possible to somehow ignore a select column from being filtered? Or maybe there is a way to achieve it using calculated fields.

Example:

October  500 clicks
November 600 clicks
December 1000 clicks

I use filter: November

I get a table:

google | 600

What I want to get after using this filter:

google | 600 | 2100

If I understood well with so little, you want to keep the total unfiltered while filtering what is displayed? If it is that, you canNOT, sorry! ;) — R3uK
Could you please be more specific? Maybe try to describe your use case, i.e .what you want to achieve with it. There may be a different way to achieve your goal - but @R3uK is right, you cannot filter items but keep the total unchanged. On other side, this is reachable by plain filtering, not using the pivot table. — Honza Zidek
@R3uK I've added a screenshot. Total unfiltered or calculated fields or anything, I just don't know how to achieve what I want. — Edgard Gomez Sennovskaya
it's unfortunately not possible with pivot table, but you may find workaround, e.g. hide the columns you don't need — Máté Juhász

Honza Zidek Honza Zidek · Accepted Answer · 2015-12-02T12:14:43

It is not possible directly. As the closest workaround I am aware of you may use the following:

Add a calculated column to your source data, calculating the sum of all the respective values

Explanation of the formula =SUMIFS($C$2:$C$9,$A$2:$A$9,A2):

Sum all the values from the C column, where the respective value in the A column matches the value in the A2 cell. So it is effectively equivalent with

= C2 + C4 + C6 + C8

Create the pivot table with an extra field Total, displayed as Max (or Min, it doesn't matter as it always contains the same value):

Without filtering it contains all the months:

Now you may filter your months:

Excel pivot table: display rows filtered, but keep total for all rows (unfiltered)

1 Answers