Microsoft Excel Advanced
COURSE AIMS
This course is for attendees who want to expand their knowledge into some of the more advanced functions, analyse / summarise tables of data and automate common tasks.
COURSE PRE-REQUISITES
Attendees who wish to attend this course must have a good knowledge of Excel and be competent at working with formulas using absolute cell references, use basic functions like sum, average, max, min, count and be able to write formulas across multiple sheets and multiple workbooks.
COURSE OBJECTIVES
On completion of this course, delegates will be able to:
- Use logical, lookup, statistical and error functions
- Use auditing tools
- Create range names and use range names in formulas
- Set restrictions on data entry using data validation
- Protect an Excel cell, worksheet, and workbook
- Automate common task by recording a basic macro
- Create, edit, format Pivot Tables and Pivot Charts
COURSE CONTENT
Introduction and Objectives
Using Logical, Lookup and Statistical Functions
- Using the VLOOKUP / HLOOKUP functions
- Using IF, AND & OR function
- Using the IFERROR function
- Using COUNTIF & SUMIF
Using Auditing Tools
- Displaying precedents and dependents
- Removing arrows
- Evaluating a formula
- Setting manual & automatic calculation
Using Range Names
- Assigning names to ranges
- Using range names in formulas
- Editing and deleting named ranges
Using Data Validation
- Validating data using a data validation list
- Creating a custom input and error message
- Editing a validation list
- Removing data validation
Protecting Excel Data
- Protecting entire worksheet
- Protecting and unprotecting certain cells within a worksheet
- Protecting workbook structure
Introduction to Basic Macros
- What is a macro?
- How to record macros
- How to run a macro using different methods
- Editing and deleting macros
Creating / Revising Pivot Tables and Pivot Charts
- Creating a Pivot Table
- Adding, moving,removing Pivot Table fields
- Formatting and structuring a Pivot Table
- Filtering Pivot Table items
- Changing the summary function
- Summarising data as percentages
- Refreshing a Pivot Table
- Changing the data source for a Pivot Table
- Adding a Pivot Table slicer
- Moving & deleting a Pivot Table
- Creating more than one pivot table on the same worksheet
- Creating a Pivot Chart
- Formatting a Pivot Chart
Hints and Tips
Related Courses
Public Schedule
Virtual Private Training
Contact us for pricing
Onsite Training
Contact us for pricing
Note
All prices exclude VAT at 20%.
VAT registration number: 450 4347 14
Microsoft Excel Advanced
9:30am - 4:30pm
Online Live
£175 per delegate
Microsoft Excel Advanced
9:30am - 4:30pm
Online Live
£175 per delegate
Microsoft Excel Advanced
9:30am - 4:30pm
Online Live
£175 per delegate
You may like...
Discover SharePoint's capabilities with our End-User course, improving collaboration, document management, and productivity in your team.
N/A
1 day
Excel in project management with our 1-day Advanced Microsoft Project course. Master complex scheduling, reporting, and customisation to lead successful projects.
N/A
1 day
Take your Microsoft Project skills to the next level with our Intermediate course. Learn advanced tools and techniques to manage projects efficiently.
N/A
1 day