What is Excel Formulas?

We can consider Microsoft Excel a calculation engine, which gives answer to questions, like 4*23 = ?. These questions are generally called mathematical operations, and in Excel, they are formulas.

Excel allows you to use formulas to perform not only mathematical operations but also hundreds of other complex actions, such as: parsing textual values, searching for certain values in a range of data, performing recursive calculations, etc… To leverage the full power of Excel formulas, you need to understand how Excel formulas work as well as some of the ground rules for working with formulas. So, let’s get acquainted with the fundamentals of Excel formulas.

Excel spreadsheet

An Excel spreadsheet is simply a grid of cells that you can use to enter data. Each cell is given an address by its coordinates in the grid, for example: the cell B5 is located in column B, row 5. When you enter a value in cell B5, let’s say 5, that cell’s value becomes 5. If you enter the number 11 in cell B5, that cell’s value becomes 11.

Inputting formulas

You can use then use above mentioned values in a formula.
For instance, you can click cell C5 and begin typing =A5+B5, as shown in below capture:

Note how you can see your formula in both the cell you’re typing in and the Formula Bar above the column headings.

Then, press enter, cell C5 will show the value of this operation as 16.

One important note is that: once you first enter = in a cell, Excel understand that the cell contains formulas, and in formulas, values of other cells are depicted by their address, like in the example: A5 & B5.

Cell address (or cell reference) is just one of value types that you can enter in a formula. Let’s see them all:

• Cell references: These include any value that points back to a single cell or range of cells. Single cells are in the above samples. Range of cells are in format: <Start-Cell>:<End-Cell>. For example: A2:C5, which means all cells in the range of A2 to C5.
• Constants: You can type in hard-coded numbers directly in a formula. For example, you can enter =5+11 directly into a cell to get the answer 16.
• Operators: These are symbols that perform addition (+), multiplication (*), value comparison (>). For example, entering =20>14 into a cell returns TRUE as the result because 20 is greater than 14.
• Text: Any text string can be used as an argument in a formula as long as it’s wrapped in quotation marks. For example, entering =“Hi”&” There” in any cell results Hi There.

Formula Operators

Excel support most of common used operators. Let’s see a table listing of them:

Type	Character	Operation	Example
Arithmetic	+ (plus sign)	Addition	=A2+B3
	– (minus sign)	Subtraction or negation	=A3–A2 or –C4
	* (asterisk)	Multiplication	=A2*B3
	/	Division	=B3/A2
	%	Percent (dividing by 100)	=B3%
	^	Exponentiation	=A2^3
Comparison	=	Equal to	=A2=B3
	>	Greater than	=B3>A2
	<	Less than	=A2<B3
	>=	Greater than or equal to	=B3>=A2
	<=	Less than or equal to	=A2<=B3
	<>	Not equal to	=A2<>B3
Text	&	Concatenates (connects) entries to produce one continuous entry	=A2&” “&B3t

Order of operator precedence

It’s common that you create a formula with several operators, and Excel evaluates and performs the calculation in a specific order. It’s very important for you to know this order. For instance, multiplication is performed before addition. This order is called the order of operator precedence.

To override the built-in operator precedence, you can use parentheses to specify which operation to be evaluated first. Let’s see an example. The  answer to (4+3)*3 is 21. However, if you take off the parentheses, as in 4+3*3, Excel performs the calculation in this order: 3*3 = 9, then 4 + 9 = 13.

The order of operations for Excel is as follows:

• Evaluate items in parentheses.
• Evaluate ranges (:).
• Evaluate intersections (spaces).
• Evaluate unions (,).
• Perform negation (-).
• Convert percentages (%).
• Perform exponentiation (^).
• Perform multiplication (*) and division (/), which are of equal precedence.
• Perform addition (+) and subtraction (-), which are of equal precedence.
• Evaluate text operators (&).
• Perform comparisons (=, <>, <=, >=).

Notes:

Operations that are equal in precedence are performed left to right.

Nested parentheses can be used to make sure things are performed in your wanted order. For example, =((C1*B1)+(E3*F4))*E1.

 

That’s a overall understanding about Excel Formulas. But to use formulas efficiently, you should refer to another post about Excel Cell References here to get more understanding about cell references, which are widely used in formulas and excel calculation in general.