Excel Formulas & Functions Part 1+2: Formulas 101 | Chris Dutton | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel Formulas & Functions Part 1+2: Formulas 101

teacher avatar Chris Dutton, Founder, Excel Maven

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (39m)
    • 1. Introduction

    • 2. The Formula Library & Auditing Tools

    • 3. DEMO: Using the Evaluate Formula Tool

    • 4. Basic Formula Syntax

    • 5. Fixed, Relative, & Mixed References

    • 6. Common Errors & the IFERROR statement

    • 7. Function, CTRL & ALT Shortcuts

    • 8. BONUS: Shortcuts for Mac Users

    • 9. Creating Custom Data Validation Rules

    • 10. Fixed vs. Volatile Functions

    • 11. Homework

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

This course is part 1 & 2 of a 9-part series on Excel Formulas & Functions: from basic to advanced.

In Excel Formulas 101, we'll explore Excel's formula library and auditing tools, practice basic formula syntax, and learn the importance of fixed, relative, and mixed reference types. From there we'll dive into common errors, helpful PC & Mac shortcuts, and more. This will give you the tools you need to cruise through the rest of the series and emerge a certified Excel ninja.

About the Excel Formulas & Functions Series:

In this series, you will develop tools to transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Courses cover 75+ formulas, and feature hands-on, contextual demos and practice exercises designed to help you not only memorize formula syntax, but to think like Excel.

You'll learn how to write complex, powerful functions from scratch, allowing you to:

  • Build dynamic tools & dashboards to filter, display and analyze your data
  • Join datasets from multiple sources in seconds with LOOKUP, INDEX & MATCH functions
  • Pull real-time data from APIs directly into Excel using WEBSERVICE & FILTERXML
  • Manipulate dates, times, text, and arrays with ease
  • Automate tedious and time-consuming analytics tasks (no VBA required!)
  • And much more

If you're looking for the ONE series covering all of the advanced formulas and functions that you need to become an absolute Excel rock star, you've found it!

Meet Your Teacher

Teacher Profile Image

Chris Dutton

Founder, Excel Maven


Chris Dutton is a Certified Microsoft Expert and Founder of Excel Maven, with more than a decade of experience specializing in data science and business intelligence. His work has been featured by Microsoft, the Society of American Baseball Research (SABR) and the New York Times.

Excel Maven provides high-quality online analytics training, hands-on workshops, and project-based consulting services to more than 100,000 students across 180+ countries.

