Excel Function

The Excel Function archives at excelguru.io deliver practical, example-driven tutorials designed to help you move beyond basic formulas. This collection focuses on how to apply essential functions to real-world tasks, featuring in-depth guides on modern lookup tools like XLOOKUP and INDEX MATCH, conditional logic with IFS, COUNTIFS, and SUMIFS, as well as powerful data analysis functions such as SUMPRODUCT and FILTER. Each guide provides clear syntax breakdowns, side-by-side comparisons, and ready-to-copy formulas suitable for every Excel version from 2003 to Microsoft 365.

Whether you need to calculate employee tenure with DATEDIF, build dynamic reports that spill results automatically, or clean up messy spreadsheets using IFERROR, these tutorials offer step-by-step solutions. The content addresses common pain points like nested IF complexity, VLOOKUP limitations, and multi-condition aggregations, ensuring you can handle tasks ranging from commission tiers and grade scales to payroll sheets and date-based grouping—all without relying on helper columns or VBA.

Designed for business professionals, data analysts, and Excel users at all skill levels, this archive transforms how you work with data. Each post includes sample datasets, practical use cases, and expert tips to help you build cleaner, more efficient spreadsheets. Explore the full collection to master the functions that drive accurate reporting, streamlined workflows, and confident data analysis.

Excel tutorial for extracting combining and cleaning data using formulas Power Query and data preparation tools

Extract, Combine & Clean Data Without Formulas

Clean and prepare messy Excel data with this practical tutorial on extracting, combining, and organizing information. Learn how to split text, combine columns, remove duplicates, standardize formats, clean extra spaces, and use Excel tools or Power Query to prepare data for reporting and analysis. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who work with raw data and need a faster way to make it usable.

Quick Analysis Tool in Excel showing formatting charts totals tables and data insights

Quick Analysis Tool: Instantly Create Charts, Totals & Sparklines

Analyze Excel data faster with the Quick Analysis Tool. This tutorial explains how to use Quick Analysis for conditional formatting, charts, totals, tables, sparklines, and quick data insights without building everything manually. Ideal for Excel users, students, analysts, finance teams, and professionals who want a faster way to format, summarize, and visualize data.

Excel Forecast Sheet tutorial showing one-click time series forecasting and trend projection

Forecast Sheet: One‑Click Time Series Forecasting (Excel 2016+)

Create quick time series forecasts in Excel with the Forecast Sheet feature. This tutorial explains how to select historical data, generate forecast charts, adjust forecast end dates, review confidence intervals, handle seasonality, and interpret future trend projections. Ideal for Excel users, analysts, finance teams, sales teams, and business professionals who want an easy way to forecast future values without complex formulas.

Analyze different business outcomes with Excel Scenario Manager and what-if analysis. This tutorial explains how to create scenarios, change input values, compare results, generate scenario summaries, and test different assumptions in one workbook. Ideal for Excel users, analysts, finance teams, business owners, and students who want an easy way to evaluate best-case, worst-case, and expected-case scenarios.

Compare Multiple Business Scenarios Side‑by‑Side

Analyze different business outcomes with Excel Scenario Manager and what-if analysis. This tutorial explains how to create scenarios, change input values, compare results, generate scenario summaries, and test different assumptions in one workbook. Ideal for Excel users, analysts, finance teams, business owners, and students who want an easy way to evaluate best-case, worst-case, and expected-case scenarios.

CUBEVALUE & CUBESET: Query Data Model from Formulas

Understand how Excel CUBEVALUE and CUBESET functions work with Power Pivot and data models in this practical tutorial. Learn how to retrieve specific values, define member sets, build dynamic reports, connect formulas to slicers, and create flexible dashboard calculations. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to build advanced reports using cube functions.

Power Query Fill Down feature image showing a before-and-after HR report where the Department column had null cells below Engineering and Finance labels, and after Fill Down every row shows the correct department name in green or purple, alongside the Table.FillDown M code, a Fill Up example showing a Travel footer label propagated upward, six example pills including merged cells fix and running total, and a warning that Fill Down crosses group boundaries if blank separator rows are not removed first.

Power Query: Fill Down & Fill Up to Replace Nulls with Above or Below Values

Exported reports often put a category label in the first row of each group and leave blank cells below for all other rows in that group. This looks tidy in a formatted report but breaks every formula and PivotTable that expects each row to be self-contained. Fill Down fixes it instantly — it replaces every null with the last non-null value above it, propagating the label through all rows in the group. Fill Up does the reverse, pushing a footer label upward through null cells above it. The critical detail most users miss: Fill Down only replaces null, not empty strings. This guide covers the five-step workflow, how to convert empty strings to null before filling, how to prevent Fill Down from bleeding across group boundaries, and six examples including classic category propagation, merged-cell repair, footer label fill-up, multi-column simultaneous fill, and a running balance using List.Accumulate.

Power Query Group By feature image showing a grouped sales summary table with North GBP 88,500 revenue, 342 orders, and GBP 258.77 average order value, alongside the Table.Group M code for multiple aggregations, the advanced multi-column grouping pattern using Region and Product, the distinct count formula using Table.Distinct inside the group expression, and six example pills including SUMIF equivalent and weighted average.

Power Query Group By: Sum, Count, Average, and Custom Aggregations

