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:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Pivot Tables.xlsx
42.2 KB Pivot Tables - Completed.xlsx
54.3 KB Creating PivotTables - Extra Practice.xlsx
Creating, adjusting and modifying basic PivotTables in Excel.
When to use
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.
- Create a new worksheet
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 field that contains numeric values from the field list into the VALUES area
- Drag a field that contains textual values from the field list into the ROWS area
Removing items from a PivotTable
- Drag the field you placed in the VALUES area back into the field list
- Drag a different field into the VALUES area
- Drag the field you placed in the ROWS area from ROWS to COLUMNS
- Right click the field in the values area, choose Value Field Settings, and choose Average
- Go back to the source data table and change any field that has a value in it
- Return to the PivotTable, right click it and choose Refresh
Lesson notes are only available for subscribers.