Pivot Tables in Excel: A Powerful Tool for Data Analysis
Data Analysis is an umbrella term used whenever professionals’ endeavor to extract key, meaningful insights from raw data. It involves tedious and repetitive crunching of numbers all in different ways, just to explain what could be going on behind the scenes. It is as much an art as it is a science.
It has been nearly a month since I dived headfirst into this vast field, working as a Data Analyst Intern in Scaler Academy. In that time, I have barely begun plumbing its depths. My days are filled with writing one SQL query after another in Metabase to collect data. Only recently have I actually started doing some analysis on the data I queried, and now truly understand just how difficult this might be.
Trying to crunch numbers in Metabase is a pain, constantly fighting with Postgres syntax again and again — even more so with Metabase’s 1 million row limit. And so, I was forced to fall back on the bread and butter of this industry: Microsoft Excel
But at times, I felt that even wasn’t sufficient. But then I discovered: Pivot Tables.
A pivot table doesn’t change your underlying data in any way; it just changes or pivots into another (summarized) view; making data analysis just that much easier.
Here’s how to make your first ever table:
1. Select the data you want to summarize.
2. Click on the “Insert” tab in the Excel ribbon.
3. Click on the “PivotTable” button.
4. In the “Create PivotTable” dialog box, select the range of data you want to summarize.
5. Choose where you want to place the pivot table (a new worksheet or an existing one).
While it’s really easy to set up a pivot table, that’s not why it is such a powerful tool. On many occasions in data analysis, we want to summarize different values on the basis of multiple fields. A pivot lets us do this in a very efficient way using the PivotTable Analyze Tab / Field List:
· Drag fields that you want values to be summarized on into the Rows area of the Field List. You will get summarized values on the basis of the fields in the Rows area and multiple fields can be broken down into groups and parent groups — allowing a more comprehensive analysis. They can be minimized and maximized at the click of button, allowing you to go from a more detailed / specific analysis to more of an overall picture at the drop of a hat.
· Drag fields that you want a summary of into the Columns area of the Field List. You can choose which function to summarize it as — Sum, Average, Variance, Count, Count Distinct and others. In true Excel fashion, you can even write your functions or formulas for summarization! These fields will then be summarized on the fields in the Rows area.
Not only this, but Pivot Tables supports filters as well! Just add which field you wish to filter values on into the Filters area of the Field List, configure some settings, and poof! No need to bash the keyboard for filtering data in Excel ever again!
And the best part about Pivot Tables? Their parameters can be modified on the fly — almost like a dashboard — making it the ultimate tool for data analysis.
But that’s not all. Pivot Table also supports timelines and slicers, 2 amazing features:
· Timelines give a visual interface to filter values on datetime: years, months, quarters -you name it!
· Slicers are the same but can be used for any field in any combination.
Together, they can be used to make user-friendly interface for data analysis that is easy to use as modern dashboards. And did you know that Pivot Tables can be converted into Pivot Charts — a visual representation of the data? They are basically Excel dashboards. But that’s a topic for another blog.
Thanks for reading, and if you want to know more, check out the official documentation.
