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.
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.
Click #4 is Ok. This will insert the PivotTable report on a new worksheet.
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.
Click #6 is to click a data field like “Quantity”.
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.