98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel 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,559 testimonials) |
From £300 List price £350
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
Our Microsoft Excel Pivot Tables course is suitable for those with a basic working knowledge of Pivot Tables who want a greater understanding of the more sophisticated functions and features available.
You may also wish to consider our soft skills training workshops.
Prerequisites
Prior use of Pivot Tables or our Excel Advanced course.
Benefits
- At the end of this course you will have a greater understanding of the functionality and potential of Pivot Tables.
- You will be able to construct and manipulate complex summaries and analyses of data using Pivot Table tools, control visual and data formatting, manage and add to the statistical analysis of your data, deal with varying data sources, automate, troubleshoot and utilise Pivoted data in many different contexts.
Course Syllabus
Pivot Table Fundamentals
What is a Pivot Table?
When and why use a Pivot Table?
Anatomy of a Pivot Table
Limitations of Pivot Tables
Creating Basic Pivot Tables
Preparing your data
· Tabular Layout
· Removing Section Headings
· Removing Repeated Column Groups
· Eliminating Gaps
· Type Formatting
Creating Pivot Tables
· Adding Fields
· Adding Layers
· Altering Structure
· Report Filters
Managing Changes in Your Source Data
· Dynamic Named Ranges
· Dealing with Zeroes
Subtotals
Summary Calculations
· Running totals
· Differences (year on year, month on month)
· Percentages of Rows, Columns, Totals and other fields
Running Totals and Top 10 reports
Pivot Table Tools
· Layout Updates
· Refreshing & Restarting
· Moving Pivot Tables
Pivot Table Views
Conditional formatting
Sorting, Filtering and Re-ordering
· Slicers (2010 / 2013 only)
Saving Custom Views
Pivot Table Calculations
Calculated Fields and Items
Managing & maintaining Pivot Table calculations
Pivot Charts
What is a Pivot Chart?
Creating a Pivot Chart
Pivot Chart Rules
· Managing the underlying data
· Adding Layers
· Altering Structure
Pivot Chart Alternatives
· Dynamic Charts
· Transition tables
Working with Data Sources
Consolidating multiple sheets or ranges of data
Using external data sources (Access)
Automating Pivot Tables with Macros
Introducing Macros
Recording Pivot Table macros
Tidying and refining recorded macros
The Power Pivot add-in (2010 / 2013 only)
Summary
Frequent Pivot Table Questions and Answers
Solutions to common problems
Prices & Dates
Version 2003 run on demand. Please contact us.
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
(last 12 months) | (2614 reviews, see all 99,559 testimonials) |
Rebecca Clayden,
Ops Analyst
A few more practical examples for the class to complete idependently, otherwise great course
Excel Pivot Tables
BMW Group
Gemma Whittington,
Accounts Assistant
Another great course executed well by Cindy
Excel Pivot Tables
Generation Media
Zermeen Shah,
Digital Media Executive
A bit slower pace, and handouts with an explanation would be useful to look at as we're using excel ourselves in the course training
Excel Pivot Tables
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”.
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’
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.
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...