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

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

Database Functions

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

  • DSUM(), DMIN(), DMAX(), DAVERAGE(), DCOUNT()
  • Extending Lookup Functions using DGET()
Database Functions

Text functions

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

  • Using TEXTJOIN, CONCATENATE and free-form concatenation
  • Using TRIM, LEFT, RIGHT and MID
  • Using LENGTH, SEARCH and SUBSTITUTE

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

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

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 DATEDIF
  • 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.

  • Designing a Control-based worksheet
  • Adding A Combo Boxes, List Boxes and Scroll Bars
  • Adding Option Buttons, Spin Buttons and Group boxes

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
INFORMATION Functions

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
  • Ensure Data is imported correctly
  • Removing unwanted characters using CLEAN, TRIM and SUBSTITUTE

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