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.

WRAPROWS and WRAPCOLS functions in Excel 365 — showing a flat list of nine values reshaped into a 3×3 grid two ways: WRAPROWS filling left-to-right row by row in green, and WRAPCOLS filling top-to-bottom column by column in teal, with key formulas for a dynamic calendar, filtered display grid, and WRAPCOLS product catalogue layout.

WRAPROWS & WRAPCOLS in Excel: Reshape One-Dimensional Arrays into Grids

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.

CHOOSECOLS and CHOOSEROWS functions in Excel 365 — showing a 4-column source table where CHOOSECOLS(A2:D20, 1, 3) extracts only the Name and Score columns, with key formula examples for reversing columns, selecting by header name with MATCH, a dynamic dropdown column picker, and removing a column using SEQUENCE and FILTER.

CHOOSECOLS & CHOOSEROWS: Extract Specific Columns/Rows from Arrays

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.

TAKE and DROP functions in Excel — a 9-row array shown alongside four sliced results: TAKE(A,3) highlighting the top 3 rows in green, TAKE(A,-3) highlighting the bottom 3 in violet, DROP(A,1) removing the first row in blue, and DROP(A,-1) removing the last row in amber, with key pipeline and pagination formulas.

TAKE and DROP Functions in Excel: Slice Arrays and Ranges Like a Pro

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.

ARRAYTOTEXT function in Excel — showing a four-name column being converted into "Alice,Bob,Carol,Dave" in format 0 and "Alice", "Bob", "Carol", "Dave" in format 1, with key formula examples for FILTER, UNIQUE, SUBSTITUTE, and a full report sentence, plus ARRAYTOTEXT vs TEXTJOIN comparison.

ARRAYTOTEXT: Convert Arrays to Strings for Clean Outputs

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.

AGGREGATE function in Excel — showing a table where SUM fails on a #DIV/0! error but AGGREGATE(9,6,...) returns 7,850 by skipping the error cell, with the 8-option reference table, AGGREGATE-only functions list (MEDIAN, LARGE, PERCENTILE), and five key formula examples.

AGGREGATE: The Swiss Army Knife of Excel Functions

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.

Subtotal Function in Excel with full tutorial and blogpost

SUBTOTAL: The Ultimate Function for Filtered & Hidden Data

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.

FREQUENCY function in Excel — histogram bar chart showing bin counts of {14, 31, 28, 19, 8} across four bins plus overflow, with a frequency table showing relative and cumulative percentages, key formulas, and bin boundary logic diagram.

FREQUENCY Function: Create Histograms & Binned Distributions

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.

CORREL Function Calculate Correlation Coefficient Between Data Sets

CORREL Function: Calculate Correlation Coefficient Between Data Sets

Two columns of numbers — do they move together? The CORREL function answers with a single number between −1 and +1. A result of +0.967 means a very strong positive relationship: when one variable rises, the other rises proportionally. A result of −0.841 means a strong inverse relationship. A result near zero means little or no linear association exists.
This guide covers eight practical examples: basic correlation between two variables, building a 4×4 correlation matrix with conditional formatting, testing statistical significance using the t-test and TDIST, filtering to a specific category with conditional CORREL, rolling correlation to track how the relationship changes over time, finding the strongest pairs across a matrix using LARGE and SMALL, calculating Spearman rank correlation for non-normal or outlier-heavy data, and building a self-updating dashboard that outputs a plain-English label like “r = 0.967 (Very strong positive).” The guide also covers the most important limitation: a high CORREL result does not mean one variable causes the other.

Tick mark in Excel — five insertion methods including Symbol dialog, Alt codes, keyboard shortcuts, CHAR formula, and Unicode, with a live checklist showing conditional tick marks, COUNTIF summary, and key formulas for IF-based ticks and conditional formatting row colouring.

Tick Mark in Excel: Every Method to Insert a Check Mark (5 Ways)

Excel has no dedicated tick-mark button, but it offers five distinct ways to insert one. The Symbol dialog works for one-off insertions with no setup. Alt+0252 in Wingdings is the fastest keyboard-only method. Shift+P in Wingdings 2 is even quicker once the column is pre-formatted. The CHAR function brings ticks inside formulas — combine it with IF to show a tick when a task is done and a cross when it isn’t. Unicode ✓ and ✔ paste directly into any font without changing a thing. This guide covers all five methods, then shows how to count ticks with COUNTIF, restrict a column to ticks and crosses only using data validation, and color entire rows green or red with conditional formatting

Convert numbers to words in Excel — showing the NumToWords VBA function, AmountToWords currency formula for USD, GBP, and AED, the LAMBDA method for macro-free workbooks, and a conversion table with outputs for 1250.75, 999.99, and 7589.45 across multiple currencies.

Convert Numbers to Words in Excel: VBA, LAMBDA, and Formula Methods

Excel has no built-in SPELLNUMBER function, but three methods fill the gap. The VBA NumToWords function is the most flexible — paste the code once into a module, save as .xlsm, and use =NumToWords(A1) anywhere in the workbook. The AmountToWords extension adds currency and sub-unit names, producing invoice-ready output like “One Thousand Two Hundred Fifty Dollars and Seventy-Five Cents” automatically. For macro-restricted environments, a LAMBDA function defined in the Name Manager achieves the same result with no VBA at all — fully compatible with .xlsx files. This guide covers all three methods with eight practical examples: basic conversion, multi-currency invoice lines (USD, GBP, AED, INR, EUR), cheque format with the XX/100 fraction convention, a dynamic currency table driven by XLOOKUP, and how to lock the output as static text before sharing a finalised document.