Monday to Thursday, 10am-4pm, EST
1.855.281.5499 (toll free)

Getting Started with Pivot Tables in Excel

Office SoftwareProductivity

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.

Getting Started

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.

Screenshot: Simple data set

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.

Screenshot: Insert Tab

This will open the ‘Create PivotTable’ dialog.

Screenshot: Create Pivot Table

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.

Screenshot: Select Table Range

For this example, we will go with the default selection for where we want to place our new pivot table: a new worksheet.

Screenshot: New Worksheet

Once you click OK, a new worksheet with your pivot table should appear.

Screenshot: New Pivot Table

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.

Screenshot: PivotTable Fields

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.

Screenshot: Analyza 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.

Screenshot: Basic pivot table

Let’s drag ‘Hours clocked’ from the fields list and drop it into the VALUES box.

Screenshot: Dragging Hours into Values

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…’.

Screenshot: 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.

Screenshot: Summarize value field

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.

Screenshot: Hours clocked

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.

Screenshot: Sort Largest to Smallest

Then you can quickly see which date in the period that the most man hours were worked.

Screenshot: Sum of Hours clocked by descending order

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.

Screenshot: Dragging 'Name' field into 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.

Screenshot: Updated Pivot Table

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…

Screenshot: Filtering by date

and employee name by clicking on the Column Labels dropdown…

Screenshot: Filtering by employee

Now you have an entirely different view of your data customized with just a few clicks of your mouse.

Screenshot: Filtered Pivot Table

Conclusion

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