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 stevechasedocs.wordress.com 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.

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant.
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:

    Steve,
    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..

    Thanks,
    Dana

    • 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.

      Steve

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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