03 Advanced Excel Course

Additional
Productivity Tools

This course is designed to introduce higher-level Excel tool-set functionality and take intermediate-level users to the power-user level. Not only does the course introduce a host of new Excel tools and functions, but it also expands on the functionality of intermediate-level tools, adding powerful complimentary functions that make Excel jump.

  • For advanced users who want to level-up their Excel tool-set skills
  • Customised course content so you learn exactly what you need
  • Learn the principles of macros, VBA and event-driven programming
Get a Quote
About course

Course Details

Customisation
This training course can be customised at no additional cost to include topics from our other courses.
Course delivery method
This course can be delivered on-site at your premises to individuals or groups, or online through live facilitator-led remote-access learning to individuals or groups.
Course duration
Full course duration is 1 day, but training can be delivered in shorter blocks.
What you will learn

Topics Covered

XLOOKUP
VLOOKUP - Advanced
Text Functions
Importing Data
VSTACK, SORT, Filter & UNIQUE
Recorded Macros and VBA Introduced
Database Functions
Additional Functions
INFORMATION Functions
Advanced Conditional Formatting Techniques
Date and Time Functions
Controls

XLOOKUP

This toolset will add power to your existing lookup functions, and reduce the time it’ll take to scale up their use.

  • Using XLOOKUP for exact and range matches
  • Creating exact match error messages
  • Searching from last to first
  • Using multiple cell lookup values
XLOOKUP

VLOOKUP - Advanced

This deeper look at VLOOKUP adds extra functionality and ease of use.
  • Automatically incrementing column numbers using COLUMN and MATCH
  • Using Wildcard searches
  • Finding matches and mismatches between columns
  • Using text functions to deal with data mismatches

Text Functions

Manipulating and separating strings of text will cut down the time you'll need to spend performing large-scale data handling.

  • Using TEXJOIN, CONCATENATE and free-form concatenation
  • Using TIRM, LEFT, RIGHT and MID
  • Using LENGTH, SEARCH and SUBSTITUTE

Importing Data and Fixing Data Errors

Bringing data into your Excel file is simple, but ensuring you have the tools to “clean” the data while doing so will save work and time.

  • Importing Data from a CSV
  • Removing unwanted characters using CLEAN, TRIM and SUBSTITUTE
  • Ensure Data is imported correctly

VSTACK, SORT, FILTER and UNIQUE

These functions allow you to use formulas to combine, sort and filter data scattered throughout different non-connecting ranges.

  • Using UNIQUE and SORT with single and multiple columns
  • Using FILTERS
  • Using CHOOSECOLS
  • Using VSTACK to combine ranges and tables

Recorded Macros and VBA Introduction

Automating your spreadsheet with recorded macros is just the start. We look at VBA code and creating event-driven procedures.

  • Recording, running and modifying Macros
  • Assigning macros to icons and buttons
  • Creating user-defined functions
  • Understanding event-driven macros
INFORMATION Functions

Database Functions


This category of functions allows you to perform AND/OR calculations on large databases at lightning speed, with zero errors.

  • Using CEILING.MATH and FLOOR.MATH
  • Extending Lookup Functions using DGET()

Additional Functions

Adding this group of tools to your Excel spreadsheets can eliminate lengthy workarounds, and help guarantee decimal accuracy.
  • Using CEILING.MATH and FLOOR.MATH
  • Using SUMIF, SUMIFS, COUNTIF and COUNTIFS
  • Using ROUND, ROUNDDOWN, ROUNDUP and MROUND

INFORMATION Functions

We look at the role of these condition testers as both stand-alone tools and as used within IF functions.
  • ISNUMBER() & ISTEXT()
  • ISBLANK() & ISFORMULA()
  • Using Information Functions to support other functions

Advanced Conditional Formatting Techniques

Conditional formatting is a powerful tool, and we add weight to the basics by focusing on formula-driven formatting techniques.
  • Using formulas to define formatting rules
  • Highlighting entire Rows
  • Identifying duplicates and unique items within and across columns

Date and Time Functions

We take date calculations past the basics, including how to factor in non-working days, holidays and breaking down time components.
  • Using NETWORKDAYS and NETWORKDAYS.INTL
  • Using WORKDAY, WEEKDAY, EOMONTH and DATEIF
  • Using TODAY, NOW, DAY, MONTH and YEAR

Controls

Controls help automate spreadsheets and are great for end-users. We'll learn to set them up and create solutions that users will value.
  • Adding A Combo Boxes, List Boxes and Scroll Bars
  • Designing a Control-based worksheet
  • Adding Option Buttons, Spin Buttons and Group boxes

Benefits

Learn how to automate your spreadsheets using macros and gain an understanding of VBA code and event-driven programming.
Understand how to manipulate complex text strings to extract valuable text-string subsets using a variety of text functions.
You’ll add power to your existing function usage by combining them with additional tool categories, creating “nested” solutions.
You’ll learn advanced conditional formatting techniques  
Learn to create dashboard-type interfaces using Controls, including spin buttons, list boxes, check boxes and scroll bars.

Full course outline

The bulleted items listed above comprise a condensed overview of course topics.

Download full course outline (PDF)

Prerequisites

This course is designed for confident Microsoft Excel users who are comfortable with creating formulas above the level of SUM, AVERAGE, MAX, MIN and COUNT. Participants will also be experienced with database sorting and filtering techniques.

Test your teams skills

Get a Free Training Needs Analysis

Request training needs analysis
Courses
Company
On-Site Training
Contact
Services
Copyright © 2020 Excel Dimensions  |  Privacy policy | Articles