Microsoft
Microsoft Excel Excel Tables & PivotTables
Turn long lists into PivotTables that summarise, group and calculate - sophisticated reports in minutes.
Overview
This course is designed to develop experience and confidence in using Excel pivot tables to analyse data and produce sophisticated management reports. Learn to create and work with Pivot Tables to view and analyse your data in a variety of ways. Discover how to perform a variety of calculations with Pivot Table Data. You will learn how to construct Pivot Tables and Charts to consolidate and summarise your data.
Who Should Attend
Users needing to manipulate and analyse data stored in Excel lists using pivot tables.
Prerequisites
Intermediate level knowledge and experience with Microsoft Excel.
Benefits of Completing This Course
• Gain quick insights into your data
• Perform powerful data analysis
• Create powerful data models
Inclusions
- Training Manual with step-by-step instructions
- Practice files relevant to the training material
- Training Completed files - you are free to take away any completed files for review
- Unlimited online support, as training doesn't stop when you walk out our door
Learning Outcomes
• Create and manipulate PivotTables
• Present data in a variety of ways
• Create calculations in PivotTables
• Create varoius views of data
• Create PivotTables from external data
• Summarise data from multiple sources using PowerPivot
Topics Covered
PivotTable Fundamentals
● What is a PivotTable?
● Why should I use a Pivot Table?
● What are the advantages?
● PivotTable Terminology
● Preparing your data for use in a PivotTable
Creating a PivotTable
● Selecting the Data Source
● PivotTable Fields List
● Filterering data with Page Fields
● Adding Fields
● Removing Fields
● Understanding the Field icons
Presenting Data in PivotTables
● PivotTable Toolbar
● Hiding and Unhiding Items
● Sorting Data in a PivotTable
● Using AutoSort
● Advanced AutoSort
● Refreshing a PivotTable Report
● Inserting Data into the Data Source
● Formatting Numerical Data
● Selecting Parts of a PivotTable
● PivotTable Formats
● PivotTable Options
● Change the Summary Function
● Adding Multiple Data Fields
● Changing Calculations
● Hiding and Showing Row/Column Details
● Displaying Data Details
Grouping Data
● Grouping Data
● Group by Dates
● Group by Number
● Ungrouping Data
Calculations in PivotTables
● Changing custom calculations
● Creating Calculated Fields
● Calculated Items
● Using GetPivotData() function to extract information from the PivotTable
Using Other Sources of Data
● Selecting data from another Excel workbook
● Connecting to an Database i.e. Microsoft Access database
● Using a Saved Query as the data source
● Using a Comma-delimited text file as the source of a PivotTable
Using PivotCharts
● PivotChart terms
● Modifying the Chart
● Caution - Loss of Formatting in PivotCharts
Publishing PivotTables to the Web
● Saving a PivotTable as a web page
● Creating Interactive PivotCharts - Web
● Adding Fields to a PivotChart - Browser
Analysing Data from Multiple Ranges
● Setting up the PivotTable using multiple ranges
● Using Multiple Page Fields
Re-using PivotTables
● Requerying data
● Using Saved queries
● Automating PivotTable Updates
● Saving a PivotTable Template
Live online - learn by doing.
Our Live Online courses are conducted through a dedicated learning platform that enables us to provide instructor-led, hands-on training to develop real skills, in real time. Each participant logs into their own virtual environment set up specifically for the course. This is a fully interactive experience - just like in a classroom. No passive lectures, no videos to follow - real-time, hands-on practice with your instructor guiding you every step.
Instructor
- Provides real-time, live hands-on instruction and demonstration
- Provides step-by-step practical activities for participants
- Can assist participants on their screen, if necessary
- Can share participant screens to enhance discussion
Participants
- Receive hands-on instruction, step-by-step exercises and skill-builder activities
- See the instructor's screen while working in their own environment
- Can interact with the instructor and the group of participants
- Can ask questions at any time
- Join the course from any location with internet, using a course invitation
- No need for any software - just a computer with internet and a browser
- No downloads or installs necessary on the participant's computer
Inclusions
- E-Book Training Manual / activity booklet provided
- Certificate of Completion
- Post-course support to the level of the course
- Access to any training exercises used during the session
Session Times
- Full Day Course: 9:00am - 4:30pm
- Half Day Course: 9:00am - 12:30pm or 1:30pm - 5:00pm
Private sessions are booked exclusively for your team and can be facilitated as Live Online group training, or in a Classroom / Face-to-Face setting at your workplace or another organised location. Content and duration can be tailored to your needs - contact our Course Scheduling team to discuss your requirements.
Classroom / Face-to-Face
Classroom sessions are delivered at a nominated location, available for Private courses only.
Requirements (if training is delivered at your site)
- A computer is required for each participant, configured with appropriate software (if necessary)
- If participants are using laptops, please ensure power cords are provided for use during the session
- The instructor will bring their own laptop - please provide power
- A screen or projector for the trainer to connect their laptop to (please indicate the connection method - HDMI or Wi-Fi)
Inclusions
- Training Manual / activity booklet
- Certificate of Completion
- Post-course support for queries relating to the course content
Get in touch with our Course Scheduling team to arrange a private session or confirm delivery options for a specific date.