Special characters in Excel

Everyone means something different by “special characters in Excel”. Different groups of characters have different special properties, and that’s why users call them special. Below I list all such characters, what they do and how to deal with them.

If you’re looking for symbols and icons to insert into cells, you need the CHAR function, and the UNICHAR function lets you insert even more: arrows, check marks, stars and thousands of other Unicode characters.

Below are all the characters that Excel treats not as plain text but as part of its syntax. In short:

Wildcard characters in Excel

Of all the special characters in Excel, the asterisk (*) and the question mark (?) probably raise the most questions among beginners. Yet they are very useful. They are wildcards: they stand in a string instead of other characters, only marking them in a certain way.

How exactly? It’s simple:

• the question mark (?) means any single character: a digit, a letter, a space or something more exotic, like a non-breaking space or a tab;
• the asterisk (*) means any number of any characters, including none.

Using wildcards in Find and Replace

Wildcards are most useful in the built-in Find and Replace. Some examples:

• “?” with Match entire cell contents finds all cells with exactly one character, for example one-digit numbers;
• “?7” finds two-character cells ending with 7;
• “7*” finds all cells starting with 7;
• “*” finds all non-empty cells.

Note the Match entire cell contents checkbox. Thanks to it, the search doesn’t look inside the cell, but compares the pattern with the whole cell.

How to find the asterisk (*) and the question mark

The asterisk and the question mark can also appear in the text itself, and you may need to find them. Then you have to tell Excel to treat them literally, not as wildcards.

Microsoft has provided for this: put a tilde (~) before them. The tilde is usually in the top left corner of the keyboard, under Esc. So “~*” finds an asterisk, and “~?” finds a question mark. More in the article on how to find and remove asterisks in Excel.

Funnily enough, the tilde itself also has to be escaped with a tilde to be found: “~~”. Otherwise Excel won’t find it.

Wildcards in filters and the Advanced Filter

Just like in Find and Replace, you can use wildcards when filtering data: Text Filters → Contains, Begins With and so on, and in the criteria range of the Advanced Filter. Escaping with the tilde works here too.

Wildcards in formulas

Besides the built-in tools, wildcards work in some formulas:

• COUNTIF and similar conditional functions: COUNTIFS, SUMIF, SUMIFS, AVERAGEIF;
• SEARCH (but not FIND, which takes characters literally);
• VLOOKUP and similar lookup functions: HLOOKUP, MATCH, and XLOOKUP with match mode 2.

For example, this formula counts the cells in A1:A100 that contain the word “sale” anywhere in the text:

=COUNTIF(A1:A100,"*sale*")

The equal sign (=): starting a formula and comparing

Every formula starts with “=”: Excel calculates everything after it. Type 2+2 and the cell keeps the text; =2+2 returns 4. You can also start a formula with “+” or “−”: a legacy of older spreadsheet programs — Excel accepts it and adds “=” at the start.

Inside a formula the same sign is the comparison operator. =A2=B2 returns TRUE if the values are equal and FALSE if not. The first “=” starts the formula, the second compares.

