Starting with Pivot and things you can start doing with itShashwat Mahaseth
“In God we trust, all others must bring data.”
W Edwards Deming
Statistician, professor, author, lecturer, consultant and a great management thinker
Use of data analysis to aid decision making lies at the center of the management principles in the current business age. There are a plethora of tools that help people analyze data and make actionable plans on it. However, across industries, people swear by Excel as the most widely used data analysis tool invented, ever.
One of the most critical data analysis module within excel is Pivot Table. If you are wondering what that is, or have just heard about it in passing remarks NOW is the right time to educate yourself about this highly powerful and intuitive data analysis tool within excel.
What is a Pivot Table?
A Pivot Table is an excel tool that helps you summarize and analyze large amounts of data in a fast, interactive and intuitive way. You can use Pivot Table to analyze data at a granular level of detail, and reveal the answers to both anticipated and unanticipated questions- all within few clicks.
How to create a Pivot Table within excel?
To create a pivot table manually. You need to follow the following steps
- Click a cell in the source data or table range.
- Go to Insert > Tables > PivotTable.
- Excel will display the Create PivotTable dialog with your range selected. In this case, we are using a table called “Sales Data,” and the range of the data is from cells A1 to C16.
- In the Choose where you want the PivotTable report to be placed section, select New Worksheet or Existing Worksheet. For Existing Worksheet, you will need to select both the worksheet and the cell where you want the PivotTable placed.
- If you want to include multiple tables or data sources in your PivotTable, click the Add this data to the Data Model checkbox.
- Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.
Now you are ready to tinker with your 1st Pivot table.
Sample Illustration of what you can achieve with the Pivot Tables
Now comes the exciting part. Let’ see the below sales data on the pivot table, and what we can accomplish. I will demonstrate the various tables and corresponding charts that are made possible with the sample data. The Pivot tables let you do this and a lot more quickly and efficiently.
Table 1: Summarizes the Monthly sales across categories and calculate the totals sales
Chart 1: The corresponding chart of the Pivot Table is
Table 2: Summarizes month wise sales in each category
Chart 2: The corresponding chart of the Pivot Table is
Table 3: Summarizes sales across each category
Chart 3: The corresponding chart of the Pivot Table is
As an exercise, you can try and make these summaries yourself. If this is the first time you are venturing into the world of pivot tables, I bet you will be amazed at the versatility of this tool. You can find the sample data that I have used here.
Things you can achieve with Pivot Table
- Pivot data in all possible ways, i.e. moving rows to columns, vice-versa, setting hierarchy and lots more to see different summaries of the source data.
- Large datasets can be queried for the required data in a fully customizable and intuitive way.
- Summarize data by categories and subcategories, and create custom calculations and formulas. By default, the pivot table can subtotal and aggregate numeric data.
- Analyze data at a granular level. Excel allows you to expand and collapse data at every level which makes it easy to zoom to the data you want to see of the top level summary
- Do all kinds of data operations on the pivot table like filtering, sorting, grouping, conditional formatting, etc. to drill the data and focus only on what you want from the data
- Presenting data concisely and attractively in the form of tables, graphs etc.
This is just a glimpse of what Pivot can do. If you quickly want to make big strides in the usage of Pivot Table and become a data analysis star of your division, I highly recommend you to explore “Master Pivot Table: Turbocharge Your Data Analysis Skills”