Excel is more than just a spreadsheet program, it’s a powerful data analysis tool with its own syntax and programming language. While you can certainly use Excel for basic data entry and calculations, unlocking its full potential requires an understanding of the syntax used to write formulas, functions, and macros.
Excel’s syntax is based on a combination of values, cell references, operators, and functions with values which can be numbers, text or logical values like TRUE or FALSE.
Basic Excel Syntax
Basic Excel syntax is the foundation of creating formulas and functions in Excel. It includes cell references, arithmetic operators, comparison operators, and text operators.
Cell References
Cell references are used to specify the location of a cell or range of cells in a formula. There are three types of cell references: absolute, relative, and mixed. Absolute cell references are denoted by a dollar sign ($) before the column letter and row number, such as $A$1. This type of reference remains constant even when the formula is copied to another cell.
Relative cell references, on the other hand, do not use the dollar sign and change when the formula is copied to another cell. Mixed cell references use the dollar sign for either the column or row, but not both.
Arithmetic Operators
Arithmetic operators are used to perform mathematical operations in Excel. The basic arithmetic operators are addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). These operators can be used to perform simple calculations, such as adding two numbers or multiplying a number by a percentage.
Comparison Operators
Comparison operators are used to compare values in Excel. The basic comparison operators are equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=). These operators can be used to create conditional statements, such as IF statements, to perform different actions based on the comparison result.
Text Operators
Text operators are used to manipulate text strings in Excel. The basic text operator is the concatenation operator (&), which is used to combine two or more text strings into a single string. This operator can be used to create dynamic text strings, such as combining a first and last name into a full name.
Functions
| Function | Syntax | Description |
|---|---|---|
| SUM | SUM(number1, [number2], ...) | Adds all the numbers in a range of cells. |
| AVERAGE | AVERAGE(number1, [number2], ...) | Calculates the average (arithmetic mean) of the numbers provided. |
| COUNT | COUNT(value1, [value2], ...) | Counts the number of cells that contain numbers in the specified range. |
| IF | IF(logical_test, value_if_true, value_if_false) | Performs a logical test and returns one value if true and another value if false. |
| VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Searches for a value in the first column of a table array and returns a value in the same row from a specified column. |
| HLOOKUP | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Searches for a value in the first row of a table array and returns a value in the same column from a specified row. |
Other basic functions of excel are explciitly explained in other pages of our blog post.
Named Ranges
Named ranges in Excel are a powerful feature that allow you to assign a descriptive name to a cell or range of cells to make your formulas and functions more readable and easier to understand.
To create a named range, simply select the cell or range you want to name, then go to the Formulas tab and click on the “Define Name” button in the Defined Names group. In the “New Name” dialog box, enter a descriptive name for your range and click “OK”. You can also create named ranges using the Name Manager, which allows you to view, edit, and delete existing named ranges.
Once you have defined a named range, you can use it in your formulas and functions just like you would use a cell reference. For example, instead of using the cell reference “A1:B10” in your formula, you can use the named range “SalesData”. This makes your formula more readable and easier to understand, especially if you have multiple named ranges in your spreadsheet.
Array Formulas
Array formulas are formulas that can handle arrays of cells and it is often denoted by the { and } characters, which surround the formula. They are used to perform calculations on multiple cells at once, and they can be used to create complex formulas that would be difficult or impossible to create with regular formulas.
How to Use Array Formulas
To use an array formula, you need to follow these steps:
- Select the cell: Select the cell where you want to enter the array formula.
- Type the formula: Type the array formula, using the
=symbol to start the formula. - Enclose the formula in brackets: Enclose the formula in brackets
{}to indicate that it is an array formula. - Press Enter: Press Enter to enter the formula.
Examples of Array Formulas
Here are some examples of array formulas:
- Summing an array of cells: To sum an array of cells, you can use the following formula:
={SUM(A1:A10)}This formula will sum the values in cells A1 through A10.
- Counting an array of cells: To count an array of cells, you can use the following formula:
={COUNT(A1:A10)}This formula will count the number of cells in the range A1:A10 that contain numbers.
- Finding the maximum value in an array of cells: To find the maximum value in an array of cells, you can use the following formula:
={MAX(A1:A10)}This formula will find the maximum value in the range A1:A10.
Error Values
| Error Value | Meaning | Explanation |
|---|---|---|
| #DIV/0 | Division by zero | Excel cannot perform division by zero. |
| #N/A | No value available | The formula cannot find the value it is looking for. |
| #NAME? | Excel does not recognize the text in the formula | Excel does not recognize the text in the formula. |
| #NULL | No intersection exists between cell ranges | There is no intersection between the cell ranges specified in the formula. |
| #NUM | Problem with a number in the formula | There is a problem with a number in the formula. |
| #REF | Invalid cell reference | There is an invalid cell reference in the formula. |
| #VALUE | Wrong type of argument or operand | The wrong type of argument or operand is used in the formula. |
| #### | Column width too narrow to display data | The column width is too narrow to display the data. |
| #SPILL | Function yields multiple results and Excel cannot “spill” them due to obstructions in the required range | The function yields multiple results and Excel cannot “spill” them due to obstructions in the required range. |
| Circular Reference | A formula refers to a cell that contains the formula itself | The formula refers to a cell that contains the formula itself, causing an infinite loop. |
