About this lesson
Creating your first PivotTable is the first step to unlocking serious Excel power in data optimization.
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.Creating your first PivotTable.xlsx
127.9 KB Creating your first PivotTable - Completed.xlsx
Creating your first PivotTable.
Where/when to use the technique
When you’d like to take the first step to unlocking serious Excel power in data optimization.
Select a cell inside your data table
- Go to the Insert tab --> PivotTable
- Choose to land your PivotTable on an existing worksheet
- Select K2 and click OK
Lay out the data fields on the PivotTable
- Drag Class to Rows
- Drag Category to Rows under Class
- Drag Units to Values
- Drag POSChitHour to columns
- Move Class to Columns
- Move it back
- Update cell E15 to 1000 (units of coffee sold in the 10 o’clock hour)
- Look at the PivotTable (N19 is where we should see this)
- No change?
- Right click the Pivot and choose “Refresh”
Activating the field list
- Click outside the PivotTable (field list goes away)
- Click inside the PivotTable (field list comes back)
- Dismiss the field list by clicking the x in the top right corner
- Try clicking out and in again (field list stays hidden)
- Right click the PivotTable and choose “Show Field List”
Key points to remember
- PivotTables are easy to build.
- PivotTables are very quick to update.
- PivotTables are not “Live” (you must refresh your data).
- You can reactivate a dismissed field list by right clicking the PivotTable.
Lesson notes are only available for subscribers.