Pivot Tables demystified

OK, so you’ve been asked to do a Pivot Table for the first time? And just like everyone else, you feel somewhat intimidated; “what’s going to happen to my data if I screw up my Pivot Table?”; “Can I undo my changes?”; “Can I delete my Pivot Table?”; “How long does it take to create a Pivot Table?”; “What is a Pivot Table in the first place?”

Let’s give the short answers first: “Nothing”, “Yes”, “Yes”, “30 seconds to an hour or more”, “A summarized view of your data grouped by selected fields”.
And yes, as mentioned in other posts, most job interviews for admin, accounting or office positions where Excel is used heavily will include a question on Pivot Tables – so be prepared!

The first thing you need prior to creating a Pivot Table is a data set in rows and columns, and preferably a normalized data set; by normalized, I mean a clean data set where individual records are populated in rows and unique field headers are in columns. Once you start working with very large data sets (and some will exceed 1 million rows) you will often find data which is not formatted correctly, numerical fields mixed up with alphanumerical, blanks, numbers formatted as text, duplicate column headers, duplicate records, even spaces instead of blanks, etc. If you were asking “How is it possible to takes as long as an hour to build?”, data integrity is the main culprit.

The next thing you need is an idea of how you want your data to be reported. This sounds obvious, but many people struggle with this visualization stage and are simply overwhelmed diving into execution mode too soon. This is when you pause and reflect how you’d like your summary report to look like.

Once you’ve reached this point, it’s all downhill! The Pivot Table wizard will guide you through which fields to place in which section. There are four sections to a Pivot Table:
  1. Filter section: this allows you to filter your pivot in fields. This may not be needed for small data sets, but becomes relevant for larger ones with multiple fields.
  2. Sum (Values): This is where the Pivot Table calculates the summary for each field. The Values section can be set to SUM, COUNT or a number of other options.
  3. Rows and 4. Columns: this is where you place your fields (how you want to group your data). As a rule of thumb, consider placing in rows the fields which have most items listed, and in columns the ones which have the less.

That’s it!! You’ve created your first Pivot Table. You can now explore all the different options and variations of Pivot Tables in the Options Menu, change the Value Field Settings; you can create a Pivot Chart from the table you just created, insert slicers or a timeline, change layout and formatting, etc.

As mentioned earlier, a Pivot Table does nothing to the data set itself; it simply looks at it and summarizes it. So, don’t worry about deleting or changing your Pivot Table; your data is safe! Have fun exploring all its capabilities.


Yves S.

Advanced Excel, Statistics and Operations Management

500+ hours
if (isMyPost) { }