What Is a Pivot Table?
The buzzword today for data analytics is " data insights". What are data insights, well they in short are the understanding an individual or team gains from analyzing and interpreting data.
A pivot table is a concept of summarising data to give insights that were created by the Pita Salas while working with Lotus Advanced Technologies in 1986. Today most people will know Pivot tables from Microsoft Excel who are widely understood to be the inventors of the pivot table we know today and that I will be training you to master in this article
A pivot table is a table you can use to display the summary of a specific data set in a condensed manner. The data can come from a database external to Excel, an Excel worksheet, or a business intelligence program.
Pivot tables consist of four attributes:
- rows
- columns
- pages
- data fields.
In addition, Pivot tables create a 3-dimensional data view that uses statistics such as totals, averages, counts etc to produce an output. that allows the user to view the differences in a vast set of information.
You can move these components to expand or group the selective data in real-time. This arranging and rearranging (pivoting) of statistics is where they got their name.
Why Pivot Tables
The major reason why people create Pivot Tables is to transfer information that supports their story with data that is easy to understand and well-structured.
They are the perfect solution when you need to summarize and analyze large amounts of information. That way, you get to put your raw data into meaningful insights in a single table – and this brings many benefits. Some of them are:
- Simplicity. It’s very simple to create and customize basic pivot tables. There is no need to learn complex formulas, as you can set one up in a few steps using the drag-and-drop tool.
- Speed. You can create an efficient, helpful report with a pivot table in a matter of minutes. Even if you are pretty good with formulas, pivot tables require much less effort and are faster to set up. Furthermore, you can speed up the process by organizing your source data before importing it to a pivot table.
- Flexibility. Unlike with formulas, pivot tables don’t get you locked into a singular data view. You can quickly rearrange the appearance to meet your needs. You can even clone a pivot table to build a separate view.
- Accuracy. As long as you set up a pivot table correctly, you can rest assured that the results are accurate. A pivot table will usually highlight problems in the data faster than any other tool.
- Formatting. A Pivot table can automatically apply consistent number and style formatting, even as data changes. Therefore, you don’t need to worry about switching data between columns and rows.
- Filtering. Pivot tables contain several tools for filtering data. You can use them to define the particular data sets you (don’t) want to display in the table. As you set up the filter, any associated visualizations or metrics also update to reflect the filtered data.
- Charts. Once you make a pivot table, you can easily create a pivot chart. It is the visual representation of a pivot table.
When Do You Use a Pivot Table?
If you find yourself feeling like you need data in real-time and it needs to move and be sliced and diced constantly, basically that it's not static, then you will need to pivot the table. Typically you would be looking to:
- Run automatic calculations on summed or counted values
- Create %’s of totals
- Segment data by date, the user, or other variables and calculate totals
- Organize data into columns and rows with automatic calculations that would otherwise be impossible with Excel
How to Use Pivot Tables?
From here on I want you to think of pivot tables as reports that provide an interactive view of your data with no formulas and relatively low effort. A snappy, efficient pivot table is worth its weight in gold.
However, for those non-technical Excel users, the Thought of Pivot Tables can be daunting. I big mistake people make is to portray pivot tables as requiring advanced Excel skills. Nothing could be further from the truth, as you will learn from this article Pivot tables are a basic Excel function and do not demand any Excel expertise
The key is to dive and come with me on this journey. Once you know I’m confident you’ll use them daily.
Let us get started:
Excel pivot tables are a very useful and powerful feature of MS Excel. They are used to create instant summaries, reports and data analysis from your raw data.
In this article, learn all about how to create an Excel pivot table and customize it.
A pivot table turns your data into report format. Here is a sample Pivot table from sales data, showing total sales by region.
We will use the 2019 sales data of a fictional company. This data contains 466 rows of sales information in columns – Month, Salesman, Region, Product, No. Customers, Net Sales, Profit / Loss. Here is a preview of our data. You can download the file here.
1.0 To create a pivot table showing total sales by region, follow these steps.
- Select any cell in the data.
- Go to Insert ribbon and click the “Pivot Table” button.
3. Click ok on the next screen.
4. You will be taken to a new spreadsheet with a blank Pivot Table canvas. Here, using the Pivot Table Fields panel set the “Regions” field to the row label area, “Products” to the “Filter” area and “Net Sales” to the values area. See the below illustration.
5. Your pivot table will be ready. We can see that the “West” is our best region. This is why Pivot tables are easy for finding answers to common business questions.
6. You can add fields to both the “Row” and “Column” label areas of a pivot. Such Pivot Tables are normally called two-dimensional pivots. Here is a demo of a two-dimensional pivot table showing Total Sales by Region & Sales Person.
7. You can also add more than one item to the “Row” or “Column” label area. This creates a multi-dimensional Pivot Report. Here is one such pivot report showing total sales by Region, Sales Person & Product for selected months.
By default, numbers in Pivot Tables tend to just look like zip codes, without any proper formatting. This is easy to fix though. Simply right-click on the values and use “Value Field Settings” to set up the formatting. To set currency formatting for our Total sales by region Pivot Report,
- Go to value field settings
- Click on the Number Format button
- Set up the formatting to “Currency”
- Done.
You can easily sort pivot reports by ascending or descending order of the value. To do this, just right-click on the value, select Sort > and specify the order.
Here is an example of a sorted pivot report of the Number of customers by a Salesperson.
You are looking at Regional total sales and want to know what the total is for just the “RapidZoo” product. You can do this by filtering the pivot table. Excel offers two powerful ways to filter Pivot Tables
- Report filters
- Slicers
Both methods are illustrated below. Read on to learn how to use them.
A report filter is a great way to restrict the data that is flowing to your pivot. To set them up, just add the field to the “Filters” area in the fields panel. Now, using the filter button next to “Product”, select the product you want.
Here is a quick demo of report filters in action.
There are a ton of cool features in Excel Pivot Tables, but slicers are hands-down the best feature. At least, that is what I think. They make filtering and ad-hoc data analysis a breeze.
A slicer is a visual filter. You can add a slicer on any field by right-clicking on it from the fields panel. See the illustration “Adding filters to a pivot report” from above.
Once you have a slicer on Product, simply click on any product name to see the report for that.
Here is a quick demo of the Pivot Table with slicers.
Apart from report filters & Slicers, Pivot Tables also allow you to filter by a field or value.
Field or Label Filter: If you don’t want to see the “Middle” region in a row label area, just click on the filter button next to “Row Labels” and uncheck the region. This type of filtering is called Label Filtering.
Value Filter: If you want to see just the top 2 regions by total sales, then you need a value filter. Simply go to the filter button next to row labels and using value filters, apply a top 10 filter but set it to top 2 values by “Sum of net sales.”
The default calculation in Pivot Tables is SUM for number fields and COUNT for all others. But you can also customize the calculation easily. Just right-click on the value field and choose a different type of summary from right-click menu.
Here is a quick illustration of how to change the calculation type from “SUM” to “AVERAGE”.
By default, Excel Pivot Tables are in a compact layout. This means, if you add multiple fields to the row label area, they will all be shown in the same column, with indentation.
You can change the layout of a pivot table to other formats too.
- Compact form (default)
- Outline form
- Tabular form
You can change the layout from the Pivot Table Design ribbon.
Here is an example of the same Pivot Table in both Compact and Tabular layouts.
