OmniDocExcel function symbols collection: the meaning and input of commonly used symbols
Organizes common operators, reference symbols, delimiters and wildcard characters in Excel formulas, explains the meaning, examples and keyboard input methods of each symbol, and provides troubleshooting methods for common input errors such as full-width symbols and delimiter settings.
- main points of this article *
- The formula must start with a half-width equal sign, otherwise Excel will display the entire content as ordinary text.
- A colon represents a continuous range, a comma separates parameters, and an exclamation point points to a cell in another worksheet.
- Add $ before the reference to lock the row or column. The reference will not move when you copy the formula.
- Commas, brackets, and quotation marks in formulas must be half-width. Full-width symbols are a common cause of errors.
- Curly brackets represent array constants, which are usually generated by Excel and cannot be directly entered manually as formulas.
If you are learning Excel functions, it is not the function names that tend to get stuck, but the symbols in the formula that look like punctuation: equal signs, colons, commas, dollar signs, exclamation marks. When a symbol is misplaced, the formula either reports an error or calculates a seemingly normal but wrong result.
This article collates a complete collection of Excel function symbols, which can be divided into operators, reference symbols, separation and marking symbols, wildcards, etc., to explain the meaning of each symbol, an example that can be copied directly, and how to enter it on the keyboard. It is suitable for beginners who are just starting to write formulas and for those who need to look up symbols quickly when looking at other people's forms.
After reading, you should be able to do this: see a formula that can tell what each symbol is doing; know which symbol to use and how to type it when you write the formula; and troubleshoot common symbol problems when the formula reports an error.
# how to pronounce the symbols in the complete book of Excel function symbols? Conclusion in a word
The symbols in the Excel formula can be roughly divided into four categories: the starting symbol that tells Excel "this is the formula", the operator for calculation and comparison, the reference symbol that specifies the location of the cell, and the auxiliary symbol that separates the parameters and marks the text. It is easier to memorize them separately than to memorize the whole table.
| Your situation. | Recommended practice | Reason |
|---|---|---|
| The formula is not calculated, only the original text is displayed. | Check whether the beginning is half-width equal sign = | Without an equal sign, Excel will not treat the content as a formula |
| To reference a contiguous cell | Use colons, such as A1:A10 | The colon indicates the whole area from the beginning to the end. |
| Misreferencing after copying formula | Add $to the row or column that needs to be fixed | The absolute reference will not change with the copy of the formula |
| To reference another worksheet | Use an exclamation point, such as Sheet2 to B3 | Exclamation points separate worksheet names and cell addresses |
| The formula reports an error, but there is no problem. | Check whether commas, parentheses and quotation marks are full-width | Full-width symbols are not recognized as formula syntax |
What do the three types of symbols do?
Operators: arithmetic, comparison and text concatenation
- arithmetic operator : + addition,-subtraction, multiplication, / division, ^ power,% percent sign. For example, = B2C2, or = 2 ^ 3 gets 8. Enter 10% equals 0.1. The- comparison operator : = equal, & lt;> is not equal, & gt; is greater than, & lt; is less than, & gt;= is greater than or equal to, & lt;= is less than or equal. The result of the comparison is TRUE or FALSE, which is often used in the condition of the IF function, such as = IF (B2 passing 60, "passing", "failing"). - text connector & *: put the text or cell contents together, such as = A2 & "-" & B2, resulting in a short dash between the contents of the two cells.
reference symbol: specify cell and range
- Colon: : indicates a contiguous region, for example, A1:A10 is column An in rows 1 to 10. The colon can be flanked by cells, an entire column (such as ARDA), or an entire row (such as 1:1). - comma, : separate different parameters in the function, such as = SUM (A1 A10, C1 C10). It can also be used as a joint reference to represent two disconnected regions, but in most scenarios you just need to remember that it is a parameter separator. - exclamation mark! : point to another worksheet in the format "worksheet name! cell". For example, = Sheet2contains B3 means to take the value of B3 in the Sheet2 worksheet. When the worksheet name contains spaces, it should be enclosed in single quotation marks, such as = 'first quarter data'! A1. - dollar sign $: lock rows or columns. A1 is a relative reference, which changes when you copy the formula; $A$ 1 is an absolute reference with fixed rows and columns; and $A1 or Aq1 is only fixed columns or only fixed rows, which is often used to make tables such as multiplication tables that require mixed references.
Separation and marking symbols: quotation marks, parentheses and curly braces
- half-corner quotation marks " : marks text constants, such as =" Total ". Numbers do not need quotation marks, and dates are usually written as functions or quoted text in formulas, depending on the format. - half-angle parentheses () : wraps function parameters and is also used to change the order of operations, for example, = (A1+B1) C1 will add first and then multiply. - curly braces {} *: denotes array constants, for example, = {1pm 2p3}. This kind of curly braces are usually generated by Excel after a specific operation, and manually entering curly braces directly into the cell does not turn a normal formula into an array formula.
# areas that are easy to be confused
wildcards: * and? Useful only in specific functions
- asterisk\ : represents any number of characters. - question mark? : represents a single character. - tilde ~ : used for escape, let or? Treated as ordinary characters.
Wildcards are mainly used in the conditions of finding and statistical functions such as COUNTIF, SUMIF, MATCH, etc. For example, = COUNTIF ("Zhang ") counts everything that starts with "Zhang". In ordinary arithmetic formulas, is just a multiplication sign, not a wildcard.
comma and semicolon: parameter delimiters are determined by locale
In many Chinese environments, function parameters are separated by commas. However, in some locales, the parameter delimiter is a semicolon, and an error will be reported when = SUM (A1 minute B1). In this case, first look at what symbols are used in your colleague's table, or confirm the delimiter in the Excel system locale, and don't blindly modify the syntax of the formula.
full-width and half-width: common sources of error
Under the Chinese input method, commas, parentheses and quotation marks are easily typed in full-width form (such as (), "). The full-width symbol looks like a half-width, but Excel does not recognize it. The problem can usually be solved by switching to the English input method and then entering symbols.
Equal sign and comparison operator: location determination meaning
The = at the beginning of the formula means "start calculation". In the case of IF, A1=B1 is a comparison and returns TRUE or FALSE. The same symbol has different meanings in different positions, so we should judge whether it is at the beginning of the formula.
# Core comparison
The following is classified by symbol, listing the meaning, examples and keyboard input methods. The keyboard position varies with keyboard layout and input method, and the common English input status shall prevail in the table.
| Symbol | Meaning | Example | Input mode |
|---|---|---|---|
| = | The beginning of a formula; compare equality. | =A1+B1; IF(A1=B1,…) | To the right of the number key |
| : | Continuous region | A1:A10 | Shift plus semicolon key |
| , | Parameter separation | SUM(A1,B1) | Comma key, need to be entered in English |
| ! | Cross-sheet reference | Sheet2!B3 | Shift plus number key 1 |
| $ | Lock rows or columns | $A$1 | Shift plus number key 4 |
| & | Text connection | A1&"-"&B1 | Shift plus number key 7 |
| ^ | Multiplier | =2^3 | Shift plus numeric key 6 |
| * | Multiplication sign; wildcard (statistical class function) | A1 to B1; "Zhang *" | Shift plus numeric key 8 |
| ? | Wildcards, single character | "Zhang?" | Shift plus diagonal bar key |
| ~ | Escape character | "~*" | Left side of numeric key 1 |
| " " | Text constant | = "Total" | Shift plus single quotation mark key |
# how to use it
- First enter the formula under the English input method to make sure that the equal sign, comma, parentheses and quotation marks are all half-angles.
two. When writing a range reference, connect the start and end cells with a colon, such as = SUM (B2:B20).
- When you need to copy a formula, check that the reference is fixed and add $to the row or column if necessary.
- When referencing across tables, check the location of the worksheet name and exclamation point. If the name contains a space, add single quotation marks.
- If the formula reports an error, check in turn whether there is an equal sign at the beginning, whether the symbol is full-width, and whether the delimiter is consistent with the locale.
If your symbolic material comes from a PDF file, such as a training handout, you can use the export function in the PDF editor of the OmniDoc desktop client to export the table contents to Excel and then check the formula. After the form is exported, whether the formula symbols are retained or not still needs to be checked one by one.
# which method is suitable for you?
- just learning the formula : first remember the four symbols of equal sign, colon, comma and $, and then look up the table when the rest of the symbols are used. - batch statistics are required for conditional data : focus on and? Wildcards, and note that they only take effect under the conditions of statistical functions. - take over someone else's form : first look at the parameter separator and cross-table reference to make sure it is consistent with your own locale. - important data need to be checked *: after the result of the formula comes out, draw a few lines to check the calculation manually. Don't just rely on the formula without reporting errors.
# frequently asked questions (FAQ)
1. The symbols in the formula are all in English, why do you still report an error?
The common reason is that the symbol looks like English, but it is actually full-width. For example, full-width parentheses and full-width commas can cause errors. Switch the input method to the English state and re-enter the symbol.
2. Why does the = A1+B1 I enter display text instead of counting?
The cell may be formatted as text, and Excel treats content that begins with an equal sign as normal text. After changing the format to normal, re-enter the formula or double-click the cell enter.
3. What's the difference between an asterisk in multiplication and in COUNTIF?
In multiplication, an asterisk is a multiplication sign, such as A1*B1. In the case of COUNTIF, an asterisk is a wildcard character that represents any number of characters. The function of the same symbol depends on its function and position.
4. How to confirm that the symbol is half-angle when entering the formula?
Just input it under the English input method. If the input method is displayed in Chinese, the symbol is usually full-width. After writing the formula, you can also check whether the parentheses and quotation marks are symmetrical and whether there are any extra full-width characters.
5. Are the formula symbols recognized by machine translation or OCR reliable?
The recognized or translated text is easy to mistakenly replace the half-width symbol with the full-width, or recognize the characters incorrectly. Please check the symbols of important formulas one by one before using them, especially quotation marks, commas and parentheses.
# Summary
The symbols in Excel formula can be divided into three types: operator, reference symbol and separation mark. After being understood one by one, most of the formulas can be understood. Confirm the equal sign and half-width sign when writing the formula, and then use the colon, exclamation point, and $to specify the location as needed. When an error is reported, priority is given to the setting of full-width symbols and delimiters, which is the source of most Excel function symbol problems.
