Excel Function

Concatenating a number with text in Excel instantly strips its formatting — the dollar sign, commas, and decimal places all disappear. The DOLLAR function solves this by converting a number directly to a formatted currency text string, preserving the symbol and precision in the output. It also rounds to any number of decimal places, including negative values that round to the nearest ten, hundred, or thousand — useful for executive summaries. This guide covers 8 practical examples: standard formatting, zero decimals, rounding to thousands, embedding currency in concatenated labels, dynamic precision from a control cell, conditional labels with IF, non-USD currencies with TEXT, and self-updating dashboard KPI strings.

Invisible characters break formulas silently. You import data from a database or web scrape, everything looks fine — until VLOOKUP returns #N/A on values that are clearly present, and COUNTIF counts zero for strings that visibly match. The culprit is almost always non-printable characters hiding inside the cells. Excel’s CLEAN function removes them in one step, stripping ASCII codes 0 through 31 that TRIM cannot touch. This guide covers 8 practical examples: basic cleaning, the standard TRIM+CLEAN pipeline, fixing non-breaking spaces with SUBSTITUTE, removing line breaks without merging words, diagnosing dirty data with CODE and LEN, and the master four-layer cleanup formula that handles every common invisible garbage type in a single formula.

Excel’s REPT function repeats any character a specified number of times — making it the simplest way to build in-cell bar charts, progress bars, and star ratings without chart objects or conditional formatting data bars. Unlike data bars, REPT output is plain text that recalculates faster, stays stable when copied, and can change color based on custom threshold rules via conditional formatting. This guide covers the syntax, the best Unicode characters to use, and 8 practical examples including scaled bar charts, filled-and-empty progress bars, star ratings, tally marks, and the classic “z×255” trick for finding the last text entry in a list.

CONCAT replaced CONCATENATE as Excel’s standard text-joining function — but many users still reach for the older name out of habit. This guide explains the four ways to concatenate in Excel, shows exactly when to use each one, and walks through 10 real-world examples covering full names, postal addresses, order codes, date labels, conditional suffixes, and comma-separated lists. Whether you’re joining two cells or an entire column, you’ll find the right formula here.

Learn how to use Excel’s INFO function to get the operating system, Excel version, calculation mode, open worksheet count, and default directory. Covers all 7 type_text values with practical examples.

Learn how to use Excel’s NUMBERVALUE function to convert text numbers to values with custom decimal and group separators. Essential for European formats, imports, and cross-locale workbooks.

Learn how to use Excel’s CELL function to extract the file path, sheet name, cell address, data type, and format code. Covers all 12 info_type values with practical examples

Learn how to use FORMULATEXT in Excel to display any formula as readable text. Covers audit sheets, IFERROR handling, LEN complexity checks, and searching for specific functions.

Learn how to use HSTACK and VSTACK in Excel to combine ranges side by side or stacked vertically. Covers SORT and UNIQUE pipelines, cross-sheet consolidation, and #N/A handling.

Learn how to use MAXIFS and MINIFS in Excel to find conditional maximum and minimum values. Covers multiple criteria, date ranges, summary grids, and the MAX(IF) fallback for older versions.

Learn how to use AVERAGEIFS in Excel to average data with multiple conditions. Covers text, numeric, date, and wildcard criteria, OR logic workarounds, and common errors.

Learn how to use RANK.EQ and RANK.AVG in Excel to rank scores, sales, and performance data. Covers tie handling, ascending rank, unique ranks, and conditional group ranking.