Apart from systems, Spreadsheet applications are still used in many industries to fulfill many complex requirements. Especially in accounting and mathematical fields.
What is a pivot table and how does it work?
Let’s explore a powerful feature in Excel called ‘Pivot Tables’, a quick and easy way to prepare valuable information using data sets. What is a pivot table and how does it work? How to create a pivot table? everyone asks these questions and many of them think it’s a complex feature in Excel. But it is not. Let’s explore how it works in a simplified way.
Prerequisites
- Microsoft Excel 2007 or later version.
- A set of data
Sample Problem
Let’s consider the following Sales Data in ABC Company.
This data set represents Sales in the ABC Company by each quarter by salesmen for various products. The quantity columns represent the number of items sold.
The Cell ranges defined as an Excel Table
We have a requirement to prepare the following report “Quarterly Sales” to analyze sales (Quantity Sold) by each salesman per quarter.
Cell Range Vs Table
Before we begin, let’s convert the “Data set” as a “Table”. You may know that “Cell Ranges” and “Tables” behave differently in Excel.
A table is a defined grid of cells for data and formulas that automatically expands as you add to it. It has the capacity to automatically sort and filter, and add a named range to your worksheet. A “named range” is just one or more cells to which you, or Excel, have assigned a name.
As limits its functions to the “named range” rather worrying about all the cells in the worksheet; Tables are much faster.
Besides, a “Cell Range” doesn’t behave as a grid of cells rather it will be handled individually.
Creating a Table
Creating a Table in Excel is very simple.
- Just highlight the cell range.
- From Home -> Styles, select “Format As Table”.
- Excel will display different Table Styles to select from.
- Select a suitable Style to create the Table.
Pivot tables for dummies step by step guide
Specify the data (source) range
Highlight the data-set and select ‘Pivot Table’ from ‘Table’ section under Insert ribbon. Highlighting the data-set at this stage is not mandatory as this can be done now or later. But if you do Excel will select the current range for the Pivot automatically.
The other way is to convert the data set range into a Table before selecting “Pivot Table”. Placing the Cell Pointer at “Top-Left” corner in the Table will automatically select the current Table.
Create the Pivot Table
When the below Dialog box appears, click “Ok”.
If you already selected the cell range, there is no need to select the data range now. If you need to highlight the cell range now, click the red UP arrow and specifiy.
Leave ‘new worksheet’ option as it is to place the Pivot table in a new worksheet.
Pivot Table interface
See the Left and Right side of the screen carefully.
The Pivot table fields are on the right side. Below that you will notice placeholders for Rows, Columns, Filters, and Values.
Rows – refers to report row fields where the values are grouped in to.
Columns – refers to report column fields where the values are grouped in to.
Filter – refers to fields where you can apply filters on the report data.
Values – refers to fields you can used as calculated fields/ values.
Pivot Table Fields Placeholder
You can drag and drop these fields to any of the place holders below.
In this instance, we used the ‘Period‘ field as Rows, ‘Salesmen‘ field as Column and ‘Quantity‘ fields as Values.
Let’s avoid any filtering at this stage to keep it simple and to understand the basic functionality of Pivot Tables first.
Final result : the Sales report
Here we go! The below image shows the final report after making some formatting.
The beauty of Pivot tables in Excel is, you can always change the report layout quickly by dragging different fields to placeholders to create different combinations. you can add multiple fields for each place holder as well. But it is a best practice to limit to two fields to keep the report simple, and meaningful.
One data-set allows you to make various reports with minimum efforts.
The most important feature of Pivot Tables is you can keep updating the data-set regularly and refresh the report to view the updated report. These updates can be either change to the data itself or adding new “Columns” to the Table structure.
All changes can be updated through the "Analyze" menu.
This concludes the “Pivot Table” example. Hope you learned something new today. Try it out yourself and explore the endless possibilities of Excel Pivot Tables.
You may also like to read about ‘Transpose’ to change the alignment of cell contents in Microsoft Excel
Bits Bytes cd charles babbage computer memory differene engine dvd earn online Exa Bytes Giga Bytes hard disk History of Computers How do I install WebMail on my laptop How do I put WebMail on my desktop How do I set up webmail on Windows 10 How many types of memory are there i know how it works internet money it quiz John Mauchly john von Neumann Kilo Bytes Mega Bytes odoo.sh odoo cloud hosting odoo erp open source erp payoneer paypal Peta Bytes primary memory ram rom school management secondary memory subject management Terra Bytes units of storage in computer usb drive What are computer memory and its types What are primary and secondary memory why do computers use binary Windows Mail Set up Yotta Bytes Zetta Bytes