Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
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.
Benefits of creating PivotTables
- Extremely quick data refresh
- Drag and drop interface to look at data in different ways
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 into the VALUES area. (Numeric fields will return a SUM, text will return a COUNT)
- Drag a field into the ROWS area, and another field below it
Removing items from a PivotTable
- Drag the second field you added to the ROWS area back to the field list
- Drag another field into the VALUES area to generate another SUM or COUNT
- Drag the field you have in the ROWS area to COLUMNS
- Drag another field into the ROWS area
- Number Formats: Right click a value in the Pivot --> Value Field Settings --> Number Format
- Layouts can be changed via PivotTable Tools --> Design --> Report Layout
- Subtotals can be configured via PivotTable Tools --> Design --> Subtotal
Hints & tips
- Remember that PivotTables are not live! You MUST refresh your data manually
- To update the data in your Pivot Table, go to Data --> Refresh All
Lesson notes are only available for subscribers.