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.
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.
After pressing ENTER, you will get the addition in that cell.
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.
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.
After pressing enter you will get your addition.
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.
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.
After pressing ENTER you will get the product in cell B12.
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.
Type the name you want to give.
Remember, the name can’t have any space. So you have to give an underscore or hyphen instead of space.
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.
After pressing ENTER you will get your value for the addition.
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
In the Insert Function box select the function you need to perform and select OK
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.
After that select the cell you want in your calculation.
Press OK in the Function Arguments box.
You will get the calculation in cell B14
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.
After pressing OK, you will get the summation
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.
After pressing ENTER you will get your value.
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.
After pressing ENTER you will get your value.
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.