Advanced Excel: the 10 Functions 90% of Professionals Ignore

90% of Excel users stick to SUM, AVERAGE, VLOOKUP and a few pivot tables. Yet, since 2020, Microsoft has deeply modernized Excel’s calculation engine with dynamic arrays, LAMBDA, LET and XLOOKUP. According to the Microsoft Tech Community for Excel, these new functions divide by 3 to 5 the time spent on classic manipulations. Here are 10 advanced Excel functions that will transform your daily work if you handle data regularly.

Table of Contents

• XLOOKUP, FILTER, UNIQUE: the modern trinity
• LET and LAMBDA: programming inside Excel
• TEXTSPLIT and TEXTJOIN: mastering text strings
• SORT, SORTBY and SEQUENCE: sort and generate dynamically
• SUMPRODUCT: the chameleon function
• Why aim for MOS Excel Expert certification?
• FAQ

Why 90% of professionals miss what Excel does best

Excel has quietly lived through a revolution since 2020. The calculation engine was rebuilt to support dynamic arrays, which propagate a result across multiple cells automatically without Ctrl+Shift+Enter. This evolution rendered dozens of tedious manipulations obsolete.

Yet in everyday Swiss practice, most professionals still use Excel like it’s 2010. VLOOKUP instead of XLOOKUP, endless nested formulas instead of LAMBDA, manual text manipulation instead of TEXTSPLIT. The result: only a minority of users actually leverages dynamic array functions, even though they ship in every recent version of Excel.

The direct consequence: an analyst who masters modern functions accomplishes in 30 minutes what takes an intermediate user 2 hours. Here are the 10 functions that make the difference, ranked by impact.

1, 2, 3 – XLOOKUP, FILTER, UNIQUE: the modern trinity

XLOOKUP: the end of VLOOKUP

VLOOKUP suffers from three historical limits: searches only to the right, breaks if the reference column isn’t the first one, doesn’t handle missing values natively. XLOOKUP fixes all three.

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")

You can search left, right, last-to-first, and handle “not found” without nesting IFERROR. In short, XLOOKUP should replace 100% of your VLOOKUPs in 2026.

FILTER: dynamically extract data

Instead of manually copy-pasting rows matching a criterion, FILTER returns a dynamic array:

=FILTER(Sales, Sales[Region]="Geneva", "No data")

This function alone removes 50% of advanced filter manipulations and simple macros.

UNIQUE: extract distinct values

No need for a pivot table just to extract the list of customers or products:

=UNIQUE(Sales[Customer])

The result updates automatically when data is added. Combined with SORT, it’s unbeatable.

4, 5 – LET and LAMBDA: programming inside Excel

LET: stop recalculating the same thing 10 times

LET allows storing intermediate results inside a formula, like variables in programming:

=LET(
  total, SUM(Sales[Amount]),
  avg, total/COUNTA(Sales[Customer]),
  IF(avg>1000, "Good", "Weak")
)

This makes complex formulas 10 times more readable and improves performance on large workbooks. It’s arguably the most underused function relative to its impact.

LAMBDA: build your own Excel functions

LAMBDA lets you define a custom formula, reusable like a native function, without VBA. Example: calculating Swiss VAT at 8.1%:

VAT_CH = LAMBDA(amount, amount * 0.081)

Once named through the Name Manager, you use it everywhere: =VAT_CH(B2). For recurring financial models, this is a major productivity gain. Furthermore, the Office Scripts documentation positions LAMBDA as the entry point into programming for business users.

6, 7 – TEXTSPLIT and TEXTJOIN: mastering text strings

TEXTSPLIT: split without macros

How many times have you used Data → Text to Columns? TEXTSPLIT replaces this with a dynamic formula:

=TEXTSPLIT(A2, ";")

Perfect for handling raw CSV files or concatenated fields. Moreover, the result updates automatically when the source changes.

TEXTJOIN: concatenate with smart separator

The reverse of TEXTSPLIT. TEXTJOIN ignores empty cells, which solves one of CONCATENATE’s classic nightmares:

=TEXTJOIN(", ", TRUE, A2:A20)

Especially for mailing lists or aggregated labels, the win is immediate.

8, 9 – SORT, SORTBY and SEQUENCE: sort and generate dynamically

SORT and SORTBY: live sorts

No more re-sorting your table manually when you add data. SORT returns a sorted array and stays dynamic:

