I’m clearly a one hit wonder fan…
(A little side note: You might have to zoom in and out a bit every now and again. Some of the images in this tutorial contain quite a bit of data)
Whenever I mention the words Pivot Table a deafening silence tends to fills the room with eerie echos bouncing of the walls and Friday the 13th Theme Song randomly starts playing in the background…
OK, I exaggerate. But Pivot Tables still cause chills for many business professionals. And yet, it doesn’t have to.
For those who are unfamiliar with Pivot Tables, Pivot Tables quite simply summarize large sets of data in a fraction of the time it would take to perform the action manually. (Hence the picture of a coffee cup next to a computer – because the more time you save, the more time you’ll have for coffee – corny I know).
In one of my earlier articles/tutorials I explained how to design graphs from a table containing summarized data. The table was created manually, which under normal circumstances would take a considerable amount of time if you had large sets of data. Pivot Tables love large sets of data.
In today’s example we’ll be using a set of basic employee information (50 employees in total) to explain the tremendous power contained in using Pivot Tables as opposed to summarizing the data manually.
You can download the data set from this link if you would like to follow along. (All data contained in this spreadsheet is fictional and used for training purposes only).
So let’s get started.
“Jesse” works for the Human Resource Department at her Organization and has to submit monthly reports to various Senior Managers, each with their own requirements. “Jesse’s” problem is that the amount of data she has to sift through takes up a significant amount of time during her day. So “Jesse” decided to start using Pivot Tables.
“Jesse” wants to generate the following information
- The demographics of the employees in each department (Race and Gender per department);
- The number of employees in each pay-scale; &
- The number of employees in each department;
So let’s help “Jesse” figure out how to perform the first task and then you can help her with the other two.
The first step is to open the worksheet that contains our data (You can download the document from this link if you have not already done so).
Once opened, it should look something like this:
Don’t worry too much about all the information in the document. In my online video series (currently in production) we’ll use more of this data. For now, we will only be focusing on a few bits and pieces needed for our Pivot Table
Now that that worksheet is open, click on cell A1 – “EmployeeNr”. Navigate to the Ribbon at the top of your screen, and click on Insert and then PivotTable
Once you’ve clicked on the PivotTable button, you should be presented with a window similar to the one below:
All you have to ensure is that “Select a table range” and “New Worksheet” is selected.
The “Table/Range” simply indicates the Excel which data we are currently working with. So if your worksheet was called “GroceryList” the description would be GroceryList!$A$1:$Y$51.
$A$1 – Refers to the first cell in our data and $Y$51 refers to the last cell in our data. The :between $A$1:$Y$51, basically means to.
So what you are telling Excel is, “please select all the information from cell A1 to cell Y51” and create a PivotTable from that.
Now that we understand what range of data we are working with, you can click on OK.
Once you’ve clicked OK, you should see a window similar to this (we’ll zoom in, in a bit):
What has happened is that Excel went ahead and created a PivotTable for you in a new worksheet. On the left you will see the blank PivotTable and on the right you will see the PivotTable Fields/Data that we can use to complete our PivotTable.
Let’s inspect the right hand side in a bit more detail. (If you ever forget which data is supposed to go where, you can just save the image below somewhere on your computer for future reference).
In one of my previous articles, I explained how to insert a Graph based on information that was manually summarized into a table like the one on the left. As we complete the PivotTable fields (on the right), Excel will generate a summarized table for us. (We are going to practice this a little later in this article/tutorial).
PivotTable Fields: (My apologies for not using numbering. I can’t use my own custom CSS on LinkedIn articles and I don’t like LinkedIn’s number formatting.)
Filter: For the most part we use the filter to remove any information that we do not want to include in our PivotTable, or alternatively, only information that we want to include. We’ll have a look at a practical example later.
Columns: We use the Columns block for the information that we want to create our PivotTable for. So for instance, if we wanted to see information per month we would add our months of the year to the Columns block and they become the headings of our Columns. In our example we want to see Race and Gender for each department. So we are going to add both fields to the Columns block (you can add multiple fields to these blocks).
Rows: In our example, we want to see the Demographics of each department, per department. So we will add the Department field in this block. This will add the Department names to our rows.
Values: Any information in the Values block will either be counted, added together or manipulated in the way we set it up. As a general rule, if you add a piece of data that contains numbers, Excel will automatically add the numbers together. If you add data that contains only text (like we will), Excel will count the data and not add them together (we’ll also look at an example). In our example, the values will represent how many people of each race and gender are in each department (similar to yearly Employment Equity reporting).
Now that we understand the function of each of the various blocks, let’s build our table.
Step 1: Select your Filter
We do not want to include any Foreign National Employees in our data. So we will drag the ForeignNational field down to the Filter block. In order to do this, simply click and drag the ForeignNational field into the Filter block. Once you’ve done that your screen should look similar to this:
You will note that the PivotTable on the left disappear and now only displays the Filter information. That will change as we build our table.
Step 2: Remove the data you don’t want
We’ll have to complete 4 steps here.
You will now see that because we changed the value of the filter, it has changed from “All” to “No”. This means it will now only include employees that are NOT foreign nationals.
Step 3: Add your Rows
Remember, in our example we want to see the demographics of each department, per department.
Let’s select the “Department” field and drag it down to our Rows block.
As I mentioned before, every time you update the blocks to the right, Excel will automatically update your PivotTable.
Step 4: Add your Columns
Excel allows us to add multiple fields to every block in a PivotTable, but for our example we will only be adding one field per block, except for our Columns block. Here we will add two fields, namely “Race” and “Gender”.
So, let’s drag the “Race” and “Gender” fields down to the Columns block.
Excel will always display the fields in each block from top to bottom. Because we put “Race” first, it’s is displayed at the top and “Gender” at the bottom. This means that if you don’t like the order in which the data is displayed, you can just swap the two fields around by dragging one on-top of the other.
Step 5: Add you Values
So far, we’ve basically built the framework for what is going to be our final PivotTable. In order for our PivotTable to be of actual value, we need…you guessed it, values.
Remember what I explained about values earlier:
Any information in the Values block will either be counted, added together or manipulated in the way we set it up. As a general rule, if you add a piece of data that contains numbers, Excel will automatically add the numbers together. If you add data that contains only text (like we will), Excel will count the data and not add them together (we’ll also look at an example).
The second important thing to remember about the Values block is that you have to use fields that would typically contain data for all employees. Now you might be asking, “what the heck are you talking about?”.
Well, every employee will typically have the following information: a name, a surname, and an employee number. But not all employees might have an email address or telephone number. So in order for us to get the most accurate data available it would be advisable touse either the Surname or Employee Number fields. So let’s use both separately and have a look at the result.
Let’s start wit the EmployeeNr field. Drag the EmployeeNr field to the Values block.
Immediately you will see that something simply doesn’t look right. We only have 50 employees, how can the data possibly display such huge numbers. Remember what I said about using either numbers or text as values.
Because we used the EmployeeNr field, which contains numbers, Excel automatically assumed that we wish to add them together. You can see this by looking at your Values block where the word “Sum” appears next to the field name. (You can change this, but that’s better left for one of my later tutorials).
So clearly EmployeeNr is not the right field to use. To remove it, just drag the EmployeeNr field back to the top block where all the other fields are.
Now let’s try it with the Surname field. Drag the Surname field to the Values block.
And would you look at that. Because we used a field that contained text, Excel automatically counted the data instead of trying to add it together.
“But wait a minute, the total says 45 employees and not 50!! What gives??”
Well, remember, we applied a filter in the beginning that only includes employees from our country. The table does not include Foreign Nationals.
Step 6: Make it Pretty
We are pretty much done with our PivotTable. All we need to do now is make it a little easier to read. We can do this in two simple steps.
Apply one Column Width:
If done correctly, all the columns you selected should now have the exact same width:
Center the data:
Select the columns once again (as we did before) and the click on the “Center” button on the Ribbon at the top (circled in red):
And now you will notice, that not only do your columns all have the same width, but the data is also centered inside each cell.
Now you can easily see how many African Females are in the Finance Department, that would be 1. You can also easily see how many Indian Males are in the Commercial department, that would also be 1.
So in case you were wondering: F = Female, M = Male, I= Indian, C = Colored, A = African & W = White.
Congratulations, you’ve have successfully completed your very first PivotTable!!
And remember, “Jesse” also had a few other assignments in the beginning of this article. Why not download the spreadsheet and see if you can help her out.
Until next time.