Fifty thousand transaction rows need to become a clean summary — total revenue by region, order count by product, average order value by month. Power Query’s Group By builds this in a few clicks and delivers a real flat table you can load anywhere, merge into other queries, or use as a lookup source. Advanced mode lets you add multiple group columns and multiple aggregations simultaneously. The All Rows aggregation type unlocks custom M expressions for patterns the UI doesn’t natively support — distinct customer counts, SUMIF-style conditional sums, and weighted averages. This guide covers both Basic and Advanced mode, six worked examples including regional revenue summaries, multi-metric aggregations, distinct customer counts, conditional aggregation using Table.SelectRows inside a group, and a revenue-weighted average price pattern.

Excel tutorial showing how to add an index column for custom sorting and organizing data in a specific order

Power Query: Add Index Column & Custom Sorting in Excel

Need more control over how your Excel data is sorted? This practical tutorial shows you how to add an index column to create a custom sorting order and organize rows exactly the way you want. Learn how to assign numerical or sequential index values, use them to control the order of categories, and sort your data without changing the underlying information. This technique is useful for custom category orders, priority lists, workflow stages, reporting, dashboards, and structured data analysis. Ideal for Excel users, analysts, data professionals, and anyone who needs more flexibility than standard alphabetical or numerical sorting.

Power Query Remove Duplicates feature image showing a before-and-after table where a duplicate ORD-101 row is marked Removed in red while the first occurrence is marked Kept in green, the M code index trick pattern using Table.AddIndexColumn and Table.Sort Descending, four method cards covering all-columns, key-column, sort-first, and index-trick approaches, and six example pills including exact row deduplication and case-insensitive matching.

Power Query: Remove Duplicates & Keep First or Last Occurrence

Fifty thousand rows and duplicates inflating every total — Power Query’s Remove Duplicates fixes this in one step. But there’s a choice most people miss: which duplicate survives? Power Query always keeps the first row it encounters in the current order. Sort the table before deduplicating and you control exactly which row wins — the most recent, the highest-value, or the last entry received. This guide covers four deduplication patterns: entire-row exact matching, single-column key deduplication, the sort-first technique for keeping the most recent record, and the index-column trick that guarantees the last original row is preserved. Six worked examples include removing CRM duplicates, keeping one row per customer, flagging duplicates for manual review, composite-key deduplication, and case-insensitive matching.

Power Query Split Column into Rows feature image showing a before-and-after comparison where a cell containing "Excel, Charts, Reporting" for product P001 is expanded into three separate green-highlighted rows (one per tag), alongside the five-step process, the M code pattern using Table.ExpandListColumn, six example pills including product tags and survey multi-select, and a tip to always include a unique ID column before splitting.

Power Query: Split Column by Delimiter into Rows (Not Columns)

When a cell holds “London, Paris, Berlin” and you need three separate rows, Split Column into Rows is the answer. Unlike Split to Columns — which creates a wide, ragged table with empty cells — splitting to rows stacks every value vertically under the same header. The result is clean, normalized data that works perfectly with PivotTables and Power Pivot relationships. This guide walks through the full five-step process, explains how to handle comma, semicolon, and line-break delimiters, shows how to remove blank rows caused by trailing delimiters, and covers six worked examples including product tag expansion, multi-select survey answers, mixed delimiter handling, and a rejoin-after-split pattern for tag popularity scoring.

TOCOL and TOROW functions in Excel 365 — showing a 3×3 grid flattened three ways: TOCOL with scan=FALSE reading row-by-row producing 1,2,3,4,5,6,7,8,9 in a green column, TOCOL with scan=TRUE reading column-by-column producing 1,4,7,2,5,8,3,6,9 in a teal column, and TOROW producing a horizontal row in indigo, with key formulas for cross-column UNIQUE deduplication, WRAPROWS pipeline, multi-sheet master list, and tag grid TEXTJOIN.

TOCOL & TOROW in Excel: Flatten Ranges into Single Columns or Rows

FILTER returns a table. WRAPROWS needs a column. UNIQUE works best on a flat list. TOCOL is the missing link — it flattens any 2D range into a single column in one formula. TOROW does the same into a single row. This guide covers eight examples: basic flattening with row-by-row vs column-by-column scan direction, all four ignore values for removing blanks and errors, stacking multiple columns for cross-column UNIQUE deduplication, converting rows to columns for SORT, the TOCOL+WRAPROWS flatten-then-reshape pipeline, building a master list from multiple sheets using VSTACK+TOCOL, feeding a 2D tag grid into TEXTJOIN, and deduplicating values across five columns for a dynamic dropdown source.

TEXTSPLIT function in Excel 365 — showing three transformation examples: a comma-separated string split into four columns, a mixed comma-and-semicolon string split using multiple delimiters into five clean tokens, and a semicolon-and-comma delimited string split into a 3×3 two-dimensional table, with key formulas for row splitting, Key=Value parsing, in-cell CSV with DROP, and a TEXTJOIN rejoin pipeline.

TEXTSPLIT in Excel 365: The Ultimate Way to Split Text into Columns/Rows

A cell contains “Alice, Bob; Carol” — TEXTSPLIT splits it into three separate values in one formula. It is fully dynamic, updates when the source changes, and handles multiple delimiters in a single call. This guide covers eight examples: splitting by a single delimiter into columns or rows, using multiple delimiters as an array with TRIM via LET, 2D splits with both row and column delimiters, Key=Value pair parsing with CHOOSECOLS and VLOOKUP, in-cell CSV parsing with DROP and SORT, counting and extracting tokens by index, case-insensitive splitting for natural language data, and a complete split-clean-sort-rejoin pipeline using LET, TRIM, SORT, and TEXTJOIN.