By: Kasper Langmann, co-founder of Spreadsheeto
For many accidental techies, the term “pivot table” might conjure up a sense of mystery, confusion, or even fear. But there’s no need to be intimidated. Pivot tables are one of Microsoft Excel’s most powerful features, and by the end of this post, you’ll be able to take the first steps toward mastering this useful skill.
Pivot tables help you make sense of raw data. For example, if you wanted to sort, count, total, or average all of the data stored in one spreadsheet, you can create a pivot table that will automatically perform these calculations and display the results separately. The options to tweak, customize, and visualize your data analyses are just about limitless.
In this quick how-to, we will demonstrating some pivot table basics. We begin with a simple data set (that you can download and follow along!) that includes hours worked by employees. For this tutorial we’ll be using Microsoft Excel 2016 for Windows. If you have a prior version, many of the following steps are identical.
When you open up the data set, you can see that there are several columns of data that we will view in a variety of ways using pivot tables.
The first thing to do is click into any single cell within the data range. Once you have done this, click on Pivot Table in the Tables group on the Insert tab.
This will open the ‘Create PivotTable’ dialog.
You should notice that by default, your data range is already in the ‘Select a table or range’ textbox. This is why we clicked into our data range prior to inserting out pivot table. In reality, this is not required, but you would have to manually select your data range at this step had you not done so.
For this example, we will go with the default selection for where we want to place our new pivot table: a new worksheet.
Once you click OK, a new worksheet with your pivot table should appear.
At this point, there’s not much to be excited about. It’s just a blank slate. But this is where the real fun begins.
Creating the Pivot Tables
For the sake of brevity, we are going to build out just a few different pivot table examples so you can see how they can provide you with varying ways to visualize the data.
Note on the right edge of our worksheet, there now a section called PivotTable Fields. It contains all the available data points from our raw data.
The bottom half of the PivotTable Fields list are the four areas available for us to place our data: Filters, Columns, Rows, and Values. More on that in a bit.
If you do not see a PivotTable Fields list in your worksheet, make sure Field List is selected (dark gray background) in the Show group on the Analyze tab.
Pivot example 1: Total numbers of hours worked per day
The first thing we might want to do is simply see the total number of hours worked per day. We can do this by first bringing ‘Date’ into the ROWS area. You can do this by simply dragging and dropping the ‘Date’ field to the ROWS box.
Now our pivot table is beginning to take shape.
Let’s drag ‘Hours clocked’ from the fields list and drop it into the VALUES box.
Note that for the VALUES that is says ‘Sum of Hours…’. This is just one of many variants available for VALUES. In this case, SUM is what we want but if you click on the down arrow to the right, you will see a drop down. From the dropdown, select ‘Value Field Settings…’.
This will open the Value Field Settings dialog. Among other things, this is where you can change how you want the value field summarized. There are many useful options like Count, Average, Max, Min, and others.
Note that the difference between Sum and Count here is that Sum adds all the numeric values for a given date. Count would simply give us a count of employees that clocked hours on that day since it would be a count of the number of rows with that date.
So now, our pivot table is complete for this task. We can see the total number of ‘Hours clocked’ for each date in our original data.
Quick tip: If you wanted to sort the pivot table from most hours worked to least, just click into that column and right click. From there, select Sort and then Largest to Smallest.
Then you can quickly see which date in the period that the most man hours were worked.
Pivot example 2: Hours worked by individual employee
In this example, we want to view the hours worked by individual employee. We will set up the pivot table to allow us to filter on certain dates as well. This will give you a chance to see some of the basic flexibility pivot tables offer for visualizing your data.
All we need to do is add another data field to our existing pivot table and that’s going to be ‘Name’. Drag and drop the ‘Name’ field into the Column box.
Notice now how our pivot table has an extra dimension, allowing us to visualize what we had already seen on a deeper level by employee name.
Not only can you see the total hours by date, you can now see those hours broken down by employee. Furthermore, you can filter both by date by clicking on the Row Labels dropdown…
and employee name by clicking on the Column Labels dropdown…
Now you have an entirely different view of your data customized with just a few clicks of your mouse.
While this introduction to pivot tables in Excel merely scratches the surface of what is possible, it gives you a quick look at how useful they are. Pivot tables are powerful tools for making data-driven decisions. Don’t wait to start experimenting!
About the Author
Kasper Langmann is the co-founder of Spreadsheeto and certified Microsoft Office Specialist. Kasper loves the art of teaching Excel to students all over the globe. More than 30,000 students have signed up for his free Excel training.