How to Create a Formula in Excel (5 Ways)

Excel can be used to perform any type of calculation and creating a formula is the basis of the calculation. In this article, you will learn how to create a formula in Excel in 5 different ways.

Consider the dataset. Here different financial information about a factory is given. To find out the missing information we need to perform Addition, subtraction, Multiplication, and Division.  We will use Excel formulas to perform this calculation.

create formula

Download Practice Workbook

5 Ways to Create a Formula in Excel

1. Using Constants and Operators

Constants are those values that are used in a calculation. Operators determine the calculation you are performing such as the plus sign for addition (+), minus sign for subtraction (), asterisk for multiplication (*), and forward slash for division (/).

For our dataset, to find out Total sales we need to add sales of product A and product B.

To perform the calculation, First select the cell, where you need to do the calculation (B6) and insert an equal sign (=). Then, type the value of Sales of product A, put plus sign (+), and type the value of Sales of product B.

addition

After pressing ENTER, you will get the addition in that cell.

addition 2

One disadvantage of this method is you have to change the constants in the formula if there is a change in Sales of product A or Sales of product B. We will overcome this disadvantage by using cell references.

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)

2. Creating a Formula Referring values in Other Cells

Instead of typing the constant, we can refer to other cells. The benefit of referring cells is that if you make any change in the referred cell, the value used in the calculation will automatically change.

For applying this method, first, select the cell where you are making the calculation, put an equal sign (=), type the reference cell number (B4), put a plus sign (+), and type the reference cell number (B5). The cell you referred to will be highlighted in your worksheet.

addition 3

After pressing enter you will get your addition.

addition 4

Now, suppose there is a change in Sales of product A, if you make the change in cell B4, the Total sales value in cell B6  will automatically change.

create formula in excel

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)

3. Using the Point and Click Method

Instead of typing the cell number in the formula, you can simply select the cell by pointing and clicking.

In our dataset, we need to find Total labor expense in cell B12 by multiplying Hourly wages (B9), Total work hours per labor per month (B10), and Number of labor (B11).

For performing the calculation, first, select cell B12, insert an equal sign(=), select cell B9 by moving the cursor on that cell and clicking. After that put an asterisk sign (*) and select cell B10. Then, put another asterisk sign (*) and select cell B11.

create formula in excel

After pressing ENTER  you will get the product in cell B12.

product

4. Creating a Formula Using Defined Names

If your cell has a defined name you can also use the name to create a formula.

First, let’s learn how to give a name to a cell. Select the cell you want to give a name to. Select the empty box on the left side of the formula bar.

create formula in excel

Type the name you want to give.

defined name

Remember, the name can’t have any space. So you have to give an underscore or hyphen instead of space.

naming

Now to find total expenses you need to add cells B12 and B13 to cell B14. Select cell B14 and put an equal sign (=). Type the name of cell B12 put a plus sign(+) and Type the name of cell B13.

CREATE FORMULA

After pressing ENTER you will get your value for the addition.

CREATE FORMULA

5. Creating a Formula by Using Functions

Functions are predetermined formulas that Excel has in build. There are thousands of formulas excel has and you can calculate almost any type of calculation by using this formula.

i) Using Function Wizard

You can use a formula from Function wizard. For that, first, select the cell where you want to put the formula. Then Go to Formula bar> Insert Function

function wizard

In the Insert Function box select the function you need to perform and select OK

Function wizard

After that, the Function Arguments box will appear. Click on the upward arrow key to select the cell you want to use in your calculation.

Create formula

After that select the cell you want in your calculation.

create formula

Press OK  in the Function Arguments box.

create formula

You will get the calculation in cell B14

create formula

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)

ii) Using Formula Bar

You can also put the function in the formula bar. Select the cell and type an equal sign (=). Then type the function in the formula bar and select the cell range.

create formula

After pressing OK, you will get the summation

create formula

Remember, there is no function for subtraction and division in Excel. You need to use a direct formula to do these calculations.

To make a subtraction, select the cell where you want to subtract, put an equal sign (=),  select the first cell, put a minus sign (-), and select another cell.

substraction

After pressing ENTER you will get your value.

substraction

To make a division, select the cell where you want to calculate, put an equal sign (=),  select the first cell, put a forward slash (/)  and select the divisor.

Division

After pressing ENTER you will get your value.

division

Read More: How to Create a Formula to Calculate Percentage in Excel

Conclusion

You can make all kinds of calculations using Excel. Please leave a comment if you have any confusion. Also, let me know if you want to know about any specific function of Excel.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo