Microsoft Excel. Unlock insights and tell the story in your data.

This short course focuses on the more advanced features beyond the Microsoft Excel Course: Level 2. You’ll learn how to use Power Query to transform and link to external data, and data consolidation to combine data. The course also includes list analysis tools, summarising and subotalling, PivotTables, using Power Pivot to create PivotTables based on multiple sources, and how to create a report dashboard with PivotTables, charts and slicers to filter reports.

Additionally, we’ll cover Lookup Functions XLOOKUP, INDEX, XMATCH and FILTER and their differences, key automation features like macros and controls (scrollbars and buttons) for easier data entry, and planning and budgeting tools from the What If analysis group. Finally, validating data to prompt and check for correct data is covered; and hints and tips chapter.

We encourage you to use the CCE Excel level self-assessment tool if you are unsure which course level to enrol in.

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.

Content

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

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

Prerequisites

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.

Delivery modes

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

Delivery style

Face-to-face classes (CCE, Newtown)

These classes run in a computer lab and you do not need your own device.

Face-to-face classes (external venues including PARKROYAL and CBD)

These classes run in a classroom and you need to bring your own device.

Online classes

You will need your own device.

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 or 365
• Recommended software for Mac OS: MS Excel 2016, 2019 or 365
• Recommended browser: Chrome