Excel Data Interview Series: Pivot Tables in Excel

Hey there everyone 😄. Welcome to Day 4 of our Excel Series Week. Read up yesterday’s discussion on Formulas in Excel. Today, we’ll be discussing Pivot Tables aka The Best Summary Tool in Excel.

As always, a case study. You’ve been given a dataset containing details of your nationwide stores. Each row contains details of a different store, and the column have details of sales/revenue/profit figures and percentages for each month. Now you’d want to analyze only the entire details of your biggest store in City ABV without wading through the details of the other stores, and what would be the easiest and fastest way to do this? Yup, you guessed right: by using a Pivot Table.

Now, what is a Pivot Table?

According to Career Foundry, “a pivot table is a summary tool that wraps up or summarizes information sourced from bigger tables. These bigger tables could be a database, an Excel spreadsheet, or any data that is or could be converted in a table-like form. The data summarized in a pivot table might include sums, averages, or other statistics which the pivot table groups together in a meaningful way.”

From the ‘Encounters’ table in our SQL for Healthcare project, let’s get the Encounter Class, the number of patients that registered under this class, and the sum of payments by patients.

Why use Pivot Tables?

Pivot tables help data analysts to summarize large datasets into a concise and meaningful table. As a data analyst, you’ll absolutely need to make use of Pivot Tables to get insights from your data and create reports efficiently. They are great for creating summary statistics with any metrics you want, including showing only the data you are interested in by applying filters/slicers.

Let’s generate some insights from our Marketing Campaign dataset using pivot tables.

Here, we made use of a pivot table and pivot chart to visualize the total spent on wines, meat products and fruits by marital status. We can see that the Married customers spend more, followed by the customers in the ‘Together’ category. This means that customers with partners spend more than those without partners.

From this second pivot table and chart, we see that the education class with the highest income is the PhD class, with an income of 162,397.

How can one insert a Pivot Table into a report?

Well, the good news is that this efficient tool is quite easy to handle. Before you insert a Pivot Table, though, you need to make sure that your data is organized in a proper format. This means it should be in a tabular data format where:
• Each column has a header (to enable the Pivot Table create appropriate fields)
• There are no empty columns/rows
• There are no total/subtotal rows

To insert a Pivot Table, go to the ribbon of your Excel report and click on Insert > PivotTable. Then click on your selected fields, and place them appropriately in the Filter, Rows, Columns, and Values sections for quick analysis.
Note: Before using this tool, you’d want to convert your Excel worksheet into a table, so when new data is added later on, your Pivot Table will be automatically updated the moment you press ‘Refresh’. To do this, click on the ‘Table’ button (or use Ctrl + T).

While we’ve discussed some of the awesome benefits of pivot tables, other uses include:
• They help to provide quick insights without you having to write complex formulas
• They are fast & easy to create and use
• They help to find relationships between your data

One helpful way to improve your Pivot Table is to make use of slicers instead of drop-down filters. All you need do is right-click the selected field (column) you’d want to use, then select the ‘Add as Slicer’ option.

Another helpful tip — To connect a slicer to another Pivot Table,
i) Select the Pivot Table that has no slicer
ii) Go to PivotTable Analyze on the ribbon
iii) Under Filter Connections, place a checkmark for the slicer and click OK.
Tada! One slicer for all.

😁😁

We’ve come to the end of today’s discussion and I hope you learnt a thing or three from it. And hey! You know the drill already- show this lots of love by clapping and commenting. Be here tomorrow for our discussion on Excel Macros. 🤗