Excel PRO TIPS Part 3: Formulas | Chris Dutton | Skillshare
Drawer
Search

Playback Speed


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

Excel PRO TIPS Part 3: Formulas

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

    • 1.

      Formula Tips Intro

      0:28

    • 2.

      Manual vs. Automatic Calculation

      4:19

    • 3.

      Formula Line Breaks

      4:05

    • 4.

      Converting Units of Measurement

      5:50

    • 5.

      Real-Time Tools with TODAY & NOW

      10:18

    • 6.

      Formula Auditing Tools

      11:18

    • 7.

      Pivot-Style Reports with Formulas

      8:37

    • 8.

      Counting Words in a Cell

      7:24

    • 9.

      Dependent Drop-Downs with INDIRECT

      8:29

    • 10.

      Hyperlinking Between Worksheets

      8:13

    • 11.

      "Fuzzy Match" Lookups

      7:15

    • 12.

      Random Selection with OFFSET & RANDBETWEEN

      6:26

    • 13.

      Combining INDEX & MATCH

      8:40

    • 14.

      Matching Items Between Lists

      11:00

    • 15.

      Counting Duplicates with SUMPRODUCT

      8:59

    • 16.

      Many-to-Many Lookups

      9:53

  • --
  • 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.

455

Students

1

Projects

About This Class

Welcome to Excel Pro Tips for Power Users!

Please note that this is PART 3 of a 6-PART series, and that this is a limited version of the full course. To access additional course materials (including quizzes, 1-on-1 support, and Excel project files), please visit courses.excelmaven.com.

__________

FULL COURSE DESCRIPTION:

This course is NOT an introduction to Excel.

It's not about comprehensive, 101-style deep dives into Excel's core capabilities, or about showing off cheesy, impractical "hacks". It's about featuring some of the most powerful and effective tools and techniques used by Excel professionals, and sharing them through crystal clear demos and unique, real-world case studies.

Unlike most courses, this one is non-linear, meaning that you can bounce around and explore individual lectures freely. Each video is designed to be an independent and self-contained demo, designed to help you learn these tools and techniques in quick, bite-sized lessons.

__________

The tips and techniques covered in the full course fall into six parts:

  • PART 1: Productivity Tips (navigation, flash fill, cell protection, advanced sorting & filtering, etc.)

  • PART 2: Formatting Tips (freeze panes, row/column grouping, custom number formats, etc.)

  • PART 3: Formula Tips (auditing tools, fuzzy lookups, uniques/duplicates, randomization, etc.)

  • PART 4: Visualization Tips (sparklines, filled maps, custom templates, form controls, etc.)

  • PART 5: PivotTable Tips (slicers & timelines, custom layouts, value calculations, etc.)

  • PART 6: Analytics Tips (outlier detection, simulation, forecasting, CUBE functions, etc.)

__________

While the level of difficulty varies considerably, the demos generally start simple and become progressively more complex within each section. There are no strict prerequisites for this course, but keep in mind that some demos may be challenging without a strong foundational knowledge of the underlying concepts (like PivotTables or advanced formulas, for example).

Students who have completed the full Excel Maven stack will be well-positioned to maximize the value from this course:

  • Advanced Excel Formulas & Functions

  • Data Visualization with Excel Charts & Graphs

  • Data Analysis with Excel PivotTables

  • Intro to Power Query, Power Pivot & DAX

__________

It's time to start working smarter, not harder. If you're looking to maximize your efficiency, supercharge your productivity, and become an absolute Excel POWER USER, this is the course for you.

See you in there!

-Chris (Founder, Excel Maven & Maven Analytics)

__________

NOTE: Most demos are compatible with Excel 2007-2016 or Office 365 (some may be unavailable for Mac or Excel Online)

Meet Your Teacher

Teacher Profile Image

Chris Dutton

Founder, Excel Maven

Teacher

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

Level: All Levels

Class Ratings

Expectations Met?
    Exceeded!
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%

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.

Transcripts

