Symbols and signs in Microsoft Excel are crucial for data manipulation, data analysis, and high-quality formulas. In this guide, I’ll run through all the key symbols (listed alphabetically), explain how you can use them, and show you some examples to make things clearer.
Ampersand (&)
The ampersand in Excel is used for joining—also known as concatenating—two or more strings to produce a single text string.
Here, the ampersand concatenates the value in cell A2 with the value in cell B2 to produce a product code in cell C2:
=A2&B2
Even though the ampersand returns a text string, concatenated numbers can still be referenced in formulas as though they were values.
In this example, the ampersand is concatenating the value in cell A2, a space, and the value in cell B2 to return a full name:
=A2&" "&B2
As well as using the ampersand sign, there are other ways to join text in Excel, such as using the CONCAT or TEXTJOIN functions.
Apostrophe (‘)
Adding an apostrophe to the start of a cell entry forces Excel to treat the cell’s contents as text. This is particularly useful if you’re typing numbers you don’t want to be treated numerically, like a phone number or ID number.
In this example, typing:
'07123456789
retains the leading zero by turning the number into a text value. You know a numerical value is treated as text when it’s left-aligned in its cell.
If the apostrophe is omitted, Excel interprets the entry as a numerical value, so it removes the leading zero. The value is also right-aligned, as this is the default alignment for numerical values in Excel.
Asterisk (*)
The asterisk has two uses in Excel—as the multiplication arithmetic operator and as a wildcard character representing any number of characters.
In the screenshot below, the average monthly profit in cell B2 is multiplied by 12 using the asterisk to generate the projected annual profit:
=B2*12
In this example, the asterisk is used in the criteria of the SUMIF function to sum the scores of all teams whose ID starts with GB, followed by any number of characters:
=SUMIF($A$2:$A$7,"GB*",$B$2:$B$7)
The asterisk can also be used as a wildcard in the text fields of the Find And Replace dialog box, as well as in filters.
At (@)
The @ symbol—also known as the implicit intersection operator—is used in formulas in Excel tables to reference a cell on the same row. It’s added automatically when you type a formula into a cell in a table.
In this table, I want to multiply each shop’s monthly average profit by 12 to return the projected annual profit in column C.
To do this, in cell C2, I’ll type the equal sign, and select cell B2.
Notice how the formula places the @ symbol before the structured reference to the column header. This tells Excel to repeat the calculation on each row as separate formulas, rather than trying to produce a dynamic array of results from a single formula:
Now, I can complete the formula and press Enter to duplicate it in the remaining cells in the column:
=[@MonthAvProf]*12
Colon (:)
The colon has two roles in Excel. First, it is used in formulas as a range operator to reference all the cells between and including two cell references. On the other hand, it can be used to force Excel to recognize a cell input as a time.
In this formula, the colon tells Excel to apply the function (in this case, SUM) to all cells between and including A1 and B7:
=SUM(A1:B7)
When you enter a time into a cell in Excel, doing so using a period means that the program recognizes it as a decimalized numerical value:
This becomes problematic if the time starts or ends with a zero (such as 00.25 or 12:50) because Excel removes these redundant zeros. What’s more, incorrectly formatted time values will miscalculate if used in formulas.
However, adding a colon between the hour and minutes converts the numerical value into a recognized time value, as seen in the formula bar at the top of the Excel window.
Add a second colon if you want to include seconds in the time value.
Using a colon to input time entries saves you from having to select the appropriate number format manually, and you can use these correctly formatted values in time-based calculations.
Comma (,)
The comma is as versatile in Excel as it is in grammar, serving wide-ranging purposes in different contexts.
Commas are most frequently used to separate arguments in formulas. Here, commas are used to separate the rows, columns, starting number, and step value arguments in the SEQUENCE function:
=SEQUENCE(10,2,1,1)
In this example, commas are used within an array constant to return a horizontal array of the largest three values in cells A1 to A10:
=LARGE(A1:A10,{1,2,3})
Commas can also be added to numbers as thousands separators to make them easier to read. To do this, select the relevant cells, press Ctrl+1 to launch the Format Cells dialog box, and in the Number category of the Number tab, check “Use 1000 Separator.”
The comma can also be nominated as the delimiter when joining and splitting text or in Power Query Editor.
Curly Braces: { }
Curly braces in Excel are used to return an array, rather than a single result.
Some functions in Excel return dynamic, spilled arrays by default, such as SORT and FILTER. However, you can use this principle to force a function that doesn’t automatically produce a dynamic array to generate more than one result.
Imagine you receive bonus payments on the 20th day of every other month, and you want to record these in a spreadsheet.
Rather than typing the dates manually, you can use curly braces to create an array constant. After formatting cells A2 to A7 as dates, in cell A2, type:
=DATE(2025,{2;4;6;8;10;12},20)
where 2025 is the year, the numbers in the curly braces represent the months, and 20 is the day.
Curly braces can also be used to shorten formulas containing logical functions, saving you from typing each logical argument individually:
=IF(OR(A1={5,6,7,8}),"YES","NO")
Equal (=)
All formulas in Excel must start with the equal sign:
=COUNTA(A1:A20)
The equal sign can also be used as a comparison operator in logical operations. See Logical Operators below.
Dollar ($)
The dollar sign turns references from relative to absolute or mixed in Excel formulas.
By default, references in Excel are relative, referring to the relative position of the cell. If you type a formula in cell A1 that involves cell A2, you are referencing the cell that is one cell below where you’re typing. If you then copy that formula to another cell, the reference would automatically perform the same relative action as in its previous location.
However, when you add the dollar sign to formulas, you can make them absolute or mixed. An absolute reference does not change if you copy the formula into another cell, whereas in a mixed formula, the column reference could be absolute but the row reference relative (or vice versa).
In this example, typing:
=B2*$E$2
into cell C2, with dollar signs before both the column and row references to the cell containing the fixed rate, and applying the formula to the remaining cells in column C ensures that the formula constantly references the rate in cell E2, regardless of where the formula is entered.
Rather than typing dollar symbols in formulas manually, press F4 as soon as you’ve created the cell reference.
On the other hand, placing a dollar before just the column or row reference creates a mixed reference. In this formula, the reference to column H is relative, but the reference to row 2 is absolute:
=C2*H$2
As a result, the formula can be applied to new columns added each week, taking into account the changing rates of pay.
To apply the dollar currency to a value, select the cell, press Ctrl+1 to launch the Format Cells dialog box, and select “Currency” or “Accounting” in the Number tab. Then, choose the dollar symbol from the drop-down menu.
Exclamation Point (!)
Exclamation points appear in Excel formulas when they reference cells in other worksheets.
For example, typing:
=SUM(
into cell B1 of Sheet 2, and selecting cell B2 in Sheet 1 creates a reference containing the sheet name and the cell reference, with the exclamation point serving as the delimiter between the two:
=SUM(Sheet1!B7
You can then repeat the process to reference other cells in the formula if required:
=SUM(Sheet1!B7+Sheet1!C7)
