Microsoft Excel - Advanced
Microsoft Excel Advanced
This course is designed for existing users of Excel who wish to move to an advanced level,
with a focus on increasing automation and employing the more advanced tools available.
Learning Outcomes
• Learn structured references, including cell/table-based references, table elements,
rows, ranges, totals and the union operator
• Master creating, editing, applying, managing and using named ranges in formulas
• Understand and apply dynamic array functions such as UNIQUE, SORT, FILTER,
CHOOSECOLS, INDEX, MATCH, XMATCH and XLOOKUP
• Develop skills in creating, customising, filtering, calculating and visualising data with
Pivot tables and Pivot Charts
• Utilise advanced excel functions including Date, Information, Math, Statistical and
Text Functions
Course Subjects
• Using Nested IF Functions
• Using AND with IF
• Using OR with IF
• Using NOT with IF
• Using IFERROR
• Using VLOOKUP for Exact Matches
• Using VLOOKUP for Approximate Matches
• Using HLOOKUP
• Using INDEX
• Using MATCH
• Combining INDEX and MATCH
• Using LEFT, RIGHT, MID
• Using LEN
• Using TRIM
• Using PROPER, UPPER, LOWER
• Using CONCATENATE
• Using TEXTJOIN
• Using REPLACE and SUBSTITUTE
• Using FIND and SEARCH
• Using DATE, TIME, NOW, TODAY
• Using DAY, MONTH, YEAR
• Using HOUR, MINUTE, SECOND
• Using WEEKDAY, WEEKNUM
• Using EOMONTH
• Using NETWORKDAYS and WORKDAY
• Creating Dynamic Named Ranges
• Using Named Ranges in Formulas
• Managing Named Ranges
• Understanding Array Formulas
• Creating Single-Cell Array Formulas
• Creating Multi-Cell Array Formulas
• Using Array Constants
• Creating Custom Conditional Formatting Rules
• Using Formulas in Conditional Formatting
• Managing Multiple Rules
• Creating Custom Validation Rules
• Using Formulas in Validation
• Creating Dependent Drop-Down Lists
• Combination Charts
• Secondary Axes
• Customising Chart Elements
• Advanced Formatting Techniques
• Advanced PivotTable Layouts
• Grouping Data in PivotTables
• Calculated Fields and Items
• Using Slicers and Timelines
• Creating PivotCharts
• Using Scenario Manager
• Using Data Tables
• Using Goal Seek
• Using Solver
• Using Advanced Filter
• Creating Complex Criteria Ranges
• Extracting Unique Records
• Recording Macros
• Running Macros
• Editing Macros in VBA
• Assigning Macros to Buttons
The above topics may include additional information and further insight depending
on the audience
John Wayne
7 Hrs + 3 Hrs Self Study
Advanced
English