One of the most powerful features in Excel is the ability to do numerical calculations using formulas. Excel acts just like a calculator, allowing you to easily add, subtract, multiply, and divide numbers. In this post, we’ll show you how to create simple Excel formulas in Excel using cell references.
Mathematical Operators
The standard mathematical operators in Excel are the Plus sign for addition (+), Minus sign for subtraction (–), Asterisk for multiplication (*), Forward slash for division (/), and a Caret (^) for exponents. All the formulas in Excel always begin with the equals sign (=).
Using Cell References
Cell references refer to the location of a cell in the worksheet. It consists of the column letter followed by the row number that intersects its location such as A1, F2 or C3. Cell references are used in a number of Excel activities such as creating formulas, in functions, in charting and so on.
The advantage of using cell references in formulas is that if the data located in the referenced cells changes, the formula is automatically updated to reflect the change, thereby avoiding the cumbersome task of rewriting the formula every time something changes. This also means that your formulas are always accurate.
You usually combine a mathematical operator with cell references to create different types of simple formulas in Excel. Excel formuals can include a combination of cell references and numbers as well. Here are a few examples.
= N1+P2 : This formula uses cell references to add the values in cells N1 and P2.
=A1-345 : This Excel formula involves a combination of a cell reference and a number. Excel subtracts 345 from the value contained in cell A1.
=B2/B1 : This Excel formula divides cell B2 by the value contained in cell B1.
=C22*3.65 : This Excel formula multiplies the value in cell C22 by 3.65.
How to Create a Simple Formula in Excel
Let us use some values to calculate the total sales value in the first quarter. In the image displayed below, we have the sales data for the months Jan to Mar 2016. Let’s find the total sales value earned in the first quarter.
1. Select the cell that will contain the formula. In our example, we’ll select cell B5.
2. Type the equals sign (=) in cell B5. It will appear in both the cell and the formula bar.
3. Type the cell reference in the formula: cell B2 in our example. A blue border will appear around the referenced cell.
4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
5. Next, type the cell reference you want to add next, in our example, it it cell B3. A red border will appear around the referenced cell.
6. Type the addition sign (+) again.
7. Next, type the cell reference you want to add next, in our example, it it cell B4. A purple border will appear around the referenced cell.
8. Press Enter on your keyboard. The formula will be calculated, and the net value will be displayed in cell B5.
If the result of a formula is too long, it will display as pound signs (#######) instead of a value. This means that the column is not wide enough to display the contents of the cell. Increase the column width to see the full value.
Modifying Cell References
As I mentioned earlier, cell references allow you to update data in your worksheet without having to rewrite formulas. In the previous example, if we modify the value of cell B2 from $3657 to $3857. The formula in B5 will automatically recalculate and display the new value.
Creating Excel Formulas Using Point-and-Click Method
You don’t have to manually enter cell references in Excel to create the formulas. A simple point and click action on the cells will suffice. This method will save a lot of time and effort when creating formulas. In our previous example, instead of typing the cell references such as B2, B3, and B4, simply select the respective cells to include them in the formula.
So what you will essentially be doing is:
1. Type =
2. Click cell B2.
3. Type the addition sign (+)
4. Click cell B3.
5. Type the addition sign (+)
6. Click cell B4.
7. Press Enter.
Copying Formulas
Formulas can also be copied to other cells using the fill handle. This can save a lot of time and effort if you need to perform the same calculation multiple times. When you copy a formula, Excel automatically adjusts the cell references where the formulas are copied to. Let’s take the previous example and add some expenses for the 3 quarters. Let’s now say you want to find the total expenses incurred in the first quarter. You don’t have to create a new formula for it. Instead simply drag the fill handle to the new cell to get the total expenses.
1. Click cell B5.
2. Place the mouse pointer at the bottom corner of the cell until it turns into a thin plus sign.
3. Click and drag the corner to the next cell, cell C5.
The formula is now pasted into the new cell with the cell references automatically adjusted to reflect the values in c2, c3, and C4.
Editing Excel Formulas
Sometimes you may want to modify an existing formula. In the previous example if you have entered =B2+B3-B4 instead of =B2+B3+B4, you can correct the mistake in the formula quite easily.
1. Select the cell containing the formula you want to edit. In our example, we’ll select cell B5.
2. Click the formula bar to edit the formula. You can also double-click the cell to edit the formula directly within the cell. A border will appear around the referenced cells.
3. In our example, we’ll change the last part of the formula to display + instead of –
4. Press Enter to complete the correction.
The formula will be updated, and the new value will be displayed in the cell.
View all Excel Formulas in a Spreadsheet
If you want to see all of the Excel formulas in a spreadsheet, you can hold the Ctrl key and press the ` (grave accent key). The grave accent key is usually located in the top-left corner of the keyboard. Press Ctrl+` again to switch back to the normal view.
If you found this tutorial helpful, be sure to check out more good stuff in our Learn Excel series. If you want to learn Excel from scratch, consider taking up our Udemy course, Excel 2016 for Beginners. It is available at a nominal price of just $10.
Leave a Reply