Printable Excel Formulas Cheat Sheet

CONCATENATE joins text from several cells, or text you type, into a single string. CONCAT is its newer replacement and does the same job while also accepting whole ranges, so =CONCAT(A2:C2) joins three cells at once. The ampersand operator does the same work with less typing, and many people prefer it: =A2&" "&B2.

With first names in column A and last names in column B, =CONCAT(A2, " ", B2) in column C returns a full name such as "Maria Lopez." Fill it down and every row gets a full name. The same pattern builds product labels, mailing lines, or matching keys such as =A2&"-"&B2 for lining up records across two sheets.

Remember to add the spaces and punctuation yourself, because Excel joins exactly what you give it and nothing else. Numbers also lose their formatting when joined, so $1,250 becomes 1250 unless you wrap it in TEXT, as in =A2&" owes "&TEXT(B2, "$#,##0"). When you need a separator between many cells, TEXTJOIN adds it for you and can skip blanks: =TEXTJOIN(", ", TRUE, A2:E2).

LEFT, RIGHT, and MID extract part of a text string. LEFT takes a set number of characters from the start, RIGHT takes them from the end, and MID starts at a position you choose and takes as many characters as you ask for. Together they break apart codes, IDs, and any text that follows a consistent pattern.

Suppose your product codes look like "FUR-10482-BLK." =LEFT(A2, 3) returns "FUR," the category. =RIGHT(A2, 3) returns "BLK," the color. =MID(A2, 5, 5) starts at the fifth character and returns "10482," the item number. Put each in its own column and you can sort, filter, and total by category in seconds.

These formulas always return text, even when the result looks like a number, so "10482" will not add up with SUM or match a numeric lookup. Wrap the result in VALUE to convert it. When the pieces vary in length, combine the formulas with FIND to locate a separator, such as =LEFT(A2, FIND("-", A2)-1), which returns everything before the first dash. Newer versions of Excel also offer TEXTBEFORE and TEXTAFTER for this job.

LEN returns the number of characters in a cell, counting letters, numbers, punctuation, and spaces. On its own it sounds minor, but it is one of the fastest ways to spot data entry problems in a long list.

If every customer ID in your system should be eight characters long, =LEN(A2) in a helper column shows any ID that is too short or too long. Combine it with IF, as in =IF(LEN(A2)<>8, "Check", ""), and you get a flag on every row that needs attention. It works just as well for phone numbers, ZIP codes, and SKU lists.

Because LEN counts spaces, it is also the best way to find hidden ones. If =LEN(A2) returns a bigger number than the characters you can see, the cell has extra spaces at the start or end, which is often why a lookup fails. Run the text through TRIM and compare the two lengths to confirm.

TRIM removes leading and trailing spaces from text and shrinks any run of spaces between words down to a single space. It does not change the letters themselves, so it is safe to run across an entire column of imported names or product descriptions.

Contact lists exported from another system often arrive with stray spaces, so " Jane Smith " sits in a cell looking almost normal. =TRIM(A2) returns "Jane Smith." This matters most for lookups, because VLOOKUP and XLOOKUP treat "Jane Smith " and "Jane Smith" as different values, so trimming both sides is often the fix for a lookup that returns #N/A for no obvious reason.

TRIM only removes the standard space character. Data copied from web pages often contains non-breaking spaces, character 160, which TRIM leaves in place. For those, use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). Once the cleaned column looks right, copy it and use Paste Values over the original so you are not left depending on a helper column.

These three formulas change the case of text. UPPER converts every letter to capitals, LOWER converts every letter to lowercase, and PROPER capitalizes the first letter of each word and lowercases the rest. None of them change spaces, numbers, or punctuation.

Customer names from an old database often arrive in all caps. =PROPER(A2) turns "JOHN SMITH" into "John Smith" across thousands of rows at once. LOWER is the go-to for email addresses, since =LOWER(B2) makes every address consistent before you remove duplicates, and UPPER keeps state abbreviations and product codes uniform.

PROPER is not perfect with names. It turns "McDonald" into "Mcdonald," and because it capitalizes any letter that follows a non-letter, it turns "it's" into "It'S." Scan the results before anything goes out to customers and fix the few exceptions by hand. As with TRIM, finish by pasting the results as values.

SUBSTITUTE swaps one piece of text for another inside a cell. You give it the cell, the text to find, and the text to put in its place. It is case sensitive, and by default it replaces every occurrence, though an optional fourth argument lets you replace only a specific one, such as the second.

If your product names all include "Basic" and you are renaming the tier to "Starter," =SUBSTITUTE(A2, "Basic", "Starter") handles every row at once while leaving the original list untouched for reference. Replacing with nothing removes text entirely, so =SUBSTITUTE(B2, "-", "") strips the dashes out of phone numbers or part numbers.

Nest SUBSTITUTE to make several replacements in one cell. For example, =SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "") removes both parentheses. Because it is case sensitive, "basic" and "Basic" are treated as different words, so clean the case first with LOWER or PROPER if your data is mixed. When you want to replace characters by position instead of by content, use REPLACE.