Akib Bin Rashid
Excel is one of the most useful tools we are familiar with. It enables us to perform myriads of calculations applying the functions available in Excel. But we can also create a formula in Excel without using a function. In this article, I am going to illustrate 6 cases where I will create a formula without using a function.
This is a sample dataset that I am going to use to illustrate how to create a formula in Excel without using a function. Here, we have the Name of some employees along with their Salary Per Day and Total Days Worked.
How to Create a Formula in Excel without Using a Function: 6 Approaches
1. Creating Summation Formula in Excel without Using a Function
First of all, I will show you how to sum two numbers without using the SUM function. I have Number-1 and Number-2 and I will calculate the sum in the Summation column.
STEPS:
➤ Select D5 and write down the formula
=54+89
Here, I am going to add 54 and 89 in D5.
➤ Press ENTER. Excel will show you the result.
➤ Similarly, you can add the remaining numbers. The output will be like this.
2. Subtracting in Excel Without Using a Function
In this section, I will subtract two numbers without using any function. This time I will use Cell Reference.
STEPS:
➤ Go to cell D5. Write down the formula
=B5-C5
Here, I am subtracting the number in C5 (54) from the number in B5 (89).
➤ Then press ENTER. Excel will show you the result.
➤ Then use the Fill Handle to AutoFill up to D9.
In D1, we have a negative value (-1) because 36 < 37.
3. How to Create a Formula for Multiplication in Excel Without Using a Function
You can also multiply in Excel without using a function. I am going to show how you can multiply two columns. I will calculate Salary by multiplying Salary Per Day and Total Days Worked.
STEPS:
➤ Select cell E5 and write down the following formula.
=C5*D5
Here, I am multiplying the number in C5 and D5 using the asterisk (*) symbol.
➤ Then press ENTER. Excel will show you the result.
➤ Then use the Fill Handle to AutoFill up to E9.
4. Division in Excel Manually Applying No Function
I will discuss how to perform division in Excel manually in this section. Here, I will use the Salary and Total Days Worked to calculate Salary per Day along the row.
STEPS:
➤ Select cell C6 and write down the following formula.
➤ Then press ENTER. Excel will show you the result.
➤ Then use the Fill Handle to AutoFill up to G7.
5. Use of Paste Special to Perform Mathematical Operations
We can also use the Paste Special feature to perform mathematical operations. I am going to calculate the Salary by multiplying Salary Per Day and Total Days Worked.
STEPS:
➤ Select the range C5:C9. Copy them from the Context Bar. The Context Bar will pop up once you right-click your mouse.
➤ Now Paste them in E5:E9.
➤ Now, Copy D5:D9.
➤Next, select cell E5:E9. Then right click your mouse to bring the Context Bar. Select Paste Special.
➤ Paste Special Window will appear. Select Multiply and then press OK.
You can choose other options too when you need to apply other operations.
➤ Excel will calculate the Salary.
➤ The result is in General Format. We have to convert it to Currency Format.
To do so, go to the Home tab >> select the drop-down box from Number Format >> select Currency.
➤ Excel will convert them into Currency Format.
6. Performing Multiple Operations Without Using a Function
You can also perform multiple mathematical operations without using a function. For instance, I am going to illustrate how to calculate in percentage without any function.
STEPS:
➤ Select cell E5 and write down the formula.
=(C5-D5)/D5
Formula Breakdown
(C5-D5) ⟹ Subtract D5 from C5 to calculate the amount of profit/loss.
Output: $185
(C5-D5)/D5 ⟹ Calculates the profit/ loss with respect to Cost Price.
Output: 0.226993865
➤ Then press ENTER. Excel will calculate the profit or loss.
➤ The number is in General Format. To transform it into %, select the % icon from the Number Format.
➤ Excel will convert the number into percentages.
➤ Then use Fill Handle to AutoFill up to E9.
Note that when the percentage is positive, profit occurs. But when it is negative ( For instance, in E7), loss occurs.
Read More: How to Create a Complex Formula in ExcelÂ
Practice Workbook
Practice makes a man perfect. That’s why I have attached a practice sheet so that you can practice how to create a formula in Excel without using a function.
Download Practice Workbook
Conclusion
In this article, I have explained 6 cases to create a formula in Excel without using a function. I hope you will find these cases helpful. Lastly, If you have any queries or suggestions, please leave them in the comment box below.
Similar Articles
- How to Create a Formula in Excel for Multiple CellsÂ
- How to Create a Custom Formula in ExcelÂ
- How to Use Multiple Excel Formulas in One CellÂ
- How to Apply Formula in Excel for Alternate RowsÂ
- How to Apply Formula to Entire Column Without Dragging in Excel
- How to Insert Formula for Entire Column in ExcelÂ
- How to Apply Same Formula to Multiple Cells in ExcelÂ
- How to Apply a Formula to Multiple Sheets in ExcelÂ
- How to Create a Formula with Structured References in Excel
- How to Create a Conditional Formula in Excel
- How to Create a Formula Using Defined Names in Excel
- How to Make FOR Loop in Excel Using FormulaÂ
- How to Apply Formula to Entire Column Using Excel VBA
- How to Use Point and Click Method in Excel
