Excel 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.

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.

Filter a table and SUM keeps counting every row — including the hidden ones. SUBTOTAL fixes this. It is the only native Excel function that automatically ignores filtered-out rows, updating the moment a filter is applied or removed. This guide covers all 11 aggregation types across both function number ranges (1–11 and 101–111), with eight practical examples: filter-aware SUM, COUNT, AVERAGE, MAX and MIN; group subtotals with a grand total that avoids double-counting; the difference between function 9 and 109 when rows are hidden manually; using SUBTOTAL(103) as a per-row visibility indicator for filter-aware conditional sums; Excel Table Total Row integration; AGGREGATE for median, LARGE, and error-tolerant totals; a live KPI dashboard with a “Showing N of M deals” label; and why SUMIF fails on filtered data — and how to fix it.

A list of 500 numbers tells you almost nothing at a glance. Group them into bins and the shape of the distribution becomes immediately visible — where values cluster, where they thin out, and whether the data skews left or right. The FREQUENCY function performs that grouping in a single formula. This guide covers eight practical examples: building a basic frequency table, charting it as a histogram with gap width set to zero, converting counts to relative frequencies and cumulative percentages, generating dynamic bins with SEQUENCE that update as data changes, counting students per grade band, measuring manufacturing defect rates across tolerance zones, comparing two distributions side-by-side, and using the FREQUENCY distinct-count trick to find unique values. It also covers the key behaviour most analysts miss: FREQUENCY always returns one more value than the number of bin boundaries — that extra row is the overflow bucket, and forgetting it silently drops data.