Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
When you need to aggregate multiple tables or create new fields in order to display properly in a Pivot Table.
Preparing your PivotTable source data.
Where/when to use the technique
When you need to aggregate multiple tables or create new fields in order to display properly in a PivotTable.
The end goal is to get all the required columns into a single table in order to feed the PivotTable. This includes aggregating data from other tables, creating new columns to hold the values you’d like to see, and splitting up fields into smaller pieces for PivotTable use if necessary.
- Use VLOOKUP() to pull data from the other table into the main table
- Use Text functions (LEFT, RIGHT, MID, LEN, FIND) to break apart text if needed
- Perform mathematics in new columns (unit count * sales currency to get total sales)
- Saves tying to write complicated formulas beside a pivot which can be overwritten or not cover the entire boundary of the Pivot
- Saves trying to make complicated Calculated Fields later
- Makes it easy to drag and drop required fields on a pivot
- Extra columns in your data source table that may not always be required
- Can be tedious to set up the needed variants of the data
Lesson notes are only available for subscribers.