Create a PivotTable in Less than One Minute and Six Left Mouse Clicks

Before you do any clicking, make sure you have gathered up your data in Excel and have a clearly defined header row. PivotTables work with Category and Data fields. Examples of category field headers include Region, Department, and Product in my example below. Data field examples include fields such as Price, Quantity, and Units. You may also have arbitrary fields such as Email and Last Name. Your data set should not contain any blank rows or columns.

data fields and category fields

Click#1 is to select one of the cells within your data range.

Click #2 is to click the Insert Tab.

Click #3 is PivotTable.

PivotTable click

Click #4 is Ok. This will insert the PivotTable report on a new worksheet.

click ok

Click #5 is to click a check box with a Category field like “Product ID”. When you click a category field check box it will be grouped by rows. In this example I have 3 unique values of Product IDs.

rows group

Click #6 is to click a data field like “Quantity”.

values group

PivotTables can be done very easily with out a lot of time if you are interested in seeing the big picture. Of course you can click more fields you want to report on. If you click a category field it will display in the rows. If you prefer to transpose it you can drag the field to the columns drop zone of the Pivot Table Field List. Also, you can change the SUM to AVERAGE, MAX, or MIN if you prefer. Just right click on the value field settings to change the calculation.

A Pivot Table is like looking in the rear view mirror and summing up the data that has occurred in the past. It is important to refresh the PivotTable if your data is updated. Right click the Pivot Table to refresh. Double click a value inside the Pivot Table to create a new worksheet with a summary of the details supporting the number you just double clicked.

Feel free to download my Excel Keyboard shortcuts list. And if you like this post and want future updates you can subscribe to my site with your email which is on the right sidebar of the desktop version of this blog. Click here to watch my Excel YouTube channel.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel. Bookmark the permalink.

2 Responses to Create a PivotTable in Less than One Minute and Six Left Mouse Clicks

  1. Dana Maynor says:

    Question for you. I am charged with creating a tool which will allow managers to have a quick way to determine the next available person for overtime based on first, seniority, then the lowest number of overtime hours worked in the past. I can create the file, but want to use data validation dropdown list to show the next person based on the criteria I have above. I thought maybe using a pivot table, but I have not used them before. I have seen you a few times while at NH during the open house days..


    • Steve Chase says:

      Hey Dana,
      Perhaps a PivotTable could help you out. I can using the columns for employees and then using the values fields for the data fields with sorting by seniority then by hours.
      I’m not sure how to do the data validation on a drop down without doing a VBA/Macro. But you should be able to make something work out with the drop down in the filter panes and/ or add slicers. The slicer are a great way to show filters in Excel 2010 or 2013.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s