Excel PowerPivot Training Courses

Build the Structure

Row Labels These are the categories you want to analyze. Each unique item in the row field will be listed down the left side of the table

Column Labels These are the categories that will be displayed across the top of the table. Each unique item in the column field will create a new column

Values These are the data points you want to summarize. You can perform various calculations like sum, average, count, etc., on these values

Filter This allows you to include or exclude specific data from the Pivot Table

• DOs and DON’Ts Preparing data for Pivot conversion

Use Column Structure with a text field in every header

No blank rows or columns to ensure all source data is included

Data Type for each header column is consistently formatted

No merged cells in source data headers

Clean the Source

1. Unmerge cells

2. Select column with unmerged cells

3. Go to HOME > FIND & SELECT > GO TO SPECIAL. Tick Blanks

4. Type = and press up arrow to create link

5. Press Ctrl ENTER to populate other blank cells

6. Now create Pivot Table

Add Smarter Calculations

CALCULATED COLUMNS

Pivot Tables allow you to create extra information in the form of calculated columns. Method 1 shows a summary Pivot with 3 calculated columns based on original ‘Quantity’ data

METHOD 1

• 1. Create a Pivot based on a breakdown of Quantity by Salesperson
• 2. Drag the Quantity field into the ‘Values’ box (again)
• 3. Right click on the 2 nd Quantity field and go to SHOW VALUES AS > % OF GRAND TOTAL
• 4. Create 2 more Quantity field columns and apply % DIFFERENCE FROM and RUNNING TOTAL to each of the columns

METHOD 2

• 1. In Pivot go to PIVOT TABLE ANALYSE > FIELDS, ITEMS & SETS > CALCULATED FIELD
• 2. Enter 'Extended Price' in the Name field
• 3. Enter Quantity*Unit Price in the Formula field and click OK