If a formula shows in the cell as text and doesn’t calculate, there are usually three reasons: the cell is formatted as Text, there’s a space or an apostrophe before “=”, or Show Formulas mode is on (Ctrl+` or Formulas → Show Formulas). And if you need text that starts with “=”, see the next section.

Math signs at the beginning of a cell in Excel

Most of you have probably seen this: if you type text that starts with a plus, an equal sign or a minus, Excel usually returns the #NAME? error.

How do you put an equal sign, a minus or a plus at the beginning of a cell? The problem is that the equal sign starts every formula, and plus and minus are math operators that Excel expects to work with numbers.

For these characters to be treated as text, put any other character before them. Another option is to type an apostrophe (') before them. That’s another special Excel character, which makes the cell value text. With it, you explicitly tell Excel that the value is just text and nothing else. Excel itself suggests this option when you type such a value manually.

The apostrophe (') in Excel

The apostrophe was already mentioned above, where it helped to put an equal sign at the beginning of a cell without turning it into a formula.

Typed at the beginning of a cell, it tells Excel that the data is strictly text. A curious fact: the length of the cell content can change in some cases. For example, if you type 50%, Excel stores the number 0.5, and if you type '50%, it stores the text “50%”. The same goes for dates: without the apostrophe, they become serial numbers. More about this in the article on the TEXT function.

The apostrophe itself is invisible to everything else. The length of the cell stays the same as without it, and formulas referring to the cell don’t see it. In other words, it doesn’t harm the data, it only makes it text. That’s also why it’s a common reason why numbers are saved as text after an import.

The ampersand (&) in Excel

In formulas, the ampersand is the concatenation operator, a shorter replacement for the CONCATENATE function. Both formulas below do exactly the same thing, joining cell A1, a space and cell B1:

=CONCATENATE(A1," ",B1)

=A1&" "&B1

Obviously, the second one is shorter and has no nesting (no parentheses), which is sometimes useful in complex formulas.

One more property: concatenation is a text operation by nature, so even if you join two numbers with an ampersand, the result is text: =1&2 returns the text “12”, not the number 12.

The dollar sign ($): absolute and relative references

In formulas the dollar sign has nothing to do with currency. It locks part of a reference so that it doesn’t shift when the formula is copied:

• A1 — a relative reference: filled down it becomes A2, A3…, filled right — B1, C1…;
• $A$1 — absolute: never changes;
• $A1 — the column is locked, the row changes;
• A$1 — the row is locked, the column changes.

Add the $ signs with F4: cursor on a reference in the formula bar, each press cycles A1 → $A$1 → A$1 → $A1. A full guide with mixed references, running totals and VLOOKUP is in the article on absolute and relative references in Excel.

Quotes in Excel formulas

Double quotes tell Excel: “this is text, don’t calculate it”. Without them a word in a formula is treated as a function or range name, or as a #NAME? error. What you need to know:

• Text in a formula always goes in quotes: =IF(B2>0,"paid","due"). Numbers and cell references don’t.
• A number in quotes is text. Excel will still calculate ="5"+1, but =A2="5" returns FALSE if A2 holds the real number 5.
• Empty quotes "" are an empty string. =A2="" checks whether a cell is empty, and =IF(B2=0,"",A2/B2) leaves the cell looking blank instead of showing an error.
• A quote inside text is doubled. To get the text He said "yes", write ="He said ""yes""". Four quotes in a row """" are text consisting of a single quote mark.
• You can insert a quote with CHAR(34) — the formula reads more easily.
• Criteria in functions go in quotes: =COUNTIF(A:A,">100"). If the number comes from a cell, close the quotes before the reference: =COUNTIF(A:A,">"&D1).

Single quotes (apostrophes) appear in formulas in one place only — around sheet names with spaces: ='Sales 2026'!B3. More on that in the next section.

The exclamation mark: a reference to another sheet

The exclamation mark separates the sheet name from the cell address: =Sheet2!B3 is cell B3 on the sheet “Sheet2”. You don’t need to type it: start a formula, click the other sheet’s tab and the cell, and Excel inserts the reference.

• If the sheet name contains a space, a hyphen, brackets or starts with a digit, it goes in single quotes: ='May report'!B3.
• If the sheet name itself contains an apostrophe, it is doubled inside the reference.
• A range on another sheet: =SUM(Sheet2!B2:B100). Several consecutive sheets at once — a 3D reference: =SUM('January:March'!B2) adds up B2 on every sheet from “January” to “March”.
• The $ sign goes after the exclamation mark: Sheet2!$B$3.

All the ways to refer to a cell, sheet or file, including hyperlinks, are collected in the article on how to create a hyperlink in Excel.

Square brackets in formulas

Square brackets play four roles in Excel.

• Another workbook’s name. ='[Rates.xlsx]Sheet1'!$B$2 is cell B2 on “Sheet1” of the file Rates.xlsx. If the file is closed, Excel shows the full path before the brackets.
• Columns of an Excel table. In a table created with Ctrl+T, references use names: =SUM(Sales[Amount]) is the whole “Amount” column of the “Sales” table, =[@Price]*[@Qty] — values from the current row. Special items: [#Headers], [#Data], [#Totals], [#All].
• The R1C1 reference style. R[-1]C[2] is the cell one row up and two columns right. Brackets mean a relative offset; the style is turned on in File → Options → Formulas.
• Number formats. In a custom format, brackets hold a color and a condition: [Red] colors the number, [>1000] sets a condition, [h]:mm shows more than 24 hours.

In regular expressions square brackets mean a character set — see the section on regular expressions below.

Separators in formulas: comma, colon, space

• , (comma) separates function arguments: =IF(A2>0,"yes","no"). In Excel versions with a comma as the decimal separator (most of Europe), a semicolon is used instead: =IF(A2>0;"yes";"no"). That’s why a formula copied from a site in another language may fail — replace the separators.
• : (colon) defines a range: A2:C10 is the rectangle from A2 to C10, A:A is a whole column, 2:2 a whole row.
• A space between two ranges is the intersection operator: =SUM(B2:D5 C1:C10) adds only the shared cells C2:C5. That’s why a stray space in a formula can produce a #NULL! error.
• Several ranges in one function are listed with commas: =SUM(A2:A10,C2:C10).

Operators and criteria: <>, >=, %, ^ and double negation

In functions with criteria — COUNTIF, SUMIFS and the like — the operator goes inside the quotes together with the value: =COUNTIF(B:B,"<>0") counts non-zero cells, =COUNTIF(A:A,"<>") non-blank cells, =COUNTIF(A:A,"") blank cells. A value from a cell is attached with an ampersand: =SUMIF(B:B,">="&E1).

Double negation “--” is two minus signs in a row: the first flips the sign, the second flips it back, and the result is that TRUE becomes 1, FALSE becomes 0 and the text “15” becomes the number 15. This lets you count cells by a condition without COUNTIF:

The order of calculation is the usual one: power first, then multiplication and division, then addition and subtraction, then joining with &, and comparison last. Use parentheses when in doubt. One catch: negation is calculated before power, so =-2^2 returns 4, not −4.

Array characters: #, @ and curly braces

• # after a reference — the whole dynamic array that spilled from the cell in Excel 365 and 2021: =COUNTA(C2#). If the array grows, the reference grows with it.
• @ before a reference or function — implicit intersection: take one value instead of an array. Excel 365 often adds @ to formulas from older files so they calculate as before. Inside a table, [@Price] is the value from the current row.
• { } in constants — an array right in the formula: =SUM(COUNTIF(A:A,{"Anna","Oliver"})) counts both names at once. Commas separate items in a row, semicolons separate rows: {1,2,3} is horizontal, {1;2;3} vertical.
• { } around the whole formula — a legacy array formula entered with Ctrl+Shift+Enter. You can’t type these braces by hand; Excel adds them itself.

More about arrays in the article on array formulas in Excel.

Special characters in regular expressions

In regular expressions — in the REGEXTEST, REGEXEXTRACT and REGEXREPLACE functions and in !SEMTools procedures — the characters . * + ? ^ $ | \ ( ) [ ] { } mean something quite different from formulas. For example, a dot is any character there, and $ is the end of the string. This syntax is covered separately in the article on regular expressions in Excel.

Other special characters in Excel

• Invisible characters: the non-breaking space CHAR(160) and the line break CHAR(10). They look like an ordinary space or nothing at all, but break searches and comparisons. See how to remove extra spaces.

Excel special characters: FAQ

What does the $ sign mean in an Excel formula?

It locks the column, the row or both in a reference so they don’t change when the formula is copied: $A$1, $A1, A$1. Add it with F4.

What does the exclamation mark mean in a formula?

It separates the sheet name from the cell address: Sheet2!B3 is cell B3 on the sheet “Sheet2”.

How do I put quotes inside an Excel formula?

Double the quote inside the text: ="He said ""yes""". Or use CHAR(34).

What do square brackets mean in a formula?

Another workbook’s name ([Book.xlsx]Sheet1!A1), a table column (Table1[Price]) or an offset in R1C1 style.

Why doesn’t a formula copied from a website work?

The argument separator differs between locales (comma or semicolon), and function names are translated in localized versions of Excel.

How do I type text that starts with = into a cell?

Put an apostrophe before it: '=text. The apostrophe isn’t displayed in the cell.

Summary: Excel special characters at a glance

I hope you found what you were looking for on this page and now know how special characters work in Excel. Most problems come from the asterisk and the question mark in searches (escape them with a tilde) from text that starts with an equal sign or a minus (put an apostrophe before it) and from a forgotten $ sign when filling a formula.

Related articles: how to find certain characters in Excel cells, how to remove extra characters from all cells and how to add characters to every cell of a range.