Instructor-led training - pivot table training london

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

Excel PivotTable Training CourseExcel PivotTable Training Course

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

Designed for Excel 365

(last 12 months)
(2614 reviews, see all 99,167 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”. 

calculated-item-in-a-pivot-table-excel-2007 

You can now either write your own formula or use the existing items in that field and insert them by double-clicking. In our case, we will insert a new item by writing something like =’Electronics’+'Home Décor’ 

calculated-item-formula-in-a-pivot-table 

Let’s provide it with the name that we decided earlier – “Electronics & Home”. Press OK. What this will do is to insert a new calculated field, one which would show the combined sum of Electronics and Home Décor. If you look at the PivotTable now, you will see that under the Product field, a new item has been inserted and which sums up the relevant figures both from Electronics and Home Décor. 

Another interesting way to create calculated items in a PivotTable is the use of relative references. In the above example, we clubbed two items into a single new item – “Electronics & Home” which contained the totals for both the fields. In the Calculated Field creation box, we could have written = Product[-3] + Product[-2] instead of =’Electronics’+'Home Décor’ (assuming that these two fields immediately precede the new calculated field that we are creating. The [-3] and [-2] actually tell the application to pick up and sum the preceding two fields. 

calculated-item-relative-formula-in-a-pivot-table 

A parameter value of [-1] would have made the application pick an item 1 step backwards and a value of [5] would make it pick up an item 5 steps forward. Again like the calculated field, a calculated item can use operators like +,-, / and * and other relevant excel formulas. For example a calculated item formula of =IF(Product[-1]>23000000,1,0) +Product[-2] would add 1 to Product[-2] if the total value for Product [-1] exceeds 23,000,000. 

Managing & maintainingPivotTable calculations 

When you find yourself maintaining and managing your PivotTables through changing requirements and growing data, you may find the need to maintain and manage your calculated fields and calculated items as well.  

Editing and Deleting Your PivotTable Calculations 

Activate the Insert Calculated Field or Insert Calculated Item dialogue and select the Name drop-down. Once selected you thenhave the option of deleting or modifying the item or field.  

Changing the solve order of your calculated items 

If the value of a cell in your PivotTable is dependent on the results of two or more calculated items, you can specify the order in which the individual calculations are performed.  

To get to the Solver Order dialogue box place your cursor anywhere in the PivotTable, select Formulas on the Tools group and then select Solve Order.  

 

The Solve Order dialogue box lists all the calculated items that currently exist in your PivotTable. Select any of the calculated items and using the Move Up and Move Down buttons, rearrange the order of calculations as required. 

Documenting Your Formulas 

To list your PivotTable calculations, simply place your cursor anywhere in the PivotTable and select Formulas and then select List Formulas. Excel creates a new tab in your workbook listing the calculated fields and calculated items in the current PivotTable 

 

What is a PivotChart? 

 

PivotTables help you summarise large data sets efficiently but it can be difficult to interpret data when all you have to go on is the raw numbers. Charts summarise the data visually making it easier to distinguish groupings and trends in your data. Just as you can create charts based on regular worksheet data sets, you can create dynamic charts called PivotCharts from the data in a PivotTable. 

 

Creating a PivotChart 

 

There are two ways to create a PivotChart. You can either create a PivotTable and a PivotChart at the same time or you can create a PivotChart from an existing PivotTable.  

 

To create a PivotTable and a PivotChart at the same time you need to make sure your source data is laid out as a data list or, preferably, an Excel table. Then on the Insert Tab, click the PivotTable button down arrow and click PivotChart. 

 

 

 

When you do, the create PivotTable with PivotChart dialogue box opens. You can then verify that Excel has identified the range properly i.e. your table. You can then choose where to position it.  

 

 

 

After you create your PivotChart you can arrange the fields using the controls in the PivotTable Field List task pane, just like you would in a PivotTable. 

 

For example: 

 

 

 

Now we’ll create a PivotChart based on an existing PivotTable. To do this, display a sheet that contains a PivotTable and click any cell in the PivotTable: 

 

 

 

Then create a chart the way you would normally in Excel. Click the Insert tab, and then select the type of chart you want to create. For example, a clustered column chart: 

 

 

 

Excel has created a chart that reflects the organisation of the PivotTable. You can Pivot the PivotTable to change the PivotChart. For example, if we pulled the month field out of the axis categories area, the result will be a column chart for FirmA and FirmB for 2009 and 2010: 

 

 

 

Note that there are some differences between ordinary charts and PivotCharts. For example, you can’t switch the row or column orientation of the PivotChart by using the select data dialogue box. This isn’t a problem, however, because you can always rearrange your data by pivoting the PivotChart.  

 

You can’t create XY Scatter charts, Stock Charts or Bubble Charts. Plus if you refresh the PivotChart you will remove trendlines, data labels, error bars and various other settings.  

 

If you would rather have a PivotChart on a separate chart sheet, click the PivotChart, and then on the Design contextual chart, click Move Chart and select where you want the chart to go.  

 

PivotCharts provide an overview of your data, providing insights you might not discover from looking at your raw numbers. You’ll find that PivotCharts are powerful tools that allow you to effectively analyse your data.  

 

Thanks. Your download will begin shortly.

Please help us

Share or create a link to this manual today!

Just follow these simple instructions...


Server loaded in 0.7 secs.