3+1 Best Ways to Make a Formula & Function in Excel

How do you make a formula and function in Excel? Did you type a math equation into a cell? Like 25+30-15=, or A2-16=, or the SUM(A2+A3+A4+A5) function to calculate the total? It isn’t like that. That’s the wrong way.

Take it easy… In this guide, you will learn how to create formulas and functions in Excel. I will give you an example of a simple formula, with cell references, formulas in defined names & table structure, to priority operators in formulas.

Note: Before continuing, you should know: In Excel, Formulas aren’t the same as Functions. Let’s find out the difference.

Formula vs Function in Excel

In Excel, formulas are mathematical equations used to calculate values in rows, columns, cells, and ranges in a Worksheet.

By default, Excel doesn’t provide special formulas. However, you can use Special Operators to complete simple formula calculations, as shown in the following table:

Name	Operator	Calculate
Plus sign	+	Addition
Minus sign	–	Subtraction
Asterisk symbol	*	Multiplication
Slash mark	/	Division
Caret symbol	^	Square, Cubic, etc.

Whereas functions in Excel are built-in formulas that are used to quickly complete complex calculations.

For example, to calculate the average, you can add up all the values, and then divide by the number of data (n). Of course, it’s more difficult if you have a lot of data. Therefore, Microsoft Excel simplifies the average formula using the AVERAGE Function.

Likewise, with other functions, such as calculating the standard deviation, Excel provides the STDEV.P and STDEV.S functions.

That’s the difference between formula and function in Microsoft Excel. Now, let’s learn how to use it.

Make a Simple Formula in Excel

To calculate a simple 4+5 formula in Excel, please follow these steps:

1. Select a cell as the formula output.
2. Type an equal sign =, to start the formula.
3. Enter the first data. In this example: 4.
4. Type an operator, such as a plus sign +, for addition.
5. Enter the second data, in this example: 5.
6. Press the Enter key and see the result:

What do you see? 9 isn’t it? Yes, that’s the result of the calculation of the formula =4+5 in Excel.

Note: I used the formulas =5-2, =10/2, and =2*5, in cells A3, A4, and A5 as examples of subtraction, division, and multiplication formulas.

But the point is, when using formulas/functions in Excel, you’ve to start with an equal sign “=”. If you don’t use the equals sign at the beginning, Excel won’t calculate your formula and display it as text. Like this:

I know this example is too simple for you. So, let’s take a look at some more complex examples of formulas/functions in Excel. Like the example in column F above.

#1 Use Formula Bar with Cell Reference

In the previous example, I used constants in the formula, such as =4+5. Constants are numbers, dates, or text that are entered directly into a formula.

However, when you want to sum values that are in multiple cells, then you must use cell references.

Pro Tips: Cell references are cell addresses, for example, A2, B5, F20, etc. If you don’t know what it is, please visit SUBCHAPTER: Range in Excel.

Now, let’s use the previous example.

I have Data1 and Data2 on columns D and E. Then, in cell F2 please use the formula to calculate the value of cell D2 plus E2 using the following method:

1. Select Cell F2, then type the equal sign =.
2. Click cell D2 as the first value. Excel will automatically enter the address of cell D2 into your formula.
3. Type the plus sign + (to add).
4. Click cell E2 as the second value.
5. Press Enter to get a result like the following image:

Note: You can also type the cell address directly into the formula as alternative steps 2 and 4 above.

Finally, the formula used is =D2+E2. This formula will instruct Excel to calculate the value in cell D2 plus the value in cell E2.

You can also combine constants with cell references. For example, with the same example, I use the formula =D2+E2–10, then Excel will return 35 results, like this:

Now, auto-calculate…

Instead of entering formulas one by one into cells, you can also calculate formulas in the next few cells automatically.

Yes, you can use the AutoFill feature or Copy-Paste the formula to the next cell. This will copy the existing formula and apply it.

Pro Tips: There are many things to go to, let’s learn more in SUBCHAPTER: AutoFill and 15 Types of Copy-Paste in Excel.

#2 Formula with Defined Name in Excel

If you’re already familiar with using the Defined Name feature in Excel, then you can also create more unique formulas.

For example, I have sales data. I have calculated the total sales using the SUM Function. It’s in cell B8. Then, I want to calculate the percentage of sales to the total for each employee. Take a look at that data view:

To save time in using the formula, I created a Defined Name for cell B8, with the name: total_sales.

That’s it… You know, calculating the percentage of sales can use the formula =B2/B8. However, it can be tedious to repeat the formula for the next cell (C3, C4, etc.).

With a Defined Name, you can also use it (name) in formulas. So you can use this formula =B2/total_sales. Then, do AutoFill or copy-paste the formula to calculate other cells. This is the result:

