Microsoft 365 Excel Intermediate (2023): Calculations and Formulas

Name: Microsoft 365 Excel Intermediate (2023): Calculations and Formulas
Author: Bob Akbar

Advance skills in using built-in functions in Excel 365 for accuracy and efficiency in data analytics

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar, and in these lessons, we are going to talk about calculations and formulas. In this lesson, I'm going to show you the difference between formulas and functions. We have some totals here, and we want to add the industrial total and the domestic total here. Let's insert a column, and there's our column, and we'll put total. If we were going to do this using a formula, not a function, we would do this manually. We would put this one here, plus this one here, plus, so that's a formula. There is nothing that is built into Excel other than the addition sign. Very easy to do, easy to put in there, press Enter, and there it is. Now, if we wanted to use a function that's built into Excel, we can use the SUMIF function. You notice when you open this, it asks for the range, the criteria, and the sum range, which is optional if it is different than your range. That's why the example, you'll see only two things that are put in here. However, we are going to use three. The range that we want to filter is the first one, we'll put a comma, and then it's the criteria. What do we want it to be? We want it to be industrial for the first one, but for the second one, we want it to be domestic. Now, you notice I typed it in. We can do that, or we can simply click on H3, which is domestic. Finally, what is it that we want to sum? There it is. We can format ourselves here into currency. There it is. Get rid of any extra decimal points, and there we have it. The first one, if you notice up here, when we click, it shows all the things it added up. It was a formula, and we simply added all the ones that were industrial, and since our table was small, this was not a big deal. We were easily able to eyeball and take a look and say, this one, this one, this one, this one, and add them together, and it was fine. For the second one, for the domestic, we used a function called SUMIF that is built into Excel 365, and this function took out any guesswork that we had, so we could simply go in there and say, whatever number in the sales column matches the criteria of being domestic, we would like to add, and that's what it did. To make sure that these numbers match now, we can still either use a formula and add these two together, which you will notice it is the same as our total here, or we could use the SUM function, and we can list the numbers one by one, separated by commas, same number, or we could define a range in our function. So you see the difference between using the two. I would suggest using formulas for something that is simple and is not going to be changed. The odds of us adding a different type of production, industrial or domestic, is pretty low. Those are probably set. However, the odds of us going in and adding more sales that might have been missing is very likely as the data is revised. The customer may change their order, they may cancel their order, or we may have a customer place an additional order. The formula falls short of being able to account for that. So if we were to add another industrial sale, it would not add that to our total here. So this total would be updated for the current time. However, in contrast, in the domestic, you will notice that it is a range and it's taking the range into account. And even if one of our sales changed from industrial to domestic. Now, think about that. You notice our domestic updated. However, since our industrial was based on a formula and not a function, it failed to update because it has no idea that we changed one of our sales here. As far as the industrial is concerned, with the formula, the type changing that was the basis of us adding this up is not a part of this formula. There is no word in there that it mentions the type. However, in the domestic, the type is mentioned and that's why when we made the change from industrial to domestic, the update was automatically made. So with that in mind, I would recommend that we go back and we change our industrial to the same summit formula. And this time we'll simply click on industrial, the cell reference, and we'll use our sales without the total. And there it is. Now our total is fixed again. This one down here is where I would say it is okay to use the formula if you don't want to use the sum, the odds of it changing are less likely. However, if you want to be on the safe side, in case there's another type added to our sales, there might be industrial, domestic, and third party or wholesale, anything else that can be added, it is a good idea to keep that sum range in there. Formulas are very useful if you are doing something that is just a customized thing that is probably not going to change. For example, say we want John and Heather work as a team. Okay, so John and Heather. And if I simply want to get their team, so I would get this and for their total, I could just simply use a formula and that's it. That's all we need because it's a very unique custom calculations and the odds of it changing are not likely. If they add somebody to their team, we can go back and add a third person, not a big deal. Stay tuned for the next lesson where we'll talk about common functions. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 25m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

While users can define their own formulas for basic actions, Excel 365 provides numerous functions that automate common processes. By using automated functions, users can ensure more accurate results, especially when using qualifier functions such as SUMIF.

There’s more potential for mistakes, misses, and duplicates in user-defined formulas. Built-in functions are not only comprehensive when it comes to common functions such as averages and sums, but they come in a vast library organized by categories. Intermediate Excel users begin to master the functions that bring efficiency to their data interpretation on the way to advanced custom formulas.

Many users find that filter-qualified functions such as SUMIF and COUNTIF will take out the guess work and need for manual update of formulas. The user can confidently have data update with the results reflecting these updates without having to worry about redoing the manual calculations as Excel 365 provides excellent tools and automatic updates to fields that qualify to be included in these calculations.

What You'll Learn

Distinguish Excel formulas from built-in functions
Apply common Excel functions such as averages and sums
Use filter-qualified functions like SUMIF and COUNTIF
Describe Excel filtering functions for data interpretation
Use Excel find functions to locate data
Build common formulas for automated calculations

Key Takeaways

Excel 365 provides numerous built-in functions that automate common processes for more accurate results.
Qualifier functions such as SUMIF help reduce the mistakes, misses, and duplicates possible with user-defined formulas.
Built-in functions come in a vast library organized by categories.
Filter-qualified functions like SUMIF and COUNTIF remove guesswork and the need to manually update formulas.
Excel 365 automatically updates qualifying fields so results reflect data changes without redoing manual calculations.

Frequently Asked Questions

Who is this course for?

It is aimed at intermediate Excel users who are beginning to master the functions that bring efficiency to their data interpretation on the way to advanced custom formulas.

What does this course cover?

It covers the difference between Excel formulas and functions, common Excel functions, Excel filtering functions such as SUMIF and COUNTIF, and Excel find functions, across lessons on Formulas Versus Functions, Common Functions, and Common Formulas.

What skills will I gain?

The course covers skills including Microsoft Excel, Spreadsheets, Excel Formulas, Excel Macros, Excel Solver, and Microsoft 365.

Why use built-in functions instead of user-defined formulas?

Built-in functions automate common processes and ensure more accurate results, while user-defined formulas have more potential for mistakes, misses, and duplicates.