Advanced Excel Functions
| Course Details | ||||||||
|---|---|---|---|---|---|---|---|---|
|
||||||||
| Languages | ||||||||
|
||||||||
| Outcomes | ||||||||
|
||||||||
| Price & Terms | ||||||||
|
||||||||
| Technical | ||||||||
|
||||||||
| Specification | ||||||||
|
||||||||
Learn XLOOKUP, INDEX and MATCH, OFFSET with dynamic ranges, and Excel database functions such as DSUM to look up and summarize large datasets.
XLOOKUP and the INDEX and MATCH pair take Excel lookups past VLOOKUP, including lookups in both directions across large datasets. The lessons then use OFFSET to build ranges that shift with a chosen period, and apply DSUM, DCOUNT and DAVERAGE to total, count and average the rows that meet set criteria.
What You Will Achieve
- Retrieve values from a table in both directions with XLOOKUP or a combined INDEX and MATCH formula
- Transpose and rank a dataset with formulas that return arrays
- Build a comparison tool and chart that update when the reporting period changes
- Set up a criteria range with AND, OR and wildcard conditions to summarize a large dataset, and fix the errors it raises
Program Overview
XLOOKUP, INDEX and MATCH Lookups
Moves past VLOOKUP to more flexible lookup and reference functions, with practical use of arrays along the way.
- XLOOKUP and INDEX with MATCH
- Lookups in both directions
- Matching positions in a range
- Transposing and ranking data
OFFSET and Dynamic Name Ranges
Uses the OFFSET function to set up ranges that move with the data, combined with INDEX, MATCH, SUM and charts.
- OFFSET in calculations
- Dynamic ranges for a chosen period
- A comparison tool and its visualization
Database Functions for Criteria-Based Aggregation
Sums, counts and averages records that meet defined conditions, with advice on the errors these functions commonly produce.
- DSUM, DCOUNT and DAVERAGE
- Simple AND and OR conditions
- Writing criteria with wildcards
- Troubleshooting common errors
Certificate Awarded (Paid)
Job Outlook
Flexible lookups, ranges that adjust themselves and criteria-based summaries are everyday tools in reporting and analysis work, wherever data is kept in large Excel workbooks.
Good for: Excel users who build reports on large tables and want lookups, moving ranges and conditional totals that need less manual editing.
Keep in mind: The title says advanced, but the lessons build step by step on existing lookup skills and stay with three function families: lookups, OFFSET ranges and database functions.
Bottom line: A practical set of exercises on XLOOKUP, OFFSET and Excel’s often overlooked database functions, including a period comparison tool and criteria-driven summaries.
Frequently Asked Questions
What are Excel's database functions used for?
They aggregate the records that meet conditions you define in a separate criteria range. In the final chapter, DSUM totals, DCOUNT counts and DAVERAGE averages the matching rows, and the lessons work through simple AND and OR conditions, wildcards and the errors that commonly come up.
What does the OFFSET chapter have learners build?
After practicing OFFSET on its own and alongside INDEX, MATCH and SUM, learners set up a range tied to a chosen period, prepare a comparison tool on that range and then visualize the comparison in a chart.
How much hands-on practice do the lessons include?
Each chapter pairs explanations with interactive exercises in which you apply the functions yourself, for example running two-way lookups, transposing and ranking data, and writing your own criteria for the database functions.
User Reviews
Write your review
You must be logged in to post a review.
Reviews can only be posted by registered learners. Log in or create a free account, then you will return here to post.
There are no reviews yet.