Selective columns in pivot tables

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
Quite new to excel pivot tables, I am compiling a simple data entry pivot table. Basically I got suppliers in my rows column, dates for my columns and 3 table values. VALUE ARE AS FOLLOWS an invoice total, nett invoice amount and vat paid. Is there a way that I can only display the vat paid and nett invoice amount in the summary totals column without having to see them populate for each date grouping in the table
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
You can Collapse the field.
You can Remove the Date from the Pivot Table. You can retain a Date Filter by Adding a Date Slicer while not having the Date in the Pivot Table.
 

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
Is there a way of not having to manually collapse fields every time you generate a new filter for a date range
 

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
You can Collapse the field.
You can Remove the Date from the Pivot Table. You can retain a Date Filter by Adding a Date Slicer while not having the Date in the Pivot Table.
I've tried the date filter, gives me my quarter choices that I like but it adds quarters up from different years e.g if I choose qtr1 and my data set is over 3 yrs say 2018 to 2020 it will sum every qtr eg qtr 1 2018 qtr 1 2019 qtr 1 2020 into one sum. I would want them each as a unique quarter instead!
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707

ADVERTISEMENT

I've tried the date filter, gives me my quarter choices that I like but it adds quarters up from different years e.g if I choose qtr1 and my data set is over 3 yrs say 2018 to 2020 it will sum every qtr eg qtr 1 2018 qtr 1 2019 qtr 1 2020 into one sum. I would want them each as a unique quarter instead!
Use the option, NO SUBTOTALS
 

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
Thanks spiller, i've got around it by using slicers, I've combined a yearly slicer with a quarterly slicer, this gives me my unique quarter now!
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707

ADVERTISEMENT

So you didn't have the YEAR in the Table as a Row or Column element?
Even when I use Slicers, I prefer to show all the fields that make up the Context of the values.

Capture.PNG
 

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
Where you have your quarters I have my suppliers so my pivot table looks slightly different
So you didn't have the YEAR in the Table as a Row or Column element?
Even when I use Slicers, I prefer to show all the fields that make up the Context of the values.

View attachment 335
Screenshot_20191121-095755_Office.jpg

My first column has the suppliers names this is why I have compiled it with combined year and quarter s slicers
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
The only small thing I might suggest is to use the Tabular style of Pivot Table.

Pivot Tables are just the beginning to reporting and analyzing Excel data with ease.
 

Fanta0575

New Member
Joined
Nov 19, 2019
Messages
13
Yes by creating pivot tables and exploring the possibilities with them I can see the possibilty for an exponential difference in studying data in a very dynamic way!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,510
Messages
5,548,488
Members
410,840
Latest member
Kar3ousse
Top