Master Excel formulas and functions with this comprehensive course taught by a former Amazon software engineer. You'll learn key spreadsheet concepts and immediately reinforce them through interactive inbrowser practice and real world problem solving. The curriculum covers everything from core formula mechanics and air handling to essential logical operations, lookup functions like XLOOKUP and modern dynamic arrays. I promise you won't find another Excel course like this out there. In this course, you'll master Excel formulas and functions, not just by watching video lessons, but by applying what you've learned to solve quizzes and hands-on practice problems, all directly in your browser with nothing to download and it's all free. Quick intro, I'm Sergio, and Excel has previously helped me land an investment banking internship and full-time offer. It also sparked my interest in logical problem solving and ultimately led me into programming. I learned to code largely through YouTube via channels like free code camp and from other online resources such as leak code. I then spent multiple years working at Amazon as a software engineer. But along the way I realized that the tools available for learning Excel lacked the type of interactivity compared to the tools available to learn to code. So, I recently left Amazon to start Formula Wars, a platform designed to make learning Excel more practical and interactive, which you'll get to use alongside this course. So, how this course will work and how it will pair with Formula Wars, is that the course will be broken down into a series of lessons. Each lesson will have a video component which you can watch directly here on the YouTube video you're currently viewing where we'll be learning by going over some material on an Excel spreadsheet. On formula wars, you'll be able to view and interact with that spreadsheet directly on the browser or you're welcome to download it and open it on Excel directly as well. Then after each of these video lesson components concludes, there will be a quiz for you to take on formula wars to test your understanding of what you just learned in the lesson. And for most of the lessons after the quiz, there will also be a series of easy introductory practice problems where you will practice using one function at a time in order to solve a realistic problem hands-on. These problems will have multiple test cases to ensure your solution is truly dynamic and that you haven't for instance just hardcoded an answer that will work for one set of inputs but not another. And if you get stuck each problem comes with hints and also a solution. Finally, after the course concludes, you're encouraged to explore the core problems offered on Formula Wars. Contrary to the introductory problems that you will solve during the course, these core problems are a lot more advanced and tricky and will generally require you to combine multiple of the functions you have learned together. These practice problems are the core formula wars advantage that are designed to help you build the muscle memory needed for genuine Excel mastery. Several of these non-course specific problems are also free for you to try. And if you want to gain access to the full problem bank as well as some other perks like an AI formula tutor, you can consider upgrading to the pro plan on the site. But again, for all the content specific to this course, it's all free. So to get access to all the course companion material on Formula Wars, you'll have to create an account on the site using the link in the description. It's super quick and easy. You can even use your Gmail if you prefer. But quick warning, if you try to sign up using the regular sign up link on the site, this course material won't be unlocked by default. So make sure you use the link in biospecifically. That's the one that unlocks the course material. All right, so one quick note on the curriculum. Again, this course is focused on formulas and functions. This is the most important part of Excel. It's the language Excel speaks. Whether you want to create a fancy dashboard or an automated workflow, all of that is ultimately a byproduct of the formulas and functions holding your spreadsheet together. So the course begins by building a strong foundation in how Excel formulas really work by learning about operators, formula evaluation, cell referencing, function structure, and error handling. With that foundation in place, we'll then learn about the functions that power real world spreadsheets, including logic functions, aggregation functions, and lookup functions. In the lookup functions lesson, you'll get to learn about my personal favorite function, XLOOKUP, which is the Swiss Army knife of Excel lookups. We'll then master the art of text manipulation and working with dates before finishing off on a high by learning about dynamic array functions which are extremely powerful modern functions that completely change how you work with Excel and will put you ahead of 99% of Excel users. So with that being said, let's jump right into the first lesson. Before we talk about functions, first let's separate the idea of a formula from the idea of a function. A formula is any Excel expression that tells Excel what to calculate and starts with an equal sign. A function is a built-in tool that can appear inside a formula like sum or if. So= 12 + 5 is a formula with no function while equals sum 2, 5 is a formula that uses a function. So a good amount of Excel work actually starts with formulas that do not need a functions at all. Okay. So to write a formula, it's important to remember to always begin with an equal sign. Otherwise, Excel treats what you typed as text instead of math. For example, notice how if we write= 2 + 3, Excel will return a value of five. However, if we omit the equal sign and just type 2 + 3, Excel will show literally 2 + 3 as the value instead of five. Now, let's get into the operators you will see inside formulas. In that example, we just saw the usage of the plus sign, which as expected is used for addition. The minus sign subtracts, so= 12 - 5 returns 7. The asterisk multiplies so equals 8 asterisk 4 returns 32. The forward slash divides so= 20 sl 5 returns 4. The carrot handles exponents so= 2 carrot 3 returns 8. The percent sign converts a value into a percentage. So equals 50 percent sign returns 0.5. So those operators were all arithmetic, but there are also non-arithmetic operators. Let's go over those as well. Inside a formula, the equal sign checks whether two values are equal. So equals A12= B12 returns false in this example where A12 is 12 and B12 is 10. The greater than symbol checks whether one value is larger than another. So equals a12 greater than symbol B12 returns true. The less than symbol checks whether one value is smaller than another. So equals A12 less than symbol B12 returns false. Greater than or equals to uses the greater than symbol followed by the equal sign. So equals A12 greater than symbol equal sign B12 returns true. less than or equal to uses the less than symbol followed by the equal sign. So equals A12 less than symbol equal sign B12 returns false. And when you need to join text together, Excel uses the amperand. So equals high amperand there returns a value of hi there. Note by the way that text needs to be wrapped in quotes or else you will get a name error. More on that later though. So those are all the core operators in Excel. Next we can look at how Excel evaluates longer formulas and decides which part to calculate first. Now that you have seen the basic operators, the next step is understanding what Excel actually does between the moment you press enter and the moment a result appears in a cell. A useful mental model is that every formula has inputs, rules, and an output. Inputs are values such as numbers, text, true or false, or cell references. Rules are the operators and functions that tell Excel what to do with those inputs. The output is the single final value Excel returns. Look at the example in the upper right. in equals E3 * F3 + G3. The inputs are E3, F3, and G3. The multiplication and addition operators are the rules and the result in H3 is the output. To decide which rule to apply first, Excel follows the orders of operations, often remembered as PDM mass, parentheses, exponents, multiplication and division, and addition and subtraction. The key idea is priority. Operations higher on that list are evaluated before operations lower on that list. Multiplication and division share the same priority level as do addition and subtraction. So when Excel encounters operations with the same priority, it evaluates them from left to right. You can see that in the example here.= = 10 + 5 * 2 returns 20 not 30 because multiplication happens before addition. When we add parentheses and write equals open parenthesis 10 + 5 close parenthesis * 2 the result becomes 30 because the addition inside the parenthesis is resolved before the multiplication. Parenthesis lets you carve out a smaller sub problem. solve that piece first and then continue with the rest of the formula. The same evaluation process applies when formulas reference cells. In the upper right walkth through, Excel starts with equals E3 * F3 plus G3. Substitutes in the underlying cell reference values, turning the formula into = 4 * 25 + 10, then applies the order of operations, multiplying 4 * 25 to get 100, and finally adding 10 to return 110. Functions follow the same model. A function is simply another rule that takes inputs and returns a value. For example, equals sum open parenthesis 2, 3 * 4 close parenthesis still evaluates 3 * 4 first before that expression must resolve to a value before sum can finish its calculation. The formula effectively becomes equal sum open parentheses 2 comma 12 close parenthesis and the final result is 14. This leads to the idea of nesting which simply means that one expression sits inside another expression. Here's an example of nesting. Equals max open parenthesis E18 * F18 comma sum open parenthesis F18 comma G18 close parenthesis * 4 close parenthesis. After resolving cell references, Excel works from the inside out. It resolves 18 * 3 first, which gives 54. It then resolves the sum of three and seven function which gives 10. Next, it multiplies that result by four to get 40. Only after those inner pieces become values does max compare 54 and 40 and return 54. The outer function cannot complete its calculation until the nested expressions have resolved into usable values. This is why longer formulas are really just chains of smaller evaluations. Excel keeps collapsing the formula step by step until only one value remains. References become their current values. Inner expressions resolve. Functions return values and the outer formula moves towards the final output. Whenever a formula feels confusing, do not look at the entire formula as one block. Instead, break it apart and ask four questions. What are the inputs right now? Which rules applied first? What intermediate value should appear next? And what final output should that lead to? That process is the core mental model behind writing, reading, and debugging formulas. One of Excel's biggest productivity advantages is that formulas don't need to be rewritten cell by cell. To see that, look at cell E3, which contains equals C3 * D3 to multiply the quantity by the price or that row. If we copy E3 down to E4, Excel automatically changes the formula to equal C4 * D4. That is exactly what we want because in the next row, the formula should use that row's quantity and that row's price. What powers that behavior is relative referencing, which is Excel's default way of handling cell references. Instead of treating C3 and D3 as fixed addresses, Excel preserves their position relative to the formula cell. In E3, C3 is on the same row and two columns to the left, while D3 is on the same row and one column to the left. So when the formula moves down one row, Excel keeps those same relationships and updates the formula to equal C4 * D4. That is why relative references are such a useful productivity tool when you want to write a formula once and fill it through a table. However, they become a problem when an input should not move. For example, calculating agent commission requires every calculation to reference the same commission rate in cell I2. If we write equals I5 * I2 in the commission table and copy it across or down, Excel changes I2 to I3, I4, and so on because I2 is still relative. But the commission rate exists only in cell I2. So we don't want that reference to drift. This is where absolute references come in. Change I2 to dollar sign I dollar sign 2. Now when the formula is copied anywhere, it continues to point to column I and row two. The dollar sign before the column locks the column. The dollar sign before the row locks the row. Use absolute references when a formula must always point to the same cell. There is also a middle ground between relative and absolute references. Sometimes a formula should move in one direction but stay fixed in the other. That is where mixed references are useful. Now look at the lower table. Each row has an order amount in column C and row 10 contains the delivery, service, and tax fee rates. Every yellow result cell in column D through F should multiply the order amount for its row by the fee rate above its column. Our goal is to write one formula in D12 that can be copied across the entire table correctly. If we start with the relative formula equals C12 * D10 in D12, it breaks as soon as we fill the table. Copy D12 one column to the right into E12 and Excel Excel changes the formula to equals D12 * E10. That is wrong because the order amount should still come from column C, not the result in column D. Copy D12 down one row into D13 and Excel changes the formula to equal C13 times D11. This is also wrong because the fee rate should remain in row 10, not drift to row 11 where the headers are. For this pattern, the amount must stay locked to its column and the fee rate to its row. That means the correct reference for the amount is dollar sign C12 as the single dollar sign in front of the column locks the column but not the row. On the other hand, the fee rate should always come from the row 10 while the column changes across the fee types. That means the correct reference for the fee rate amount is D dollar sign 10 as the single dollar sign in front of the row locks the row but not the column. Now if we copy that formula across and down the entire table, every cell stays aligned to the proper amount on the left and the proper fee header above. For instance, if we look at F15, notice how it is pointing to the correct row amount and the correct column fee. So that is the core mental model model. Relative references move freely. Absolute references lock both the row and the column. Mixed references lock only the part that should not move. Before copying a formula, decide which parts of a cell reference should move and which parts should stay anchored. Then add dollar signs only where needed. In this lesson, we'll build a simple mental model for how functions work. Every function has two parts, a name and arguments. The name tells Excel what to do. The arguments tell it what inputs to use. So in equals round C4, 2, round is the name and C4 and two are the arguments. Think of the name as the verb and the arguments as the inputs it acts on. Arguments go inside parenthesis. If there's more than one, they're separated by commas. For example, in equals left F5, 4, left is the name. F5 and four are the arguments. Excel reads that as return the first four characters from F5. The specific functions don't matter. The pattern does. name first, then inputs in parenthesis. Not all arguments are required. Some are required. The function won't work without them. Others are optional. Excel uses a default if you leave them out. Round is a simple example of a function with no optional arguments. In equals C4, 2, both arguments are required. Without the first, Excel doesn't know what to round. Without the second, it doesn't know how many decimals to use. Left is an example of a function that has an optional argument. The second argument in left called num cares is optional. We know it's optional because Excel shows it in square brackets in the syntax helper. We would also know this if we use Excel's formula builder, which shows each argument as a labeled input and makes it easy to see which ones are optional and what default Excel will use if you leave them out. To open formula builder, click inside the function, then click the FX besides the formula bar. This shows the function structure and tells us that num cars defaults to one if omitted. And if that quick breakdown still is not enough, formula builder also includes a more help on this function option. Clicking that opens a full explanation of the function, its arguments and examples. Especially helpful when the function is new. So if you write equals left F5, 4, Excel returns the first four characters. If you write equals left F5, Excel uses its default and returns the first character. Some functions also include multiple optional arguments of different types and this introduces an important nuance in how you enter them. Text before is a good example. If we write equals text before f18, dash comma 2, we are setting only the first optional argument instance num. That tells Excel to return everything before the second dash, which gives us red blue in F-16. But suppose we want to set only the third optional argument match end. We still have to preserve the positions of the earlier optional arguments. So we write text before f18 comma slash comma empty comma empty comma 1. Those empty comma slots mean instance num and match mode are being left blank. So Excel will use the defaults for those because a slash does not appear in F18. Setting match end to one tells Excel to treat the end of the text as a delimiter which returns the full text red, blue, green in F17. The function itself isn't important. The key idea is this. You can skip optional arguments but not their positions. Order still matters even when arguments are blank. Some functions accept many arguments. Sum is a classic example. You can write equals sum f_sub_10 colon f13 or equals sum f10 comma f11 comma f12 comma f13. Some can keep accepting more inputs. Excel denotes this in the function syntax helper by ending the function with an optional argument followed by three dots. The three dots mean you can keep adding more arguments of the same type. Finally, some functions take no arguments at all. For example, equals today open parenthesis closing parenthesis takes no arguments. The name alone returns the current date. These examples all follow the same structure. The name tells Excel what action to perform. The arguments provide the inputs. Some arguments are required. Some are optional. Some functions accept many arguments and some accept none. Once you understand this structure, learning new functions becomes much easier. In this lesson, we'll build a practical mental model for Excel errors and the two main functions used to handle them. Let's start with the table in rows 3 through 8. The pattern we'll use is simple. First identify what each error means. Then look at a concrete formula that produces it. Row 3 shows the div 0ero error that appears when you attempt to divide a number by zero. For example, in D3, the formula is H3 / H4. And because H4 contains zero, Excel returns the div zero error. In row four, the error type is value, which means the formula received the wrong kind of input, usually the wrong data type. In D4, the example formula is H5 plus H6. H5 is a number, but H6 contains text. So, Excel can't perform the arithmetic and returns the value error. In row five, the error type is name, which mean Excel doesn't recognize something you've typed as a valid function, range name, or reference name. In D5, the example formula contains a typo where it tries to use a function called sue instead of sum. Because sue is not a real Excel function, Excel returns the name error. In row six, the error type is ref, which means the formula contains a broken or invalid reference. In D6, the example formula is equals H9. Right now, the reference still works, but when we delete the reference cell, notice how Excel turns it into a refair. There is also another common way refers happen. when a formula contains a relative reference and then you copy and paste that formula into a new location where the shifted reference would point outside the workbook. You can see that with H13 which contains equals H4. If you copy H13 and paste it into H9, Excel tries to preserve the same relative position. But from H9 that shifted reference would need to point above row one which is impossible. So the pasted formula becomes a ref error. Next in row 7 the error type is NA which usually means no match was found. In D7 the example formula is attempting to conduct the lookup of P500 in cells J3 to J5 via the match function. The formula itself is valid, but P500 is not in the lookup list. So, Excel returns the NA error. In row 8, the error type is num, which means Excel was asked to produce an invalid numeric result. In D8, the example formula is attempting to get the square root of -1. And because the square root of negative one is not a valid real number result in Excel, it returns the num error. Now let's take a look at how to handle these errors. There are two main error handling functions we can use here. The first is if air and the second is if na. Both use the same pattern where the first argument must be any value and the second argument is what value should be displayed if the first value is an error. However, if error catches any Excel error type, while if NA only catches NA errors. In E3, we can see how if error is used to catch the div zero error and display a more appropriate message. The same pattern is displayed in E4 to E8 to handle the other error types as well. Now, let's look at the cells in F3 to F8, which all contain identical formulas to the cells in E3 to E8, except that they use if NA instead of if. Notice that all the errors are still passing through, except for the NA error. So use if error when any error should lead to the same safe fallback and use if NA when you specifically expect lookup style not found results and you do not want to hide other issues. In many lookup heavy models if NA is the better default because it preserves the difference between no result and broken logic. By understanding if error and if NA as well as the core error types in Excel, you can more effectively troubleshoot issues either by fixing the root cause or by intentionally handling errors with the appropriate error handling function. In this lesson, we cover Excel's core logic and branching functions. If, and or, not, and ifs. The core idea is simple. A logical formula asks a true or false question. And branching functions decide what to return based on the result. Think in two steps. Test a condition, then return an outcome. Let's start with if. It's the foundation. In cell E3, we want to label a score as pass or fail. The formula is equals if D3 greater than or equal to 70, pass, fail. Read it as if D3 is at least 70, return pass. Otherwise, return fail. if always follows the same pattern. Test value of true, value of false. Notice that the same formulas are used in each row, but they are applied to different student inputs. So the results change based on the data. So that formula only required evaluating one condition. But often one condition isn't enough. That is where and comes in and returns true only if every test is true. In cell F3, allegibility requires both a passing score and at least 80% attendance. So we write equals if open parenthesis and open parenthesis D3 greater than or equal to 70 C3 greater than or equal to 0.8 8 close parenthesis alleible comma not allegible close parenthesis. The and function produces a single true or false result which if then uses to branch or is different or returns true if any test is true. In cell G3, we want the tutoring flag if a student either scored below 70 or attended less than 75%. So we write equals if open parenthesis or open parenthesis D3 less than 70 comma C3 less than 0.75 close parenthesis tutor comma okay close parenthesis. Use and when every requirement must be met. Use or when any condition is enough. Next is not, which flips true to false and vice versa. Use it when you need the opposite of a simpler condition. In cell H3, we want to mark students who are not exempt from the final. Column B says yes for exempt students and no for students who must take it. The formula is equals if open parenthesis not open parenthesis B3 equals yes close parenthesis final required comma exempt close parenthesis. Since B3 equals yes is true for exempt students not reverses it letting us branch from the opposite condition. Sometimes you need more than two outcomes. You can do this by nesting if functions, which is common in real spreadsheets. This works by placing another if inside the value if false part. So, Excel keeps checking conditions until one is true. Ifs is the more modern option and is usually easier to read. In cell I3, we assign a letter grade from the score using ifs. The formula is equals ifs D3 greater than or equal to 90. A otherwise D3 greater than or equals to 80. B otherwise D3 greater than or equal to 70. C otherwise D3 greater than or equal to 60. D otherwise true. and then return f. Ifs evaluates tests in order and returns the value for the first true result. So order matters for ifs. Start with the highest threshold first. The final true, f acts as a catchall case when none of the earlier thresholds are met. In column J, you can see the nested if version of the same logic. It gets the same result, but it is a little harder to read, which is why I ifs is usually preferred when it is available. To recap the lesson, if handles a single branch and combines tests when all must be true, or combines tests when any can be true, not reverses a test. Ifs handles many branches in sequence. When building logical formulas, first decide what question Excel should ask. Then decide whether the answer depends on one condition, multiple required conditions, any acceptable condition, the opposite of a condition, or several graded outcomes. In this lesson, let's cover the core aggregation functions in Excel. Aggregation means taking a group of cells and summarizing them into a single result. Instead of reviewing values one by one, you can ask Excel for a total, average, minimum, maximum, or count. These are some of the most commonly used formulas because they turn raw data into quick summaries. We'll use one simple example to demonstrate most of them based on the sales value in cells B3 to B13. Notice this list includes duplicate values, one blank cell, and one text entry free. This is useful because several functions behave differently with blanks and text. Let's start with the most basic summary, the total. To compute the total, use the sum function over the full B3 to B13 range. Sum adds all numeric values in the range and ignores blanks and text. So it gives us the combined sales amount across the whole list, which is 1,130. Next is average, which follows the same syntax as sum, except the name is, of course, average and not sum. Average sums all the numeric values in the range and divides by the count of numeric values. A key detail is that blank cells and non-numeric cells are ignored. So even though B3 to B13 contains 11 cells, Excel divides only by 9 based on the nine numeric entries. That gives us an average of 125 and some change. This makes average particularly useful when your data has missing values or text mixed in and you don't want those cells affecting the calculation. If you want the medium instead, the function again follows the same structure but with the name median. Here it returns 120. Next, if you're interested in the most frequent value, use mode. 120 is returned here because it appears a record three times in this list of sales. Note that modesgl is equivalent to mode and can be used in its place as well. To find the smallest and largest numbers, use min and max which correctly return 95 as the minimum and 200 as the maximum. These are useful for quick range checks like finding the lowest score, highest sale or earliest and latest dates. So far all of those functions summarize the actual numeric values. Let's now let's switch to counting functions because these summarize what is present in the cells rather than the values themselves. Let's start with count which returns the count of numeric cells. Since one cell in our range is blank and another contains text, count returns nine. Count ignores both blanks and text. If we wanted to instead count all non-MPY cells, we could use count A on B3 to B13, which will include the free value and return a count of 10. And finally, count blank counts blank cells in the range. In this example, it returns one. So, count, count day, and count blank give you three different ways to describe completeness in a data set. How many numeric entries exist, how many cells are filled at all, and how many are empty. These three count functions and especially the other six functions we looked at are simple but very prevalent in Excel and they help form the foundation for more advanced analysis. Before we jump into the next lesson, we have actually made it to a key milestone in this course. With the lessons we have completed already, we have now learned enough to be able to complete our first core problem on the site. To find the problem, go to problems and make sure you're under the core problem section. The first problem here, score compared to middle, is the one we should now be able to solve. Its difficulty rating is easy, but again, even an easy core problem is a lot trickier than any of the intro problems. By the end of the course, you should have enough knowledge to solve any of the free core problems. But for now, let's try our luck with this first one. Okay. So, the problem description says, update the output cells so that each student's status indicates whether their score is above, below, or equal to the middle score of all scores. The middle score is defined as the score that would appear in the center if all scores in the list were sorted from smallest to largest. For each row, return above if its score is greater than the middle score, below if it is less than the middle score, and equal if it matches the middle score exactly. If you want to try out this problem for yourself, I encourage you to pause here. Otherwise, let's jump into the solution. Okay. So the first thing to realize is that when the description says middle score based on the definition they are giving for middle score what they are really describing is the same as a median score with median being one of the core aggregation functions we learned about. The next thing to realize is that for each student we are comparing their score to this median score and based on that giving them one of three possible statuses. So, because there are three possible outcomes, this seems like a good candidate problem for using ifs, which we learned about in our logical functions lesson. Piecing this together, the formula for the first self could look something like equals ifs the first student score is greater than the median of all scores, return above. Otherwise, if the same student score is less than the median of all student scores, return below. And finally, otherwise, if it's not above and it's not below, we know it's exactly equal to, in which case, we don't even actually need a logical test here. We can just set it to true, so it always fires as the final fallback. And in that case return equal. That gives us below here. Now before we drag this down for the other students, let's remember our lesson on cell referencing. So here we want the individual student score to drag down as well with the cell reference adjusting as we drag down. However, for the median calculations, that already includes all the student scores, and we don't want that range dragging down as we go down. So, for both of those references, let's lock in the rows. Now, we can drag that down. And if we go to the last student, grace, we can see the individual score reference adjusted, but the range for all scores inside of median stayed fixed, which is what we wanted. All right. Now, if we submit this, we should see we got the answer right. Perfect. We just completed our first core problem, which required us to piece together learnings from several of the lessons. In this lesson, we'll look at conditional aggregation. Conditional aggregation means you don't summarize an entire range blindly. Instead, you define which rows qualify, then aggregate only those rows. This has two parts. Choosing the aggregation and writing the criteria. Let's start with functions ending in ifs. They're the most useful in practice. They apply one or more conditions before returning a result. In the summary table in cells B12 to I17, the first example is countifs. The formula is count ifs C3 to C10, L7, F3 to F10, L8. L7 contains east and L8 contains closed. Count ifs counts rows that meet all conditions. Excel checks region for east and status for closed. Three rows match. So the result is three. The mental model is simple. Criteria range criteria criteria range criteria and so on. Now compare that to sum ifs. The formula is equals sum ifs i3 to i10 C3 to C10, L7, comma F3 to F10, L8. The filtering logic is the same as count ifs. Region still has to equal east and status still has to equal close. But sum ifs needs one extra input. It needs the sum range first. In this case, i3 to i10 in the revenue column. This tells Excel what values to sum. So count ifs asks how many rows match while sum ifs asks what total should be returned. Here the matching revenues values are 240, 150 and 180. So sum ifs returns 570. That's the key difference. Once this clicks, the rest is easy. Average ifs, maxif, and minifs follow the same pattern as sum ifs. They also start with the aggregation range and then the criteria range and criteria pairs come after that. Only the aggregation changes. Average ifs averages the matching values. Maxifs returns the largest and minifs returns the smallest. Using the same east and closed filters, those three functions return 190, 240, and 150. The summary table also shows the older single criteria equivalents. Count, sum, and average each have one. Count if, sum if, and average if. But max and min do not have a max if and minif. Those do not exist in Excel. In practice, there is not really a need to ever use count if, sum if, or, or average if yourself because the ifs versions already handle a single condition just fine. So, you can absolutely use count ifs, sum ifs, and average ifs even when there's only one condition. But you'll still see them in existing spreadsheets. So, it is good to be aware of them and be able to read them. Count if takes a range and a criteria. Sum if takes a criteria range, a criteria, and then a sum range. Average if takes a criteria range, a criteria, and then an average range. With that overview out of the way, let's now look at criteria syntax itself. The examples in row 20 through 28 all use countiffs for illustration, but the way the criteria are written is the same across all functions. Start with exact text criteria in row 20. The formula count ifs B3 to B10, apple counts cells in the product column that exactly equal Apple. Text criteria must be in quotes. Row 21 shows exact numeric criteria. Count ifs I3 to I10, 100 counts how many revenue cells equal exactly 100. Plain numbers don't need quotes. Row 22 introduces logical operator criteria. Countifs i3 to i10, greater than 100 counts how many revenue values are greater than 100. If you use an operator, the criteria must be text inside quotes. Row 23 shows cell reference concatenation. countifs I3 to I10, greater than amperand, L4. That's why we use an amperand. The operator stays as text and we join it to the cell reference. If L4 changes, the criteria updates. Row 24 shows date criteria. Countiffs E3 to E10, comma, greater than or equal to, amperand, L5. Dates follow the same rule. You can build them as text or more safely concatenate with a cell or date function. Rows 25 and 26 cover blanks and non-blanks. Countifs G3 to G10, blank in quotes, counts blank cells. Count ifs G3 to G10 comma less than symbol greater than symbol inside quotes counts non-blank cells. Two quotes means blank while less than and greater than in quotes means non-blank. Rows 27 and 28 show wild cards which are special characters that let you match patterns instead of exact text. Countiffs B3 to B10, apple asterisk uses an asterisk which means any number of characters. So it matches apple, apple juice and apple pie. Countifs D3 to D10, L6 uses the pattern capital A question mark lowerase A from cell L6. A question mark means exactly one character. So it matches Ava and Anna with one N but not Anna with two N's. Once you understand how to write criteria using text, numbers, operators, and wild cards, you can control exactly which rows are included. From there, conditional aggregation is simply about choosing what to calculate and applying those criteria. In this lesson, let's build the core mental model behind Excel lookups. A lookup starts with something you know like an SKU or product name and uses it to return a related value from somewhere else. Let's start on the XLOOKUP basic sheet. At the top of the sheet, cell B2 shows the full XLOOKUP syntax. The arguments highlighted in red on this sheet are lookup value, lookup array, return array, and if not found because those are the pieces we are focusing on first. In the product master table from B5 to F12, column B holds the SKU and column C to F hold related fields such as product, category, price, and region. In this first example, the goal is to start with a product name and return its SKU. That is useful because it shows XLOOKUP does not have to work left to right, which is the case for functions like VLOOKUP. In cell I6, the formula is XOOKUP I5, C6 to C12, B6 to B12. Here I is the lookup value meaning the thing we know and want to search for. C6 to C12 is the lookup array meaning the list Excel searches through. B6 to B12 is the return array meaning the values Excel gives back once it finds a match. Although in this example and in most practical scenarios the lookup array and return array are adjacent, this does not have to be the case for Xookup to work. The key idea is position, not physical adjacency. Xookup finds the position of the match inside the lookup array, then returns the value from the return array at that same position. Here i5 contains marker inside C6 to C12. Marker is the fourth item. So, Excel returns the fourth item from B6 to B12 which is P103. That is the base mental model. Lookup value lookup array return array. It is also worth noting that the lookup is case insensitive. So even if we change marker to marker in all caps, we still get the same result. Just below it, I7 adds the optional if not found argument which is the fourth argument. Here it is set to not found. If we change marker to a product that does not exist, such as table, you can see how the not found argument comes into play. It displays the not found fallback text we set in i7. But for i6, it just returns an NA error since no fallback was set up. Another cool feature of XLOOKUP is that it can also return more than one cell at once. In the XLOOKUP array return section, H11 uses XOOKUP P105, B6 to B12, C6 to E12. The lookup array is now the single vertical SKU list B6 to B12. And the return array is now the three column block C6 to E12. Because the lookup array has seven rows, the return array also has seven rows. So the shapes are compatible. Once Excel finds P 105, it returns the full matching row from the return array and spills mouse, electronics, and 24.99 across H11 to J11. Before moving on, the rules table makes the structural requirements explicit. The lookup array must always be one-dimensional, meaning one row or one column. For a vertical lookup, such as the ones we have been looking at, the return array must have the same height as the lookup array, but it can return one column or many columns. For a horizontal lookup, the return array must have the same width as the lookup array, but it can return one row or many rows. The lookup and return arrays also do not need to be adjacent and they do not even need to come from the same table or the same sheet. They just need compatible dimensions so the same position can map correctly from one array to the other. The examples we looked at so far are for vertical lookups. Now let's look up an example for a horizontal lookup in J15 which looks very similar. The formula is XLOOKUP H15, B5 to F5, comma, B9 to F9. H15 contains price. The lookup array B5 to F5 is a horizontal header row. So this is a horizontal lookup. Price is the fourth item in that row. So Excel returns the fourth item from B9 to F9, which is 3.25. Now let's move on to the XLOOKUP match mode sheet and discuss how match mode changes what Excel is willing to treat as a match. At the top of the sheet, B2 shows the same XLOOKUP syntax again and match mode is highlighted in red because that is the argument we are focusing on. Now the reference table in E4 to F9 lists the four options. A match mode of zero means exact match and that is the default. The examples we have looked at so far all use this default. Again, even an exact X lookup is still case insensitive. So marker in lowerase and marker in uppercase would be treated as the same text. Now let's look at the other three match modes which can all be thought of cumulatively as approximate match modes since they don't require an exact match. A match mode of negative -1 means exact match or next smaller. And a match mode of one means exact match or next larger. Those two match modes mainly make sense in numeric scenarios such as dealing with threshold levels. In J6, XLOOKUP with match mode negative1 looks up 175 and returns silver because 100 is the largest threshold that does not exceed 175. In J7, match mode one on that same lookup value returns gold because 200 is the next larger threshold. An important advantage here is that unlike the approximate match mode behavior in older functions such as VLOOKUP and HLOOKUP, these XLOOKUP match modes do not require the lookup data to be sorted in order to work correctly. Finally, match mode 2 means wildcard match, which really only makes sense in the context of text. In J8, plat asterisk means any text that starts with plat. So, Excel searches the tier names and finds platinum and returns the corresponding minimum spend of 500. So, match mode changes Excel's definition of a valid match. And you only really need to use it when you don't need an exact match. Now, let's go to the XLOOKUP search mode sheet and discuss how search mode changes how Excel moves through the lookup array. At the top of the sheet, B2 shows the XLOOKUP syntax once more and this time the red highlighted argument is search mode. The reference table in F4 to G9 shows the options. Let's look at the first two options. One means first to last and that is the default. Negative one means last to first in the duplicate SKU table from B6 to D10. P 100 appears three times with different statuses. In J6, we search for that status of P 100 using the default first to last search mode. This returns open because Excel finds the January record first. In J7, the same lookup uses search mode negative one and returns refunded because Excel starts at the bottom and hits the May record first. So when dealing with duplicates, the default search mode will return the first record found in the array and the negative one search mode will return the last record found in the array since it starts its search from the bottom. The other two search mode options are binary ascending and binary descending. Their purpose is speed. On very large sorted data sets, binary search can massively reduce lookup time, but the tradeoff is that the data must be sorted exactly the right way. If it is not, the result can be wrong without any obvious warning. Binary search is also not a good fit when duplicates matter because with duplicate keys, you should not assume which matching row Excel will land on. So binary search modes are advanced performant options, not general safety defaults. Now that we have fully covered XOOKUP, let's move on to the X match plus index sheet and discuss why these two functions are so often paired together for lookups. At the top of the sheet, B2 shows the X match syntax and B3 shows the index syntax. X match is a position finding function. It works by taking a lookup value and a one-dimensional lookup array, searching that array, and returning the relative position where the match is found. Xmatch also supports the same optional match mode and search mode arguments as XOOKUP. And it uses the same default behavior of exact match for match mode and first to last for search mode. Index does a complimentary job. It takes an array, a row number, and optionally a column number and returns the value sitting at that specific position inside the array. Index also works with one-dimensional arrays. In that case, you're really just giving the position within the single dimension, which would be the row if the array is vertical or the column if the array is horizontal. That pairing is exactly why the two functions work so well together. XMatch figures out a lookup position to target and index uses that position to retrieve a value. Let's start looking at some examples. In the sales table, quarterly sales metrics are shown by product. In I8, the formula is X match I6, B7 to B10. You can read that as look up the value in I6 which is marker inside the vertical array B7 to B10 and return the position where a match is found. This returns four meaning marker is the fourth item in that vertical array. In I9 X match is instead used to look up the position of Q3 inside the horizontal quarterly headers array spanning from C6 to F6. and the formula returns three. Meaning Q3 is the third item in that horizontal array. Now let's see how we can combine both of these formulas with index in order to find Q3 marker sales. In I10, we write index C7 to F10, the X match marker lookup we just saw, the X match Q3 lookup we just saw. This returns 120 which we can see is correct. So the row position comes from one X match. The column position comes from the other X match and index uses both positions to retrieve the intersecting value from the array it is given. That is why index plus X match is especially useful for two-way lookups. The nested XLOOKUP version in I11 achieves the same result, but index plus Xmatch makes the row logic and column logic more explicit. Now we will move on to the legacy lookup function sheet to discuss some older functions you will still see in real files. At the top of the sheet, B2 to B4 show the formula syntax for VLOOKUP, HLOOKUP, and match. It helps to think about their jobs separately. VLOOKUP is a vertical lookup function. It searches for a value in the first column of a table and returns a value from another column in that same row. HLOOKUP is the horizontal version. It searches for a value in the first row of a table and returns a value from another row in that same column. Match is the predecessor to X match and is also used to find the relative position where a match is found inside a one-dimensional lookup range. In the legacy lookup reference table, we have example usages of the three functions. I8 uses VLOOKUP to return 95 for notebook Q2 sales. It does this by searching for notebook in the first column of the table B8 to F11, finding the notebook row, then returning the value from the third column of that table, which is 95. I9 uses HLOOKUP to return 140 for Q2 pen sales. It does this by searching for Q2 in the first row of the table B7 to F11, finding the Q2 column, then returning the value from the second row of that table, which is 140. I 10 uses match to return the position of stapler in the product list by searching B8 to B11 for an exact match and returning three because stapler is the third item in that range. The next thing to understand is how these older functions control matching behavior. For VLOOKUP and HLOOKUP that is handled by the range lookup argument. A value of false or zero means exact match. A value of true or one, which is also the default if you leave it out, means exact match or next smaller. And that only works safely when the lookup list is sorted in ascending order. That default is usually impractical except for niche breakpoint style lookups. So with VLOOKUP and HLOOKUP, it is usually safer to be explicit and use false for exact matching. Match uses a very similar idea, but its argument is called match type instead. A match type of zero means exact match. A match type of one, which is the default, means exact match or next smaller and requires ascending sort. A match type of negative -1 means exact match or next larger and requires descending sort. So even though these older functions can still handle exact and approximate matching, their defaults are less intuitive and their rules are easier to misuse than Xookup and X match. These functions still work and you'll absolutely encounter them, but in modern Excel, they shouldn't be needed. Xookup makes VLOOKUP and HLOOKUP obsolete because it handles both vertical and horizontal lookups with far more flexibility. For instance, Xookup can look left or right, up or down, return a single value or an entire spill range, and lets you define a friendly fallback when nothing is found. Whereas VLOOKUP and HLOOKUP are locked into much narrower search patterns. X match makes regular match obsolete because it supports the same core idea with better match options and search options. So the practical takeaway is this. Use XOOKUP for the most direct modern lookup. Use index plus X match when a two-way lookup or positionbased thinking is cleaner. And learn to read VLOOKUP, HLOOKUP, and match because they still appear in real workbooks even though they are usually not the best choice for new models. In this lesson, we'll cover text normalization and comparison. Messy text data is all too common in Excel. Values may contain extra spaces, inconsistent capitalization, or different code formats. The goal of normalization is to turn inputs into a consistent format because they are used in formulas. Let's start off with some text normalization basics by looking at the first table up top. Column B contains raw customer names. In column C, we use the trim function to remove leading spaces, trailing spaces, and extra spaces between words. Leading spaces are blank spaces before the first visible character, like the spaces before acme and B3. Trailing spaces are blank spaces after the last visible character, like the spaces after LTD in B3. Both are easy to miss because cells can still look normal. Trim also reduces the double space between fabricam and west in B5 to a single space. Next, lower and D3 converts the trimmed value to lowercase. Upper and E3 does the opposite and converts the text to all caps. Lower and upper are useful when capitalization should not matter in which case standardizing on all caps or all lowercase makes things easier to work with. Finally, proper and F3 capitalizes the first letter of each word which is useful for display names though you should be careful with names or brands that intentionally use unusual capitalization. Now let's look at the next table starting in row seven. This table focuses on code cleanup. The substitute function replaces matching text with different text. At a high level, use substitute when you know the exact characters you want to find. Its arguments are the text to work on, the old text you want to find, the new text you want to put in its place, and then an optional instance num argument. If you leave instant num blank, substitute replaces every occurrence of the old text. If you provide instance numbum, substitute replaces only that specific occurrence. In C8, we use substitute on the raw code. In B8, the old text is the dash and the new text is an empty string. So the dash gets removed and old-1 becomes old 1001. The replace function is different. Replace edits text by position instead of searching for matching characters. At a high level, use replace when you know at what character position the edit should start and how many characters should be swapped out. Its arguments are the text to edit, the start position, the number of characters to replace, and the new text to insert. In D8, we use replace on the clean code in C8. The start position is one, the number of characters is three, and the new text is SKU. So the old prefix gets replaced with SKU. That forces codes like old 1001 and TMP 1002 into the same prefix pattern. Let's move on to the third table where we'll look at the text function. The order dates in column B are real Excel date serial numbers. The text function converts a value into text using a format you choose. It has two arguments, the value you want to format and the format text that tells Excel how the result should look. In C14, the value is B14 and the format text is mmmm space d, space y. That format turns the serial number into a readable label like January 15th, 2024. The important thing to remember is that text function returns text not a real date or number. So it is best for labels, exports and display formatting. Finally, the last table compares the regular equals operator with the exact function. Column D uses the direct equals comparison like B19 equals C19. Column E uses exact on that same pair. In row 19, urgent and urgent in all caps return true with equals because equals is not case sensitive, but exact returns false because it does care about casing. Rows 20 and 21 are the intuitive cases where there is no difference between what is returned by a direct equals versus the exact function. When both input values are the same, even accounting for casing, both comparisons return true. When both values are outright different, even ignoring casing, both comparisons return false. So to recap this tutorial, normalize text before you rely on it by using trim and the various case functions to remove common noise. Use substitute or replace to standardize codes. Use text when the final output needs a specific text format. and choose between direct equals comparison and the exact function based on whether capitalization should matter or not. In this lesson, we'll focus on searching inside text and extracting the pieces you need. The mental model is position first, extraction second. Find and search tell you where something appears. Left, right, and mid use positions to return part of the text. len tells you how long the text is, which is especially useful when the number of characters can change. In the workbook, column B has structured document codes, column C has email addresses, column D has a region search term, and column E has a note. Let's start with find and F3. Find locates one text string inside another and returns the character position. In this example, find returns the position of the first dash inside the code. Find is case-sensitive, so it is best when capitalization matters. Search is similar, but it is not case-sensitive. In G3, search looks for the lowercase region term from D3 inside the note in E3. Even when the note starts with an uppercase version of the word, search still finds it. It is also worth noting that both find and search have an optional third argument called start num. It tells Excel which character position to begin searching from. If you leave it out, Excel starts at character one. Setting start num is useful when the text contains the same delimiter more than once and you want to skip an earlier occurrence. However, note that the position returned still counts from the beginning of the full text, not from the starting position. Moving on. Once you know how to locate text, extraction functions are more straightforward. Left returns characters from the beginning of a string. In H3, left returns the three-letter document type from the code. Right returns characters from the end. In I3, right returns the final three-digit identifier. Mid starts in the middle. In J3, mid starts at character 10 and returns two characters, which pulls out the region code. Len in K3 counts the total number of characters in the code. It sounds simple, but len is often what lets you build formulas that adapt when the length changes. The email examples combine searching and extracting. In L3, left uses find to locate the at sign and subtracts one from that in order to return everything before the at sign. That gives us the username. In M3, MID starts one character after the at sign and uses len to return the rest of the address, aka the domain portion of the email. This is a common pattern for text extraction. Use find and search to locate the delimiter. Then use left, right, mid, and len to pull the segment you want. When the delimiter is known and the text is consistently structured, these formulas let you turn long labels, codes, and email fields into usable columns. In this lesson, let's build a practical foundation for working with dates in Excel. The first thing to remember is that an Excel date is stored as a serial number. Date formatting makes that number appear as a recognizable calendar date, but the underlying numeric value is what lets Excel compare dates and perform date arithmetic. For instance, if we set the formatting of the date in C7 to general, we can see the actual serial number for that date, which is what Excel actually cares about under the hood. In this case, it is 46,249. What this technically represents is the dates position within Excel standard 1900 date system where January 1st, 1900 is a serial number one and each subsequent day increases the number by one. One historical quirk is that Excel treats 1900 as a leap year even though it was not. So these serial numbers should be understood as positions in Excel's date system rather than a perfect count of elapsed dates because the system begins in 1900. Earlier dates are not supported as normal Excel date values. Excel will generally treat them as text which means standard date arithmetic and functions will not work with them without a workaround. All right, moving on. Let's look at the core date logic section at the top of the sheet. Cells C4 through C6 contain the year, month, and day inputs 2026, 2, and 28. In G4, the formula is date C4, C5, C6. Date takes its arguments in year, month, and day order and returns a real Excel date from that. Here the result is February 28th, 2026. This is a reliable way to build a date when the pieces live in separate cells. It is also safer than typing a datel lookinging text value into a formula because text dates can be interpreted differently depending on regional settings. Also note that the date function can normalize inputs that cross a normal boundary. For example, month 13 would roll into January of the following year. That behavior can be useful, but in most ordinary models, the inputs should still represent the year, month, and date you intend. So the date function lets you go from a year number, month number, and day number to a date. But what if we want to go in the other direction? That's where the year, month, and day functions come into play. In G5 through G7, these functions are used to return the year, month, and day from the August 15, 2026 date stored in cell C7 by taking that date serial number as the single argument. The final core function is week date. In G8, the formula is weekday C7, 2. Weekday converts a date into a number that identifies its day of the week. Its first argument is the date. Its optional second argument return type controls the numbering system. We use return type two because it gives a straightforward business week pattern. Monday is 1, Tuesday's two, and so on all the way until Sunday is 7. August 15th, 2026 is a Saturday, so the formula returns six. If you omit the return type, Excel uses a different default where Sunday is one. So it is worth being explicit whenever other formulas depend on the weekday number. The result from weekday is especially useful inside logical tests. For example, with return type two, any result greater than five represents a weekend. If you're curious about all the return type options, they are listed on this sheet just off to the right here. Let's move down to the business day logic section now and look at the workday and network day functions which are two functions which are useful for calculations involving business day deadlines, schedules and elaps working time while excluding weekends and holidays. The workday function lets us move from a given start day forward by a given number of business days. A business day is any day that isn't a weekend or holiday. It works by taking a start date as its first argument, then days as its second argument, which is the number of work days to move forward, and holidays as its optional third argument, which is the dates that need to be considered holidays during its calculations of the return date. In G13, we can see it in action here. is used to get the date that is 10 business days ahead of the start date in C13 while accounting for the two holidays in C15 to C16. Of these two holidays, only August 24th affects the result because it falls on a weekday within the calculation period. September 7th falls outside that period and therefore has no effect. The result of the formula in this case is September 1st, 2026. The next function to look at is network days which handles a complimentary task. Instead of returning a date after a number of work days, it returns the number of work days between two dates. It takes a start date, end date, and optionally a final holiday argument. In G14, it is used to get the number of work days between the start date in C13 and the end date we just computed in G13 while accounting for the same two holidays as before. This returns 11 which may come as a surprise given in the workday function we had only added 10 working days. The difference comes down to how each function treats the start date. Work day does not count August 17th itself as one of the 10 days being added. It begins counting with the next allegible work day. Network days however includes both its start and end dates when they are valid work days. It therefore counts August 17th plus the 10 work days traversed by workday, producing a total of 11. Finally, let's move down to the month logic section and look at EO month and E date, which perform month-based date calculations while accounting for differences in the number of days each month contains. Let's start with EO month, which is short for end of month. It takes a start date as its first argument and a number of months as its second argument. It then moves forward or backwards by that number of months and returns the final day of the resulting month. In G20, EO month takes the January 15th, 2026 date in C20 and uses zero for its months argument. A value of zero means to stay within the month containing the start date. So the formula returns January 31st, 2026. In G21, the month's argument instead references C21, which contains 1. This moves the calculation forward by one month and returns the final day of February, which is February 28th, 2026. Positive month values move forward while negative values move backward. Eate takes the same two arguments, but behaves a little differently. Instead of returning the end of a month, it shifts the start date by the specified number of whole months while preserving its day of the month whenever possible. In G22, E date moves the January 15th date in C20 forward by one month specified in C-21 returning February 15, 2026. So the key distinction is that EO month moves to a month boundary while E date moves to the corresponding date in another month. If the target month does not contain the original day, EATE returns its last valid day instead. For example, moving one month forward from January 31st, 2026 would return February 28th, 2026th since February 2026 only has 28 days. To recap this lesson, date constructs dates from their individual components, while year, month, and date extracts those components from an existing date. Week date identifies the day of a week. Workday and network days handle calculations that exclude weekends and holidays. And EO month and E date handle month-based date calculations. In this lesson, we will use three worksheets to learn four modern dynamic array functions. Filter, sort, sort by, and unique. These functions change formula design because one formula can return an entire range. You enter it in one anchor cell and Excel spills the result into neighboring cells. When the source data or criteria change, the spill can grow, shrink, or reorder itself. That lets us design around arrays and combine functions directly. Instead of copying formulas down, building helper columns, or reserving a fixed size output area, start on the filter sheet. Cell B2 shows the syntax. The arguments are array, include, and optionally if empty. Array is the range you want filtered to return. Include is a matching true false array that determines which rows or columns are kept. and if empty specifies what to return when nothing matches. Let's look at a couple of examples now where we will filter on the order table in B4 through G14. The input panel is in I4 through J6. We'll start off by filtering based on a single condition in I10. The anchor formula is filter B5 to G14, comma, C5 to C14 equals J5, comma, no matches. B5 to G14 is the area to return data from. It contains all six columns. So every matching row comes back in full. C5 to C14 equals J5 is the include test. Here we are telling the filter function to only include rows where the region matches the filter input region of east defined in J5. Note that the filtering criteria is specified differently here compared to how it is specified for conditional aggregation functions in Excel such as sum ifs for instance. Here the way we specify filter criteria is by comparing the entire region range directly with the value in J5. Excel evaluates that comparison rowby row, producing an array of true and false values. Filter then returns the source rows corresponding to true and excludes those corresponding to false. Finally, the last argument supplies a friendly message if no rows qualify. In this case, four records have east in the region column. So the single filter formula entered in I10 spills a four row six column result set. Now let's look at a multiple condition example in B18. The formula is filter B5 to G14, C5 to C14= J5 multiplied by G5 to G14 is greater than or equal to J6, no matches. The first condition filters for orders where the region is east. The second filters for orders where the revenue is at least 900. Each comparison independently produces 10 true or false results and should now be fairly straightforward. However, the fact that these two conditions are multiplied together is not as intuitive. So, let's break that down. The helper area in I16 through L27 makes the calculation visible. Column I carries the order ids. Column J shows the region test. Column K shows the revenue test. And column L multiplies the two results together row by row. When Excel performs arithmetic, true behaves like one and false behaves like zero. That gives us four possible combinations. True * true is the same as 1 * 1 and produces one. However, any of the other three combinations, be it false time true, true * false, or false time false will result in at least one zero being included in the multiplication, which results in a product of zero. Since anything multiplied by 0 is zero. So, the result of these two arrays multiplied together is a new single array of the same size, but only including ones and zeros. Each one aka true tells filter to include the row and each zero aka false tells filter to exclude it. Orders 1001, 1003, and 10,0 pass both tests. So B18 spills those three complete records through G20. Note that the dimensions matter here. The source array has 10 data rows. So each include range must also have 10 rows. Excel needs exactly one include decision for every source row. The tests do not have to come from adjacent columns, but they must align with the array being filtered. So you can actually follow this same pattern with any number of conditions. If you have five conditions that all need to be true, set up each condition so it'll produce the array of trus and false and then multiply all those conditions together to filter for only the rows that meet all conditions. Now let's move on to the sort and sort by sheet. The sort syntax is in B2 and the sort by syntax is in B3. Let's start with a standard vertical sort example where we want to sort the orders table by highest revenue to lowest revenue. In B19, the formula is sort B6 to G15, 6, -1. The array is a six column order table without its header row or priority score column. The sort index is six because revenue is the sixth column inside the selected array. Negative -1 means descending. So the largest revenue appears first. A sort order of one would mean ascending. The result is that the source table remains in its original order while the sorted result spills from B19 through G28. If a revenue changes, the formula recalculates and the affected rows move automatically. For instance, if we set order 1007's revenue to 5,000, notice how it jumps to the top of the sorted array. Note that in this example, we emitted the final optional by column argument. The by column argument changes the direction in which sort rearranges the array. False sorts whole rows from top to bottom and is the default if the argument is emitted like in this case. True sorts whole columns from left to right. Let's look at an example that uses true here in cell K11. Here the formula is sort K6 to N8, 2,1, true. This formula is helping us sort the quarterly revenue table in K6 to N8 by highest revenue to lowest revenue. To achieve this, it sets the by column argument to true and the sort index to two, which now means sort by the second row, not the second column. So the meaning of the sort index argument actually changes depending on whether the by column argument is set to true or not. If it is set to true, it represents the row to sort by. Otherwise, it represents the column to sort by. Now, let's compare sort with sort by. Sort is convenient for a simple positional sort. Sort by can produce the same result, but its syntax makes it a little less convenient, although more powerful. With sort, we just specify the column or row index we want to sort by. With sort by, we reference the actual range that controls the order. This actually lets us even sort by a range outside the array being returned if needed. Additionally, while sort restricts us to sorting by only one range, sort by lets us sort by multiple ranked ranges. This is useful when you want to sort within records that are tied based on the first sort criteria. For instance, let's look at a sort by example in J19. The formula is sort by B6 to G15, comma, D6 to D15, comma 1, comma, H6 to H15, comma1. B6 to G15 is still the array returned. D6 to D15 is the rep range, and one sorts reps in ascending order alphabetically from A to Z. H6 to H15 is the priority score range and negative 1 sorts the scores in descending order from high to low. Note that this priority score actually lives just outside the target array that we want to have displayed. The result of the formula is that sort by applies these sorts in order. It first groups the records by rep and when two or more rows have the same rep, it uses priority score as the tiebreaker. For instance, for Avis 3 records, we can see order 1005 appears first because it has the highest priority score of 94, followed by order 108 with a priority score of 82, and finally followed by order 101, which has the lowest priority score of the three at 72. The view displays the six required columns, emitting the priority score column while still using it to determine the sort order. This is something that could not have been achieved with just the sort function directly. All right, now let's go to the final unique sheet. Cell B2 shows the syntax for unique, which is a formula used to return the distinct values, rows, or columns from a range. The arguments are array, optionally by column, and exactly once. Let's start with the one-dimensional list in B6 through B13, which contains some repeated product names. Mouse appears three times and monitor appears twice. In B17, we are able to wrap unique around the whole range to pull out just the distinct values in the order in which the products first appear. As with the other functions we had looked at, the five item result spills down. If a new distinct product is added to the source range, the spill can expand. For instance, if we change one of the source mouse values to rat, we can see the spill range now includes rat and the final tablet value goes past the formatted region. One thing we haven't covered yet is how we can reference a spill range which is dynamically sized and can change shape. Let's do that now. In B26, the formula is count a B17 hash sign. The hash sign tells Excel to include the entire spill range generated by the B17 anchor cell, not just B17 by itself. The result is a count of six. However, notice how if we set the rat value back to mouse, the formula automatically updates and the count is now five again. Let's see how unique can also compare complete records in a two-dimensional array by operating on the source data in D5 through F13 which contains repeated SKU region and status rows. In D17, we wrap unique around these source rows. Because by column is omitted, Excel uses false and compares full rows. A row is considered a duplicate only when all values in the row match those of another row. The repeated P100 east open row is returned just once, as is the repeated P101 west closed row. The result spills six distinct full rows. In L16, we can see a use of unique, which turns the comparison sideways by looking for unique columns instead of unique rows. It does this by using the plan source data directly above it for its array argument and critically by setting the by column argument to true. Within the selected array, plan A and plan B each appear twice with identical revenue, units, and refund values, while plan C appears just once. The result keeps the metric column plus one plan A, one plan B, and one plan C column, which all spill over from the anchor L16 cell where its formula lives. Now, let's discuss how setting the optional exactly once argument affects how the unique function works. If this argument is set to false, which is also the default when the argument isn't set, unique returns any distinct record regardless of how many times it appears in the source array, whether it appears once, twice, or even 10 times, while dropping duplicates along the way, of course. However, when this is set to true, unique will only return records that appeared exactly once in the source array. For records that appeared more than once, it drops all such records, not just the duplicates. Column H through J help illustrate this. In the source table in H4 through J13, ticket 101 appears twice with the same status and owner, and ticket 102 also appears twice. Tickets 103 through 106 each appear exactly once. In H17, we wrap unique around this table without setting the exactly once argument. Unique returns one copy of every distinct record. So one 101 record and one 102 record survive along with the 103 through 106 records. In H26, we set the same formula, but this time we set the exactly once argument to true. The result is that records 101 and 102 don't appear at all now. Only records 103 through 106 appear since those are the records that appeared exactly once in the source array. Okay, moving on to the final discussion point. Let's address the spill error which can affect any of the spill functions we have looked at. In cell U6, we attempt to get all the unique values from the products array in S6 to S10. This should lead to four spill values. One for pen, one for mouse, one for keyboard, and one for monitor. However, because cell U8, which is only two cells below the formula anchor cell, contains a value. In this case, the text blocker for illustrative purposes. The use unique function actually returns a spill error instead of the otherwise expected outcome. This is Excel's way of letting us know it can't safely place the results of the formula as there is existing content in the way. To resolve the error in this case, we would have to clear the way for the spill results and remove the text in U8. So whenever you are working with a function that can spill results into neighboring cells, it's important to always allocate enough cleared space below and to the right of it to avoid the spill error. Okay, so to recap this lesson, dynamic array functions let us build formulas that return complete flexible result sets instead of just one value at a time. Filter controls which records are included. Sort and sort by control how those records are ordered. And unique allows us to return distinct values, rows or columns while removing duplicates. Instead of designing formulas one cell at a time, these formulas allow us to define a transformation once in an anchor cell and let Excel manage the resulting array for us. If you made it this far, congratulations. You just completed the full course and have gained that strong foundation in Excel formulas and functions which will benefit you in your career or classes or even personal projects. However, if you want to take it to the next step and really build those Excel brain pathways, I encourage you to try some of the free core problems on the site which are more advanced and practical. Thank you for watching and if you enjoyed the content, please consider leaving a like or subscribing and I welcome any feedback you may have in the comments.
Generated algorithmically for Search Engine
Indexing.