Newbie pivot table

tim

New member
Joined
Jul 24, 2013
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2016
Hello there,

I only recently found out about the pivot capabilities beside my 20 and some years experience with excel vba etc ..
I somehow cannot get the pivot i want.

This is the situation:
I have a table for work with 4 columns

Name, Certificate Name, Expiry Date and a column with a formula calculating the number of days expired

There is hundreds of certificates (with at least 25 different certificates) in the table so it would be nice if I could have the following pivot

- How many people obtained some certificate
- For each certificate how many expired (and how many are still valid)

The first is obvious and I got, the second I cannot find how ... does it have to do with the Value Field Settings ??
Any pointers would be great!

Thankx
 
Ok ... attached a sample.
Already did the first Pivot ... now I want to know how one goes about ... counting the expired and non-valid certificates.
Or is this abusing Pivot?
 

Attachments

  • Veiligheidsfuncties-test.xlsm
    145.6 KB · Views: 16
Added column F to your source data and created a pivot or two on the Pivot sheet in the attached.
 

Attachments

  • ExcelGuru9438Veiligheidsfuncties-test.xlsm
    149.9 KB · Views: 10
Added column F to your source data and created a pivot or two on the Pivot sheet in the attached.
THank you ... ok this is clear. SO there is no way to add criteria to the pivot in order to avoid unnecessary columns?
 
I suspect there might be with Calculated Fields/Items but (a) I'm not up to speed on them and (b) adding a column to the source data is robust.
If you're talking of unnecessary columns in the pivot table, you can reduce those by putting a field into the page field, or adding a slicer instead.
 
Back
Top