Updated: Jan 24
After discussing the Tables, we will move towards Pivot Tables. This is by far the most strong and effective function of Excel which can not only give you automation but a quick analysis of the data however you require it.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
Uses of a Pivot Table
Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field.
Pivot tables are most commonly used in situations where data needs to be aggregated, and sliced and diced for analysis. It’s particularly useful when you are looking to calculate and summarize data to make comparisons.
How to create a Pivot Table?
It is a customary approach to make a Pivot Table on a fresh sheet to avoid confusion with the actual data tables and the overlapping with the pivot table.
Go to a new fresh sheet, or insert a new sheet in your workbook. Go to the insert toolbar where you can find the Pivot Table command.
We will use the same car model’s data to play with our pivot table learning.
Before inserting a pivot table, it is a useful technique to sort your data for a particular attribute, here in our example, we will sort our table for “Car model year”.
Inserting a Pivot Table
As told before, go to an empty sheet (you can perhaps name that sheet as the pivot), choose a cell for your pivot table to start. I have chosen “B3”, perhaps you can click on any cell, it doesn’t matter. Click on Insert and then Pivot Table. You will see the following message appearing:
You have to select a data range for the pivot table. As you can see that in the field “Select a table or Range”, I have put the name of my table, i.e., “carmodels”. If you don’t remember the exact name of the table, just go to your table and click on the design and see the table name on the left. You can also select a data range instead of a table, but as we have already converted our data range into the table (and it is always wise to convert your data into a table), we will just use the table name.
As we have already selected a new fresh sheet for our pivot table, so we will click on Existing Worksheet. Leave other options untouched and click OK. You will see two things that happened on that sheet.
A pivot table is inserted as you can see in the above image. We will see how to make use of this table. You can change the name of the Pivot Table according to your choice for later reference. Excel will go on putting the names in numerical order.
Secondly, on the right side, a new Pivot Table Column appears, which will be showing all Pivot Table fields along with a way to use these fields.
Pivot Table Options
Right-click on the Pivot Table and click on Pivot Table Options, and you will see this:
You can change the Pivot Table Name here too. There are a lot of other options, some of which need to be looked into, others may be ignored for the time being. Let’s look at the first Tab “Layout & Format”.
Leave the Layout, Come to Format and you will see “For error values show”. As you can imagine, that wherever there will be an error in the data, you can choose to show a value or a text, which you can enter here, after selecting the checkbox.
Sometimes there are empty cells in the data, if you want empty cells to be shown as your desired value, you can put it here.
Whenever you open the workbook or any calculation is made on a sheet, the Pivot Table will update itself. The last 2 options are self-explanatory. If you want Autofit columns on each update you can select this option. Sometimes you might have changed the widths of pivot table columns according to your choice, so with each update, this formatting will be lost, therefore, if you don’t want this, deselect it. The second one is similar to this. You want to preserve the formatting of pivot table cells (e.g., fonts, colors, etc.) check it.
The Second Tab labeled “Tools and Filters”:
If you want to have a Grand Total for either Rows or Columns, select the appropriate checkbox, otherwise deselect them.
Leave the other options under this tab for now.
In the “Display” Tab of the Pivot Table Options, you will see this:
If we have used more than one column from the data in the “Rows” or “Columns” fields, the “-” or “+” sign will appear as you can see shown by the point of arrows above. You can choose to either show or hide these – or + signs by selecting or deselecting “Show expand/collapse buttons”. Tooltips will be shown when you hover your mouse on the data analyses in the pivot table, choose whether you want it or not.
If you want that Filter Dropdown arrow to be displayed along with that heading, select this option, otherwise, deselect it.
Last is whether you want the pivot table list to be sorted as your Data table sort, or you want to sort it again in alphabetical order, make a choice here.
The Printing Tab:
Look at the options: The first one, if you want to print those + or – signs select it. The second one if your pivot table is extending to more than 1 page you want your row headings to be repeated on all pages, so leave it as selected. The third option, if you want certain rows to be printed on every page select it.
The “Data” tab in the Options:
The first option is selected by default because this pivot table will be saved along with your file. The third option is deselected, but if you want the pivot table to be refreshed and update according to your data upon the opening of this file, you can select it.
Getting used to Pivot Table
As you can see in the newly inserted Pivot Table Toolbar (on the right of the sheet), clicking on the gear will show you the options to change the display of the toolbar. These options can also be found in the upper toolbar.
This toolbar only appears if you click on the pivot table. Clicking elsewhere on the sheet will make it disappear. We will make use of this toolbar to analyze our data in the pivot table. Remember our data was the table of “carmodels” on another sheet.
Analyzing the data
Suppose we want to analyze our carmodels data to see how many different year models we have and in each year group, how many cars we have. We will arrange our pivot data commands as follows:
To put the data accordingly for analyses, we have to use the Pivot Table toolbar, which appears on the right side of the screen when we click on the pivot table:
As we want to see the car model analyses, so we will use that column. Don’t be intimidated, just play with different aspects of the Pivot table and see how it goes. I will start with all these 4 boxes of the toolbar and explain them one by one. Please remember, whichever column you are using in any of these boxes, you just have to drag and drop the column header from above in the toolbar.
Let me explain each one of it:
The “Filters” box is used to show the data of the selected item only. Suppose, we drag “Car Model Year” from above and drop it in the Filters box, see what happens. The Pivot table changes to this:
You can use this feature to single out the items from a column which you put in the “Filters” box and analyze just that item.
But as I said, we aimed to analyze our data so that we can see how many years of models we have and the number of cars each year.
We can drag the “Car models Year” from above either in the Columns or Rows, for these simple analyses, either one is ok depending upon how you want to display your analyses.
I will preferably put that in the “Rows” box, to put my data length-wise:
You can see that the Pivot table shows “Row Labels” of all the values from the Car Model Year column and all the duplicate values have been removed. Every year is represented only once. If you scroll down, you will see
Now what we have to do is to get the number of cars in each of the years. For all types of calculation, you have to drag and drop the column in the “Values” box. So we will do the same:
We will hold “Car Model Year” once again from above and drop it in the “Values” box, like this:
If we now look at the pivot table, we can see another column is added. The heading displayed shows “Sum of Car Model Year”.
But we don’t want “Sum”, we want the count of “Car Model Year”, to know how many cars do we have each year. Just right click on the second column and select the “Value Field Settings”
You will see that the second column field options open up:
As you can see in the above image, not only you can change the name of that 2nd column, but you can choose any function desired for the data to be analyzed. Here we will choose “Count” instead of Sum, as we want to know the number of each model year car. Clicking Ok will give you the result as below:
Playing more with Pivot Table
If we now drag and drop the Color field from above in the “Columns” box we will see the Pivot Table like this:
We not only can see what particular year model we have but each year which color car we have.
If we drag Color and put it in the “Rows” box alongside the Year model already placed therein, we’ll see the data analyzed like this:
Continuous playing with each field placed either in Rows, Columns, or “Values” boxes we can see how we can analyze the data. You should spend a lot of time practicing with the Pivot table. I will advise you to get another fake data of something else, and practice with Pivot Table construction and put some effort and time into it to understand every bit of it.
Pivot Table is the strongest ever function of Excel for analyses of data. You can also right-click on the Pivot table and see other shortcuts and options to change how your data will look.
That’s it for this part. We will continue in the next parts with Forms and Graphs. Go on following this series and you will enjoy how easily you can learn Excel, bit by bit. Thank you very much for your support.