See full profile

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: All right, welcome to Section two formulas, 101 This section is really just about laying the groundwork for what's to come. So we start with a deep dive into the formula library and the auditing tools, which could be really helpful if you've got broken formulas, I'll talk basic forming syntax going to fixed in relative references, which is a really important section. I'll talk about common error messages why you might be getting them. How to get around them. Also share a tip cult if error, which basically lets you customize those error messages. Gonna talk useful shortcuts, eso function and control shortcuts, plus all key tips going to data validation. Really nice tool to help make your work more dynamic and excel on. Then last but not least volatile versus fixed functions. In this section, you'll see two files available for download. Got the Excel for analysts formulas. One a one pdf file, plus the Section two fixed relative references. Excel file. So go ahead and give those download and then let's dive right in 2. The Formula Library & Auditing Tools: All right, So let's take a minute to talk about the formulas tab and specifically the formula library and the auditing tools. So this year is basically lay of the land. Got your formula library here on the left with drop down options. You've got your auditing tools over to the right. I'm actually gonna jump into a new Excel sheet just to show you how these work. So as you can see him in the formulas tab, I can drill down into any one of these categories squirrel through to get a sense of what each of these functions does. So, for instance, that look up looks up a value either from a one row or one column ranger from an array on etcetera. So it's kind of an interesting way Teoh. See all of the formulas that Excel has to offer. There's some crazy stuff in here. To be honest, I have no idea what ceiling math is. We will be covering that in this course, but if you're interested, it's here for you to dig into. So when you're writing a formula, there are a couple ways to do it the first way, which actually would not recommend is to go into the library, pick your formula in this case, have just picked match kind of arbitrarily. And what that does is it populates the formula in the formula bar, and it opens up this wizard to kind of guide you through each piece of the function. You know, that's that's great. But I feel like it's kind of the lazy man's approach to writing Excel formulas. And as soon as you want to do anything more complicated, like nest formulas together or customize your formulas, this kind of breaks down because it it holds your hand a little bit too much. So I had actually recommend not doing that and recommend clicking in the formula bar, starting with the new cool sign and writing out the old fashioned way. And as you can see, Excel still helps you along. This little box pops up. This is actually really useful. It guides you through each step to help you with this intact. So I'm writing a match function. I know that I need to start with the look up value pick around himself and then as a comma over you see the bold tax changes. So now I need a look up array and then I'm going to need a match type and so on and so forth. So don't worry about match. Specifically, we'll cover that in the look up reference section. But just say no. That tool is there to help you along. The next thing to talk about is the auditing tool section, which is over here, does some really cool things. It allows you to trace precedents which basically shows which sells affect the outcome of your formula and then kind of the opposite of that. It shows which sells your formula effects. You can show all your formulas in the workbook as text as opposed to the output, a result of your formulas. And then the evaluate formula tool is a nice way to diagnose a formula when you have an error and you're not sure why you're getting that error. So to demonstrate that I'm just gonna do really quick example gonna pop in some values in row 1578 and then in selling to I'm just gonna dio that simple formula a one a times B one time see one which equals 280. That's great, but What if my finger slips instead of an eight? I write the letter P now the sun. I'm getting an error value, and it's pretty obvious what happened in this case. But let's say you know, those input cells were on different sheets or they were hidden, and it wasn't quite so clear where that ever was coming from. So what I could do is select eight to where my formula lives. Click evaluate formula. What this does is it walks through each step of the formula and evaluates piece by piece so you can see where your formula breaks down. So first pieces underlines. 81 cook, Evaluate change to five. So that's saying a one equals five. B one equals seven. Five times seven equals 35 c one equals p and 35 times p equals hashtag value. So this tool just showed me that the error in my formula is coming from the value in C one , which is P. And as you probably know, you can't multiply number by a letter so I could take a look and say, Oh, yeah, I accidentally hit P. Let's just change that back to eight and then all is right with the world again. So there you go. You got your formula library here. You're auditing tools here. Both within the formulas tab in Excel. 3. DEMO: Using the Evaluate Formula Tool: I just want to take one minute to walk through the evaluate formula tool one more time, with a much more complicated example. So a lot of people assume they'll never need to use the evaluate formula tool. Or they think that because Excel already doesn't pretty good job helping you through formulas that there's really no point. I'll be honest. I've had a number of times where my formulas will break and without the evaluate formula tool and may have never figured out why. So here's a good example to help illustrate that in this case, we're looking at a really advanced, really complicated array function, which nests all sorts of functions, index small if Roe and Rose to basically return a subset of values from a range and Collins A through D and unlike the last example, which only had three simple elements to it. In this case, when I evaluate the formula, it's gonna cycle through dozens of different steps as it walks through each piece of the function, and this will give me the ability to pinpoint exactly where things are breaking. Now, obviously, I'm not going to explain exactly how this is functioning just yet. But hopefully this is a good example of one. The evaluate formula tool can be a lifesaver. Eso here it's finally gotten to the end. It evaluated properly and return the value 14 2016. So don't count this out. Remember, especially as your work starts to get more complicated, that that evaluate formula tool can be extremely helpful. 4. Basic Formula Syntax: Okay, so let's take a minute to talk about formula syntax, which is essentially how formulas written or how it's built. So all Excel formulas are going to start with the equal sign. You can either select them from the Formula Library. I wouldn't recommend that I'd actually recommend typing them directly into the formula bar . And like I mentioned, once you've entered your function name, this box will pop up That will guide you through each component of the function, step by step. Most formulas, not all but most of them contains some sort of reference to a cell, and those references could take three different forms that could be single cell references that could be a ray references, and they could be noncontiguous references. So single cell reference just describes an individual cells location within a worksheet in terms of the intersection between a column noted with a letter and a row noted within number eso. As of Excel, 2013 columns go from a through X F D, which is just over 16,000 columns and rose extend from one through 1,040,576 so a single cell reference will just look like the combination of a letter and in number. A. Rare references describe a contiguous group of cells, meaning that they share a border. Andi. It's described based on location of the top left cell and the bottom right, So eso If I selected the range from a one through C four, the array reference would look like this. It would be the top left cell, which is a one followed by a colon and then the bottom right? So which is see? For last but not least, you have noncontiguous references. Thes describe selections of individual cells that don't share common border eso a one and C four separately, for instance, and those air separated by a comma. So let's hop over to excel. I'll do really quick demo just with a blank worksheet to show you how this is working. I'm just going to type one to sell a one, drag it down 10 rows and then just to fill serious that it gives me the Siri's from 1 to 10 and then in cell C two is going to show you a few different forms that sell references can take using a simple some function so if I start with the sum of just a individual cell a three, for instance. As you can see, a three is a single cell reference. And when I hit enter, obviously I'm just getting the value of a three. I can change that reference. So rather than a three, I can either delete and type of new reference in the formula bar, where you can just drag out my reference right here. So now I'm looking at the son of a contiguous reference from a three through a seven when they hit, enter and get 25 because three plus four plus five plus six plus seven equals 25 on and then last but not least, if I wanted to add or some 83 and a 10 I could just basically delete that reference. Select a three hold control, select 8 10 And as you can see, Excel has separated those two noncontiguous selections with a comma. When I press center, I get 13. So there you go. Those are the three main forms of cell reference types 5. Fixed, Relative, & Mixed References: all right, fixed relative and mixed references. You may have noticed that some formulas have dollar signs included. Some don't. Basically, that dollar sign is what's used to indicate the reference type. So what it's doing is it's basically locking a specific cell range or reference or part of a cell ranger reference so that it won't change if you apply or drag or paste that formula toe other cells. So, therefore, different types. You can have a completely fixed reference, which would have a dollar sign in front of the column indicator and the row, so dollar sign a dollar sign. One. You can also just fix the column or just fix the row, in which case you'd only have the dollar sign in front of one or the other. Or you can leave it completely relative, which is the default version and excel where there would be no dollar signs whatsoever. So it's a little tricky to wrap your head around, but let's try to visualize what's going on here. So if I have a formula in a one, are formula that has a reference to a one and then I paste that formula down. Two C four If my reference was completely fixed, a one would not change as I move that formula down and over. If I were only fixing the row as they paced down, you can see that the column shifts from A to C, but the row remains one. Also, if it just fix the cone, A stays the same, but the road changes from 1 to 4 as you move it down. And then, of course, if you have a completely relative both the A and the one change, it becomes a C four. So quick little pro tip here as your writing or formula. If you actually put the cursor in the middle of one of your references and hit F four, you can repeatedly hit that to scroll through each reference type just a little bit quicker than typing and dollar signs every time. So I always think about your references, especially as you're using multiple formulas. Let's jump into excel. I want to show you kind of a hands on demo of how this is really working. If you could open up the Section two fixed relative references file, I'm gonna work. From there, you see, there's only one tab called Multiplication Table. Basically, I've set up a blank table with values from 1 to 10 and I want to do is populate this multiplication table. So if I start and just say equals B one times arrow over to a two, enter one times one equals one. It's fantastic. Dragon over to come K. I'll drag it down to Row 11 and obviously something's gone horribly wrong here. This isn't correct. Math at all, and Teoh diagnose what might be going on. What we can do is select any cell hit the F two key, and that will show us which sells the formula references. And if I just do that in a few different cells, I can tell that it's always referring to the cell immediately above and immediately to the left of my formula, which isn't what we want. We always want to multiply by the horizontal row and by the vertical column in a so click within the B one in my formula just hit F four. As you could see, that adds basically completely fixed reference. It fixes the column B and it fixes the road one. If I do the same to a to and hit Enter Now what happens if I apply this all the way over? I get one every single time. And as you can see, it's always referring to be one and a two, no matter where the formula lives. So one more step than any to take is to think about which piece of each of these references do I want to change. In which piece do I want to stay the same? So for my first piece, be one what I want to be able to change the column B or the row one. So obviously my multiplication values always live in Row one, so I don't want that to change. But as I dragged the formula over, I do want it to update two multiplied by the value in column C, D E and so on. So all I'm gonna do is remove the dollar sign before the B and then think about the same situation for my A to reference. In this case, my multiplication values always live in column A. But I want to update that two multiplied by the value in row 3456 etcetera. So in this case, I don't want my road to be fixed. But I do want my column to be fixed. Hit? Enter now. If I apply this out and down There you go. You can see I have a proper multiplication table. 6. Common Errors & the IFERROR statement: all right, so let's just take a minute to talk about common Excel errors and if error statement, it doesn't matter if you're the most talented Excel user on the planet. At one point or another, you're bound to come across some of these error messages, and it's not necessarily a bad thing. It's not something we should be afraid of, you know. In fact, sometimes errors can be really helpful things. So let's talk about five common era types, what they mean and how we can go about trying to fix them. The first is just a Siris of hash marks or pound signs. What that typically means is that the column just isn't wide enough to display your values . That's the most common cause. In some cases, you might get this value if Excel is having a hard time recognizing a particular date value . But more often than not, the easiest fix is just to drag or double click your column border to increase your with or right click to set up a custom column with so super easy fix there. The next era type is the name error, which means that except doesn't recognize text in a formula. So this could happen if you type your function name wrong if you mistyped a reference, if you mis quotation marks or Coghlan's so make sure that all of those things air correct. Nine out of 10 times that I get a name error. It's because I didn't surround a text string within a formula with the proper quotation marks or I left out of print Sister Akama similar in my formula. The value error means that formula has the wrong type of argument, so just make sure your formulas not trying to do something wacky like perform an arithmetic operation on text strings, make sure you don't have values that maybe accidently formatted as text you try to multiply . Eight by P Excel is going to give you a value error. Next up did. Zero. This one speaks for itself. You're trying to divide by zero or by an empty cell, so just check the value of your divisor on. Like I mentioned, zero might be the correct number, so there's nothing wrong, so you can either keep the div zero error there, or you can use an if statement specifically, if error to display an alternate value if you choose to do so. So I'm gonna show you a quick example of how to do that in just a moment last. But at least we've got the ref error. Which is that the formulas referring to sell its not valid and, ah, how to fix this. Just make sure that you didn't move, delete or replace cells that are referenced in your formula. I'm So, for instance, if you have a look up reference function that's reading from a specific column of cells and then you accidentally delete that column, all of your functions that were referencing that column are now going to give you a rough error because they don't know what values are cells to read from. So those are five common types of errors. Let's jump into the if error statement, which, as a personal favorite function of mine, it's really great tool to eliminate annoying error messages. Your div. Zero is your enemies, you refs, which could be really useful for front and formatting. So that's kind of the most common application that I found is that if you're sharing something like a dashboard or presentation with colleagues or clients and you want to just polishing Button it up. You can use the if error statement to customize those error messages to something a little less in your face as thes Accel Hours. Maybe the other thing I'll note is that if Eric could be really great tool to eliminate errors among values, because sometimes those errors will prevent excel from performing operations on those values. Um, and you might want to replace on any with zero, for instance, so that your statistical and arithmetic calculations continue to work properly. Will also explore that in one of the later sections, but just a heads up syntax wise, the affair statement is is quite simple. Just two components. It starts with your value. This could be either value itself or, more likely, a formula which may or may not result in another. And then after that, you come over to the value of error piece, and this is just the value that you want to return. In the case of an error in place of the air message that it would have thrown s 02 examples here. You know, if we have a division problem, a one divided by B one and we know that the B one might potentially be a zero. In some cases, we can say All right, instead of giving me a div zero when you do this function instead, return the text string invalid formula. In this other example, we're saying, if you get an error when you run this view, look up function, just show me a dash in quotes, which is a lot cleaner. So, pro tip. If you're writing a formula that may trigger an error like a look up value, we're not all values might have a match right the full formula first and then at the end, wrap it in an affair statement. Otherwise, things can get a a little bit sketchy pretty quick, especially as we start working with some longer, more complicated, nested functions. So I just want to hop over to excel really quickly show you an example of how the if error statement can be used in practice. I'm gonna show you more examples specific to certain formulas in the upcoming sections, but just as a quick example, we're looking at a weekly performance dashboard here. I've got some volumetric spend impressions and clicks, and then a couple calculated fields the ones that I want to focus on. Uh, right now our click the rate and cost per click on which are defined as clicks divided by impressions for click the rate and spend divided by clicks for cost per click. Um, so since we have data for spend impressions and clicks thes formulas, air populating just fine. But let's say for any given week we could expect these numbers toe actually dropped down to zero. So do this for just two weeks as an example. Now, as you can see these formulas or not flagging DIV zero errors and it's ah, it's kind of annoying. I could remove the green flags in the Excel options, but really, I just don't want to see this error at all. What I want to do is essentially wrap these formulas with an if error statement so that instead of Div zero, I want to just show a little dash. So what I'm gonna do has jumped to sell F six and wrap this formula in an if air. So right after the equal sign in a type if error opened, the prince sees jump to the end press comma and then Here's my value of error which is just gonna be a dash surrounded by quotes Close off the princess and press enter And again we're gonna practice this a bit more shortly. But I'm gonna do the exact same thing for the CPC column If error jump to the end Comma dash quotes close the parenthesis enter As you can see, nothing changed for cells F six g six because the formula didn't return in her. But if I drag that down and I'm just gonna fill without formatting to preserve the formatting underlying now you can see those div errors I replaced by nice, clean little dashes So just looks a lot nicer now a lot more polished. So there you go. Just one quick example of how to use if error 7. Function, CTRL & ALT Shortcuts: okay. I want to share some of my favorite shortcuts. I'm gonna walk through three different types who got function or EHF key shortcuts. Talk about control, shortcuts and all shortcuts, otherwise known as all two key tips. Eso starting with function shortcuts there to that I use more than any others. The first is at four, which have talked about one way. That it's useful is by allowing you to add or modify cell reference types. So, like I showed you in one of the earlier lectures, you can place your cursor directly into the cell reference and use that four to cycle through fixed relative and mixed reference types. You can also use that four to repeat your last command or action. This is really useful for things like inserting or deleting rows or columns, changing south formats or styles, etcetera. One thing that at four does not do is it won't repeat entered values or formulas, so I'll show you some examples of that. And then the other F shortcut is the F two, which basically dives into formula. It displays the cell ranges that are tied to a given formula, and it allows you to edit those ranges or edit the formula itself really useful that help diagnose formula errors or make quick adjustments to sell references and a raise on the fly . So I showed you a sneak preview of how the F two key works when we built that multiplication table. But let's jump into Excel in and take a look at one more example. So I've got a formula and column see very basic data array here again, if I click in any reference, F four will cycle through each of the different reference types. I can also use it to repeat in action, such as inserting a row so now select row for and just hit at four. So, like six hit at four. As it can see, that's working nicely, and I can actually just undo that. If I decide I don't want to make those changes Now the F two key. If I select any formula, sell hit F two, it's going to allow me to dive in and edit the formula. As you can see, it highlights the cells that are referenced. So one little trick is if I want to change the reference. So let's say I want to be be two divided by a three instead of a two instead of going in and typing a three. What I can do is actually just grab this reference, drag it down, and it will change it for me. So that's a nice little tip trick with the F to function moving on to the control functions there. Three that I use all the time. There's Control Arrow, which jumps to the left right top or bottom edge of a continuous data array. So basically, the last cell that contains data control shift Arrow does the same thing, except it selects the data as you move. So most common application of the control shift arrow shortcut is when you want to select an entire array of data and you don't wanna have to scroll all the way down. Sometimes you have an array that's, you know, 100 or 200,000 rows long so you can just start in a one, holds control and shift arrow right Arab down, and you've got your entire array of data. Last tip. Here's control page up and page down, which lets you jump between tabs of a workbook. Note that there's full, comprehensive list of control functions at the link below thes air, just the three that I tend to use most often so jumping back into ourself that workbook, Let's use the control arrow Short cut first start on a one hold control error to the right arrow down. So, as you can see, it's just jumping to the last cell containing data. I'm going to the same thing but hold shift as well. Now you can see that it makes the selection as it moves and again the control page down and page up. Just moves between tabs. If I hold control and hit page down, jumps the sheet to sheet three on page up moves back to the left, back to sheet one. Finally, we've got our all to function, which enables keep tips. This basically just allows you to access any function in the ribbon using keyword keyboard shortcuts. You don't need to hold down the out. You can just press ault and you'll see the key tips. Activate eso these little numbers and letters in gray boxes, those air, the key tips so you press all to reveal the tab level shortcuts and then you press whichever one you want to dig deeper into on it will reveal another set of key tabs, and you continue to drill down until you get to the specific function that you want to jump to. So sometimes it's a combination of multiple key tips to get to the function that you need to that I use way more often than any others are all H V V an altar H V F Ault H V V Paste Special as values. All H V F paste special as formulas, so show you another quick example. Ran our values and column CDs are all formulas that can select all those hit control c to copy them and then with the one selected instead of going into paste paste, special values. I'm just gonna hit Ault. You see the key tips appear h dug into the hometown V. I'm into the paste sub options, and then one more V K specialist values. So whereas column C contains the formula, com de contains just the value that was the output of that formula. A similar case with all H V F that can copy the formula and self see one if I want to paste this formula down. You know, if if these cells didn't contain the formula, let's just delete them to show this copy C one and then select C to 37 hit Ault H V F. And it will pace the formulas into that range. So again, those air a few examples, but they're the shortcuts that I tend to use more than any others. 8. BONUS: Shortcuts for Mac Users: all right, Just want to take a minute or two to talk about Mac equivalents of some of these shortcuts that we just talked about. So one of the more frustrating things and Excel is how the user interface and user experience is so different whether using it for Mac or PC. Luckily, because this course focuses on formulas and functions, specifically, the syntax, structure and usage of formulas is gonna look basically identical between versions. Where you'll see the biggest differences are again the user interface and the shortcuts, but also things like pivot tables and charting and graphing. So let's quickly cover what you would use if you're on a Mac on. You needed to use some of the shortcuts that we just talked about for PC. So instead of F four, you're gonna use command T to cycle between your cell reference types and command. Why? To repeat your last user action rather than F to you can use control you to display your cell ranges that are tied to a given formula or just double click the cell in which the formula lives. Now the control functions for PC or the control shortcuts are really similar you'll just use command for Max. A command arrow and command shift arrow will jump to the edge or extend the selection to the edge of a contiguous data array and the command function or FN. Up and down, we'll jump between workbook tabs on a Mac. The last thing to note here is that, unfortunately, the altar key tips just don't exist for Max. So those tips that let you drill down into very specific components of the ribbon you just can't do, which is a little bit frustrating at the best resource, a reference point that I've found to remember. This is that Excel jet dot net slash keyboard dash shortcuts. And what I love about this site is that not only does it list more shortcuts than you could ever possibly want to use, but it shows the PC version and the Mac version right there side by side. So there you go Mac equivalents of some of my favorite PC shortcuts. 9. Creating Custom Data Validation Rules: Let's talk about data validation. It's one of my favorite tools and features of excel. I use it all the times really cool way to add a dynamic element to your workbooks. Basically, what data validation does is it allows you to specify what someone can type into a cell so you can say Onley allow whole numbers. Only allowed numbers between one and 10 only allowed positive into jurors or decimals. Whatever. One of the most useful forms of data validation, the one that I use most often is called list, which creates a drop down menu of options based on a source list that you specify So you can link to a list in cells that exists in your worksheet or for the source. You can actually just type in the values that you'd like that cell to take. Separated by commas. You can also change the input message and the error alert, which can be a lot of fun. So let's jump into Excel and do a very quick example. Just got a blank worksheet here. What I'm gonna do, let's create a list of months. Start with Jan. Use a little shortcut here called Phil Siri's and so Excel recognized that when I type Janet probably meant January. So when I pulled it down and 11 more rose, it guessed that I wanted that to fill in month abbreviations through December. Wouldn't you know? It was right. Cells, actually pretty smart sometimes. So now I've got that list. What I'm gonna do in a new cell, let's just say D two I'm going to go into the data tab going to data validation here and just select data validation. So in the settings, this is where I set my criteria. So in the allow drop down, these are those options that I was referring to. I could say Onley allow whole number between zero and 10 for instance, and then air alert, you can say all right, I want a pop up box to appear that says, you know, cut it out and say, OK, now all of a sudden, this D two has the data validation criteria built in. So if I tried to type 11 I get my error. That says, Cut it out. Same thing. I try to take 3.5. It's not a whole number. So we get the same error So to get rid of a data validation room back into data validation settings, choose any value. Once you do that, you've kind of reset so you can take anything you want in there. I want to show you list because it's, in my opinion, the most useful. I'm gonna go to allow a list and then my source This is where it can select data in a range . I'm going to select a two through a 13 which is my source months. I understand. Okay, once I do that, it's created this drop down. We're now the only values that you're able to input into the cell are the values that are part of the list that I specified. So as we go on, I'll show you more examples of how this could become a very useful tool when you're building dashboards to kind of change other values in your workbook. Based on these selections, that user makes so data validation really helpful toe 10. Fixed vs. Volatile Functions: All right, let's wrap up this 101 section by talking about fixed versus volatile functions. Basically, volatile functions are just functions or formulas in excel that change every time the workbook Recalculates on. I don't just mean when a cell that's tied to a given function changes or an array or reference that feeds into a formula changes. I mean, any time you enter data anywhere in any open workbook, so they call them volatile functions for a reason. And that's because they're just constantly changing by nature of what they do. So there aren't Aton of volatile functions and excel. The more common ones, and the ones that we're gonna cover specifically include now and today, which you're dating Time functions Rand, which is a random number generator and some advanced look up functions like offset and indirect eso. One tip here you can actually change the calculation option if you choose from automatic, which is the default to manual. Automatic means that any time you change, sell a reference that's tied to an existing formula. The output of that formula will change, which is what you want 99.9% of the time. Um by changing that option to manual, you're essentially saying I want to freeze all of my formulas in place as if their values and I only want you to recalculate when I press the read, calculate or calculate now button in the formulas tab. So a word of warning. This can be useful at times, but it's very easy to forget that you change this setting to manual. Um, if you do change it, just make sure you change it back. Otherwise, little. You'll end up with frozen or outdated formulas in your workbook. But there are benefits, you know, if you have a workbook with 10,000 or 100,000 formulas that are constantly recalculating, might be able to save yourself some headaches by switching to manual mode and only calculating when you choose to. So let's jump into excel and do a quick example. Um, use the rand function and column A. So this is just gonna basically create a random number between zero and one, and don't worry about the details will cover this in Section four. I believe so. Basically, what I've done is just create 10 random numbers out to 15 decimal points from anyone through a 10. And now, if I change any value, you know I can call him. See, for instance, you'll note that those numbers in column a continued change. If I go into the formulas tab calculation options switched to manual. Now, as I continue to make work but changes my random number, generators are frozen in place. If I do want those to calculate, I just sent to calculate now button right here. And as you can see, that's basically having the same effect as any other workbook change. Except now I'm in manual mode, so switch back to automatic so you don't forget. But in a nutshell. That's what volatile functions are all about. And with that, let's jump into the next section. Talk about logical operators. 11. Homework: all right, made it through the end of the formulas. One of one section. If you're feeling really good, really comfortable, go ahead and keep cruising on in the next section. If you do want a little bit of extra practice, you can open up the Excel Homework Exercises file and take a look at the first tab. The formulas wanna one tab and here have included some instructions with four steps, and through these steps, they're gonna practice, creating some very simple calculations. You'll practice using shortcuts like F two and F four, the Trace Precedents tool, and you also get some experience replacing errors using excels if error function. So go ahead and give that a shot. Keep in mind that you also have access to the answers workbook. Both of these are available in lecture to the course, but, as always, reach out. If you need anything, good luck