About this lesson
Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data).
Multiple versions of this lesson are available, choose the appropriate version for you:
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 a numeric field into the VALUES area
- Drag fields containing text into the ROWS area as desired
Removing items from a PivotTable
- Drag any field from the layout area back into the field list
- Drag a field from ROWS to COLUMNS
- Drag a field from COLUMNS to ROWS
- Click the arrow beside any field in the VALUES area, choose Value Field Settings, and choose Average
- Go back to the source data and update any cell to a new value
- Return to the PivotTable, right click it and choose Refresh
Lesson notes are only available for subscribers.