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 Tables, 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
- Rounding and math
- AND and OR
- Nested IF
- IFS function
- VLOOKUP and HLOOKUP
- Transposing data
- Concatenating
- INDEX and MATCH
- 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
- Get Data
- Transform in Query Editor
- Clean up data in Query Editor
- Create a table
- Add a table formula
- Add records to a table
- Add totals row
- Work with an outline – group and hide
- 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 VBA code
- Deleting a Macro
- Circular error messages
- Locating circular references
- When circular references are necessary
You might be interested in:
