How to Create a Formula in Excel

How to Create a Simple Formula in Excel

To create a formula in excel must start with the equal sign “=”. If there is no equals sign, then whatever is typed in the cell will not be regarded as a formula.

Here’s how to create a simple formula, which is a formula for addition, subtraction, multiplication, and division. An addition formula using the plus sign “+”, subtraction formula using the negative sign “-“, a multiplication formula using an asterisk sign “*” and division formula using the slash “/”.

Addition Formula

There are two numbers in cell B1 and B2. How to create a formula in excel to add both of them?

There are several ways of writing a formula. The first way is using the keyboard and the arrow keys, the second way using the keyboard and mouse and a third way to use the keyboard by typing directly the formula and the address of cell involved.

For the above addition, the formula will be used the first way.

1. Place the cursor in cell B4 and then type the equals sign “=”

2. Press the up arrow button, point to cell B1, the cursor turns into dashed line.

3. Type the plus sign “+” for the addition operation

4. Press the up arrow button again, point to cell B2

5. Press the ENTER key

The result is as shown below

Subtraction Formula

How to write a formula in excel for subtracting number1 by number2?. The reduction formula to use the second way, i.e. using the keyboard and mouse.

1. Place the cursor in cell B5 and then type the equals sign “=”
2. Click cell B1 using the mouse
3. Type the negative sign “-” for the subtraction operation
4. Click cell B2 using the mouse
5. Press the ENTER key

For more details, you can see the animation below.

Multiplication Formula

How to make a formula in excel to multiply number1 by number2. For the multiplication formula using the third way of using the keyboard by writing directly the formula and the address of the cell involved.

1. Place the cursor in cell B6 and then type the equals sign “=”
2. Type B1
3. Type an asterisk “*” for multiplication operations
4. Type B2
5. Press the ENTER key

For more details, you can see the animation below.

Division Formula

How to do formulas in excel to divide number1 with number2.

1. Place the cursor in cell B7 and then type the equals sign “=”
2. Point the cursor to cell B1
3. Type a slash mark “/” for the division operation
4. Point the cursor to cell B2
5. Press the ENTER key

The result is as shown below

How to View Formulas in Excel

The easiest way to see the formula in a cell is to look at the formula bar. Point the cursor to a cell that contains a formula, then the formula bar will display the formula in the cell. The location of the formula bar is below the ribbon menu.

The picture above shows the formula contained in cell B4. To see formulas in other cells just move the cursor to the desired cell and see the formula in the formula bar.

Formula bar can only display formulas in the active cell, meaning only one formula can be shown. To be able to display all the formulas in a worksheet, please read the article below “How to Display Cell Formulas in Excel”

How to Edit Formula in Excel

There are two ways to edit a formula, using the F2 key or using a formula bar.

Editing with the F2 key

Place the cursor in the cell containing the formula, then press the F2 key. The contents of the formula will appear, and the cells involved in the formula will be marked with a colored box.

The picture below shows the existing addition formula in cell B4. There are two cells involved in the formula: cell B1 and B2. The address of the cell B1 light blue colored then the cell B1 will be surrounded with the same colored line, likewise with cell B2, the color of lines around it is same as cell B2 address color.

For example, the formula will be edited by adding the number 2. Type the plus sign “+”, number 2 and then press the ENTER key.

The results are as shown below. The formula in cell B4 has changed.

Editing with the formula bar

Place the cursor in the cell containing the formula, then click on the formula bar section. The formula in the cell will appear automatically. The display will be the same as when pressing the F2 key.

The picture below shows the existing subtraction formula in cell B5. For example, the formula will be edited by subtracting the number 2. Type a negative sign “-” number 2, then press the ENTER key.

How to Copy Formula in Excel

The cell that contains the formula can be copied like any other data. The difference, which is copied is the formula, not the value of the cell and the cell address forming the formula will be changed according to the location.

See image below. Cell B4 contains a formula that adds the value of cell B1 and B2. The formula will be copied and placed in the range C4: F4.

Place the cursor in cell B4. Do a copy (CTRL + C). Select C4: F4 range, then paste it (CTRL + V).

The result is as shown below.

The value of cell C4 to F4 is not equal to the value of cell B4, because excel copied the formula, not the cell value. Cell C4 contains a formula that adds C1 and C2 values, as well as cell D4 until F4; all contain a formula that adds cell values in row 1 and row 2 in the same column.

Another way to copy formula in excel

In addition to using the keyboard, there is another way to copy the formula, which is using the mouse. Eg formula in cell B5 will be copied. Click cell B5, point mouse to bottom right of cell B5 until the cursor change shape become thinner. Click and hold, then drag the cursor until cell F5. The result is as shown below.

Is there any other way to copy the formula with the mouse, of course 😊. For example, the formula in range B6: B7 will be copied. Select range B6: B7, then right click select copy. Select range C6: F7, right click, select Paste. The result as shown below.

How to Paste Special in Excel

If the cell that contains the formula is copied, the formulas are copied, not its value. The question is what if you want to copy the value, not the formulas. The solution is using Paste Special.

For example, there are data like the image above. Range A4:F7 mostly contains the formula, what if the range is copied and placed on the range A9:F11?.

Select range A4:F7. Do a copy (CTRL+C). Place the cursor in cell A9, and then do a paste (CTRL + V). The results are as shown below.

The results are different. If checked, cell B9 contains formula =B6+B7, as well as other cells in range B9:F12, all containing formulas.

For copying its value only. Select range A4:F7. Do a copy (CTRL+C). Place the cursor in cell A9, and then do a paste special (CTRL+ALT+V). A dialog box appears as shown below. Select “Values”, then click OK.

The results are shown below.

The results are equal to the range A4: F7. If rechecked, the content of cell B9 and other cells in the range B9:F12 is not a formula but a number.

How to Display Cell Formulas in Excel

To see the existing formula in a cell already discussed earlier. There is one drawback, only can see the formula in one cell only. If you want to see all the formulas that exist in the worksheet, the only way is to use the “Show Formulas” command.

The location is on Ribbon Menu, Formulas tab, auditing group formula. Click once, then all data generated from formula will show its formula. Click once again; it will return to its original state.

If you want to use the Show Formulas command faster, use the shortcut CTRL + ` (grave accent, in the left position of keyboard 1 and above the left tab)