Advanced Excel Course
Instructor-Led Advanced Excel Courses
Learn new advanced Excel skills with our one-day course.
Using Excel for awhile and want to expand your knowledge? Explore new advanced features of Excel to improve your data analysis skills with our one-day advanced Excel course.
This course is available in-person or online.
Explore advanced data analysis tools.
Learn to use the full range of features available in Excel including data protection, data linking, tables, scenario manager, Pivot tabes, advanced filters, data validation and more.
Advanced Excel Course
Who is this course for?
This course is aimed at those who are experienced Excel users and wish to expand their skills to utilise some of the advanced features of Excel.
Learning Outcomes
At the completion of Microsoft Excel Advanced you should be able to:
- protect data in worksheets and workbooks
- use data linking to create more efficient workbooks
- create summaries in your spreadsheets using subtotals
- use the Data Consolidation feature to combine data from several workbooks into one
- create, use and modify data tables
- create and work with scenarios and the Scenario Manager
- create and edit a PivotChart
- construct and operate PivotTables using some of the more advanced techniques
- use advanced filters to analyse data in a list
- use a variety of data validation techniques
- create and use a range of controls in a worksheet
- create recorded macros in Excel
Prerequisites
Our advanced Excel course assumes reasonable knowledge of the software as well as a general understanding of personal computers and the Windows operating system environment.
Course Duration
Our advanced Excel course is run over one-day but can be customised to suit your needs.
Course Overview
Protecting Data
- Protecting a worksheet
- Disabling worksheet protection
- Password protecting a workbook
- Opening a password protected workbook
Importing and Exporting
- Importing from an earlier version
- Understanding file formats
- Importing tab delimited text
- Importing space delimited text
- Importing Access data
- Working with connected data
- Exporting to Microsoft Word
Data Linking
- Link data between worksheets
- Link data between workbooks
- Update links between workbooks
Group and Outline
- Create an automatic outline
- Working with an outline
- Creating a manual group
- Grouping by columns
Summarising and Subtotaling
- Create subtotals
- Using a subtotaled worksheet
- Copying subtotals
- Using subtotals with AutoFilter
- Creating relative names for subtotals
Data Consolidation
- Consolidating with identical layouts
- Creating a linked consolidation
- Consolidating from different layouts
- Consolidating data using the SUM function
Data Tables
- Using a simple What-If model
- Creating a one-variable table
- Creating a two-variable table
Scenarios
- Creating a default scenario
- Creating scenarios
- Using names in scenarios
- Creating a scenario summary report
PivotCharts
- Insert a PivotChart
- Defining the PivotChart structure
- Changing the PivotChart type
- Moving PivotCharts to chart sheets
PivotTable Features
- Counting in a PivotTable
- Formatting PivotTable values
- Working with PivotTable grand totals
- Finding the percentage of total
- Finding the difference from
- Grouping in a PivotTable
- Creating calculated fields
- Sorting in a PivotTable
Advanced Filters
- Using an Advanced Filter
- Extracting records with advanced filters
- Using formulas in criteria
- Using database functions
- Using DSUM
- Using DMIN and DMAX functions
Validating Data
- Creating a number range validation
- Testing a validation
- Creating an Input message
- Creating an Error message
- Circling invalid data
Controls
- Preparing a worksheet for controls
- Adding a combo box control
- Adding a list box control
- Adding a scroll bar control
- Adding a spin button control
- Adding a group box control
- Protecting a worksheet with controls
Sharing Workbooks
- Sharing Workbooks
- Open shared workbooks
- Enabling Tracked Changes
- Accepting or rejecting changes
- Adding worksheet comments
Macros
- Setting Macro Security
- Saving a document as Macro enabled
- Recording a simple macro
- Viewing a macro
- Editing a macro
- Running a macro
- Delete a macro
Course Inclusions
Skills Analysis
Take advantage of our free online skills analysis prior to your training to ensure have are undertaking the correct course.
Certificate
After completing the course you’ll receive a certificate to show off your new-found knowledge and skills.
We come to you
To save your business time, money and unnecessary travel, we come to you, delivering our training at your premises.
Hands-on training
We’re big believers in hands-on learning so you’ll be on a computer and building your skills in real-time.
Email Support
Access unlimited support following your training course to build on your learning and ask any burning questions.
Real-world examples
You'll work through real-world examples to reinforce learning and increase your confidence.
Customer Testimonials
“Discussing the learning material, providing examples and then being able to personally put this into practice was beneficial to my learning experience.”
“Very informative, learnt a great number of time saving techniques.”
“Amazed at how many tips and tricks I was not aware of in Excel. Explained and demonstrated in an easy way for all attendees to understand.”
“The training was easy to follow and understand the reason why you would need and use certain functions within Excel. There were so many more uses for Excel than I ever would have realized.”
“Belinda was fantastic, explained everything in terms we can understand.”
“Belinda was great, the course content & her instructions were easy to follow & a bubbly personality keeping everyone engaged.”
“Course was informative and being able to perform the activities in real-time assisted with my ability to retain the information as we were working through.”
“Great! So easy to digest. Found a lot of extra features I could be using & shortcuts.”
“I was a bit worried at first. It was very easy to listen and learn from Belinda. I was ok after the first 5 minutes.”
“Gave organised steps & reasons for them. Practical examples of where and why we use certain functions.”
“The trainer was extremely knowledgeable and taught the course content in language that was easy to understand.”
“Trainer had great enthusiasm and knowledge about Excel and how to use it.”
“Great – will save an hour a week through efficiency improvements.”
“Belinda was very knowledgable of the subject and was good at tailoring the training to suit the way we use the software.”
“Very informative. Learnt a lot of new things and shortcuts.”
“It was great to expand on previous knowledge and pick up a wide variety of new skills.”
“Belinda was great. Everything was covered in detail and explained so we could understand.”
“Professional, thorough, easy to understand. Very well presented.”
“Very helpful and explanatory. Belinda is very thorough and patient.”
“Belinda breaks everything down so it’s easy to understand.”
“Really enjoyed it. Explained so much that will help me in my job.”
“Absolutely amazing, Belinda explains everything & if you don’t understand, she will go back through it.”
“Clear info, wonderful to have everything explained so well. Belinda took her time and was patient when we needed things explained again.”
“Belinda was very clear in her presentation. The pace of the training was helpful.”
“Today has been a wonderful day learning more in a fun, interactive & personal way. Thank you so much.”
“Extremely well communicated. Clear and concise instructions.”
“Brilliantly simplified and explained information with a very practical focus.”
“Belinda was great! The pace was just right for me. I have learnt so much. Belinda is very organised, which improves the learning experience.”
Related Courses
Not quite ready to undertake an advanced Excel course? Check out our other Excel course options.
Excel Introduction
- 1 day course duration
Excel Intermediate
- 1 day course duration