1. Formula Tips Intro: all right, welcome to one of my favorite sections formula tips. Now this is one of the more challenging and technical portions of the course and covers topics ranging from basic like calculation modes and auditing tools to quite advanced, like many too many look ups and some product to raise. Keep in mind, that will be moving very quickly, so it may be difficult to keep up. If you aren't already comfortable with the basics, let's dive in. 2. Manual vs. Automatic Calculation: all right. Next tip is a formula tip. This is a one star, pretty basic concept, but it's something that not every Excel user is aware of. And it's how to change your formula. Calculation modes now Excel has two primary calculation modes. Automatic, which is generally your default and manual. So if you head to your formula tab off to the right, you'll see your calculation options. There are actually three different options. They're the two that we're gonna focus on right now. Are automatic, which allows your formalistic calculate on their own with any work of change and manual, which essentially freezes those formula calculations until you decide to refresh a recalculate them. And you can do that by either clicking that calculate now button in the formula tab or using the F nine shortcut. Now that third option automatic, except for data tables, we're gonna talk about data tables in the analytic section of this course. Those are often used to randomize and run a large number of simulations. It's part of excels. What if analysis tools and because they generally iterated over a very, very large number of rows, they can slow things down if they're recalculating constantly. So that third option basically gives you the choice to say, you know, treat all my other workbook formulas as automatic. But let me decide when I want update those data tables specifically. So let's say you have your calculation mode set to manual like we see here, and then you write some sort of function like this. This is the prophet formula that we're gonna write in this demo and basically accounts for quantity, retail price and unit costs. You drag that formula down, apply it to multiple cells and see the same value returned in every case. First place to look is there calculation mode, because nine times out of 10 that means that it's accidentally been switched to manual. But there's good news. It's a very easy fix. All you do is head back to that tab, check the automatic option, and then those formulas will automatically recalculate on their own. So to summarize couple common use cases, in some cases you may have a very large workbook, very formula heavy, using things like look up and index and reference functions, or a raise that iterating many, many times over. A large number of rows In those cases where the calculations take quite a while, you may want to temporarily switch to manual mode as you're kind of making other workbook edits. Just for the sake of speed and efficiency, Just make sure to change it back to automatic when you want those formulas to recalculate in. The second common use case. Again, like I said, is allowing your formulas to calculate on their own automatically. But treat your data tables separately. So let's jump into a pro tip workbook and I'll show you what this looks like. All right, so from our table of contents, gonna look in our green category or formula tip here. First item calculation modes go ahead and link out to that town, and here we've got a sample of about 100 products along with quantity sold. You've got the retail price and the unit cost for each of these products, and we just want to calculate the sales profit here. Now, before I do, let's hop into her formulas tab. Drill down into our calculation options. You'll see that by default, my workbook set to automatic. Go ahead and just for the sake of demonstrations, switch this to manual and then right out or function here. So our sales profit formula it's gonna be the quantity times the price minus the cost so say equals B two times open parenthesis. Retail price minus the unit cost. Close it out. Press enter. That returns $12,058.25 which is the correct value for road to. But just like I showed you, if we apply this all the way down, we'll see that same duplicate repeating value, which obviously is incorrect. So I'll get to do back to formulas calculation options automatic. And there you go, everything. Recalculates it on to rewrite the formula or anything like that, and we're good to go. So again, very simple concept, but really helpful. One to keep in mind once you know that these two calculation modes exist 3. Formula Line Breaks: this pro tip is all about using Formula Line breaks, which is a great way to improve readability and organize your formulas. Now it's a one star tip very, very basic. But it's something that I actually wasn't aware of for quite some time. And the idea is that while you're actually typing or editing within the Excel Formula Bar instead of just pressing, enter, which will submit your formula, hold Alz first so that when you press ault and enter your gonna create a line break and split your formula into multiple lines. So to give you an example in this demo that will walk through, we've got this nested if statement, that's not extremely long. But it's it's rather long, and what we're gonna do is take that formula used the's line breaks to break out each condition, each individual if statement into its own roe. And that won't change anything about how the formula calculates or the results that it produces. All it's doing is helping to improve organization and readability. So very straightforward stuff here. Common use cases again, breaking down functions, especially like conditional functions like this into these multiple lines to isolate individual criteria or making more complex nested formulas easier to interpret by introducing each of the new component functions on a new line. So you'll get to the point as you get very advanced with formulas and functions where you're combining all sorts of things like Offset and Index and Count Day and Rose. And if you ness them all within a single endless string of functions, they can be very, very hard to interpret and diagnosis. Something's going wrong. So let's jump into Excel and give this a shot. All right, so from our table of contents, I look for the formula line, breaks demo and go ahead and press link, and this will take you to a tab that contains information about 24 different stores or supermarkets. I've got a region i D. You've got City State, country, And then this Total Square Foot column, which is driving this formula in column H. And this is a nested if statement, we're testing multiple criteria and essentially applying some sort of a label based on the total square feet, so depending on the size of the store, were labelling it as small, medium, large or very large. So this will be a good candidate for those Ault line breaks here in the formula Bar. Now let's click into the formula Bar. First thing we can do is hover over the bottom edge until you see that two point an arrow click and hold and drag it down. That's just gonna give us a little bit more space to work with inside of that formula bar. And now what we want to do is create line breaks between each of the if statements, each of the individual conditions or criteria that were testing. So just place your cursor right before that. First, if, after the comma and hold ALTs as you press enter, that's gonna go ahead and move the rush. That formula down to the next line. Same exact thing here before the next. If statement Ault enter and then one more time for the final. If statement Ault enter. So now we can see much more clearly. Okay, we're testing the values in G to write in the first test. Is the square footage greater than 35,000? If so, we're gonna label that store very large. If that is not true, we're gonna move to the next line is the size greater than 30,000. If so, label at large. And this is kind of how these nested if statements work is they progress through a series of true false tests. And then if none of those air true, you've got this kind of catch all value false. And I suppose we could actually drop this down to its own roe as well, cause that's the final result. The final label, which is other. And there you haven't we press, enter and apply that down. Nothing should change whatsoever, with the exception of the fact that we've made our formula a bit more readable, a bit more user friendly. So there you go, adding line breaks your formulas using all to enter. 4. Converting Units of Measurement: So this next demo is one that I'm really excited about because it's a function that I actually just discovered for the first time a few months ago. Called Convert, we can talk about changing your measurement units using this convert function So formula acts exactly like it sounds allows you to change values from one unit of measurement into another. So you may have data that's in pounds, and you need to convert into kilograms. Maybe you've got temperature readings and Celsius, and you want Fahrenheit given apply, convert to date or time fields by converting minutes into seconds without having to multiply by 60 or add extra constants into your formulas. So we're gonna talk there a few different demos, one of which is gonna be average temperatures that are recorded in Fahrenheit. And what we'll do is simply use that convert function. You'll see a box pop up with all of your options, and what's important to note is that you won't see the full list of measurement units every time that list is gonna be dynamic, and it's gonna depend on your from unit. So in this case, the only options that I see, given that I've inputted Fahrenheit. Temperature units are Celsius, Fahrenheit Kelvin and those others, which I'm not familiar with, but their temperature specific options. So it's a simple is selecting the option you want in this case, the sea label for Celsius. And there you go. You've created a new converted calculated column. So pretty straightforward Use cases here. Number one. Making those quick conversions without needing to look up conversion rates or calculators, and second, converting date or time specific values without having to add formula constants. Let's jump into our pro Tips workbook and make some conversions. So from your table of contents were looking at her formula tip section here. Look for the measurement conversion demo PressLink, and when you land on that sheet, you'll see two different tables. Data got average monthly temperatures here, which are recorded in terms of Fahrenheit Column B. We're gonna use convert to turn those Fahrenheit values into Celsius, and we also have some individuals here with height and weight metrics, and we've got height in inches and weight in pounds just because the US likes to confuse people and not use the metric system, So we're gonna turn those inches in two centimeters were in turn the pounds into kilograms . So let's get started temperature in Celsius, we're gonna start with an equals convert Open the prince is the number that we want to convert lives right here and be three, and we're gonna convert from Fahrenheit. So if you know the label, you can simply type in f in quotation marks or you can search through. To be honest, I'm not sure what order these, Aaron and I don't know a quick way to search, but here it is kind of in the middle a little bit towards the bottom. Then you can click that option either double click or tab to pull it into the formula. And then the last argument is, what do you converting Fahrenheit into? And now, at least it kind of dynamically shrinks down that list in tow on Lee, the temperature specific options. So Celsius is the one that they need This time all tablet in close, the parenthesis and press OK, and there you have it. So 41.7 degrees Fahrenheit is about 5.39 degrees Celsius. And I did check these with a conversion calculator just to make sure But you can do the same if you like and double click and their ago. We've converted all of our Fahrenheit temperatures into Celsius with the click of a button . Now let's move on to heights and weights, so we'll start with height. We're gonna convert inches in two centimeters. So equals convert. We're converting F three from inches. Search a little bit for it. There it is, I m tablet in and the unit that we want to convert those inches too well. Here are the options. Got meters, miles? Nautical miles back in inches, feet, yards, Angstrom. But you'll notice we don't see centimeters here. Fact. The closest option that we have is meters, so you might be tempted to say, All right, well, I'm not a luck. Let's drop meters in, close it off and then multiplied by 100 to turn those into centimeters, which will give you the right answer. 83 inches is 210.2 centimeters. But here's the thing that I discovered, is that even if the option doesn't exist in many cases, typing the abbreviation will work regardless. So I'm going to say, Listen, I don't want meters I want centimeters which is universally noted as cm put that in, quotes close my formula press enter and they're going to get the correct answer without having to introduce that constant at multiplication by 100 into our formula. So there you go, apply it down. And now, very similar case we're gonna deal with converting pounds two kilograms equals convert the number that were converting his cell h three We're going from pounds, Mrs Pounds Mass El bien Double click, and we're converting it into kilograms which doesn't live on this list. We have grams. So just like before, one option could be to use grams and then divided by 1000 to turn into kilograms. Or we can simply say, Hey, the symbol for kilograms is K g. Close the parenthesis. There you go. We get the correct answer. £260 is equal to just under 118 kilograms. So there you go couple demos for how you can use this super handy convert function to change your units of measurement 5. Real-Time Tools with TODAY & NOW: all right. This next demo is a fun one. We're going to use volatile today and now functions to add real time formless to our workbooks so that today and now formulas basically just returned the current date and time . And because they're volatile functions, that means they automatically re calculate with any workbook change. Now, if for whatever reason you don't want them to change, you just want to stamp the current fixed date or time. You can use the control semicolon shortcut to return the current day or the control shift semi colon to return the current time. And unlike today and now functions, those will just stay hard coded. Those won't change dynamically. Now for this demo, we're gonna build a couple of pretty quick project pacing tools. And in the first case, we're going to use a Today function to build a project tracker based on days past and days remaining. And then we'll use a now function to build a similar tool that's based on hours passed and hours remaining for shorter term projects. Now, common use cases. Any time you want to display the current date or time in a worksheet cell, you can use today or now and creating tools like this, like schedule or timeline tools that you want to update based on the current day or time. So let's jump into Excel and actually practice working with these today and now functions. All right, So from your table of contents tab, look for that today and now Demo in your formula Tips section and go ahead and press link to jump to that green tab. And from here you'll see that I've built out a blank placeholder template for these two project tracker tools that we're gonna build, and the 1st 1 is gonna be based on the Today function. So selecting Cell C four if we were to just use control semi Colon. That shortcut will drop in the current day, and that current day is correct. It's October 16th but the problem is, if I come back tomorrow or next week, this value will continue to read 10 16. It's not dynamic. It's static and hard coded. And that's not what I want here, because we want to build a tool that's dynamic that changes based on the current day and current time. So because of that, I'm going to use the today function, and I don't even need to enter any arguments. You just open and close the parentheses, press enter. And there you go. Now we have that same value. 10 16 2018. But it will change every single day that I access this workbook. So I've dropped in a sample, start, date and end date Here. You can change these values as you see fit if you want to explore and play with this. Now, one thing that's important to know about Excel dates is that every date, just like this one has an underlying date value. That's a whole number. And that number represents the number of days that have passed since excels start of time, just January 1st 1900. And because every date is backed by that whole number date value, it means that we can apply simple mathematical and statistical operations against date cells to do things like time intelligence functions. So to give you an example of that, if we want to calculate the project length in terms of days, we can simply subtract due date and the start date. That will tell us OK there. 728 days in this entire project. Timeline. Now, same logic for the other formulas. Number of days that have passed. Well, that's just today's date, minus the project Start Date C four minus C six and the days remaining is a simple as the due date minus today's date C seven minus C four. When you press OK, you'll see this doughnut chart populate, which now tells us. Okay, you're 288 days into this project that represents 40% of the entire project timeline. Good way to kind of keep a pulse on progress, especially for long term projects like this one. Now, the one tweak I'll make here is that if we populated a due date that has already passed like October 1st 2018 for instance, you'll see some weird stuff happened. We get a negative days remaining that gets plotted on the chart and kind of weird way to prevent that from happening. Let's go ahead and modifier formula here and sell. See 11 and I'm gonna place the cursor right after the equal sign and added if statement simply to say if today's date is greater than the due date. The due date has already passed in a comma over to our value. If that's true, And if the values true, if the due date has passed, then we're just gonna enter a zero because there are no days remaining in this project common one more time to the value of false. Otherwise, we're just gonna run our formula C seven minus C four just like before. Close out that princess press enter. And there you go now, or graft kind of caps at 100%. And we can go ahead and add whatever date you want in the future and do 11. 25 2019 for instance, That case were 42% of the way through that project. So that's a good example of how to use the Today function with whole number calculations. Now we're gonna get a little bit more granular and practice working with dates and times as well. And because we want the date time, not just the date as a whole number, we're gonna use the now function again. No formula arguments just open. Close those parentheses, press enter and you'll see that it gives me a value of 10 16 2018 15 42. That's military time for 3 42 PM And keep in mind this isn't just giving me just the time component. It's giving me the date and the time combined. So I've got just like before I entered in some initial constraints for this project. Gonna start time of 9 a.m. Do time of 5 p.m. So we're dealing with a much shorter timeline here within a single day. And the one thing that I need to do before it can move forward is actually strip out just the time from this now. So otherwise, I won't be able to make direct comparisons against these values. 9 a.m. and 5 p.m. So here, in cell C 21 I want this cell to return just the time component of that date. I'm gonna show you two ways to do that. The first is using excels time function, and it looks for an hour in put a minute input and a second input, and I'm gonna get those inputs using time functions and pointing to my now. So So the time that I want to return is based on current our which is the hour of now the current minute, which is the minute of now, and the current second, which is the current second of now. Close off to parentheses, Press center. That translates the entire date time string into just the timepiece just 3 44 PM, which is exactly what I want now. There's one quicker way to do this, but it requires a little bit of a deeper understanding of how these date and time functions and date values are really working. The bottom line is that individual days and excel will be treated like whole numbers, right? If I right click this date, sell 10 16 2018 format, the cells go into general category. That's a good way to see what the actual underlying date value looks like. 43389 Again, that's the number of days that have passed since January 1st 1900. Note that it's a whole number if I compare that, so that's cancel out so we don't apply that format. Let's compare that to the now function, which remember, is the current day, plus the timepiece as well. Can you guess what that's gonna look like? Go in general. Note that it's that same date value with additional values after the decimal point. And that's because times are treated as fractions of days and fractions of days. In terms of date, values just look like fractions of whole numbers. So if we want just the timepiece, which would be just the 65577 following the whole number, all we need to do is literally write a formula that takes the now calculation and subtracts out the day, which is the whole number, and you're left with just that remainder, which a minute has passed. So now it's 3 46 PM Slightly easier way to do it, but again, it requires that kind of deeper understanding of date values. Alright, so back to the project, all we need to do now to take this to the finish line is calculate the project length in hours, which is simply the due time minus two start time. 19 miles, 18 hours passed is gonna be our current time minus the start time. This is just like those date calculations we just did above and then last but not least, the hours remaining is going to be the due time minus the current time C 19 minus C 21 Press enter and there we go. So our project started at nine. AM It's gonna end at five. Currently, it's 3 46 which means that I only have an hour and 13 minutes left on 85% of the way. Through this timeline, I better get cracking if I want to finish this project now. Last but not least, we want to create that same if statement modification to control for cases where we've already passed the due time. We can do that using the exact same methodology here. If our current time in C 21 is greater than the due time. If we pass the due time, then there are no hours remaining in her project. Otherwise, calculate C 19 minus C 21. So that was a very quick crash course in Excel date values and today and now functions. But hopefully that's giving you some good ideas for how you can use these volatile functions to create your own real time calculations. 6. Formula Auditing Tools: This next tip is all about finding and fixing errors in your worksheet using formula auditing tools. Now, in your formulas tab, you'll find a group of individual tools under the formula auditing category that are designed to help you trace references, evaluate your formula, calculations and ultimately diagnosis and fix any errors in your worksheet. So we're gonna talk about all of these different options in this demo. We're gonna start with tracing Precedence Independence, which basically draws arrows to any cells that either impact or are impacted by the selected value, kind of like a parent child relationship. And here's an example of what that looks like in this case, which will talk there in our demo. We're tracing the precedence that impact these cash to close and monthly expense values so that we can understand which input cells will impact those values or those outputs. We also have a number of other tools we're gonna talk about show formulas, is simply gonna temporarily display any worksheet formulas as text, and then error checking is gonna scan the sheet for errors, help us trace the source back to any precedent cells. And finally, my personal favorite, the evaluate formula tool that's going to allow us to actually evaluate every single individual component of a function or formula step by step. And that's great for pinpointing where a formula might be breaking, especially if you have a very complex formula or a number of nested functions. So a ton of practical use cases here for one, simply understanding how some of these complex formulas and functions might be operating number two. Visualizing, which sells factor into a certain formula, output or sell, and then three. Tracing, diagnosing and hopefully fixing the source of any errors that you run into so that let's jump into the demo. Let's get hands on in our pro tip workbook and practice working with some of these auditing tools. All right, so if you're following along, go ahead and open up your pro tip workbook. Look for the formula auditing tools demo in the Formula Tips section and go ahead and press linked to jump straight to that sheet. And what we're looking at here is a property calculator. It's basic model that I actually used to evaluate property and loan costs, and essentially what's going on here is you've got some basic information about a property , got a purchase price and a tax rate. You can enter some loan terms here like a down payment and interest rate and a term like And those values will run through a number of calculations and ultimately spit out the cash required to close on the property and an estimate for monthly expenses. But obviously, we're not here to talk about mortgage payments and property costs. Right now, we're here to talk about evaluating formulas. So with that, let's go into our formulas tab. Take a look at our Formula auditing group, which contains those tools that we talked about. And let's start by talking about precedence and dependence because it sounds very complicated. It's actually quite simple, and one way to think about it is, you know, for this monthly expense value, the cell that I have selected this is kind of the end of the calculation flow. This monthly expense number doesn't feed in tow any other formulas. No other output cells are impacted by this monthly expense value. In other words, this cell has no dependent cells, and I can prove that by clicking trace dependence, and I'll get this kind of warning message that says there no formulas that refer to this active cell. But there are cells, of course, that service inputs to this monthly expense value. In other words, there are cells. That act is precedence to the selected cell. And to show those precedents, all I need to do is click that button and it's gonna say, Yep, these five values here H 14 through H 18 all impact this monthly expense number. In other words, if you change any of these values, monthly expenses will change as well. Same story here, with cash to close, there are no dependence. This is the end of the calculation flow, but there are a number of precedence, so that cash to close value is a function of purchase price, down payment and estimated closing costs. Now, another way to kind of tell a similar story here we can remove the Arabs is to select a formula, sell and either click into the formula bar or used the F to shortcut to edit. And what this will do. You won't get the arrows, but you'll still see selected cells that are referenced within your formula and what I actually like about this approach is that they're color coded as well, so you can kind of map them to the individual components within that formula, just yet another tool that you can put in your back pocket to help you diagnosis and understand your formulas. So go ahead, press enter, and we've traced precedence from these cells. But the opposite relationship holds true as well, so we know that cash to close was a dependent of purchase price. Therefore, purchase price is a precedent to cash to close, and we can show that by tracing the dependence from this cell. And now what this is showing us is that if we change purchase price, there are 1234 five cells or outputs that are going to change as a result. There five dependent cells based on this value and, ah, one cool tip that I didn't discover until just recently is that this only shows you the first step of the calculation path when in turn, some of these dependent cells also have additional dependence from there, so purchase price impacts, loan amount, closing costs and property tax. But if we click trace dependence again now, we can see there's another layer here where the property tax value, as we've shown, also impacts this monthly expense output. So now we're seeing kind of the full chain of events and the full calculation flow across multiple steps. Really powerful tool. Go ahead and remove those arrows. I'll show you what the show formulas tool looks like. Pretty straightforward. It just stretches out yourselves so that it can show the actual formulas. Asked text kind of all side by side. Good way to compare. You know which sells in a sheet are constants and which ones are formulas and then could simply toggle that honor off. Now onto my favorite option, the evaluate formula tool. Let's pick one of these formulas here, Um, something like property tax. Click evaluate formula. It's gonna pull up this dialog box and you'll see right from the start the exact same formula that you'll see in your formula bar. But what you can do now is actually look at the underlines component when you click evaluate here just that underlying component will evaluate. So it just said that H three evaluates to 499,000 because H threes that purchase price and then when that gets divided by 1000 that evaluates to 4 99 Then, as we step through, we can continue the process. H four is 9.5, 9.5 times for 99. This 47 40.5, divided by 12 gives us that 3 95 monthly property tax payment. So again, Great way to can't understand your formulas at a deeper level. And there's one other feature to that evaluate formula. Tool that can be helpful. Show you that we're gonna go to our cash to close formula, and we're gonna evaluate that one. And it's pretty simple. Formula H three times H seven plus H 11 h three is for 99 h seven This point to multiplying together. It's 99 800. Now here's the thing we get to H 11. H 11 contains a value of 99 80 but that 99 80 isn't a static, hard coded value. It's produced itself from another function. So this step in button allows you to say Okay, each 11 is a little bit more complicated. Let's see how H 11 itself is being produced and by going one level deeper by peeling back kind of one more layer of the onion we can now see that H 11 is calculated as H three times 30.2 can evaluate this formula kind of nested within our original one and then step back out and say, OK, that's how we're getting to the 99 80. Then when we add them up, we get to our final product, which is 1097 80. So there you have it. That's evaluate formula. Obviously, everything looks a lot easier and simpler when things were going well. So let's walk through one quick sample of when things might not be functioning quite as you'd expect. So let's say we want to enter down payment percent of 10% but our finger slips whips and we type 10 p percent. You know, obviously something's wrong here. We've got four cells that are now spitting out value error. This is a good time to use that error checking tool here, which will actually cycle through your sheet and say, Hey, we found a bunch of errors. First ones in Cell J nine. You click next. It's another one in h 10 Got one in each 14 one in J three and then you'll have a bunch of options here for each of the errors. Ah, help on this error will take you to the office support website. Show calculation steps will actually take you to the evaluate formula tool and specifically to the step that yields the error. And this right here will show me that we're trying to multiply 499,000 which is a value by this text string surrounded by quotes. 10 p percentage which evaluates to that value error which, obviously, as you carry it through, is just going to yield a final error at the end of the day. So it's closed that out one other way that you can use this error checking tool if we go to the next error. What's it? Completed it. This is the one we want Monthly expenses. Go back and error checking. Sometimes you get this option actually traced the error as well. And when you do that, it will populate those precedent arrows with one difference. It will turn the arrow red at the stage where the errors taking place and this is a nice way to kind of trace things back and say Okay, Step one is fine. Step two is fine. Point where Step three should be evaluated. That's where an error is taking place. So I know that it's the input value right before Step three that's causing my problem, which in this case is sell H seven. It's that messed up down payment percentage, and we've isolated the problem so we can go ahead and close out of this. We can remove our arrows and we can fix that percentage to 10% and press enter and all is right with the world. So a lot of tools in there a lot to cover, but really helpful valuable options to keep in your back pocket if you work with formulas and functions and excel. 7. Pivot-Style Reports with Formulas: all right. This pro tip is all about using stats, functions and specifically conditional stats functions to generate pivot style reports. So you may be wondering what the heck I'm talking about talking about some ifs. Count. If average ifs, these conditional stats functions that allow you to summarise data, given a certain condition or set of conditions. And when you think about it, that's exactly how pivot table operates, where those conditions air defined by row labels, column labels and your filters. So to give you an example, we're gonna be looking at a subset of that I M D B movie data here and Columns A through F and what we've got is a small pivot table view like this, which basically it's filtered down to genre equals action. We've got the top five countries by count of title, and we're summarizing three different metrics or values here. We've got the count of the titles arose. We've got the some of the revenue column, and we have the average of the IMDB Score column, and the idea here is to produce something like this, which is essentially replicating that exact same table layout and functionality. Onley will be using cell formulas outside of the pivot table environment. To do that, we're gonna use these trusty conditional stats. Functions, renews count, ifs to calculate title count some ifs to calculate the revenue and average ifs to calculate the average IMDb score. Now you might be wondering if you could do it in a pivot without writing one formula. Why would you go through all this extra work and basically recreate the wheel from scratch ? So my take is that pivots are a great tool for kind of unstructured unguided analysis, meaning they're great when you don't really know what you're looking for and you want to just slice and dice and explore things toe. Learn more about your data. On the other hand, if you know how you want to segment your data and you know which values you want to show as part of something like a report or dashboard, using these stats functions allows you to do that within workbook cells, which will give you a lot more flexibility to design and format and layout your report exactly as you choose, especially compared to building a pivot style report with things like slicers and pivot charts, which are also great for their own reasons. So the use cases here that I'm gonna focus on our designing custom formatted dynamic reports without using pivots and filtering or segmenting your raw data based on a given set of criteria, which is exactly what these functions are designed to do. So let's go ahead into our demo in our pro tip workbook and see if we can make this work. Okay, so from your table of contents, you're gonna look for the pivot style reports Demo the restart IP. This is a moderate difficulty, so it helps to have a little bit of background in these stats functions. Ah, but even if you don't, hopefully it will be able to follow along pretty closely. Let's link out to that worksheet. And just like I described here, we've got a subset of the IMDB data. It's got about 3700 titles, and the columns we have to work with are the titles themselves, the genre language country. I am DB score revenue and budget. And here we have a pivot table for going to Rick Tools change data source. You'll see that this pivot is indeed connected to our data and columns A through G. Go ahead. Press OK, And again we've got here is Ah, quick view showing the count of the titles. Some of the revenue and the average IMDB score are filtered on action titles specifically. And we've got a value filter to show the top five items or in this case, countries based on account of title. So press okay and here have just added a little bit of ah, placeholder blank template where we're gonna use these conditional stats formulas to basically replicate the exact values that we're seeing up here. So I've just copied dropped in the same country names. I've added a cell here which just contains the word action. For now, you could turn that into data validation. Drop down if you like. All we're gonna do here is trying to replicate these values about So starting with the count of the title obviously are summaries. Ation mode here is a count. So for that reason, we're gonna use a count if function and because we're testing for two criteria, we want to count rows under two cases. One where the country equals U. S. A. And to where the genre equals action because we have multiple conditions. We're going to use the plural version of that counted function called Count Ifs, and go ahead and open up the parenthesis. And here's where we just feed this formula. These pairs of criteria ranges and criterias. So our first criteria is country. There's country values Living column d press F four to lock it in and the criteria that we're looking for, what we're trying to filter within that column D in this case is the country named USA. We can leave this relative in this case, or you can cycle with F four until you locked your column, because thes country labels will always live right here in column I. But we want the road to shift down to UK and rose 17 France and Row 18 and so on and so forth. And that's comin over to our next Criteria Range, which is a genre Range lives here and column B F four to lock it in in the criteria for the genre lives right here and sell J 13 and it will always live right here in J 13 so we can fix that entire reference. Close the parenthesis press enter. And there you go. 700 to titles or rose for us. A action films which matches are pivot table above. And because we've set our reference types correctly, we can simply drag that value down and get 69 27 24 14. So we've got a match. All is good there. Now, we're gonna follow a very, very similar approach here to calculate revenue. Except we're not counting this time. We're summing so same exact logic. We're gonna use some ifs. And the only difference is going to start with the some range values that we want to add up . Live here in column F the revenue values. And that would just enter those criteria, ranges and values just like we did before. So first criterias country and D criteria is U S A block. The I reference second criteria is the genre and be and the final criteria to is action in J 13. Lock it in close the parenthesis press Enter. There you go. $59 billion apply it down. 3.991 point 766 49 8 43 Boom. We've got a match. We've essentially just done the job of a pivot table using these conditional stats functions. Now, last but not least, we want the average of the IMDb score. So you guessed it. We're gonna use average ifs this time. Same as some ifs. Except instead of a some range, we're gonna plug in an average range. This case that's e on. Go through that same process. Criteria range one country. What USA criteria range to genre. And we want action locking in, close it off press enter and drag it down. 623656 All the way down to 648 And there you have it. And now, because these conditional stats functions are pointing to cells that users can actually update or change, that means that our values in this little report that we've built are completely dynamic, just like a pivot. So if, for instance, filtered on adventure films here, you could change your criteria Cell J 13 to adventure. And there you go. You get the same matching values. So we've almost, like, built our own little pivot table, um or pivots style report. At least using these conditional stats, functions like count If summit and averages really helpful tool, especially if you need to build reports or dashboards to summarize your data and want a little bit more flexibility in terms of the layout and the design. 8. Counting Words in a Cell: all right, This next pro tip is one of my favorite ones. It's a really clever way to combine text formulas in Excel to count the number of words within a cell. And anyone who's familiar with text functions has probably heard of the length or L E N formula, which essentially just calculates the number of characters in a word string or a text string. And that's helpful, but not quite what we need here, since we're trying to calculate account of whole entire words. And so to do what we need to do, we're gonna need to get a little bit creative here and combine some additional functions like substitute and trim to get what we need. So for our demo, we're gonna be looking at some wine tasting data where we have these taster descriptions in column F. And our goal is gonna be to create a word counter in column G that counts. The words from column F Simple is that now what will end up with is something that looks kind of like this, which is a little bit of a beast. It may not make much sense at first glance. Don't worry, I'm gonna walk you through every single step of this function until you understand exactly how it's working now. Use cases here, obviously, like we're talking about adding counters in cases where word counts are important or analyzing text fields things like tweets blog's search ad copy on whatever might be based on the length. So a lot of different practical places where you could use this tip. Now let's jump into our pro tips workbook and actually build a word counter of our own. Okay, so we're looking for the counting words demo to one of our green tabs. If you're in your table of contents, go ahead and press link and we'll jump straight out to the tab. And right here we've got our wine tasting data. We're looking at the wine name and column A. The variety and column B got the individual taster names here in column C and Colin D is the one we care about this. Is there actual written description from there tasting and what we're looking to do is use those text functions here in column e calculate the number of words in those descriptions. So what I'm gonna do is kind of break this down bit by bit, and then we're gonna assemble the pieces together in order to create what we need. So perhaps the simplest individual piece is the Eliane or length function, and show you what that does. We're gonna take the length of cell D two with a simple is that you just point to a cell containing some text press, enter and returns 58 which is not the word count. It's the character count. So keep that number in the back. Your mind. This original description contains 58 total characters. Now, the next piece that we're gonna be using is a formula cult substitutes. I'm gonna just overwrite that length function with substitute. I always have a hard time spelling this one for some reason. And the way this substitute function works, there we go. We can see a little bit better is we're gonna point to our text field again. Right D to. And I were saying, What in the original text string are we looking to substitute? What's the older original text? This could be an individual character. It could be an entire word, an entire string of words in this case what we actually want to substitute is a space. So I'm gonna open up a quotation marks at a space and close it off and then comment to the next argument, which is okay, that's the texture. Looking for now? What do you want to substitute for that space? And in this case, this is where it kind of gets a little bit clever. Right is we actually don't want to replace that space with anything. We want to get rid of the space entirely and to tell Excel. That's what we want. I'm gonna type two consecutive quotation marks, basically, just like I did, but without the space in between them and because we want to replace or substitute every instance of those spaces, we don't really care about this last optional argument. The instance number. We're just gonna close the parenthesis press center and what you end up with something like this. Berry and cherry aromas were surprisingly sturdy and clean. It's the text string that we had originally in D two without any of the spaces. So now if we go ahead and modify that formula, click right after the equal sign. What if we take the length of this new modified string that we created closed apprentices at the end Press enter 50. Okay, so we know the original string was 58. Now we know that the version of the string with no spaces is 50. By taking the difference of those numbers, we can essentially count the number of spaces that we pulled out or removed or substituted . So it's a Ziff. We used a function like count if text equals space. But unfortunately, that doesn't exist in excel, which is why we're taking this approach. So let's go ahead and actually take that difference in the formula again. I'm gonna modify right after the equal sign, we take the length of our original string de Tu minus our new length of that substituted version of the string, and that will give us the number eight. Let's drag this back in here. So we've removed eight spaces and we can check cause this is a pretty short description. 12345678 spaces were in fact removed. But we're not quite there because if you count the words, they're actually nine words because every sentence is gonna begin and end with a word. So there's always gonna be one more word than the number of spaces. Very simple adjustment to make. Just add one to the you never formula press enter. And there you go. You get a word count of 9123456789 And we should be good to go by simply double clicking, applying it down. And there you go. It's gonna take the word count for every single one of these descriptions. You can count them to confirm if you like, But trust me, they're getting the job done. Now, the only other piece that we can add if we want to take one more measure of, ah, Angus security, you could call it to account for cases where, you know, there may be a leading space, you know, accidentally like that, which would indicate that there's 1/10 word when they're actually is not to account for that. We're gonna use that trim function, and all we're gonna do is just nest it in here. So instead of the length of D two, we're gonna take the length of the trimmed version of D two and same logic, instead of substituting D to we're gonna substitute the trimmed version F D to right here as well. So I'm just nesting that within the function here. And I think we need one more closing princess there. And let's give that a shot and press enter. There we go. So now it's been corrected to nine, which is what we want here. Same thing if we add trailing spaces like this, whether it's one whoops or two or three or four, it doesn't matter. That trim is going to account for that for us and strip out those leading or trailing spaces. So now we can apply that down. And while a we've got a fully functional word counter in Excel. 9. Dependent Drop-Downs with INDIRECT: all right. This next pro tip was inspired by a question that I got a while back. Have actually gotten it a few times since then. Which is? How do I create a drop down or data validation menu that's dependent upon another one? So, in other words, how do I create a primary drop down and then a dependent one where the list will change based on the user selection? And it's an excellent question and a great opportunity to use a function called Indirect. So this is a four star tip, definitely getting a little towards the advanced end of the spectrum. But let me walk you through how this is gonna work. So essentially we can use indirect combined with data validation rules to create those drop down lists that update based on a user selection. So we're gonna run through three steps here in our demo. We're going to start by creating individual lists containing all sets of possible selections. So we've got our primary drop down, which in this case is the season either summer or winter. And then we've got the lists that could potentially populate the second or dependent drop down, which is the list of summer specific sports and winter specific sports. So once you've created those lists of unique values that capture those possible selections , Step two is to turn those into actual named ranges. You can use the name manager in your formulas tab, or you can simply type the name right in the name box to the left of the formula bar. So if you look at the two options in the primary drop down summer or winter, you're gonna want to match that wording exactly. So instead of summer sports or winter sports, we're using those exact words summer and winter. And then from there, all we're gonna do is create a drop. Downs are primary and secondary and then configure the list source for the secondary. Drop down the dependent. Drop down to reference that first cell as part of an indirect function. I'm gonna talk about exactly why and how we do this when we get into the demo, but that's the quick summary of the approach that we're about to take. So some common use cases here creating dynamic reports with multiple user inputs with data validation or perhaps more commonly, configuring your drop down lists to prevent users from selecting invalid combinations of options. So in this particular demo, that's exactly what we're doing. We don't want user to be able to select the summer season and ice hockey or Alpine skiing as the sport, because whatever cells are dependent upon these data validation selections, they'll obviously just show up as blank in those cases. So enough talk enough theory. Let's go roll up our sleeves, head to her pro tips workbook and build some dependent drop downs. All right, so from your table of contents tab, you're gonna look for the dependent drop downs demo in our green formula Section four Star Tip. Go ahead and click link Jump to the town and what you'll see here is we got some Olympic athlete data broken down by seasons and by sports and Collins and be with the athlete names and come see and I've added just kind of a blank template or placeholder where we're gonna populate our information in order to create these. To drop down sells the season which is our primary drop down, and the sport which is are dependent drop down. In other words, we want this sport dropped down to display a list of either summer or winter sports, depending on what's been selected here and sell F three and then I've added a formula here . Number of athletes. This is just account ifs function that says, Okay, let's count the rows given these two criteria so that we can see the number of athletes for a given sport and season so that lets get started Step one is to simply populate all of our lists, right? We're gonna start with season for a primary drop down only two values here. It's a simple is that summer winter. Just type them in manually. It's probably the quickest way and how to get the unique summer sports and winter sports few ways to do it. We could use the advanced filter option, which recovered another pro tip. Or in this case I'm gonna do is filter my source data here. Let's only look at summer minute. Grab Selby to and control shift Arrow down to select the entire Column of Values Control. See to copy, go back up, select I to and control V to Paste and then in my data town, gonna remove duplicates and continued just with that current selection and calm. I press remove. Okay. And there you go. We have 36 unique summer sports listed here, and we're gonna follow that same process for the winter sports. So instead of filtering on summer filter on winter, grab the first value control shift. Arrow down Control. See to copy. I'm going to scroll up, and we're gonna need to unfiltered this so we can get access to row to again. Clear that filter. Select J to control V to paste data removed duplicates and remember to continue just with that current selection and press to remove. So there were going to get 15 unique winter sports. So there you have it. Step one is complete. Got our unique list of options. Now, Step two is to actually give these lists meaningful names. So what we're gonna do is select all of our summer sports first all the way through wrestling. The easiest way to do this is not to go through formulas defined name, but to simply type the name right here in the name box to the left of the formula bar. And remember, I want to name it exactly consistent with my primary drop down so summer enter same thing with winter. Select those sports in this one winter enter and that's step two. Final step is to actually set up our drop downs here in F three and F five. So our 1st 1 are seasoned selection. Very, very simple. We're gonna go to data data validation. We're gonna allow a list here and that list. Contains are two seasons press. OK, so now you can select summer our winter. There you go. Now here's the key. It all hinges on this, right, Because this sport selection and F five when we head to data validation, we're going to allow a list like we normally would. But instead of selecting a range which would create a fixed reference to arrange, that wouldn't change, Wouldn't be dynamic. This is where we need to use that indirect function. And what we're trying to do is we're gonna indirect and reference cell F three now before I hit. OK, what we're doing here is we're telling excel. Hey, look at cell F three. You're going to see a word either summer or winter by using indirect what we're telling XLs that there's more to it It's not just a text string that has the word summer or winter. Those words are more meaningful. Their actual defined workbook objects, so by using indirect, were telling Excel Toe actually treat those as the named ranges that they are. And when we press OK, got summer selected? Take a look that drop down contains all of our summer sports and none of our winter sports , right? And if we change this to winter now, take a look. Alpine Skiing Biathlon Bobsleigh These are our winter sports all the way down to speed skating, so that indirect function is able to recognize this word as unnamed range and populate the rate list accordingly. So now take a look. We've got our little athlete counter telling us there were 645 Alpine skiers in the Winter Games. We can switch it over to summer and select one of our summer sports like baseball 191 athletes. So there you have it. This is one of my favorite tips in the course, because it's not that hard, but it's extremely powerful and a really great option when you're creating dynamic reports and dashboards. So there you go, creating dependent data validation, drop downs using the indirect function 10. Hyperlinking Between Worksheets: all right for this pro tip. I want to show you how to create dynamic workbook links using the hyperlink function. So what we're gonna do here is we're gonna use hyperlink to connect users straight to specific workbook or worksheet locations. Now, if you're following along with the course, this should sound very, very familiar because that's exactly what I've done in the table of Contents tab in the pro tip workbook, you'll see a formula like this which is driven by that hyperlink function, and that will point you straight to sell a one for a given tab. In this case, they go to options tab. Now, in theory, this is pretty straightforward, but you'll notice that it's a four star tip. So it's on the advanced end of the scale. And the only reason why I have labeled it as an advanced tip is because syntax is extremely specific and pretty inflexible. So I've included an example here. Hyperlink sheet one cell a one with a link name label and notice all of those special characters in there. The quotation marks single quotes, the hash mark, the exclamation point. All of those symbols are critical. And if you don't have them all in the exact place. This function won't work. So really more than anything, getting hyperlink to work is more a matter of attention to detail than anything else on one thing to note. Here, you can type in an actual sheet reference like the example in this bullet. Or you can replace that with a cell reference like I've done in the table of contents sheet . When you do that, it makes things a little bit trickier because you essentially need to reverse engineer the exact syntax using Can Canton eight functions toe actually plug in that hash exclamation point and quotation marks. So the use cases here, for one, adding tools to help users navigate larger, complex workbooks like, for example, a pro tip workbook that has about 100 tabs of information in it. Um, and two. This is one that actually do quite a bit, creating reports that have links to additional details or data sources. So maybe you've provided a report for a client. That's top lines summary view. You can add a link that may be controlled down to a much more detailed sheet. If the user wants to access that level of detail Really helpful tool in those types of cases. So let's go ahead and build our own hyperlink in the pro tip workbook. All right, so, appropriately enough, here we are in our table of contents sheet, which just so happens to include dozens and dozens of examples of this hyperlink demo that we're about to practice. In this case, let's actually go to the dynamic workbook links demo right here in our formula tips. And you guessed it. We're gonna hyperlink out straight to that tab. And what we have here is like a little mini version of that table of contents that we're gonna practice building out ourselves. So the idea is gonna be to populate column H with links based on these workbook names or worksheet names and column B. But before we try to get all crazy and advanced right from step one, let's try just writing a simple kind of more basic hyperlink function for a nearby tab. So counting words is close by. Let's work with that one first so we can try. Just typing equals hyperlink link location is going to be counting words. Sell a one comma over to the next argument, which is the friendly name like link. Close quote, close the parenthesis press enter. Things look good, right? If we just select that cell, we're hyper linking to the counting word sheet. Specifically cell a one and we've labeled it link, and it's created a link for us. Problem is, when we click it, we get this cannot open the specified file error. And basically what we need to tell Excel is Hey, Excel, we're not trying to open a separate file. We're trying to just access one of our sheets or tabs within this file. So to tell Excel, that's what we want. We need to add some characters here, and right after that open Perrin, we're gonna add a double quote followed by a hash mark. And then at the end, right after the cell reference, a one we're gonna close out another double quote and then press enter and check this out. Now, when we click link boom, a one counting word sheet, it's worked perfectly. So adding that quotation mark to surround the sheet reference as well as that hash mark has told Excel we're actually trying to navigate to a worksheet location within this file. So let's jump back to our dynamic workbook links sheet, and we could follow the same approach and do it, you know, 15 more times to populate the rest of these links. But obviously that's a little bit slow. It's a little bit manual, and if you needed to populate several 100 links, that's really not a feasible option. And because we've got our worksheet names here in column B, we should be able to simply reference those names as part of our hyperlink function. But here's the catch. We simply tried to replace that sheet reference here with a cell reference like B 11 impressed. Okay, we'd link just straight to that cell location within the current sheet excels not reading it as the name of a tab. So we're gonna need to do is get a little bit clever, a little crafty with Can Canton eight functions to force the syntax that we need. So pay attention put on your by folk ALS. Let's get nice and zoomed in here because we're about to get pretty detailed with this syntax and delete what we had, and we're gonna start with a clean canvas open parenthesis Follow me closely here. Double quote, hash mark. Single quote. So this looks familiar so far. Here's where it gets a little bit weird. We're gonna add another double quote an ampersand That's a dollar sign. Ampersand in a reference, the cell that we want be 11 and another and percent close the double quote off at another single quote an exclamation point, and then they sell reference that we want. So if we want to drop people off its cell a one which makes sense, get type in a one here and then close off one more double quotation marks comma over at our friendly name or label like link. In quotes, close the parenthesis and press enter. Now I'm gonna hover over this again so you can pause your screen. Make sure you've cut all of those individual characters in place. Here's the thing. I would add spaces for you to make it a little bit more readable, but even that would break the function so very, very fragile syntax. And that's why this is a four star tip. But now let's give that a shot. Right click link boom cell. A one in the counting words tab now here's the moment of truth, right? Let's go back and simply grab this and drag it to a few more. So dependent. Drop downs. Click link. There we are dependent. Drop down cell a one dynamic workbook links. That's the cell we're currently in. Boom cell A one index and match a one working perfectly. So now we've essentially just automated the process of having to create all of those links one by one. And now, like any other formula, you can just click and drag and apply it toe every other place. Let the references change to drive users based on the workbook sheets and column B so that just about does it good way to use the hyperlink function to drive users to specific worksheet locations in Your Excel file. 11. "Fuzzy Match" Lookups: I want to take a few minutes and talk about something that I call fuzzy match v lookups. And specifically I'm going to describe how to find ranges of values as opposed to exact look up values using these types of lookups. Now fuzzy match, which is more formally known as approximate match, can be used to determine where a specific look up value falls in a given set of ranges. So the idea here, the key point is that instead of trying to find the exact match for our look up value, what we're doing in this case is searching for the closest match that's either equal to or less than the look up value. And to make this happen, all we do is modify that last argument and the look up function. Notice the range look up by entering a value of true or the number one, which signifies fuzzy match or approximate match. Now, like most things in Excel, this is best illustrated with a Rheal contextual example. So take a look at this data set here. We've got a bunch of products being sold in column B. We have a sales quantity or order volume and column C. But the retail Price and D The idea is, we're trying to calculate the discount level in column e based on the sales, quantity or order volume. And essentially, the idea here is that larger volume orders will be given a larger discount. So for orders of 0 to 5, there will be no discount applied. 6 to 10 would be a 5% discount, all the way up to orders of greater than 50 units, which would earn a discount of 20%. So to make this logic actually work, we're going to use a V look up function, and we're going to say, Let's look up the value in column. See the quantity. We're gonna look it up in this order discount table. Specifically columns H through. I We want the second column over and here's the key. We're using the true range look up type, which is approximate match. And when we enter that formula, what's happening now is that X Ellis cycling down from the top of column H to the bottom again, not just looking for the exact match in this case 25 but the closest match that's either equal to 25 or less than 25. So in this case, it stops at 21. The column index tells us to move over to the next column in the table array, and then that 15% discount. That's the value that gets pulled up into our discount column. E. So what we've essentially done is not matched the quantity value exactly, but identify which range of values that quantity falls into in our table array or a look up table. So really helpful tip to have in your back pocket. One thing to note that's pretty critical here. To make this work properly, you're groupings must be defined by the minimum value, which is the order minimum number that, you see they're in column H in the look up table and those order minimums. Those minimum values have to be ordered ascending from small to large because remember how a V look up works. It's going to start at the top. It's gonna go cell by cell until it finds that closest match. So order is critical here. Now this is a four star tip. It's relatively advanced. If you don't have a good handle on traditional view, look up functions yet how this may be a little bit confusing. I do cover V lookups as well as H Lookups Index Match offset, etcetera in my formulas and functions. Course, we'll cover more of the fundamentals in depth, so it definitely encourage you to check that out. Now a couple of use cases here we can define something like a variable commission rate based on an agent sales volume. Or we can do something like we're showing here calculating tear discounts based on something like a purchase quantity. So let's jump into the pro tip workbook and practice writing one of thes fuzzy match or approximate view. Look up functions All right, So from our table of contents were in the Formula tips section. Look for the fuzzy match lookups demo and go ahead and press link to get out to that green tab. And what we have here basically order records with different order ideas on each line, and we're looking at which product was sold. Quantity sold, retail price, which we're not going to use in this example, And the key is column E. That discount column that we need to populate using Avi look up function that references this order and discount table here. Now. One thing to note this column h is the one we really care about, and this is the one that's going to service the input for the function. Note that it's defined based on the minimum value in each group or bucket, and it's sorted ascending these labels here and column G. These aren't actually used in the function. I've just added them for a little bit of readability, so jumping in to sell it to let's think about how we might approach this problem right? There are a few ways we could possibly tackle this. You could use a bunch of nested if statements and say, You know, if the quantity is less than X and greater than why, then discount equals 10%. That's totally fine. In this case, you'd be nesting. You know, five different conditions into the same formula. It's going to get a little bit long, a little bit messy, and you'd have to modify each of those conditions any time you wanted to change things. So that's not really the most efficient option here. What if we used an exact match to be look up right kind of the standard approach. We said, Let's look up quantity and seat to Let's try to find that quantity. You're in our table array each to three by six, Press four to lock that in. And what we want is the discount, which lives in the second column of that table array. And then, if we did our standard kind of false or zero to signify exact match and closed it off, we could press, enter and start to kind of apply this down. And what you'll see is that some fields happen to populate properly. But those air on Lee the ones that were exactly equal to the order minimums 21 6 for instance, to change that logic from an exact match to a close match and equal to or less than match all we need to do. Now it's changed that zero to a one or a true press enter, and now it has become an approximate or fuzzy match. Look up that will apply properly as it's applied to all these rows, so it's double click to apply it all the way down. Looks like we've got about 100 order samples here and that seems to be working just fine. Weaken spot check. Five would be 0 25 would be 15%. An order quantity of 75 would be 20. Looks great. So there you have it. That's a great example of a riel practical use case for approximate match or fuzzy match V look up functions. 12. Random Selection with OFFSET & RANDBETWEEN: Alright, guys, I am so excited for this next tip. It's one of my favorites of all time. I know I say that a lot, but I'm serious this time. It's definitely top two or three. At least we're gonna talk about how to randomize items from a list using offset and ran between functions. So essentially, what we're gonna do here is we're gonna combine offset with a whole number random number generator using rand between to essentially jump around two random rose within a list or cell range. Now, you might be asking yourself, Why would I ever want to do that? What's the point? And I can assure you that there are very serious, very applicable reasons for learning these tools, like building your own band name generator. Here we have two lists of words. We've got hilarious adjectives in column A and we've got hilarious now owns and column B, and our mission here is to use those offset and ran between functions to randomly generate the name of our next band. Now the beauty of this is that because ran between is a volatile function. It means that our band names will regenerate with any workbook change or if we hit that F nine calculate shortcut. So I've already wasted an embarrassing amount of time. Just refreshing this, you know, late on a Friday night, laughing to myself. But I'd love to hear some of band names you guys come up with. I think my top ones right now are Ah, belligerent snowman. That was pretty good at whimsical kittens was a nice one. And, of course, the white hot sunflowers. I'd say those there in my top three, So let me know if you can top those. Leave a comment. Love to hear him. Now, of course, there are other common use cases, like creating riel business models or scenarios where you want to randomize certain inputs . Or, of course, building your own hilarious band named Generator. Because why not So with that enough talking about it, Let's jump into excel and actually build this tool. All right, so once you've gotten your pro tips workbook opened, we're gonna be looking for the selecting random list items. Demo. It's a green tab in our formula tips section, or you can head to your table of contents and link straight to it. And here we've got our band name generator to lists each containing 30 items, and all we need to do is pull that random item from list one random item from List two and can Canton eight or combine them together into our band name. Now there are actually a few ways to do this. Could use index functions you could use offset both their perfectly valid tools for pulling values from a specific location within a range. In this case, we're gonna use Offset. Let's go ahead and start typing this function. We're going to offset from a given reference or starting point in this case are header from list A cell? A one. Let's lock it in with F four now the number of rows that we want to move this reference down. This is where the random ization comes in. We don't always want to move down one or two or 10 rows. We want to move down a random number between a certain minimum and maximum known as the bottom or top values. So the minimum number of rows that we want to move is one, because that would take us one road down into the first item of our list Aquatic and the largest number that we want to return the top value is 30 which would take us all the way to the bottom of our list. Now that's all we need to do for this iteration of the function. We can go ahead and close off the parenthesis comma over to the next argument, which is columns. And because we don't want to move from our starting point left or right, we're gonna use a zero here. And because we're not looking to return a range or array with a given height or width, we can close off the function right here and press enter. So it's pulled a random item in this case, elegant out of list one. And instead of reinventing the wheel, let's go ahead and just copy that formula. Paste it inward, too. And all we need to do is change that starting point from cell a one to be one, and we should be good to go. So ugly and artworks are the two words that were randomly returned. Last step here is just to contaminate them. Combine them together into that band name, so let's say equals the space in quotes ampersand for concoct Nate. Grab that first word and percent open quote at a space and then finally grab that second word press enter and there we go. The Magnificent Bubbles is our first randomized band name. And remember, because of the fact that ran between his volatile, you can either press the F nine shortcut or going to formulas. Calculate now to see this recalculate over and over and over so we could just call it a day right there if our lists we're not gonna grow or shrink at any point. But if we want to get a little more flexible, what we can do is actually replace this hard coded, maximum random number of 30 and use a function like Count A to make that reference more dynamic. And what that will do is it will count the number of items in that list, whether it's 30 or 40 or 50 or 10 and used that as the maximum range for our ran between function. So again, it gives us a little bit more flexibility so that we can add to or remove from these lists . So I'm gonna delete the 30 type count A and referenced the entire column. A locking in with F four and only catch here is because we have a header that's non blank as well. We're just gonna close out that parenthesis. Subtract one from that and we should be good to go suppressed. Enter there. We can go ahead and copy that same formula, paste it and where to change the references to be one and the count of column B press enter , and we are good to go. So there is our handy little randomized band name generator that we've built right here in Excel. 13. Combining INDEX & MATCH: all right for this pro tip going to talk about how to combine index and match functions to serve as a more flexible look up. Now, this is a four star formula tip, definitely a little bit more advanced. Now, if you're not comfortable with the fundamentals of look up functions or index and match, keep in mind you may have a hard time following along here may be worth going back and taking some of those foundational courses. Essentially, here's what we're gonna be doing. We're gonna be combining index and match to replace the purpose of a V Look up, and that's gonna offer several different benefits. For one, we won't have to. Hard code. A column index. In other words, Match is going to automatically identify our column headers, find the right match in the look up table and pulling values from the correct columns. Second are look up. Values won't need to live in the first column of the table array in the same way that they do with the V. Look up. It can live anywhere in the array that we're referencing. And then third, it will give us more flexibility for complex situations like trying to find the second or third instance of a match, which will cover in a separate protest. So let's try to visualize what's going on here before we jump into our demo. Considered data set like this got raw data and columns A through C looking at revenue by store by year. And what we're trying to do is populate columns D, E and F to bring in the city, state and country based on a look up table like this one. Got information about each store i d. A label on address, a city estate and a country. Now to see how this is all wired up. Let's pay attention to one particular cell like D four, for instance, which is pulling in the city name of Bremerton, and you'll see this pretty complex index and match function right there in the Formula bar . But essentially, here's what's going on. We're telling Excel to index the entire range of potential values from our table array below and then using match functions to isolate the specific row that we want and the specific column toe land at one individual value. So the first match function is looking for the store I D number in this case, Number three within column I from our table array. It's finding that store I d in the third row beneath the headers and telling Excel. All right, this third row is where I want to live. This is where we're gonna pull our values from for the index function. And then a very similar process happens with the second match function, which determines our column index. Except in that case, we're looking for these city name or the header name in D one within Row one or the header row of our table array. In this case, we find that city header. We match that header name in the fourth column, and that tells our index function. We need a value from column number four. So together, those two match functions are telling the index to move down to that third row and over to that fourth column and returning the intersecting result, which in this case is Bremerton. And that's the value that gets pulled back into our table up top and to sell Defour so definitely tricky to get a hang of at first. But a really powerful combination of functions to use so common use cases here, populating many look up columns without having to manually update the formulas. And there's column index numbers and to working with more complex scenarios. Like I mentioned, for instance, lookups, that may have many too many matches and multiple matches in that table array. So let's jump into Excel and practice writing one of these index match functions. All right, so in a pro tip workbook, look for combining Index and match Green Tab and our formula tips can link straight to it. And here we've got our revenue data by store by year and Collins A through C and these three columns that we want to populate using a look up function on a pole city, state and country for each store I d. Based on this look up table here in columns h through em. So for the sake of demonstration, let's start with a simple V look up formula first and see how that goes. So we look up the store i d from B to gonna fix just the column because that's where that story I d will always live. We're gonna look it up within the table array and note. I can't start in column H because that's not my look up a starting column. I I'm gonna pull I to through m 11 press F four to fix that reference and for the City column here. I want to return the value from the 1st 2nd 3rd column over. That's my column index number and my range Look up is exact match because I want to match the exact store I D. In that column, I so that populates Acapulco, which looks good, can apply it down, and everything is functioning as expected. And now here's how most people would continue with this. We need to populate to more columns, state and country. You could drag this formula over, and because we've set our references properly, all we need to do is change the column index from the third column to the fourth for state and from the third to the fifth for country. And there we go. We can grab those to climb down, and we've populated all of these cells properly in a matter of minutes, using a traditional be look up function. And there's absolutely nothing wrong with doing that. Gillick up is incredibly powerful. The only catch is that what if we had to populate Ah, 100 columns instead of just three. Now all the sudden updating all of those column index numbers manually starts to feel like a much more tedious headache. Now one option is to replace the column index with a match and combined V look up with match, which in this case would work just fine. The only downside, remember, is that we would still have to be referencing that Look up that store I d in the first column of the table array. So let's go ahead and delete these V look up functions. I want to show you the index match approach as an alternative. So we're gonna start with an index. That's always how we start because we're telling Excel. We want to grab one value from this entire array, and now we can include column H as well, and we'll fix that reference. And now the only two arguments that we care about here we need to tell the index which road to pull from and which calm to pull from within that array. And remember from the demo, we're gonna use a match function in each of those cases. So to identify the proper row, we're gonna say, Let's match the store. I d here and be tuned will fix the column. And where we looking for the store? I d. While we're looking for the store, I d in the table array where our story ideas live, which is calm I at four. To fix that in, use a zero for exact match and then close off that first match function. That's our row number that we're gonna feed into our index. Only other piece we need is the column number. Again. We're gonna take a very similar approach here. Is that this time we're gonna match our header name, which is currently City. And we're gonna fix just the row this time because our headers always live in Row one. And where we looking to match that header? Well, we're looking to match it in the hetero from art. Look up array H one through M one at four. To fix that one zero for exact match. Clothes that match function closed the entire index function and press enter. So there you go, we get Acapulco again. And now here's the beauty of it. If we take Acapulco, drag it over. Boom. We've populated state and country automatically without having to touch those column index numbers. And, like I mentioned that I d or that look up value does not need to live in the first column in this case that lived in the second column. And that means that we can do things like this and say, instead of country, give me the store label And there you go. It's gonna pull the correct labels, even though those were to the left of the store idea that we're looking up, which is something that it would be impossible with the V look up function. So there you go quick crash course in combining index and match to create more flexible look up functions. 14. Matching Items Between Lists: All right, Next up, I have a pro tip for you guys. It's a five star, expert level formula tip. These are the types of tips that you'll want to pay attention to if you want to become a true formula power user in Excel. And what I'm gonna show you is one approach to count matching items between two or more lists. And to do this, we're gonna combine these some product with the count if function to return the number of matching values across those lists now, in this demo will have two different tables. Arranges containing products, product list A and B. They may have different numbers of items in them, but we know that there is some overlap between them, and the goal is to use a cell formula to identify and count that overlap. So what we'll do is calculate a few items here. The number of items in list A and list be using simple count a formulas. And then here's the key. We're gonna calculate the actual number of matching items. In other words, the overlap between list A and B. So what's going on here is the count. If function is iterating or cycling through the rose in one of the lists, and it's checking if a match exists in the other list. For every road that finds a match count, if signs at one and for every row where a match doesn't exist, accounts a zero, and from there, the some product function basically just adds up. The resulting array of values and that total gives us the number of matching items. So one quick note here in this case were referencing list a first, followed by List B. You can swap the order doesn't matter. Either list can be used as the basis. So a couple common use cases here like we're showing here, just calculating the overlap between two lists and excel, or confirming that lists are unique to make sure that you're avoiding any sort of double counting errors. So let's go ahead and jump into the Pro tips workbook and actually practice writing one of these some product counted functions. All right, so in your protest workbook, we're looking for the counting matching items Demo Green tab in our Formula tips section, go ahead and press link and will jump out to our product lists here and scrolling through. You can see we've got two lists of products. Ah, product A list is about 100 units long. I List B is about 89 or 90. In fact, we can use count Day functions just to kind of return those descriptive stats. So count a call him a minus the header. It's gonna give us 100 items and column A and then same process. Here, count a column B minus the header. 89 items enlist beef. Now, one thing that's gonna help us out, Justus faras readability is concerned is to give these lists proper names. So instead of referencing a two through a 101 in a formula, I'd like to name this something like list A so I can select the first item hold control shift arrow down to grab that full list and in the name box here to the left of the formula bar, I'm gonna enter list a given in a proper name. Do the same thing with this Be control shift arrow down in this one list B. That's just gonna make our formulas a little bit easier to work with as we continue. So before I go ahead and start populating a function here and sell e five. The way I like to learn these more advanced techniques is to start with the component pieces, understand those really, really well and then assemble them together into the final result. So let's go ahead and do that. I think that will be helpful. That's right. Click and just add a column here. This is gonna be like some blank workspace. And if we ignore the some product component altogether for now and just focus on what the count if peace is doing, let's try writing accounted function right here, and the first range is going to be list A. And as you start typing, you'll see that named Range Populate Unjust Tab it in. And so we're gonna count the items in List A that match a given criteria, which in this case, let's start with Selby, too. Close it off, press enter. And now if we drag that formula down or double click to apply it, you'll see a series of ones and zeros going all the way to the bottom of List A. And what's happening here in each row is that the count? If function is saying, Okay, does B to exist within list A. Yes or no? If yes, return a one if no return a zero. And those references changed to be three before be five. So essentially everyone you see here in column c reflects a match. And in fact, if we select all of column C or the specific range and look at the sun, that sum is 33 which in theory should be telling us the total number of matches between these two lists. Now one thing to note is that I don't have to just reference be to here. It can change that reference to the entire list. Be reference and press enter and watch What happens when I apply that down? Nothing changes as I click through. You don't see the formula changing at all. But the actual reference the value that we're trying to match is changing row by row. So we still get that number. 33 matches between the two lists and now one other thing. If I swapped the order of the lists here in column D. Same approach count. If this time list be first, followed by list A. You'll see a different pattern of zeroes and ones because now we're checking for the items . Enlist A and mapping them to list B, but check it out. The total when you select the whole range is still 33 which is why I said that you can use either list as the base. So it's worth noting that you could stop right there and use a normal traditional some function toe. Add up these values and get you that matching item number of 33. But we're gonna take things a step further and create that value. Calculate that value without having to use these two helper columns. Instead, we're going to use some product which could essentially create these arrays within the formula itself to calculate the same some and return that value of 33. So show you what that looks like right here and sell F five. We're gonna start with the some product. And the array that were summing in this case is literally that array that we just created either version, which was based off of count. If so, we can type that same function we just did inside of the some product function count. If list. A criteria is list. Be again. Either order. Close it off and press enter. There you go. 33 items now we can actually delete these helper columns. Doesn't matter. Won't impact the formula at all. We still get that correct value of 33. Now, for those of you who aren't familiar with excels a race style formulas. You may be kind of scratching your head a little bit here because we've essentially just created a formula that behaves in a very unique and unusual way right. It's creating its own array of values behind the scenes and then processing that array to produce a single value. Now, when I was first learning about array functions, one thing that I found very helpful was using the evaluate formula tool. And what this will help us do is just break things down and diagnose how all these pieces are working together. So we're gonna start from the inside out. So this is a nested function, and we're going to evaluate this piece by piece. So list A, which is the range argument of the count. If is a simple s the cell range that were referencing a two through a one a one and list B is gonna be our list of criteria. And so what we should see when we evaluate this list, be argument is an actual array containing every product or every item within that list, which is exactly what we see here. 89 individual items all packed into one ray, and then the next underlined component is the entire counted function. Now that we've evaluated both arguments, we can evaluate the count if itself and check this out. This race should look very familiar because it's the exact same Siris of 89 ones and zeros that we generated by writing account. If cell formula on applying at 89 times or 2 89 rose, the differences were doing that here as part of a some product function and essentially replacing those 89 functions with one. So it starts to give you a sense of why these air a style functions are so powerful Now. Last piece here is that some product in this case, we're using some product in a pretty simple way. We're actually not even using the product part of it because we're feeding it just a single array all the some products going to do is add those values up. I do have one tip. We're gonna talk about you niks and duplicates where we add one level of complexity to this . But in this case, keeping it simple. Just gonna feed the some product, this one array, add up the ones and zeros and we evaluate that final step. There you go. We get our 33 and that should do it now. For those of you who are familiar with array functions, the question that I guarantee is on some of your minds is why didn't you use control shift ? Enter when you type this some product that you're calling Honore function. And the answer is some product is one of the few functions in excel that behaves like an array function but doesn't require the control shift enter approach. So just like I showed you, simple is pressing Enter and it behaves just like any other function. In this case, I actually could replace the some product with a simple some and actually used the array approach and enter this with control shift enter and I'll get the correct answer because again, we're not using the product component of some product. So either approach is totally valid. I actually prefer the some product approach and control z to get back to it just because, you know, I don't like to remember that I have to use control shift enter. I'd rather treat it like a normal formula. So there you have it. Great demo showcasing how you can use these tools like some product and count if to do really powerful things in excel. 15. Counting Duplicates with SUMPRODUCT: all right for this pro tip. I want to talk a little bit about counting, duplicate or unique rose in a list using self formulas. Now it's important to keep in mind that Excel does offer a number of different ways to identify or count duplicate and unique values. You could use advanced filters. You could remove duplicates and count the number of rows. You can plug your data into a pivot table and look at distinct items or even use tools like Power Query that have built in count distinct functions. But until there's an equivalent formula to count distinct, doing this with cell formulas alone can be deceptively tricky. But one really fascinating approach that I want to share is to use some product and count if to count those unique values and then subtract from the total to arrive at the number of duplicates. Definitely not the simplest way to get to the end result, but a fascinating one nonetheless. So what we're gonna look at here is my grocery list, which is just constantly growing. I forget what items I put on there, and as a result, I end up buying duplicate products. So what I want to do here is analyze that list and use cell functions to calculate total number of items arose in that list, the number of unique and any duplicate rose in cases where I may have written down the same product twice. So to do this, we're gonna use a few different functions for total items were going to use Count Day. In this case, we've defined the cell range before through B 52 as groceries. Now, the tricky one is this second item unique items here and to calculate those unique So we're gonna use a combination of some product and count if I'm gonna break this down and show you exactly how it's working in our demo. Then once you've got those two pieces, we'll be able to simply take the difference to arrive at the number of duplicates. So again, not my recommended approach, necessarily as an everyday tool, but a great way to see exactly how functions like this especially like some product, are really working. So different use cases here. It can be used to calculate the number of unique or distinct products ordered, for instance, or you can use something like this to help with error, checking to avoid inflation that might be caused by duplicate records. So let's jump into our Excel workbook and see if we can write one of these some product counted functions. All right, so we're gonna be looking for are counting unique and duplicates demo. A green tab within the formula tips section can link straight to it from the table of contents. And here you can see my grocery list going down to about Roe 52 and the first thing I'm gonna do before I write a single function is give this grocery list range and name. Click the first item control shift arrow down to grab the last item and in the name box right to the left of the formula bar, give it a name like groceries. That's gonna make it much easier to reference this list in our formula. Names moving forward. So first things first. Total number of items. That's a simple is using a count a function count a council, non empty cells in a range and arrange is gonna be that groceries list that we just created Tab that in close it out, press enter. So I've got 49 items in this list, 49 rose on my grocery list. Now the key is to try to figure out how to calculate, quantify the number of unique specifically and just like I showed you with the matching items tip earlier in the course. What I'm gonna do here is actually break down this approach into its smallest, most component parts. Understand exactly how the mechanics of those pieces air working, and then we're going to reassemble them together into our final formula. So let's go ahead and add two new columns here in insert to Collins to the left of deep. And this will be like our sandbox that we can play with here. And we're gonna start with account if function and we're going to use count if to simply return the number of instances of each item within the entire list. So to do that, we're in a type count If and our range is the entire list groceries and for now, for criteria, let's select the first item in the list. Close that parenthesis. Press enter. Now. What this number tells us is the number of instances of this product out of the entire grocery list and right off the bat, we've got a duplicate here. This tells me that green ribbon canned peaches appears twice in the list. So as we drag this down to the entire list, we're going to see the number of instances associated with every single line item in the grocery list. So Washington cranberry juice only appears once mango drink appears twice. In fact, here's the second instance of Washington mango drink, also with the label of to. And then, for some crazy reason, it looks like I've entered club string cheese four different times. I think I might have really been hungry for cheese. So as you can see, this new column has basically told us the number of instances of each product in our list. Now, one little tweak that we can make its a helpful tip here is that we don't actually have to reference before. If we referenced the entire list again and apply this down. Even though we don't see the reference changing row by row, it's accomplishing the exact same thing. It's iterating, item by item, counting the number of instances. Now that's great. It's helpful to have the number of instances, but It doesn't get us all the way there as a next step. What we need to do, it's actually create a new calculation here and take one and divide it by that value we just created using that counted function, and I'll show you exactly why we're doing this in just a second. Let's apply it all the way down to the full list, and I look. What's happening when you can start to notice is that we're essentially assigning a value of one to every unique item in the list every unique product. That means that the items that only show up once like Jeffers, oatmeal or Queen City map we'll get a value of one here, since one divided by one equals one. But the catch is that for items that show up more than once, we're essentially taking that credit of one for the product and spreading it out Among the number of instances. So Washington mango drink shows up two times, and each of those instances gets half credit or 0.5. That way, when we sum up this column, we will not be double counting any product that appears more than once. In fact, for cases where we have two or four instances. Those fractions altogether will never equal more than one. So if we select the entire list we can see down, here are some is 44. That's the golden number. That's our number of actual unique items or rose in our grocery list, so we could simply take the sum and plug that some function in here and be done with it. But just like our other examples, we don't want to have to rely on these helper columns here. Let's assemble this counted function into a some product, which is essentially going to create this whole array and sum it up all within the function itself. It's gonna act like an array formula. So let's go ahead and type equals groups, some product. And all we're gonna do is write those same functions that we had just used in columns D and E, but nest them inside of this formula. So some product one divided by count. If groceries comma groceries, we're gonna close out to parentheses and press enter. And there you have it, 44 unique items or rose in our list. And now that we know the total and the unique weaken simply take the difference and arrive at the number of duplicate rose. So we get number five here. That means we had one duplicate row for canned peaches, one duplicate for mango drink, and those three duplicates String cheese rose in our list as well, adding up to fight. So now I can delete D and E, and we'll still get our proper value. And there you have it. So certainly not the easiest or quickest way to arrive at that number, but a really good opportunity to practice some of these more advanced functions, like some product. 16. Many-to-Many Lookups: all right. This next tip is for those of you who are trying to dive deep into expert level territory with your excel formulas and functions, we're gonna talk about how to use count if, along with index and match, to help handle many too many look ups and excel. Now, generally speaking, Excel is designed to work with 1 to 1 or one to many relationships. In other words, if you're trying to connect or join two tables together using look up a reference functions , at least one of those two tables should only have one instance of your look up value or primary key. If that's not the case, both tables have multiple instances of that look up value. Then you have what's called a many to many relationship. So to give you an example of what that might look like, take a look at this data set. We're looking at revenue data here, tracked back to an order i D. In column A and a store i D and column B. And the goal is to populate this address column based on a look up table like the one you see below. Now, if you look closely you'll notice that two of those stores I. D. Number five and I. D Number eight, have multiple instances in that look up table. And because we have multiple orders tracked from each of those stores, were stuck with a many to many relationship here. And to give you a sense of why this is a common problem. Think about what would happen if we used a traditional V look up or index match function alone on this relationship, you tell Excel toe look up Story D Number five. It would search column G and the look up table, and it would stop the first instance, returned 59 22 LaSalle court and have no idea that there was ever another instance of store I. D. Number five in that table. So the good news is that there are tools that we can use to handle situations like this, and in the approach that we're about to demonstrate, we're going to use count. If index and match functions toe actually return values from subsequent look up matches. So in this particular case, the address from the second instance of store number five or store number eight, so to give you a sense of how this is gonna work. Let's quickly hone in on one of these cells. Cell E four, which is 67 64 Glen Road, The address for store number eight. And that's the second address in the look up table. Now the key to getting that second address is this new column seat. A number of instances and that column is driven by account. If function. That's basically counting the store I D in each row and checking to see how many instances of that I d exist in column g of our look up table. So for most of those store I DS, the number of instances and column C is one. But for ideas five and eight, that counted function returns to since their two instances of each of those I DS and what that piece of information allows us to do is continue writing an index and match function like we normally would. But instead of stopping at the first match of the store, I D were able to jump down to subsequent rose in cases where there are multiple instances of that, I d in the look up. So in this case we're matching Store I D. Number eight, jumping down one additional row and pulling in the resulting address. 67 64 Glen Road. Now very important note here. You've got to understand why there are multiple instances of your key, and it's very important to confirm that they are, in fact, valid records. Because many times when people run into this many to many relationship issue, it's indicative of a deeper problems with their database, in which case formula workarounds like this really won't help solve the root issue. That said, there are some common use cases that do create situations like this. One example is if you're tracking historical changes within an existing look up table. So maybe you have a product whose price changed over time, in which case you've got the same product i d. But one of its attributes, in this case retail price has changed. Or maybe in the case that we're looking at here, store number five in store number eight moved locations, but we want to track it as the same entity. Both of those are potentially feasible explanations for why we could run into a relationship like the one we see here. So with that, let's jump into Excel. Open up our pro tip workbook and let's see what we can do with one of these many to many relationships. All right, so from our table of contents, we're gonna head to your green formula tips section. Look for the many to many lookups demo. Go ahead and link right out to that sheet. And here we've got revenue tracking based on order I DS map to a given store. I d here in column B and like we talked about, we're gonna try to populate this address column using the look up table here in Collins, F and G. But remember, the catch is that we've got to stores here five and eight with duplicate rose in the look up table. So if we were to write a traditional index match function, for instance, we're gonna index that look up range right from F G, lock it in the row number that we want to move to is wherever were able to match that store I D and beat to and out of habit. I'm gonna lock the column B and where we trying to match that sore? I D. Well, we're trying to match it in the list of store I ds in column f Lock it in, come over exact match and clothes that match function off. So we're indexing that range. We're moving down to the row in which we match the store i D. And the column that we want in this case is calling number two. And that should do it for the index match function Press. Enter and apply it down. And just like you'd expect, we're getting the correct values for stores. 1234679 and 10. Start to captains are away. Store six. Mitchell Canyon. Here's the catch Store eight Buenavista, which is the first instance. Store five LaSalle again first instance. So on its own this index match function same with the V. Look up. Had weak on that route has no clue that these rose even exist in the look up table. So we need to build another piece of information to help us account for that. And that's where that instance column comes into play. So I'm gonna add it. Call him here between B and D. Call it the number of instances and It's a simple is account if function and the range in which were counting is the store I d range from the look up. We'll lock that in the criteria. What we're trying to count within that range is our store I d. In this case, Selby, too. Close it out, entered in and drag it down. And there we go. So now we've effectively flagged or labeled the store I DS that show up more than once. Everything in here is a one, with the exception of our eighths and are fives, which is exactly what we'd expect to see here. And now that we have this additional piece of information, we can utilize this data and integrate it into our index match function to jump down one extra row, but only for those store I DS Onley for five and eight. So how can we do that? Well, we look at her index function. The row number argument, which is where our match function lives, is what's telling excel which road pulled data from. So we need to upend some value after this match function to jump down one additional Row four stores five and eight, and we'll need to do that by using this new number of instances column that we just created now in column C. But if we were to just add the value from column C, take a look at what that's going to do. It's actually going to jump one extra row for every single store and two extra rose for stores five and eight. And if we were to do that, as you can see, everything gets thrown off. So store to is now returning. Ramsey Circle, which is one off store 10 is returning a ref error because it's pushing it outside of the look up range. And then for five. We're still getting the wrong answers because now we're finding the first instance of five and jumping to more rows down, which is more than we need. So with a quick adjustment here, all we need to do is instead of adding C two, we're gonna add C two minus one. Close the parenthesis and hit enter and now check this out. Perfect. So now all of our other store's 1234679 10 are returning the proper values. And now look at this store. Five for 90 Risdon Road, which is the last or the second instance of I D number five in that look up number eight Glen Road Exactly what we're looking for so you can continue to customize this as much as you like. You can add additional logic. You can nest additional functions, but hopefully this gives you an idea of how some of these tools, like count ifs and index and match, can be used to accommodate cases where you might have many to many relationships like this .