Excel is an incredibly powerful tool for manipulating, analyzing, and organizing data and information. Given a certain set of existing data, you can write formulas using Excel's built-in functionality to analyze and update the data any way you can imagine. But in order to successfully write a formula in Excel, you need to know about the functionality that Excel offers.
A formula in Excel is a statement that takes in certain information and returns a result. For example, you could use a formula in Excel to add numbers together, update text, or search for a specific value in a long list of cells. Below is a basic overview of the structure of a formula in Excel.
Every formula in Excel begins with an "equals" sign (=). This is to let Excel know that you are typing a formula, and not simply typing text or numbers into the cell. While this may sound trivial, beginners often make the mistake of forgetting the equals sign and wondering why their formula isn't returning the desired result.
=In addition to the equals sign, an Excel formula needs operators. The operators tell Excel what the formula should do. Excel recognizes standard arithmetic operators, such as addition (+), subtraction (-), multiplication (*), and division (/).
For example, to find the result of two plus two, you can write the following formula in Excel:
=2+2Excel will return a value of 4.
To get the result of ten divided by five, you would write the following formula:
Excel will return a value of 2.
In addition to simple arithmetic operators, Excel also offers a variety of useful built-in functions, which we will explain a bit later.
In addition to the equals sign and operators, a formula in Excel has inputs. Inputs can be numbers, text, dates, or booleans (true or false values), or references to other cells.
In the example of =10/5 above, 10 and 5 are the inputs (or operands), because the formula is using those values to derive the result. If you swap out different inputs, the formula will yield a different result.
If we're writing a formula with a text value, we must enclose the text in quotation marks. This tells the formula exactly where a text input begins and ends, so it is not confused with the rest of the formula.
A formula can also reference other cells in the spreadsheet. A cell reference is typically a letter (the column letter of the cell) followed by a number (the row number of the cell). For example, the cell in column B and row 5 is B5.
A cell reference substitutes the contents of that cell into the formula. Using cell references in a formula might look like this:
=B2+B2For example, say cell B2 contains the number 10 and cell B3 contains the number 20. The formula would return a value of 30, because it is adding the contents of cell B2 (10) and the contents of cell B3 (20).
If you write a formula which utilizes cell references, the references will change if you copy or drag the formula to a new cell, because the formula will keep trying to reference the cell in the same position relative to the cell where the formula is being written.
You can imagine this would be useful when you have a long list of numbers, and you wish to write a formula which references the first number and then copy the same formula down the list, using each number as a formula input. If you write a formula in cell B1 which references the value in cell A1, you could copy the formula to cell B2, and the formula will automatically update to reference the value in cell B2, and so on down the list.
If you wish to prevent this from happening, and continue to reference the same cell no matter where the formula is dragged or copied, you can use an absolute reference.
Excel also offers a variety of built-in functions which can be incorporated in formulas. Excel has hundreds of functions for specific types of tasks. For example, the SUM function adds several numbers together and returns the sum, the TEXTJOIN function joins different segments of text together, and the TODAY function returns the current date.
An Excel function is written with the name of the function followed by a set of open and closed parentheses. Inside the parentheses go the function inputs (or arguments, or parameters) which are separated by commas.
If we wanted to write a formula which uses the SUM function to add together the numbers, 10, 20, and 30, we would write the following formula:
=SUM(10, 20, 30)The formula would return a value of 60. Notice how the numbers we're adding together are inside the parentheses and separated by commas. Different functions require different types of inputs. For example, if we attempted to use a text value with the SUM function, we would receive an error. Additionally, certain functions require different arguments in different positions. In the VLOOKUP function the lookup value must come first, followed by the lookup range, followed by other arguments.
The best way to learn about all the functions at your disposal is to get hands-on experience working with them in Excel. If you don't use many functions on a daily basis for your job, you can work on a personal project to try manipulating data in different ways, or you can try one of our Excel dashboard projects for a firsthand look at how functions work.
Finally, a formula gives us a return value, or a result. This is the number, date, text, or other information that the formula was written to retrieve or calculate.
It may seem daunting at first, but with plenty of practice and experience you'll be writing Excel formulas in no time.
