Excel formulas help you calculating mathematical values using those values and return the resulting value in the cell of your choice. Formulas you can automatically perform include sum, subtraction, percentage, division, average, and event dates/times.
Excel formulas are also called “functions.” To insert one into your spreadsheet, highlight a cell in which you want to run a formula, then click the far-left icon, “Insert Function,” to browse popular formulas and what they do.
1. SUM: All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you’d like Excel to perform.
The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you’d like to add together using the format, =SUM(value 1, value 2, etc).
The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet.
- To find the SUM of 30 and 80, for example, type the following formula into a cell of your spreadsheet: =SUM(30, 80). Press “Enter,” and the cell will produce a total of both numbers: 110.
- To find the SUM of the values in cells B2 and B11, for example, type the following formula into a cell of your spreadsheet: =SUM(B2, B11). Press “Enter,” and the cell will produce the total of the numbers currently filled in cells B2 and B11. If there are no numbers in either cell, the formula will return 0.
Keep in mind you can also find the total value of a list of numbers in Excel. To find the SUM of the values in cells B2 through B11, type the following formula into a cell of your spreadsheet: =SUM(B2: B11). Note the colon between both cells, rather than a comma. See how this might look in an Excel spreadsheet for a content marketer, below:
2. IF The IF formula in Excel is denoted =IF(logical_test, value_if_true, value_if_false). This allows you to enter a text value into the cell “if” something else in your spreadsheet is true or false. For example, =IF(D2=”Gryffindor”, “10”, “0”) would award 10 points to cell D2 if that cell contained the word “Gryffindor.”
There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.
We’ll go back to Sprung’s example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10’s next to each Gryffindor student’s name, we’ll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.
- The formula: IF(logical_test, value_if_true, value_if_false)
- Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor.” Make sure your Logical_Test value is in quotation marks.
- Value_if_True: If the value is true — that is, if the student lives in Gryffindor — this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.
- Value_if_False: If the value is false — and the student does not live in Gryffindor — we want the cell to show “0,” for 0 points.
- Formula in below example: =IF(D2=”Gryffindor”,”10″,”0″)
There isn’t an Excel “formula” for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you’re not stuck calculating and reentering the numbers yourself.
The basic setting to convert a cell’s value into a percentage is under Excel’s Home tab. Select this tab, highlight the cell(s) you’d like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say “General” at first). Then, select “Percentage” from the list of options that appears. This will convert the value of each cell you’ve highlighted into a percentage. See this feature below.
Keep in mind if you’re using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells’ format to “Percentage” from the Home tab — as shown above.
4. Subtraction: To perform the subtraction formula in Excel, enter the cells you’re subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you’re subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4.
Like percentages, subtracting doesn’t have its own formula in Excel either, but that doesn’t mean it can’t be done. You can subtract any values (or those values inside cells) in two different ways.
- Using the =SUM formula. To subtract multiple values from one another, enter the cells you’d like to subtract in the format =SUM(A1, -B1), with a negative sign (denoted with a hyphen) before the cell whose value you’re subtracting. Press enter to return the difference between both cells included in the parentheses. See how this looks in the screenshot above.
- Using the format, =A1-B1. To subtract multiple values from one another, simply type an equals sign followed by your first value or cell, a hyphen, and the value or cell you’re subtracting. Press Enter to return the difference between both values.
5. Multiplication: To perform the multiplication formula in Excel, enter the cells you’re multiplying in the format, =A1*B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1*B1 would return a value of 60.
You might think multiplying values in Excel has its own formula or uses the “x” character to denote multiplication between multiple values. Actually, it’s as easy as an asterisk — *.
To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1*B1*C1 … etc. The asterisk will effectively multiply each value included in the formula.
Press Enter to return your desired product. See how this looks in the screenshot above.
6. Division: To perform the division formula in Excel, enter the cells you’re dividing in the format, =A1/B1. This formula uses a forward slash, “/,” to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5.
Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, “=,” and follow it up with the two (or more) values you’d like to divide with a forward slash, “/,” in between. The result should be in the following format: =B2/A2, as shown in the screenshot below.
Press Enter and your desired quotient should appear in the cell you initially highlighted.