Excel Function

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.

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.

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.

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.

A flat list of 12 months becomes a 3×4 calendar grid. A 50-item product column reshapes into a 5×10 display table. WRAPROWS and WRAPCOLS perform this transformation in one formula. WRAPROWS fills left-to-right and wraps to the next row. WRAPCOLS fills top-to-bottom and wraps to the next column. This guide covers eight examples: basic grid reshaping with scan direction comparison, controlling the pad_with argument for partial rows, a dynamic self-updating calendar using SEQUENCE, a product catalogue display grid, filtered display grids via WRAPROWS+FILTER, pairing two columns with TOCOL+HSTACK, reshaping survey data into a month-by-question grid, and a dynamic wrap count that auto-resizes to a target number of columns.

INDEX retrieves values from a table by row and column number. CHOOSECOLS and CHOOSEROWS do the same for entire columns and rows — and they do it in a single readable call. Pass the array and a list of column or row numbers, and the function returns exactly those columns or rows in the order you specify. Negative numbers count from the end, so you never need to know the total width. This guide covers eight examples: selecting named columns by position, reversing and duplicating columns, CHOOSECOLS on FILTER output, a dynamic column picker driven by a dropdown, CHOOSEROWS for specific and alternating rows, combining both functions for rectangular sub-table extraction, removing a column with SEQUENCE+FILTER, and selecting columns by header name using MATCH for reorder-proof formulas.

FILTER returns everything that matches. SORT returns everything reordered. But sometimes you only want the first five rows, or everything except the last three, or a specific middle segment. TAKE and DROP fill this gap in Excel 365. TAKE keeps a specified number of rows or columns from either end of an array. DROP removes a specified number and returns the rest. Negative numbers work from the bottom, so you never need to know the total row count. This guide covers six practical examples: first and last N rows with positive and negative arguments, stripping header and footer rows with DROP, extracting a middle slice by chaining DROP and TAKE, two-dimensional slicing with both rows and columns arguments, a SORT+FILTER+TAKE pipeline for a self-updating top-5 leaderboard, and in-worksheet pagination where a page number cell controls which block of rows is displayed. It also covers the pre-TAKE workarounds using INDEX and SEQUENCE, so you can understand what these two functions replace and choose the right approach for your Excel version.

Dynamic array formulas in Excel 365 return results that spill across multiple cells. That is powerful for analysis, but it creates a problem for display: how do you show all those values as a single readable sentence? ARRAYTOTEXT solves this. It converts any array, range, or dynamic expression into a single comma-separated string in one cell. This guide covers six practical examples: joining a column of names into a compact list, combining ARRAYTOTEXT with FILTER to produce a dynamic filtered list that updates as data changes, using SORT and UNIQUE to build a deduplicated string of categories, replacing the default comma delimiter with any character via SUBSTITUTE, building a full report sentence that combines COUNTIF, ARRAYTOTEXT, and TEXT(SUMIF()) in a single formula, and a direct comparison against TEXTJOIN showing when to use each. It also covers the three most common issues: the Excel 365 availability constraint, the blank-cell double-comma problem and its FILTER workaround, and why ARRAYTOTEXT output is always text and cannot be used in downstream arithmetic.

SUBTOTAL handles 11 aggregation types. AGGREGATE handles 19 — and it ignores errors. It does everything SUBTOTAL does, then adds MEDIAN, LARGE, SMALL, PERCENTILE, and QUARTILE to the same filter-aware framework. When a #DIV/0! in one cell breaks your SUBTOTAL total, AGGREGATE skips it. When you need the median of filtered data — which SUBTOTAL cannot compute — AGGREGATE delivers it with a single formula. This guide covers both syntax forms, all 19 function numbers, all 8 option codes, and six practical examples: error-tolerant SUM and AVERAGE, filter-aware MEDIAN and five-number summary, k-th LARGE and SMALL on filtered data, PERCENTILE and IQR outlier bounds, a trimmed mean that removes extreme outliers before averaging, and visible-row RANK. It also explains the most important option choice: use option 5 for most filtered tables, option 7 when data also contains errors, and options 0–3 in grouped reports to prevent grand totals from double-counting group subtotals.