Focus video player for keyboard shortcuts
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data).
Download this lesson’s related exercise files.Creating PivotTables
33.6 KB Creating PivotTables - Completed
Creating, adjusting and modifying basic PivotTables in Excel.
Where/when to use the technique
PivotTables are an amazing tool that can summarize, slice and dice data in a variety of formats. While they shine with large data sets, they are equally powerful with small data sets as well.
Benefits of creating PivotTables
- Extremely quick data refresh
- Drag and drop interface to look at data in different ways
- Ease of use
Key Point to remember
- PivotTables are not live! You MUST refresh your data manually (but it’s easy to do)
Preparing your data to turn it into a PivotTable
- Make certain that your data is in tabular format (preferably formatted as a table)
- Ensure your data has a clear header row
- Select any cell in the data range
- Go to the Insert tab, choose PivotTable and place it on a new worksheet
- Drag Price (or another field containing values) into the VALUES area
- Drag Inventory Item into the ROWS area, and Sold By below it
- Drag Date into the COLUMNS area
Removing items from a PivotTable
- Drag the Date field back into the field list
- Drag Price back into the field list
- Drag Sold By from ROWS to COLUMNS
- Drag Commission into the Values area
- Right click the values in the PivotTable, choose Value Field Settings, and choose Average
- Go back to the source data and update F4 to read 1000
- Return to the PivotTable, right click it and choose Refresh
Lesson notes are only available for subscribers.