Excel Advanced Functions

Develop Excel formulas for analytical work with tables, named ranges and multiple arguments. Practise conditional logic, time calculations, consolidation and formula-based formatting without jumping straight into the expert dynamic-array course.

View dates and fees

Level: Advanced
Length: 4 hours
Public class size: Up to 10

Who this course is for

People already using Excel who need the particular skills covered in this outline

What you will work on

Use tables and named ranges together in formulas
Apply ranges and references in functions
Conditional logic and data summaries
Time calculations and data consolidation
Advanced filtering and chart customisation
Text functions
Protect workbooks and worksheets
Conditional formatting driven by formulas

Outline checked against the Excel Advanced Functions booking page on 2026-09-11. Confirm any must-have feature or software version with us before registering.

Choose your starting point

A useful starting point is Excel Intermediate 2 or comparable experience. This is a suggested learning path unless an explicit requirement is listed below.

Review the topics in Excel Intermediate 2 to compare your current skills.

For another step in this subject, compare Excel Expert Functions and check that its topics match your next task.

Browse the Excel course comparison or ask for help choosing a course.

What comes with the public course

90 days of course video access with class exercise files
A free retake of the same course for one year
A Power Concepts certificate of completion

Already registered? Use student sign-in for your account, or find course manuals and exercise files.

Excel course questions

What should I know before Excel Advanced Functions?

A useful starting point is Excel Intermediate 2 or comparable experience. This is a suggested learning path unless an explicit requirement is listed below.

Where can I check dates and fees?

Use the course booking links on this page to see the available sessions and their current prices. Check the session time zone, delivery format, currency and tax total before completing registration.

Can my team take this training together?

Contact Power Concepts to discuss a private session. Tell us the number of learners, their current experience, the work they need to do and the software versions they use so we can recommend a suitable scope.

What certificate does the course include?

The course includes a Power Concepts certificate of completion. It is not a vendor certification exam or a professional accounting qualification.

Dates and registration

Dates and fees: Excel Advanced Functions

Choose a session on the booking site, then review the time zone, delivery details and final fee before registering.

Compare courses and training options

Free guide
Excel formulas, and how to pick the right one for the job
There are over 500 functions in Excel and you need about fifteen. This is the map — organised by what you are trying to do rather than alphabetically, which is how function lists usually fail you.
For teams
Excel Training for Teams and Businesses
Plan Excel training around your team’s starting skills, recurring spreadsheet tasks and the results learners need to check.
On this site
Excel Skills Assessment: Choose Your Course
Eight questions, no signup, and a recommended course at the end.
Course
Excel Expert Functions course
Extend an established Excel formula skill set with modern functions that expand as data changes. The expert course covers spill functions, TrimRange options, financial functions and lookup or reference functions.

Develop formulas that another person can check

Advanced Functions is a step beyond routine entry and basic calculations. Its published topics include conditional logic, text and time calculations, ranges, references and formula-driven formatting.

For practice, explain the inputs and expected result of a formula before making it more complex. Test a blank, an unexpected value and a normal record. If your objective is a particular newer function, confirm its coverage and version support before booking rather than inferring it from the word advanced.

Choosing training for lookup problems

A useful lookup exercise starts with an order table containing a product code and a separate price list containing the same code. The learner should explain which value identifies the product, which column supplies the answer and what should happen when the code is missing. That understanding matters more than memorising one function name.

If XLOOKUP is essential, identify your Excel version and ask for explicit syllabus confirmation. The published Advanced Functions outline describes lookup-related foundations and references broadly; this page does not promise that every named function is included in every session.

Working with an existing VLOOKUP workbook

Bring a sanitised example of the lookup table and identify whether the workbook expects an exact or approximate match. A price-list lookup should not quietly return a nearby product just because the requested identifier is missing. Test an existing product, a missing product and a duplicated identifier before trusting the result.

Training should also explain how a changed table layout affects the formula. Ask which lookup approach the instructor will demonstrate and whether your task is maintaining an existing workbook or designing a new one.

Understanding INDEX and MATCH together

Separate the question into two parts: find the position of the required record, then return the value from the chosen range. In a staff-directory example, a staff ID identifies the row while a department or location range supplies the result. Checking the two stages separately makes a broken lookup easier to diagnose.

Use a sample where a row is inserted and an identifier is absent. Ask for a lookup-focused exercise if maintaining INDEX/MATCH formulas is your main objective; a broad course label does not establish coverage of every combination.

Make IF rules explicit before writing them

Write the business rule in ordinary language first. For example, an order might be marked for review when its value exceeds an approved threshold. Decide what happens at the threshold, what happens below it and how a blank value should be treated. Those are different test cases.

Then translate the rule into a formula and test each case. Conditional logic is part of the published functions pathway; bring the rule you need to model so the course choice follows the complexity of the decision.

Summarise with more than one condition

A practical SUMIFS exercise uses a transaction table with a date, department and amount. Define the period boundaries and the department label before calculating a total. Otherwise, an apparently correct formula may exclude a late entry or combine two differently spelled departments.

Check the result by filtering the source table to the same conditions and comparing the visible records. Ask for the precise function coverage you need when booking; the key learning outcome is a summary you can reconcile, not a formula that merely returns a number.

Count records without confusing them with totals

COUNTIFS is useful when the question is how many rows meet several conditions, such as open enquiries assigned to a team during a month. Establish what one row represents first. Counting invoice lines is different from counting distinct invoices, and neither automatically counts unique customers.

A good exercise includes a repeated customer and more than one line for an invoice. Use the difference to decide whether you need conditional functions, a PivotTable or a data-model measure for the real report.

Clean identifiers with text tools

A useful text exercise separates a reference such as NORTH-1042 from a descriptive label while preserving the original value in another column. Discuss leading spaces, inconsistent separators and codes that start with zero. Those small details can decide whether a later lookup matches correctly.

The Advanced Functions outline includes text functions. Bring a few anonymised examples of the awkward values you receive, including exceptions, so you can choose between a worksheet formula approach and repeatable preparation with Power Query.

Treat dates as data, not just display formats

A date can look correct while being stored as text or interpreted with the wrong month and day order. Build an exercise with an unambiguous date, an ambiguous imported date and a blank. Confirm how each behaves in sorting and time calculations before formatting the result.

For scheduling or period reporting, agree which dates belong inside the period and whether time-of-day matters. The functions pathway includes time calculations; repeated import problems may be better addressed in the data-management pathway.

Handle errors without hiding missing information

An error can reveal a missing code, an invalid input or a broken connection. Decide which condition is acceptable to display as blank and which must be investigated. Returning zero for every problem can make a report look tidy while changing its meaning.

Use a test list containing one valid record, one missing identifier and one invalid amount. The right training outcome is a visible exception process with a correct calculation, not a sheet where all warning signals have been suppressed.

Use formatting to reveal a rule

Conditional formatting should communicate a defined condition, such as a missing date or an amount outside an approved range. Write the rule first and test a value on either side of its boundary. Colour alone should not be the only way to identify the issue; pair it with a label or exception list.

Intermediate 2 introduces conditional formatting in practical work, and Advanced Functions includes formula-driven rules. Choose the level that matches whether you need a simple visual cue or a more complex rule tied to other cells.

Audit the dependency before changing the answer

When a total looks wrong, identify its inputs before replacing the formula. In a budget example, trace the rate, quantity and reference to the assumption cell. Compare a correct row with the first incorrect row and look for a shifted reference or unexpected text value.

Ask for a formula-auditing exercise that includes an error you can reproduce. Keep the original workbook copy so that a fix can be compared with the prior calculation rather than judged solely because the error message disappeared.