Excel Function

Chapter headings, legal sections, Super Bowl editions, and copyright years all use Roman numerals. Writing them manually for large numbers like MMXXVI is slow and error-prone. Excel’s ROMAN function converts any integer from 1 to 3999 into its Roman numeral equivalent instantly. It also offers five notation levels — from strict classical (CDXCIX) to simplified (ID) — and pairs with the ARABIC function to convert back for calculations. This guide covers 8 practical examples including chapter labels, self-updating copyright years, lowercase preface numbering, sequential outlines with ROW, event edition labels, and everything you need to know about the form argument.

Excel has no built-in English function that converts numbers to words — but it does have BAHTTEXT (Thai only) and the Microsoft-published Spell Number VBA macro (English, any currency). This guide covers both fully. You will find all BAHTTEXT examples for Thai invoices, the complete Spell Number VBA code with plain-English annotations, step-by-step installation instructions, examples for cheques and payment vouchers, how to customize Spell Number for pounds, euros, or rupees, and a full comparison of every available method — including the GOOGLETRANSLATE workaround in Google Sheets and LAMBDA-based alternatives in Excel 365.

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 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 retrieve information about the current operating environment and spreadsheet setup. This tutorial explains the different info_type arguments available in INFO and how to use them to check system details, operating system information, current directories, recalculation settings, and other Excel environment properties. Explore practical examples and use cases for troubleshooting, spreadsheet auditing, compatibility checks, and advanced workbook automation. Ideal for Excel power users, analysts, developers, auditors, and professionals who need to inspect or troubleshoot their Excel environment.

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.