Excel Formula Symbols

Ever seen strange symbols like #, @, { }, or ~ pop up in Excel formulas and wondered what they actually do? You're not alone. These symbols show up in spreadsheets all the time, and many users either ignore them or use them without really understanding their purpose. But here’s the thing: these small characters can completely change how your formulas work.

In this guide, we’ll unpack what each symbol means and show you practical ways to use them with confidence and clarity.

Watch the Video

Get the Practice File and Cheat Sheet

1. Asterisk * - The Wildcard for Any Number of Characters

The asterisk is a wildcard used in text-based formulas like COUNTIF or SEARCH. It stands for any number of characters.

Example:
If you want to count how many product names contain “Apple” anywhere in this list:

An asterisk either side of the search term will return all instances of ‘apple’ anywhere in the text string:

=COUNTIF(B6:B13, "*apple*")

=8

Want to find “Apple” only at the end of the text string? Use the asterisk wild card like this:

=COUNTIF(B6:B13, "*apple")

=6

Use it when you need flexible pattern matching in strings.

2. Question Mark ? - The Wildcard for Exactly One Character

The question mark matches exactly one character.

Example:
To sum values for Order IDs that start with “AB12” followed by any one character from this list:

Use the question mark in SUMIF like this:

=SUMIF(B6:B11, "AB12?", C6:C11)

=$6,000

Want to allow two characters after “AB12”? Just use two question marks:

=SUMIF(B6:B11, "AB12??", C6:C11)

=$1,000

Use ? when the pattern has a fixed number of varying characters.

3. Tilde ~ - Escape Character for Wildcards

What if you want to find an actual * or ? in your data?

Use the tilde ~ before the symbol to treat it as a literal character.

Example:

=COUNTIF(C6:C8, "*~?*")

This counts cells that literally contain a question mark anywhere in the text string.

On row 12 of the screenshot below you can see the difference between using the tilde to count text containing instances of a question mark surrounded by any number of characters vs the formula on row 11 which counts all cells because *?* is essentially saying count any cell that contains at least 1 character.

Use tilde to escape special characters:

~? → search for a real question mark

~* → search for an asterisk

~~ → search for the tilde itself

4. Curly Braces {} - Define Arrays

Curly braces are used to define arrays directly in formulas.

For example, let’s say you want to create a series of quarter starts dates for 2025.

We can use the DATE function:

Syntax:

=DATE(year, month, day)

And instead of a single month number, we can use an array of the months we want returned:

This returns a vertical array but if you want a horizontal array, replace the semicolons separating the month numbers in the array with commas:

Excel 2021 and Microsoft 365 automatically spill the results. In older versions of Excel, you’ll need to select the cells you want the dates returned to first, then press Ctrl + Shift + Enter to complete the formula.

5. Trim Ref Dot Operator . - Trim Empty Cells in Ranges

This new feature lets you dynamically trim ranges by using a dot before or after the colon range operator. It ensures Excel only refers to cells containing data while allowing new data to be included automatically, resulting in more efficient calculations.

Example:

=SUM(C6:.C20)

This tells Excel to ignore any trailing empty cells at the bottom. You can also trim from the top with a dot before the colon:

=SUM(C6.:C20) 

or both with a dot either side of the colon:

= SUM(C6.:.C20).

For even more flexibility, try the new TRIMRANGE function, which trims rows and columns together.

6. Hash # - Refer to Spilled Arrays

If you're using dynamic arrays like UNIQUE, the # symbol lets you refer to the entire spilled range.

Example:

In the screenshot below you can see cells F7:F10 contain a UNIQUE formula that spills an array of 4 values. As shown in cells I6:J6, I can count those values with COUNTA by simply referring to the first cell in the spilled array followed by a hash sign:

=COUNTA(F7#)

This spilled array references adjusts automatically as new data spills into the array, so I never need to update my COUNTA formula.

7. Dollar Sign $ - Lock References

The $ symbol locks parts of a reference in place when copying formulas.

Examples:

• $A$1 - Absolute row and column
• A$1 - Absolute row only
• $A1 - Absolute column only

Use the F4 key to quickly toggle between these when writing or editing formulas.

The SUMIFS formula below illustrates the use of mixing relative and absolute references which enables the formula to be written once for cell N6 and then copied to the remaining cells in the table:

8. Square Brackets [] - Structured References in Tables

When working with Excel Tables, column references use square brackets:

Example:

Find the sum of the Stock column in the Table below (the Table name is ‘Stock’):

We can use the SUM function and reference the table name and column name inside square brackets:

=SUM(Stock[Stock])

Likewise, for the total of the Value column:

=SUM(Stock[Value])

Tip: You can type the structured references in or select the table column with your mouse and have Excel complete it for you.

Note: Structured references don't respond to F4. You must use extra brackets to lock them. More on making Excel Table Structured References absolute here.

9. At Symbol @ - Current Row in Tables

Inside Excel Tables, the @ symbol refers to the current row. The formula in column G below multiplies the values from the current row’s Price and Stock columns:

This formula is exactly the same on every row and because it’s inside a table, it will automatically copy it down the column and apply it to any new rows added to the table.

Outside the table, you need to include the table name:

=Stock[@Price]*Stock[@Stock]

IMPORTANT: the @ symbol only works if the formula is in the same row as the data it's referencing.

10. Apostrophe ' - Force Text Format

Starting a cell with an apostrophe tells Excel to treat the entry as text — even if it looks like a number, formula, or date.

Examples:

• '01-2571 — Stops Excel converting it into a date
• '00123 — Keeps leading zeros
• '=$A$1+B1 — Displays the formula without calculating it

The apostrophe is invisible in the cell, but you’ll see it in the formula bar.

Conclusion: Use Symbols Intentionally

Symbols like *, @, { }, #, and [ ] may seem minor, but they pack a punch. Understanding how Excel interprets them gives you greater control over your formulas and spreadsheet design.

If you're ready to level up even further, check out our Advanced Excel course — built for professionals who want to build smarter, faster, and more reliable spreadsheets.