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
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
