Advance your Excel expertise with tools like data consolidation, lookup functions, and automation via macros. This course focuses on complex analysis, PivotTables, and efficient data management techniques.
 

Aims

This course aims to provide experienced Excel users with proficient skills in developing more complex formulas, list analysis using a variety of tools and creating simple macros as well as features and tips to assist efficiency.

Outcomes

By the end of this course, you should be able to:

• modify Excel options
• import data into Excel using Power Query
• use Power Query to transform and link to data
• use data linking to create more efficient workbooks
• create summaries in your workbooks using subtotals
• use a range of lookup functions
• use the Data Consolidation feature to combine data from several workbooks into one
• create, use and modify data tables
• create and work with scenarios and the Scenario Manager
• construct and operate Pivot Tables using some of the more advanced techniques
• create and edit a Pivot Chart
• use Power Pivot to create summary reports from separate lists with a Pivot Chart and Slicer
• use PowerPivot to summarise data from multiple sources
• use a variety of Data validation techniques
• create and use a range of controls in a worksheet
• create recorded macros in Excel.

Set Excel options

• Understand and personalise Excel options
• Understand and set save options
• Default local file location
• Set number of recent workbooks
• Set number of pinned workbooks

Import & Link to External Data

• Understand Data Importing
• Understand Text File Formats
• Import Data from a Picture
• Import Data from a Text File using Power Query
• Edit a Query
• Refresh Data
• Unlink Connections
• Transform Data using Power Query

Data Consolidation – Combining Data

• Understanding data consolidation
• Combining data from multiple sources

Summarise & Subtotal Lists

• Creating Subtotals
• Using a Subtotalled Worksheet
• Creating Nested Subtotals
• Copying Subtotals
• Using Subtotals with AutoFilter

PivotTable features

• Create a PivotTable (refresher)
• Display Percentage of Total
• Changing the Calculation in a PivotTable
• Creating and deleting Calculated Fields
• Creating and deleting Calculated Items
• Rename PivotTable Fields
• PivotTable Options
• Instant PivotTable Reports
• Refreshing PivotTables

PivotTable Dashboard

• What is a Dashboard?
• Dashboard Elements
• Understanding Slicers
• Inserting a Slicer
• Formatting a Slicer
• Inserting a Timeline
• Connecting a Slicer to Multiple PivotTable Reports
• Connecting a Timeline to Multiple PivotTable Reports
• Create a PivotChart

Power Pivot

• Power Pivot – Overview
• Understanding the Data Model
• Enabling Power Pivot
• Insert a Power Pivot report from multiple tables
• Insert a Pivot Table using the Data Model

Lookup Functions

• XLOOKUP
• INDEX
• XMATCH
• FILTER

Validating Data

• Understanding Data Validation
• Creating a Number Range Validation
• Creating an Input Message
• Creating an Error Alert
• Creating a Drop Down List
• Using Formulas as Validation Criteria
• Circling Invalid Data
• Removing Invalid Circles
• Copying Validation Setting

Data Tables – A What If Analysis Tool

• Understanding Data Tables and What-If Models
• Using a Simple What-If Model
• Creating a One-Variable Data Table
• Using One-Variable Data Tables
• Creating a Two-Variable Data Table

Controls

• Understanding Types of Controls
• Preparing for Simple Checkboxes
• Inserting a Simple Checkbox
• Count Checkboxes
• Adding a Scrollbar Control
• Changing Control Properties
• Using the Cell Link to Display the Selection
• Protecting a Worksheet with Controls

Recorded Macros – Automate Repetitive Actions

• Understanding Excel Macros
• Setting Macro Security
• Saving a Macro Enabled Workbook
• Displaying the Developer tab
• Relative Cell References
• Recording a Simple Macro
• Running a Recorded Macro
• Viewing a macro
• Editing a Macro
• Assigning a Macro to a Control Button
• Assigning a Keyboard Shortcut to a Macro
• Deleting a Macro

Scenarios – A What If Analysis Tool (optional topic)

• Understanding Scenarios: Preparing for Scenarios - Creating Range Names, Creating a Default Scenario, Creating Scenarios, Displaying Scenarios
• Creating a Scenario Summary Report: Managing Scenarios - edit, merge and delete

Hints and tips (optional topic)

• Focus Cell
• Navigation Pane
• Check Performance
• Picture in cell
• Copying pictures in cell
• Using lookup formulas with pictures

Audience

Assumes an intermediate level understanding of the software and experience in the construction and modification of workbooks. It is designed for those who are ready to explore more of the advanced analysis and automation tools. 

Delivery mode

• Face-to-face, presenter-taught training
• Online via Zoom

Materials

Course materials are provided electronically using Dropbox. Teaching instructions and materials are specifically tailored to Windows PC users.

Additional information

Recommended operating system: Windows or Mac
Recommended software for PC: MS Excel 2013, 2016, 2019, 365
Recommended software for Mac OS: MS Excel 2016, 2019, 365
Recommended browser: Chrome

Participants should have already completed the Excel Level 1 and Excel Level 2 courses or have Excel skills up to the Level 2 (intermediate) course.