Essential Excel Formulas and Functions
- 2.5 hours on-demand video
- 1 downloadable resource
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- Understand the key elements which are used to create Excel formulas and feel confident about building formulas of their own.
- Excel 2016, 2013, 2010 or 2007 running under Windows. Mac users will also benefit from this course, but there are a few differences between versions.
Formulas and functions are at the core Microsoft Excel and learning to use them effectively is an essential step in becoming proficient in Excel. This course provides clear and easy-to-follow instructions on how to build formulas for analysis, projection and data cleansing.
- The course starts by discussing all of the different components that can be used to create a formula. You will be given clear demonstrations of the use of relative, mixed and absolute references, the use of dollar signs and how to name cell ranges to add clarity to your formulas.
- Naturally, Excel functions are discussed in depth and many examples of using functions to analyze your data, beginning with every day functions like SUM, AVERAGE, MIN and MAX.
- We then examine the use of text functions to clean up data prior to creating reports; functions like TRIM, UPPER, LOWER, PROPER, LEFT, RIGHT, MID and CONCATENATE.
- Next, we turn our attention to conditional functions like IF, IFERROR, AND and OR. These functions allow you to make the value displayed in a cell dependent on the results of logical tests.
- We then see how conditional functions can be combined with mathematical functions; using COUNTIF and SUMIF to produce calculations which only include those cells which match certain criteria.
- And, naturally, no discussion of Excel functions would be complete without lookup functions. We discuss the use of VLOOKUP and, also, how the INDEX and MATCH functions can be used to lookup up data whose format makes the use of VLOOKUP impossible.
When you have completed this course, you will feel more confident about creating your own Excel formulas and taking advantage of Excel's wide variety of powerful functions.
This course was last updated on 25th March 2017, with the addition of a new project: the creation of an invoicing model.
- Excel users both inexperienced and experienced who don't feel confident about using Excel formulas.
Formulas are one of the key components of an Excel worksheet; this is, after all what makes Excel so useful; the fact that it can perform calculations on the data that you enter in your worksheets. In this video, we examine the key elements that can be included in a formula by entering the formulas required to create an invoice template.
In the last video, we saw how you can create a formula once and then copy it into other cells; and have Excel automatically modify the formula, based on the new location. In this video, we'll get some more practice on doing that and discuss, in a bit more depth, exactly how Excel arrives at the correct conclusions.
In this video, we'll look at an occasion where it is not useful to have Excel modify cell references when a formula is copied; and how you can tell Excel which cell references should not be changed when you copy a formula.
Excel has three functions for changing the case of your text, two of which you should be able to guess with no problem: LOWER and UPPER, which of course correspond to upper and lower case. The third one is PROPER; this is what is sometimes called title case, whereby the first letter of each word is capitalized.
Excel's CONCATENATE function allows you assemble different textual components into one cell by stringing them together. In this example, we are starting with three separate columns containing "Title", "First Name" and "Last Name"; and we are looking to assemble them into a single cell; and this is exactly what CONCATENATE does.
In the first video of this section, we had a look at a basic IF statement; sometimes, however, you want to cater for more than two eventualities; and, in this scenario, one approach is to use what is called a nested IF statement. This is where you use more than one IF statement; and Excel requires you to put one IF statement inside another, as its argument.
In this video, we examine the key features of the completed template.