Pivot table is a powerful tool of MS Excel. A PivotTable provides an interactive mechanism to quickly summarize large amount of data. It allows you to;
- analyze data having up to 1 million rows with just a few mouse clicks.
- show the results in an easy to read table (Pivot table).
- modify the report layout with the quick option of dragging fields around.
- highlight key information to management and include Charts & Slicers for your presentations.
Pivot tables are used by Finance Analysts, Project Managers, Auditors, Cost Analysts and Controllers, Sales Analysts, Financial Experts, Human Resource Personnel, Doctors, Statisticians, and informally Software Developers or Support Personnel in the small businesses. Pivot table is one of the most powerful features using which Excel really shines.
Now let’s roll up our sleeves to start off a little practice on creating and using Pivot tables.
Fire up excel and open the worksheet which has the source data that we need to summarize through various Pivot tables.
For this exercise we use some real estate specific data as a sample as you can see from the image below. Note that the source data from which we’re going to build a pivot table, should not have a blank column or row.
This data contains some information about the plots in various precincts of the real estate company. We’re interested to summarize the plots in categories, plot size, precincts and plots’ development status.
Let’s create our first Pivot table. First off, click on any cell of the worksheet and click Ctrl+A to select the entire data.
Now click Insert (from the top menu) then Pivot Table as shown in the image below. You’ll be presented with Create PivotTable dialog box.
Under Select Table or Range radio button it will show up the entire range of the data you’ve just selected. So leave it as it is. If it is found empty then you have to either manually enter it or click the tiny image on the right side of the box and then select the range from the worksheet. Keep “New Worksheet” selected and click Ok.
It will display the created Pivot table as shown in the image below. Just click anywhere on it and it will show the PivotTable Fields box on the right edge of the new worksheet and here the story begins.
The PivotTable Fields box will show all columns of your source data. At this time we’re interested in Category. Therefore check Category from the fields list and make sure that it goes under Rows section. Now click Sr. No. and make sure that it goes under Values section. If anything has fallen in the undesired section you can drag and drop it to the desired section.
Instead of Sum of the Sr. No. we want the count so click Sr. No. entry from Values box and then click “Value Field Settings…” from the context menu. Now enter “View By Category” for the Custom Name and select count under “Data from the selected field” list. Click Ok and you’re done creating your first simple Pivot table. You can see the Categories on the left side and their respective counts on the right side in the Pivot table as depicted in the image below.
Repeat the same steps to create Pivot tables for Size, Development Status and Precincts.
Remember that at any time if you updated your source worksheet by adding or editing data you have to update all Pivot tables. For this purpose click PivotTable -> Analyze -> Refresh (arrow) and then click Refresh or Refresh All to refresh all Pivot tables.
One significant thing is left. For your particular requirements you may need to filter the Pivot table data. This is particularly required to see the whole picture (all Pivot tables) with the selected type of data. For this purpose Excel provides us with Slicers for each Pivot table.
Click anywhere on Category PivotTable and then click PivotTable Tools -> Analyze -> Insert Slicer as shown in the image.
You’ll see a box with all categories. You can simply select one, more or all categories. In order to select all categories you can also click the small icon at the top-right corner of the slicer box. As soon as your selection changes it will be reflected forthwith in the Pivot table.
But wait, this is not the end of the story yet!
You may need to apply filters on all Pivot tables by creating slicers such that the selection from one slicer should reflect the changes in all existing Pivot tables. Just Right click anywhere on a slicer box and click “Report Connections” option from the context menu. Check all Pivot tables and click Ok. Repeat the same for all slicers. Your created slicers will look like the following. Since you’ve just connected every slicer will all Pivot tables you have now got the facility of selecting one, more or all options from these slicers to reflect the Pivot tables data, as per your choice. For instance you can select Developed plots having size 300 Sq. Yrd. with Boulevard or Corner plots in Precincts 2 and 3 and check how many plots match this selection.
This is just a little more than the introduction of PivotTable. There are myriad of things you can do with Pivot tables. Stick around with us and you’ll learn lots more on the benefits of using PivotTable pretty soon.
