Instructor-led training - pivot table training london

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Excel PivotTable Training CourseExcel PivotTable Training Course

Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.

Designed for Excel 365

(last 12 months)
(2830 reviews, see all 97,377 testimonials)

From £300 List price £350

Free manuals

We are providing a range of our course manuals free of charge.

Why not share this resource with your friends and colleagues?

Training manual sample

Below are some extracts from our Excel training manuals.

Calculated Fields and Items 

PivotTables enable you to analyse your data efficiently but some questions can only be answered by performing calculations on the data used to create the PivotTable. To perform that type of analysis you can create custom fields which summarise PivotTable data using a formula. Without calculated fields, you would have to copy the data from the PivotTable and paste it onto another worksheet and create the formula there which is a pain.  

The button to create a calculated field is found on the Options contextual tab in the Calculations group, Click the “Fields, Items & Sets” drop down and the Calculated Field button.  

 

 

When you click Calculated Field the Insert Calculated Field Dialogue box appears: 

 

You can now give your calculation a name, such as Average Sale, and create the formula. Click into the formula box and delete the 0. Now insert fields and any mathematical operators you need. For example: 

 

Excel adds the average sale field to the body of the PivotTable, building a further analysis into the table. Note that even though the newly calculated field appears in the PivotTable field list, it hasn’t been added to the original data source. The new field only exists in the PivotTable’s data cache – its temporary memory. Calculated fields extend the type of analysis you can perform in Excel, making PivotTables even more useful.  

Calculated Item 

Let’s say we want to insert a new item in a Product drop-down say, Home & Electronics. This particular field, when selected from the drop-down, should show us the combined totals for both Home Décor and Electronics. Select either the particular row field or the column field in which you would like to insert a new item. Click on the particular field (where you would like to insert a calculated item) in the PivotTable and then click the “Options” tab in the ribbon. Click the “formulas” button and select “Calculate Item”.