Microsoft Excel Advanced

This course exposes several advanced features of Excel for What-if analysis, database analysis and how to build calculations using advanced functions as well as how to automate processes

2 days course
4.61  (1781 reviews)
What you will learn:
  • Build complex calculations with advanced functions
  • Link, export and consolidate data
  • Limit data entry and build complex models with Lookup functions
  • Analyse data with simple PivotTables and PivotCharts
  • Create and modify Macros
What you'll get:
Workbook
Refreshments
Capped class sizes
eCertificate
Course prerequisites

Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Intermediate course.

What's covered?
  • Workbook protection
  • Protecting cells
  • Protecting structure
  • Math and rounding functions
  • The AND function
  • The OR function
  • Nested IF function
  • The VLOOKUP function
  • The HLOOKUP function
  • Transposing data
  • Concatenating functions
  • INDEX and MATCH functions
  • Using help for functions
  • Defining range names
  • Using names in formulas
  • Navigating with names
  • Using named constants
  • Documenting range names
  • Limiting cell values
  • Creating drop-down cell lists
  • Validation error messages
  • Using goal seek
  • Importing CSV files
  • Text import wizard
  • Workbook linking
  • Dealing with broken links
  • Consolidation by cell position
  • Consolidation by headings
  • Saving as a different file type
  • Exporting to Word
  • Pasting data with a link
  • Copying a graph with a link
  • Create a PivotTable
  • Rearranging data
  • Applying filters
  • Adding fields
  • Modify field settings
  • Refreshing data
  • Formatting a PivotTable
  • Creating a PivotChart
  • Filtering a PivotChart
  • Recording a Macro
  • Editing a Macro
  • Relative/Absolute Recording
  • Stepping through a Macro
  • Macro buttons
  • Customising the toolbar
  • Writing a line of code
  • Deleting a Macro
  • Locating and deleting circular references
  • When circular references are necessary
  • Formula error checking
    Reset options
- +
Need your training approved? Get a quote
4.61
4.61
from 1781 reviews
5/5
“Great structure, just the right detail. Overall happy. Thank you Annie!”
Annika, May 2019
5/5
“Very knowledgeable instructor. Very enthusiastic and engaging.”
Oliver, May 2019
5/5
Fabien, May 2019
5/5
“Annie was lovely and very knowledgeable. ”
Kelly, May 2019