Microsoft Excel: Formulas for Beginners

1. Intro: Hi everyone. I'm Jeremy, or as some call me Big dog. Welcome to my Skillshare course on Microsoft Excel formulas for beginners. Before we jump into the content, let me take a second to introduce myself. I've been in the working world for almost seven years and all that time has been spent in Excel for my time in college to my current role today, Excel has been a part of my daily toolkit. I've developed a passion for analytics and interests in figuring out the most efficient solutions and a love for automation. My goal for this video series is to make you an expert on the basic formulas that you need to succeed in Excel. This course was designed to be short so that you can learn formulas quickly without any additional fluff. At the end of this course, you will have a solid basis in Excel fundamentals, and you'll be well on your way to mastering it. Without further ado, let's jump into the course and get learning. 2. SUM() Function: Welcome to the video on the SUM function. In this video, we'll be going through some high-level bullet points on what the sum function is. And then we will transition into some real-world examples to get you rolling. Let's get into it. Let's start with a basic definition. The sum function adds values. The values can be individual values, cell references or ranges. Individual values would be numbers like 1.25 or 2.50. Cell references would be cells that contain numbers like C5 or D5. And ranges would be groups of cells that contain numbers like C8 through E8. Let's take a look at an example to solidify our understanding. Just like in the summary slide, we have three different examples for each of the different values that can be entered into the sum function. For the first value type individual values, we start typing the sum formula and enter two numbers, 1.25 for number 1.502 for number two, Excel adds the numbers together and boom, we're done. In the next value type cell references. We enter the values from the previous section into individual cells. In C5 will enter 1.25. In D5 will enter 2.50. For the sum function, instead of typing in numbers, we will use the cell reference where the numbers are contained, C5 for 1.25 and D5 for 2.50. Lastly, in the range value type, we will follow our previous example, C8 for 1.2542.50 and E eight for an additional number, 1.25. The sum function here, we'll take the first number in the range, colon, the last number in the range. The sum function will add all the numbers together within the range C8 through E8 or C8. And sweet, Let's jump into the next formula. 3. AVERAGE() Function: Welcome to the video on the average function. Just like in the video on the SUM function, Let's take a look at some high-level bullets and then we can jump into some examples. On we go, Let's start with a basic definition. The average function returns the average or arithmetic mean of the arguments. Just like in the sum function, the average function accepts individual values, cell references, or ranges. Let's jump right into the example. As in the summary slide, we have three different examples for each of the different values that can be entered into the average function. For the first value type individual values, we start typing the average formula and enter two numbers, 1.25 for number 1.502 for number two, Excel averages the numbers together and boom, we're done. The next value type cell references, we enter the values from the previous section into individual cells. In C5 will enter 1.25 and in D5 will enter 2.50. For the average function. Instead of typing in numbers, we will use the cell reference where the numbers are contained, S5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our previous example, C8 for 1.25, D eight for 2.50, and E eight for an additional number, 1.25. The average function here, we'll take the first number in the range, colon, the last number in the range. The average function will average all numbers within the range C8 through E8, or C. D and E eight were on fire. Let's keep rolling into the next formula. 4. COUNT() Function: Welcome to the video on the count function. Just like in the video on the sum and average functions. Let's take a look at some high-level bullets and then move on to the examples. Here we go. Let's start with a basic definition. The count function counts the number of cells that contain numbers and counts numbers within the list of arguments. Again, just like in the sum and average functions, the count function accepts individual values, cell references, or ranges. Onenote, the count formula only count cells that contain numbers. If you'd like to count cells that contain other data types like text, you would need to use count. A. Great, Let's move on to the example, as in the summary slide, we have three different examples for each of the different values that can be entered into the count function. For the first value type individual values, we start typing the count formula and enter two numbers, 1.25 for number 1.502, for number two, Excel counts the numbers and boom, we're done. The next value type cell references, we enter the values from the previous section into individual cells. In C5 will enter 1.25 and in D5 will enter 2.50. For the count function, instead of typing in numbers, we will just use the cell reference where the numbers are contained. C5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our previous example, C8 for 1.25, DH for 2.50, and EH, for an additional number, 1.25. The count function here, we'll take the first number in the range, colon, the last number in the range. The count function. We'll count all numbers within the range C8 through E8 or C8. And Easy-peasy, Let's move on to the next formula. 5. MIN() & MAX() Functions: Welcome to the video on the Min and max functions, just like the previous videos, Let's take a look at some high-level bullets and pop right into the examples. Let's do it. Let's start with some basic definitions. The Min function returns the smallest number in a set of values, and the max function returns the largest number in a set of values, as we're used to. And just like in previous functions, the Min and max functions except individual values, cell references or ranges. Let's take a look at the example. As in the summary slide, we have three different examples for each of the different values that can be entered into the Min or max functions. For the first value type individual values, we start typing the Min or max formulas and enter two numbers, 1.25 for number 1.502. For number two, excel determines the minimum or maximum of the numbers and outputs the results. The next value type cell references, we enter the values from the previous section into individual cells. In C5 will enter 1.25 and in D5 will enter 2.50. For the Min and max functions. Instead of typing in numbers, we will use the cell reference where the numbers are contained, C5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our previous example, C8 for 1.25, D8 for 2.50, and EH, for an additional number 1, the Min and max functions here we'll take the first number in the range colon, the last number in the range. The min and max functions will determine the Min or max of the range C8 through E8, or C8 and E8 onto the next formula. 6. CONCAT(), LEN(), & REPLACE() Functions: Welcome to the last video in the series. In this video, we're going to go through some more common formulas that don't need as much time as the other formulas we've learned, but are still essential to understand. We're going to take the rapid fire approach here. This video will cover the concat function, the Len function, and the Replace function. Let's jump into some definitions and then we'll take a look at the examples. Lego. Let's start with some definitions. The concat function combines the texts from multiple ranges and or strings, but it doesn't provide delimiter or ignore empty arguments. Next, the Len function returns the number of characters in a text string. Lastly, the Replace function replaces part of a texturing based on the number of characters you specify with a different texturing. Let's hop into the examples to understand this more. In the first example, we have the CONCAT formula. As we looked at in the previous examples, we have individual strings, cell references that contain strings, ranges that contain strings. Let's look at how each example works. The first example has the CONCAT formula, taking in strings directly with a no cell references. The second example uses D1, D2, and D3 as cell references, but returns the same output as our individual example. The third example uses brains D1 through D3, and again gives us the same result as the individual example. Onto the Len function. As with the CONCAT formula, we have three examples, strings, hello, goodbye, and how the lens formula is simply gives us the number of characters in each of the strings. Hello has five characters, goodbye has seven characters. And how did he also has five characters? Easy enough. Onward. For the last function replace, we have the same three strings. Hello, goodbye. And how the replace formula starts by asking us what the old texts cell or string is. This is basically asking us what our starting cell or string is. Next, it wants us to define the position within the string that it should replace. It wants both the starting character and the number of characters pass the starting character that needs to be replaced. Finally, the formula needs to know what we will be replacing the character's width. Let's look at the examples. The first example, hello, starting at position one and going through position two, we're going to replace the H and E with a dollar sign. We get dollar sign, LLC. Next for goodbyes, starting at position one and ending at position three, we are going to replace GO with a dollar sign. Lastly, for Howdy, starting at position one and ending at position four, we're going to replace HOW D with a dollar sign suite. We got exactly what we intended. Since this is the last video in the course, I want to thank you all for joining. It's been fun working through these examples. Feel free to ask questions in the discussion board if you need help. Peace out.