=SORT(FILTER(Sales, Sales[Region]="Lausanne"), 4, -1)

This example combines FILTER and SORT to dynamically display Lausanne sales sorted by amount descending.

SEQUENCE: generate series

SEQUENCE creates a number series:

=SEQUENCE(12, 1, DATE(2026,1,1), 31)

Generates the first 12 days at monthly intervals throughout 2026. Especially useful for dynamically building parameter tables or simulation calendars.

10 – SUMPRODUCT: the chameleon function

SUMPRODUCT isn’t new, but it’s probably Excel’s most versatile function. It alone replaces many SUMIF and COUNTIF combinations, and enables advanced conditional calculations:

=SUMPRODUCT((Sales[Region]="Geneva")*(Sales[Year]=2026)*Sales[Amount])

This formula calculates the sum of 2026 Geneva sales. Its magic comes from the automatic conversion of boolean conditions into 0s and 1s. It’s the go-to tool for experienced controllers. However, since FILTER and XLOOKUP arrived, its use has shifted: SUMPRODUCT remains unbeatable on multi-condition aggregations.

Why aim for MOS Excel Expert certification?

The Microsoft Office Specialist Excel Expert (MO-201) certification officially recognizes mastery of advanced Excel functions. In Switzerland, this certification clearly stands out on your CV for finance, controlling, data analysis and reporting roles.

On the Swiss job market, many roles in finance, controlling and data analysis value a recognized Excel certification. The exam content covers precisely the functions presented here, making it an excellent learning objective to structure your skill ramp-up.

Bonus: 3 Power Query M patterns that change everything

Beyond cell-level functions, Excel integrates Power Query M, a data transformation language often ignored by advanced users. Yet three Power Query patterns alone can eliminate 80% of repetitive manual handling. Here are the three highest-impact ones.

Pattern 1: combine multiple Excel files from a folder

The classic case: your finance team receives 10 similar Excel files from subsidiaries each month. You stack them manually into a consolidated file. With Power Query, a single operation does it. In Excel, Data → Get Data → From File → From Folder. Point at the folder, Power Query auto-detects the shared structure and stacks the 10 files into a single query refreshed at every opening.

Benefit: what took 30 manual minutes each month becomes a single “Refresh” click. And if an eleventh file arrives, it integrates automatically without modification.

Pattern 2: pivot and unpivot a matrix

Files received from partners often come in “crosstab” format: one column per month, one row per product. To analyze in Power BI or Power Query, you need to flatten them (“long” format, one row per month × product). That’s the Unpivot function in Power Query.

Select the monthly columns, do Transform → Unpivot Columns. Power Query instantly produces a flat, query-ready table. Conversely, Pivot reconstructs a matrix from a long table. Mastering both saves hours on every external data treatment.

Pattern 3: parameterize a query from an Excel cell

Want a Power Query query that automatically switches its source file based on an Excel cell? That’s the role of Power Query parameters. Create a named parameter (e.g. “Month”) and use Excel.CurrentWorkbook() to read a cell. Power Query dynamically reads the selected month and adapts source or filters.

This pattern turns an Excel workbook into an interactive app without VBA. Particularly powerful for simulation models used by controllers or relationship managers.

These three Power Query patterns, combined with the 10 modern functions above, form the technical foundation of a 2026 Expert-level Excel user. The MOS Excel Expert certification, in fact, validates precisely this skill combination.

Bonus pattern 4: auto-refresh with Power Query and Excel

Beyond the three patterns above, Power Query can drive a complete automation without any external script. Imagine: every morning, opening your workbook automatically triggers fresh data retrieval from an API or a shared folder, with no manual action. That is exactly what Power Query’s auto-refresh options enable, accessible via Data → Queries and Connections → Properties.

Three key settings to know. First, refresh on file open guarantees data is always fresh. Then periodic refresh (every 30, 60 or 240 minutes) keeps data current during the day for users in continuous data entry. Finally, background refresh lets you keep working on the file while the query updates.

Combined with the dynamic parameters from Pattern 3, you get a real mini-dashboard inside Excel that refreshes without intervention. For a controller or relationship manager, it’s the equivalent of a “Power BI Light” embedded directly in their daily tool, without requesting a new license.

Going further: Office Scripts to automate beyond Excel

