Microsoft Excel Advanced

Refresher on VLOOKUP’s and Tables

Text manipulation

• Understanding text formulas
• Format data using Text to Columns
• Annotate Text from lists
• Remove formatting within cells

Consolidation

• Bringing multiple data sources together Connecting data to external sources including PowerBI

Working with the web and external data

• Inserting a hyperlink
• Importing data from an access database or text file
• Importing data from the web and other sources
• Working with existing data connections

Customising Excel

• Customising the ribbon
• Customising the quick access toolbar
• Using and customising autocorrect
• Changing Excel’s default options
• Creating a custom autofill list
• Creating a custom number format

Analysing data

• Creating scenarios
• Creating a scenario report
• Working with data tables
• Using goal seek
• Using solver
• Using text to columns
• Grouping and outlining data
• Using subtotals
• Consolidating data by position or category
• Consolidating data using formulas

Working with macros

• Recording a macro
• Playing and deleting a macro
• Adding a macro to the quick access toolbar
• Editing a macro’s visual basic code
• Inserting copied code in a macro
• Declaring variables and adding remarks to vba code
• Prompting for user input
• Using the if…then…else statement

Working with data ranges

• Sorting by one column
• Sorting by colours or icons
• Sorting by multiple columns
• Sorting by a custom list
• Filtering data
• Creating a custom autofilter
• Using an advanced filter