Excel Function

Create quick grouped summaries in Excel using the Subtotal tool from the Data tab. This tutorial explains how to sort data, apply subtotals, group rows automatically, use outline levels, summarize by category, and remove subtotals when needed. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need an easy way to summarize large datasets without building formulas manually.

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.

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.

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.

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.

Power Query loads data in whatever order it arrives. Index columns fix this — they assign a sequential number to every row, giving you a stable identity that persists through refreshes and transformations. Sort by Revenue descending, add an Index from 1, and you have a live rank column that updates automatically every time the data changes. For semantic orderings — fiscal months, clothing sizes, custom priority lists — a sort-mapping table assigns a number to each value, the merge sorts by it, and the column is then removed, leaving the data in the correct logical order with no trace of the helper. This guide covers six examples including revenue ranking, fiscal year month ordering, XS-to-XXL size sorting, alternating row flags for sampling, multi-column sorting with Table.Sort priority lists, and a month-over-month change calculation using an index self-merge to replicate SQL’s LAG function.

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.

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.