98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Excel PivotTable Training Course
Excel PivotTable Training CourseFace to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
| (last 12 months) | (2660 reviews, see all 98,621 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
Virtual 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
Summary
| (last 12 months) | (2660 reviews, see all 98,621 testimonials) |
Capita Resourcing
Faye Rivers,
Admin
I cant believe I hadnt done this course sooner!
I finally understand excel including formula's and their not scary anymore, I can now make excel work for me!
Excel Pivot Tables
Gateley Smithers Purslow
Kirsty Gray,
Credit Control Assistant
Thank you Jens, I really enjoyed the course and feel what I have learnt will benefit me in my role.
Excel Pivot Tables
Ideaworks
Beth Payne,
Finance Assistant
Really good course and gave me a lot of knowledge in pivot tables! I could do them very basically but after the course, I feel so much more confident to do them
Excel Pivot Tables
Buy now Public schedule Live
Get On-site/Closed quote
Excel range
Excellent
HSBC
Project Manager
Alexander Orlov
I was looking for a specific training for my data analytical team to equip them with more tools to slice and dice the data, building more trust worthy sophisticated propensity models, using SQL, VBA and Excel. I came across with STL training and my research and expectations were match by 100%. Spot on! Great professionals! Highly recommended for all banking and financial professionals that are using Excel on BAU basis.
Resources
Promotions
Latest Feedback
Latest Tweet
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”.