Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Setting up your data sets to ensure that they will be able to be consumed by a Pivot Table.
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Source Data Best Practices.xlsx
129.8 KB Source Data Best Practices - Completed.xlsx
Understanding best practices for PivotTable source data.
Where/when to use the technique
Setting up your data sets to ensure that they will be able to be consumed by a PivotTable.
Data must be contiguous
- Remove all blank rows
- Remove all blank columns
- Make sure there is a header row
Key points to remember
Data should be un-aggregated
- PivotTables do the aggregation for use
- Remove all subtotals
- Remove all grand totals
Data should be consistent
- Columns of numbers should contain only numbers
- Columns of dates should contain only dates
- Columns of text should contain only text
- Only exception is the header row, which should be text
Choose good table headers
- Data must have a header row
- Make the header row descriptive
- These headers will become the field names on your PivotTable
Lesson notes are only available for subscribers.