September 27, 2023
Excel Formulas & Functions Tutorials
Formulas VS Functions
You most probably have heard the words Formulas & Functions both being used in Excel. What is the difference between them?
Excel Formulas are expressions which calculate the value of a cell. An Excel Function is a predefined formula that is made available for you to use in Excel:
We use both terms (function and formula) interchangeably.
In our example above, the Excel Formula adds 100 to cell A1 then multiplies it by 1.1. As for our Excel Function, it uses the VLOOKUP Function to lookup for a specific value in your Excel worksheet.
Entering Formulas in Excel
Entering Formulas in Excel is done with these simple steps:
– Select a cell in your worksheet
– You type in the equal sign, as you are telling Excel that you want to input an Excel Formula
– You can type inside cell A1, =A2 + 2
– This means you are telling Excel, get the value of A2, add 2 to it and display it in cell A1
– If you change the value of A2, the value of A1 changes too! As A1’s value is linked to A2 because of your Excel formula.
You would be mesmerized after seeing the automatic updating of the values! This is one of the coolest and most powerful features of using Formulas for Excel!
Editing Formulas in Excel
OK, now you have typed in your Excel Formulas, the question now is how do you update this if you want to make changes?
That’s easy! When you select that specific cell, Excel will show you the actual value or the formula in the formula bar at the top. Pretty much Excel will show you the actual contents that led to the value of the cell you have selected.
Now that you have the cell selected. Let us see how we can make some quick changes.
– To update your formula, click in the formula bar then make your changes to the formula.
– For example, our original formula was =A2 + 2. You can change this inside the Formula bar to =B2 + 3
– After making the changes, press Enter.
Order of Operator Precedence in Excel Formulas
Once you gain more proficiency is using formulas for Excel, your formulas start to get longer and more complex.
One important concept you need to master is the order on how calculations occur in Excel. Excel uses a default order for this. The basic rules are:
– If there is a part of your Excel formula in parenthesis, then evaluate that part first
– After that, multiplication and division operations take place, from left to right
– Then after that, addition and subtraction operations take place, from left to right as well
Let us have this example and assume the value of cell A2 is 4:
=3 * (A2 + 2 * 5)
– Since we have a part of the formula in parenthesis, we calculate that first
– We evaluate (A2 + 2 * 5) first
– Inside that group, we evaluate 2 * 5. As multiplication takes precedence over addition.
– So our formula will now look like =3 * (A2 + 10)
– Now we will evaluate (A2 + 10)
– So our formula will now look like = 3 * 14
– The rest should now be easy, multiplying these will result to 42!
Easy right? Let’s try another one and assume the value of cell A2 is still 4:
= 3 * A2 + 2 * 5
– Now this one looks exactly the same as our previous example. But without the parenthesis.
– Since we do not have any groups enclosed in parenthesis, we will evaluate the first multiplication first.
– So now we have: = 12 + 2 * 5
– Now we will evaluate the second multiplication operation.
– So now we have: = 12 + 10
– And our final result is 22!
The Insert Function Wizard
The Insert Function wizard is a handy tool that you can use for inputting formulas for Excel. As there are a lot of Excel functions, it could be hard to remember what each one does and how to use them.
What if you forget how to use the AVERAGE function? No problem as the Insert Function Wizard is here to the rescue!
– Let us assume you forgot how to average values in Excel. So the first step is to click the Insert function right next to the Formula Bar:
– Once you have the new window, type in a short description so that this wizard will suggest the best Excel formula for you. (e.g. type in the word average)
– Now select the function that applies to you, like in our case we will select the AVERAGE function.
– Fill in the arguments one by one depending on the function you have chosen. The cool thing is Excel provides explanations for each argument that you need to fill up with.
– So there is no need to memorize how you would use the function as Excel provides all the necessary details for you!
– Click OK and your Excel formula is now ready!
To learn more about specific Formulas more in depth, we have a lot of tutorials waiting for you below!
Top 10 Tutorials
- 101 Excel Formulas & Functions Examples– You will find 101 Excel formula & functions examples here for key formulas & functions like VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more.Read more
- VLOOKUP Example: Vlookup with a Drop Down List– The VLOOKUP function in Excel can become interactive and more powerful when applying a Data Validation (drop down menu/list) as the Lookup_Value. So as you change your selection from the drop down list, the VLOOKUP value also changes.Read more
- VLOOKUP Multiple Columns – The VLOOKUP function can be combined with other functions such as the Sum, Max or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula. A very powerful feature for any serious analyst!Read more
- BETWEEN Formula in Excel – There is no explicit Between formula in Excel, however we can come up with creative ways to create this functionality.Read more
- Jump To A Cell Reference Within An Excel Formula– There is a cool tip where you can jump to the referenced cell or range within the formula and make your changes.Read more
- Advanced SUMPRODUCT Function: Sum Multiple Criteria – The SUMPRODUCT function is my favorite Excel function by a stretch! You can create some powerful calculations with the SUMPRODUCT function by creating a criteria for a selected array. For example, you can see how much sales your sales rep did in a particular region and for a particular quarter without having to create a Pivot Table.Read more
- Concatenate With A Line Break– Excel’s CONCATENATE functions joins two or more text strings into one string. The item can be a text value, number, or cell reference.Read more
- Named Ranges with Vlookup Formula – A Named Range makes it easier to understand Excel formulas, especially if the said formula contains an array argument. We can combine this with the VLOOKUP function.Read more
- Match Two Lists With The MATCH Function– I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2.Read more
- INDIRECT Function for Dependent Dropdown Lists in Excel– The INDIRECT function is really cool as it opens up a lot of interesting combinations in Excel. We will use the power of the INDIRECT function right now on creating Dependent Dropdown Lists.Read more
September 22, 2023
September 18, 2023
September 6, 2023