Isn’t that right?

So when you find a cell that contains a formula like this (whether it’s from your coworkers, or your boss’s), You know, it uses the name range of the Defined Name.

+1 to your skills.

Next, see how Excel professionals create formulas from a table.

#3 Formula with Table Structure in Excel

When using a table format in Excel, you can give the table an identity (Say: name). When a table has a name, the columns in the table form a structure.

For example, I created a table with the name “Sales”. This table consists of four columns (Name, Sales, Cost, and Profit). This is how it looks:

You know, you can calculate the profit by subtracting sales and costs. If you’re using a simple formula, you can use the formula =B2-C2 in cell D2. This formula can give correct results.

However, instead of using reference cells, it is much better to use a table structure.

Remember again, I will subtract the Sales column by Cost. Then you can use this formula =[@Sales]-[@Cost]. This formula will calculate all rows of data in the Profit column. Result:

Note: You can type [@Sales] and [@Cost] directly into the formula. Or, you can also click cell B2 or C2, and it will insert the table structure into the formula automatically.

Again… +1 for your skills.

Make a Function in Excel

Just like typing formulas into cells, you can also type functions through the formula bar. One thing that separates formulas and functions in Excel is that “Functions can make complex calculations easier.”

For example, I have sales data in cells C₂ through C₇. If using a simple formula, you must type all cell addresses into the formula, i.e. =C2+C3+C4+C5+C6+C7.

But, with a function, you only need to enter the short function like this =SUM(C2:C7). These two formulas and functions will give the same result like this:

Voila! What do you think?

Note: To enter the range C2:C7 in the SUM function, please select cells C2 to C7 (you can use a mouse/touchpad). You can also type =SUM and open/close the parentheses “()” manually using the keyboard.

Using functions can save you time. What’s more, if you want to sum the data contained in very many rows. What if you have 1,000 rows of values? How length your formula be?

BTW, You also have to prefix the function with an equal sign =, just like using a formula. Also, each function in Excel has its arguments. So you have to adjust it too.

Important: The focus of this guide isn’t on learning the arguments for each function, but on how to enter a function into cells. You can learn a guide to each function in the Next SUBCHAPTER.

More Insight: Use Insert Function Feature

But anyway, basically, you can also create functions in Excel through the Insert Function feature. For beginners, I recommend using this feature instead of the formula bar.

You can find the Insert Function feature on the tab Formulas > Insert Function or the fx icon on the left side of the formula bar. Let’s see how to use it:

1. Firstly, Click cell C12 as the function output. Then click the Insert Function icon on the left of the Formula Bar (see image).
   
2. In the Insert Function window, select Category Function. For example, I use the SUMIF function in the Math & Trig category. Please select Math & Trigonometry.
   
3. Select SUMIF, then click OK to continue.
   
4. In the Function Arguments Window, type the required arguments in the function.
   
   Note: Each function has different arguments. The SUMIF function takes two arguments: Range and Criteria.
5. In the Argument Range, select a data range you want to calculate. Please click the Range field, then select your data.
   
6. Type the criteria for the SUMIF function (yes, that’s what the SUMIF function is for). For example, I want to calculate total sales that are over $150.00. Then, type “>150” (with double quotes).
   
7. Finally, click OK to continue. Result:

Excel returns the result of $522.00. That’s the sum of cells C4, C5, and C6 = $155.00 + $193.00 + $174.00.

If you’re using a simple addition formula, you can use this: =C4+C5+C6. But the problem is how long will it take you to find a value greater than $150.00 from the data range?

Isn’t that true?

Now, see how to edit formulas/functions in Excel.

Edit/Delete Formula in Excel

You can edit formulas/functions in Excel using the Formula Bar. Just click the cell that contains the formula, then click the part to change in the formula bar, like this:

Meanwhile, to delete a formula/function, please click the cell, then press the del/backspace key on your computer.

Priority Operator in Excel

It’s like calculating math equations. Multiplication, Division, Power of numbers (such as square and cubic), Root, calculated before Addition and Subtraction.

Also, all calculations in the open and close brackets will be performed first regardless of what calculation operation is used.

For example, for the formula =2+5*5, Excel will prioritize the calculation of 5*5, so it becomes =2+25 and returns 27 as shown below:

However, if 2+5 is inside the opening and closing brackets like this: =(2+5)*5, then Excel will prioritize the calculations inside the brackets (2+5), so it becomes =7*5 and returns 35 as the result in cell A3.

Note: This priority operator also applies to Excel functions.

So, what do you think about this guide to creating formulas and functions in Excel? I hope this guide can help you.

However, if you’re having trouble or have other ways to complete the guide to make a formula and function in Excel, please leave a reply in the comments form.