Excel Function

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 equals operator treats “APPLE” and “apple” as identical — which is correct most of the time but wrong when case is meaningful. Product codes, status flags, and reference IDs often need an exact case match. The EXACT function compares two text strings character by character and returns TRUE only when every letter matches precisely. This guide covers 8 practical patterns: basic comparisons, IF+EXACT audit labels, data validation rules that enforce uppercase or proper case, case-sensitive INDEX MATCH lookups, SUMPRODUCT counting without COUNTIF’s case-blindness, prefix validation with LEFT, and a three-way audit formula that separates case errors from content errors.

Customer names imported from CRMs and databases rarely arrive in the right case — all caps, all lowercase, or randomly mixed. Excel’s PROPER function fixes the entire column in one formula, capitalising the first letter of every word and lowercasing everything else. This guide covers the full picture: when to use PROPER over UPPER and LOWER, its four known limitations (Mc/Mac prefixes, acronyms, apostrophes, and numbers), and the SUBSTITUTE workaround that fixes each one. You will also find the sentence case formula Excel is missing natively, the TRIM+CLEAN pipeline for dirty data, and eight practical examples from customer names through to mail-merge salutations.

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.