For users who want to cross another threshold after MOS Excel Expert, Microsoft offers Office Scripts, a modern automation system based on TypeScript integrated into Excel Online. Concretely, you record your manipulations as a macro, then edit the generated TypeScript code to make it parametric, robust and shareable. Combined with Power Automate, Office Scripts orchestrates cross-application automations (Outlook → Excel → Teams) without any local installation.

For intermediate controllers and data analysts, this is the natural entry point into the Microsoft Low-Code ecosystem, without having to learn Python or VBA. The learning curve is gentle, the integration with the rest of Microsoft 365 is immediate, and the ROI on recurring tasks shows up within the first month of use.

The integration with Power Automate

The real power of Office Scripts emerges when you combine it with Power Automate. A typical workflow: Power Automate detects a new email with an Excel attachment, then triggers an Office Script that opens the file, runs your transformation logic, saves the result on SharePoint, and notifies the team in Teams. This entire chain runs in the background without any human intervention, replacing several manual tasks that took 15-30 minutes per occurrence.

For organizations producing recurring reports (monthly financial close, weekly sales follow-up, daily inventory reconciliation), Office Scripts can free up dozens of hours per month for an entire department. The skill curve to write your first useful Office Script is comparable to learning advanced Excel formulas: two or three days of dedicated practice are enough to be productive.

Practical entry points

To get started concretely with Office Scripts, the recommended approach is to take a simple Excel manipulation you already do every week, record it as an Office Script via Automate → Record Actions, then edit the TypeScript code to add a parameter (e.g. a date or a region). This first iteration takes one hour and immediately demonstrates the value. From there, you can gradually replace your recurring manual tasks with reusable scripts shared across the team.

Common pitfalls when adopting modern Excel functions

Adopting the modern functions also brings a few classic pitfalls worth flagging. First, file compatibility: a workbook using XLOOKUP, FILTER or LAMBDA will not open correctly in Excel 2019 or older. Always check what versions your stakeholders run before standardizing on dynamic arrays. Second, spilled arrays can collide with existing data: if a FILTER returns 50 rows into a column that already contains values, Excel raises a #SPILL! error. Plan your layout accordingly, ideally on dedicated sheets. Third, formula performance: LET and LAMBDA improve readability but very deep nested LAMBDAs can slow down recalculation on large workbooks. Test on a representative dataset before deploying widely.

Combining functions: a concrete dashboard example

The real power of modern Excel shows when you chain these functions together. Imagine a sales dashboard that must display, in real time, the top 5 customers for a given region. In a single formula, you filter the region’s sales with FILTER, sort them by amount descending with SORT, then keep only the first five rows. Everything recalculates automatically the moment a new sale is entered, with no macro and no manual handling.

You can then wrap that logic in a named LAMBDA function, for example TopCustomers, to reuse it on every region without rewriting the formula. This exact way of thinking (reasoning in reusable building blocks rather than isolated cells) is what separates an intermediate user from a true Excel expert. The modular approach also reduces the risk of error, since a fix made inside the LAMBDA propagates everywhere it is used.

Conclusion

Modern Excel functions (XLOOKUP, FILTER, UNIQUE, LET, LAMBDA, TEXTSPLIT, SORT, SEQUENCE, SUMPRODUCT) are not reserved for technical profiles. They radically transform the productivity of any user who handles data regularly. Their learning ROI is measured in hours saved each week and in analysis quality.

Concretely, start by adopting XLOOKUP instead of VLOOKUP for two weeks, then gradually integrate FILTER, LET and UNIQUE. You will be surprised never to want to go back.

FAQ

Are these functions available in all Excel versions?
Dynamic array functions (XLOOKUP, FILTER, UNIQUE, SORT, SEQUENCE) are available in Microsoft 365 and Excel 2021+. LAMBDA and TEXTSPLIT arrived more recently and require an up-to-date M365 version.

Should I abandon VLOOKUP?
Yes, in new files. XLOOKUP is simpler, more robust and faster. Keep VLOOKUP only for backward compatibility with older Excel versions.

Can LAMBDA replace VBA?
Partially. For reusable calculation functions, LAMBDA suffices. However, for full automation (loops, user interaction, APIs), VBA or Office Scripts remain necessary.

How hard is the MO-201 certification really?
Moving from intermediate to Expert takes about 3 days of intensive training plus practice. The exam covers concrete cases, not theoretical questions, making it a highly operational certification.

How do I keep my Excel skills up to date?
Follow the Microsoft Tech Community for Excel, which publishes new functions every month. Practice on real business use cases, not disconnected exercises.