Excel: Advanced Formulas & Features to Create Efficient Team Workflows | Al Chen | Skillshare
Drawer
Search

Playback Speed


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

Excel: Advanced Formulas & Features to Create Efficient Team Workflows

teacher avatar Al Chen, Excel Trainer & Coda Evangelist

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.

      Introduction

      2:32

    • 2.

      Use OFFSET W/ AVG & SUM

      11:04

    • 3.

      Pick Scenarios Using OFFSET

      9:03

    • 4.

      Make Decisions W/ IRR & NPV

      10:15

    • 5.

      Search W/ Ease Using Wildcards

      9:48

    • 6.

      Find Optimum Inputs W/ Goal Seek

      5:09

    • 7.

      Complex Optimizations W/ Solver

      12:09

    • 8.

      Final Thoughts

      0:58

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

285

Students

2

Projects

About This Class

Learn advanced features and formulas in Excel to make better business decisions with your team!

This class is all about using some advanced features and formulas in Excel to help make your model, dashboard, and analysis more efficient and impactful. Excel is great for doing scenario analysis--or figuring out what the impact will be when you choose option A, B, or C. By using the skills taught in this advanced Excel class, you'll learn how to do better scenario analysis and ultimately make optimal business decisions.

A sample of skills you'll learn in this class:

  • Using the OFFSET function to create more robust models and scenario analysis
  • NPV and IRR functions to figure out the profitability of different opportunities
  • Finding optimal inputs with the advanced Goal Seek and Solver features

Wow your teammates and business stakeholders by using these advanced Excel features. You'll be actively using the Excel workbook I created for this class (see Projects & Resources).

Who is this class for?

Advanced Excel users, but all students are encouraged to participate and enjoy. 

Prerequisites:

This class moves fast. You should have a basic understanding of Excel including:

  • Navigating around Excel
  • Writing basic formulas (including VLOOKUP)
  • Business/finance metrics like revenue, expenses, net profit, etc.

Some classes to consider taking as a prerequisite:

Software

I’ll be using Mac Excel 2016 for Office 365. You can use any of the following versions of Excel for this class:

  • Windows: Excel 2019, Excel 2016, Excel 2013  
  • Mac: Excel 2016, Excel 2011

Meet Your Teacher

Teacher Profile Image

Al Chen

Excel Trainer & Coda Evangelist

Teacher

I have been an Excel power user for 10+ years. I started learning Excel when I was a financial analyst at Google. 50,000+ students have taken my online Excel classes and I have facilitated in-person workshops to over 5,000 MBA students around the the U.S. 

I founded KeyCuts, an Excel training and consulting company to Fortune 500 companies. If it isn't clear I'm addicted to Excel, perhaps my podcast about Excel and data analysis (Dear Analyst) will convince you :). 

Outside of Excel and spreadsheets, I work full-time at Coda, an all-in-one doc for teams. If you would like to read my full journey with spreadsheets and Coda, check out this blog post.

Feel free connect with me on LinkedIn and ask me any questions about Excel, ... 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. Introduction: Hi there. My name is Al, and I'm a solutions architect at Coda, but I've also been an Excel power user for many years. I first started using excel at an internal finance team at Google. I'm really excited to teach this class to share with you some advanced features, and functionality in Excel that will help you, and your teammates be more productive, analyze data better, and ultimately make better business decisions. Today's class is all about how to use some advanced features, and functionality in Excel. These features include offsets, NPV, and the Goal Seek feature to help you find the most optimum input for your model. Now, this is an advanced course, but I'm going to walk you through all these features step-by-step so you can apply them to your models. I see a lot of teams struggle, and make bad business decisions or investment decisions when they don't use some of these features. They tried to put together a formula in an ad hoc way. While many Excel classes teach you about how to be more productive, and how to automate tasks. This class is all about how to think more analytically, and critically about your data. The key takeaway I want you to walk away with is how to use these functions, and formulas to make better investment decisions. Whether you work in finance, marketing or another field where you have to evaluate multiple opportunities. There's a lot of dollars or a lot of resources on the line. You'll walk away with the skills you'll need to help your team think more critically, and analytically to make better business decisions. For the class project, you'll pretend to be an analyst at a big online retailer. Your goal is to help this online retailer figure out what new product lines to invest in, given historical revenue data. You'll also try to predict future revenue using growth rates, and also using Goal Seek, and advanced feature in Excel to find the most optimal growth rate given the constraints in the model that you'll eventually built. I really hope that you'll post your questions in the discussion forum of the class, and also post screenshots, and questions about your class project in the project section of the class. Now let's go ahead, and get started with Lesson 1. 2. Use OFFSET W/ AVG & SUM: This lesson is all about how to use the offset function in combination with the average and the sum functions. This is a really useful function, especially for time-series data, because it allows you to quickly sum up or average data across months, quarters, or years to quickly share this data with your teammates and your colleagues. We're going to write a basic offset function in Step 1, where we're going to reference the first month of new MRR in cell C25 in this table below and we're going to offset this cell by two rows and three columns. It is a really, really basic offset function. Before we get into doing this up, let's just quickly take a look at our simple growth model here. We have revenue and customer growth here with a few assumptions. We also have a monthly recurring revenue here for new MRR, lost MRR, the growth rate. We won't be using all these different metrics but just giving you an idea of what this looks like. Let's start writing this offset function. We're going to start in cell C25 so equals offset. Now the offset function can take up to an optional number of arguments, five arguments in total. In this case, we're only going to use the first three reference rows and columns. Heightened width we'll use later. But for now, let's go to offset. We're going to click on our reference, which is C25. We're going to offset it by two rows and three columns. Two rows, see how this row is the second parameter. Then three columns and hit "Enter". Let's look at what this does. Ten thousand four hundred and forty-five is the number. Remember we offset this by two rows and three columns starting in C25. A basic offset function like this, all it does is it goes down two rows, 1, 2, and then three columns to the right, 1, 2, 3. That's how we get to 10,445 in this offset function. You can also use negative numbers here. If this was negative two. This will go from 2,373 up two rows, and negative columns will go backwards as you might imagine. This is a basic way of using offset functions, but the offset function really comes into play when you're building some more advanced models and you want to summarize the data or average data depending on what your needs are. Let's go to number two. We're going to write an offset function below, that uses the starting month and ending month inputs, these two cells right here, to sum up, the new MRR revenue for a specific time period. We want to give our teammates the ability to enter in different number of months here. This could be one for January, two for February, and notice how these month numbers are along the top right here. If it's 3-6, we want to sum up all the new MRR revenue from March through June. This sum should be basically 3-6 right here and the total should be $14,482. Let's go ahead and start writing this offset function right now. We're going to start using a more advanced use case of the offset function. Offset, we're going to start with the reference, which is C25 again. Now, do we want to move this reference around by any rows or columns? In this case, no, we want to stay on this cell. That's where we're going to put comma zero, comma zero. We tell Excel, we don't want to shift this reference around by any rows or any columns. Now the height and width is telling Excel how big of a selection, what's the range that you want to put inside this offset function? Let's test, for example, if I put one as the height and five as the width, let's see what happens as an example. Nothing happens, but what we're telling Excel to do, is take everything from cell C25 and take a width of five cells, this will be 1, 2, 3, 4, 5. What does that mean? This means that right now this formula, it says that there's an error, but that's because it's returning the reference of these five cells. If I add a sum around this offset function, it's going to give me the sum of these five cells; 1, 2, 3, 4, 5, 15,279, and matches 15,279. You can see how we can construct this offset function to put inside or nest within a sum function or an average function or whatever it is that you want. Let's build this offset function to take into account these starting months and ending months. I'm going to delete the sum function for now. Let's just zoom in a little bit. Instead of putting 0015 here, we want to tell Excel if there is a starting month that's greater than one, so maybe it's starting March in this case, we want to actually shift over the columns by a certain amount. Instead of this zero for the columns parameter, I'm going to select the starting month and then put minus one. The reason is because if this is three, this will start from C25 and we'll go 1, 2, 3, and notice how this would put the reference in cell column F, which is Month 4. We actually will want to take the starting month that our teammates and colleagues enter into the cell, subtract one, so that this becomes number two. This will be starting from C25 will go to 1, 2, and properly start in Month 3. We've shifted over to a recap to start in C25. We're shifting down zeros and we're shifting over to three columns, three months in this case, since we're entering the number three here, the height is how wide or what's the height of the range we want to use. In this case, we want to stay on Row 25. Here is the last parameter, which is the width. How do we tell Excel basically we want from cells 3-6? We can take this ending month number that our colleagues enter in. All we can do is take 6 minus 3 and then plus 1. The reason why we want to do that is because the width has to include the first and ending cells. What that looks like is this. This part of the parameter equals to 6 minus 3, which is 3 plus 1 which is 4. What Excel is going to do is start in cell E25 and then go over 1, 2, 3. This is the width, the four that we just talked about, which is the C11 minus C10 plus 1. Now that we have our offset function written correctly, we can not put a sum around this. Now we have 14,482. If I take three through six remember starting month is three ending month is six, 1,442 I change this to a one, this will now be one through six. This is now 19,536. If I change this from a seven through twelve, this will take from July through December and 36,991. This offset function is really interesting because it can return you a given range of cells depending on how you enter in the parameters here. Then once that range is returned, you can then put a sum function around this or an average and that's what we did here in this nested sum offset combination. Let's build another variation of the sum and offset function where we're going to sum up a new MRR revenue starting Month 1. We are always going to start in Month 1 right here. Then we're going to set the ending amount or the ending month as whatever you entered in step two. We're going to go to any month here. Let's move this back to six for instance, and this would be one. Essentially we want to tell Excel is if my colleague enters in a six here, I want to take everything from one through six. If my colleague enters in a seven here, I want to take everything from Month 1 through Month 7. But we're going to build this in a different way by writing the sum function first and putting the reference to the first cell, which is C25 because we're starting in Month 1, column how do we tell Excel which cell to go to, depending on what my colleague enters into cell C11 the ending month. Normally you might just put C25, then put something like that and have a simple reference. But in order to reference that cell, we're going to write an offset function. I didn't write offset correctly. We're going to start with C25 yet again. Then, how many rows do we want to offset? We don't want to go any rows below, so we're going to put a zero there. Then how many columns do we want to offset starting from new MRR? That's going to be the ending month. We're going to put the ending month. This will shift things over by 1, 2, 3, 4, 5, 6, 7. This puts us in a Month 8, so we actually want to do a minus one here to account for that extra movement over on the cells. Now if I put a closing parenthesis around this, this gives me 24,262. If I sum up Month 1 through Month 7 here so Month 1 through Month 7, 24,262, 24,262. This is just a different way of building out this sum function using offset as the second reference cell because we're telling Excel starting in C25 move over to the right, a given number of columns, in this case, 7 minus 1. This part of the function actually returns back to the sum function, this cell right here. We're telling Excel to sum everything up from C25 through H25 using this offset function to reference the cell. Practice using these two versions of the offset function. Typically you'll use this version of the offset function with the sum function more often because you're typically trying some different parts of your model up. This one could be interesting if you have your team as provided inputs into where they want to sum up data or average data. But this is a little hard to debug, whereas this one is a typical nested function where you have offset within the sum function. 3. Pick Scenarios Using OFFSET: We will continue to use the offset function, but this time for a different use case scenario analysis. You'll typically use a function like the choose function when you're doing scenario analysis, but the offset function has some advantages because your teammates and colleagues can add new conditions or new assumptions to the scenario analysis. The offset function will automatically pick up these new scenarios without you having to adjust the formulas. You can automate your modeling a little bit with this tip. We're going to continue using this SaaS growth model we started using in lesson one. But notice how the new customers line, this is now missing. We don't have any data except for the first month of January 2020, lesson one, we had some hard coded numbers here, but in lesson two, we're going to use the offset function to help our teammates select different scenarios using different growth rates to predict with a new customer amounts will be. This is really common method you use when you're building out a model, and for just to quickly understand what we're going to do here, we're going to give our teammates ability to enter the numbers 1, 2 or 3 here. Depending on what option they pick, this will correspond to different growth rate assumptions. If they picked one, this will correspond to 10 percent. They pick three, this would correspond to 20 percent. Then these numbers will grow depending on what growth rate is entered here. Let's start with step one, and we're going to write a basic choose function starting in cell D22, which is right here. We're going to take into account the growth rate that's selected based on what our teammates enter into the scenario in cell C8. Let's us go off over here really quickly and figure out how the choose function works. I write equals choose. It's a very basic function. All it does is takes in a few different parameters. The first one is what cell contains the number you're going to choose or the index you are going to choose. That's going to be our enter scenario cell which is C8. Then in value 1,2,3, notice how these are all optional arguments. I'm going to select D12, then D13, then D14. Hit "Enter". I'm just going to paste this formatting over here. Notice how this corresponds to 20 percent, because we're telling Excel is based on what number is entered in here, the index number, take that number from the options I've provided in values 1,2, and 3. Since this is three, it takes D14. If I pick one, it gives me the 10 percent because that's the first value it might choose function. Two of course, will be 15 percent. Now that we know how the choose function works, we can use this choose function in the formula to predict new customers in future months in 2020. All I'm going to do is write equals the previous month times parentheses one plus. Let's write the function one more time the show. We're going to choose based on what's indicated here by our teammates and then provide the different rates. The growth rates fall by commas. Then that's it. But we're actually going to want the I want to make sure we do here is lock-in. That's all references because we're going to drag this formula over to the right. Notice how this is 39 percent growing by the second growth rate assumption, which is 15 percent. Let's just just drag this over to the right. You'll see how the numbers proportionately increase based on what growth rates are entered. I put a one here, it'll grow less fast. I put a three here grows more. That's how we can use the choose function to do a quick basic scenario analysis in our model and gives our teammates ability to change the assumptions here that affects the rest of the model for MRR and our model. In step two, let's add two more growth rate assumptions to the customer scenarios. Let's just do the function we wrote in D22 accordingly. Let's say we want to add in some new assumptions, growth rate, assumption number four. This will be option number four. Let's say this is the worst case scenario we are going to put five percent. Let's do another one. I'm just going to just copy this and change the number. This will be the most optimistic scenario, which is like 25 percent. Let me just copy the formatting down here to these numbers. How do we take into account these new assumptions because our view, recall, our original function only takes into account these three. Anytime you add new assumptions, you'll then have to go back into your choose function and put in the additional options. I have four. I make that as reference. Then I have this fifth one. Is it this one? Let me do that again. The fifth option is D16. Lock that in and then like I did before, I have to drag this all the way to the right to make sure that this accounts for the new rates or new growth rates. If I put four here, this will be five, this broke. That's fine. But you can see how if you have a really complex model, you will have to constantly adjust the choose function here and then drag those formulas out if you have new growth rate assumptions. Similarly, if you delete new growth rate assumptions, this won't work anymore. I think there's actually just defaults to zero. Notice this is all three fours. Doesn't really handle adding and deleting growth or assumptions here in your model which makes scenario analysis really hard because you have to constantly debug your formulas to account for these new growth or assumptions. For step 3, we're going to use the offset function one more time. Instead of using the choose function let's use the offset function and D22 that picks the correct growth or assumption. The best part about this is it won't matter if we add or remove growth or assumptions to our list. Instead of using choose, I'm going to delete all this and write offset. I'm going to start in cell D11. If you recall how the offset function works, we have a reference here. We're going to lock this reference in for now. Now how many rows do I want to offset this by? Well, we know that our teammates are going to be entering that scenario number here. I can simply use this input as the number of rows to offset, I I want to lock this in so it doesn't move when I drag this over. If this is five, this will go from rate 1, 2, 3, 4, 5. If it's one that will go just down one. Number of columns I want to offset over this will be zero, and this should just work. I think I might have got a closing parentheses. In this case, if I put one here, I need to drag this over to the right. Look here, how if I put in number one here, I put a two here, three, it does the same thing as the choose function. But the cool thing here is if I automatically add in a new growth or assumption, just copy and paste this again. Make this a five. This will be 25 percent. I don't have to readjust this offset function again because it already knows a look at this and offset my growth rate assumption by however many cells starting in D11. If I put five here, it already picks up this new cell. Because what, again, just to recap what this is doing, it's offsetting from D11, going down five rows, 1, 2, 3, 4, 5, no columns. It returns that growth rate back into my formula. Now I can quickly add assumptions. I can delete assumptions. Well deleting would also not really work because I have to, this will just be defaulting to zero. But the cool thing is I can add new growth rate assumptions and the beauty of the offset function is that it would pick up those new growth rates depending on what my teammates or colleagues enter into cell C8. 4. Make Decisions W/ IRR & NPV: IRR and NPV stand for internal rate of return and net present value. These are functions that are really important for you and your teammates to learn because they can help you evaluate different investments or business opportunities given certain risks and profitability of these opportunities. This will help you create a business case to bring to management or to whoever on which opportunity to pursue. Now these functions are actually pretty easy to use, but knowing these scenarios and the specific environment in which to use them is going to be important. I'll talk about all that in this lesson. The goal of this lesson is to teach you some formulas and frameworks to help you and your teammates figure out how to evaluate different investment decisions. Especially if you're looking on investing in real estate or machinery. This is all about how to use formulas to help you guys collaborate and figure out how to make a sound business decisions. Before we get into the steps here for this lesson, let's quickly take a look at some of the assumptions in our fictitious real estate model here. We have some assumptions here on. We're basically looking at a real estate investment and this specific real estate property has a thousand square feet. The rent per square foot of $50. The OpEx is $25 per square foot. The purchase price of $10 million. We have a few other growth rates here for income expense and also the expected cap rate. We have a very abbreviated income statement which shows our gross revenue every year. Starting in Year 1 and all the way through Year 11. This number simply grows by that growth rate we have here in the income operating expenses is the square feet multiplied by the OpEx per square foot. This also grows at a fixed rate every year, which gives us our net operating income in Row 32. This is our income statement, and down here is our statement of cash flows. Again very basic in before Year 1. Before we generate any cash flow. Our first outlay of cash is our purchase in Year 0 so it's negative $10 million for this investment. We are pushing this property and then our cash flow from operations is simply the NOI, the net operating income every year starting in Year 1, Year 2, etc. Then the net cash is simply the net amount. Now you'll notice here that in Year 10 we have our NOI, but we're missing the cash we get from selling the actual property because we want to see this investment is worth making a seller property to hopefully make a profit, and this number is missing. Our goal is to figure out what that number is and also to figure out some other formulas and metrics to figure out whether or not we should invest in this property versus some other property in this real estate example. In Step 2, the cash flow from sale in the casual segment is missing and we're going to assume that the property that's $10 million property is sold in Year 10. Calculate this value by dividing the NOI itself by the cap rate. We have a cap rate assumption which is 10 percent. All we need to do here for us to calculate the cash flow from the sale of this property after 10 years of holding onto it and getting the cash flows is taking the net operating income at the end of Year 10, which is this number, 2.392 million. Then we're simply going to divide that by the cap rate to get the expected sale price. We have an estimated sale price of $24 million. It's rounded up. That's assuming that we get these expected cash flows and they grow every year and that by the end of Year 10 this is already going to be our expected net operating income, which becomes our cash flows for Year 11. If we were to hold onto the property, in this case, we're selling the property at the end of Year 10. Our cash from the seller's property is going to be equivalent to this number which you calculated, which is dependent on the cap rate. If you look at our cash flows over the period of the 10 years. We have negative 10 million for the first purchase of the property. We have then cash flows from Year 1 through 10 and at the end of Year 10, we sell this property for $24 million or cash. At the end of Year 10 is $26 million. It seems like a pretty good investment. Let's take a look at what the internal rate of return is. Now that we have a complete cash flow statement. Again, this is a very simple cash flow statement, but this is more to show the power of using these formulas to help evaluate different investment and business opportunities that your company might be facing. We're going to calculate the internal rate of return on this investment by simply doing equals IRR, or internal rate of return. I can literally just give all the values from my net operating income. Each of these values is going to be plugged into this IRR equation. It will spit back out the discount rate at which my net present value of my cash flow is zero. Let's once again equals IRR. I just realized we are using the wrong numbers. We should actually be including the net outflow, which is the 10 million. Let's include all this data. There we have the internal rate of return is 20.7 percent. I forgot that you can't just use the yearly annual cash flows. We have to include the initial outlay, which is $10 million, as well as the final sale. I was using the net operating income by accident and should be using the net cash. This is the internal rate of return on this project assuming and this again, the IRR, I'm not going to get too deep into what internal rate of return is. But this is a number that you can compare across your various investment opportunities to see whether or not this is a good investment. If I found another investment that had a 25 percent IRR then I would obviously go with that investment over this one, because this is the discount rate needed to have the net present value of our cash flows to be zero. Now here, I just talked a little bit about net present value. Let's take a little bit into what net present value is and how we can calculate that by using the NPV function. We're going to double check that this net present value of this number using this IRR is zero by using NPV function. I'm just going to write equals NPV and for the first parameter I need to input the discount rate, [NOISE] which is the internal rate of return, which I just calculated so 20.7 percent, then similar to the IRR function, I have to provide a bunch of values. Now, [NOISE] I'm first going to just select all the cash flows from Year 1 through Year 10. I'll show you what this looks like after we do it. Notice how this equals the net present value of these cash flows. Assuming a discount rate of 20.7 percent is $10 million. In reality, in order to check that this NPV is zero for this rate. You would actually also input in the initial outflow of cash, which is this negative 10 million. As you can imagine, this will become zero. This basically shows that our IRR is correct. But it's important to know that the net present value of all these cash flows should equal to your initial outlay, which is $10 million. That's why you'll see this as 10 million, we don't include that negative 10 million here in the purchase in Year 0. But if I do include it obviously it sets everything to $0. Now what's interesting here is if you change this discount rate, you'll notice how the NPV will change as well. Remember how the first parameter in this NPV function is the IRR. Let's say I decrease this investment IRR to 15 percent. Now our net present value has increased because we are looking at a lower discount rate to discount our cash flows to the net present value. This will not be the correct IRR for this project because our net present value is greater than zero. Of course, if I increase this to beyond the IRR to 30 percent, then the net present value of the cash flows is negative. Since again, the first parameter we're using in the present value is indeed the IRR. Typically when you're comparing investment opportunities, you might use a combination of the IRR and also the net present value to get the absolute dollar amount for a given investment. Let's bring this back to 20.7 percent. If I were comparing investments just based on their internal rate of return, I will just look at this number. But sometimes when you're comparing different opportunities, you might also want to look at the absolute net present value of cash flows to get an idea of how much cash is coming in. You might remove this initial outlay to see what that total net present value is, assuming various discount rates. 5. Search W/ Ease Using Wildcards: Sometimes you don't know exactly what you want to sum up or lookup or average in your dataset. That's when you can use wildcards in Excel to do some fuzzy searching for your data. You can use this lesson to build a fuzzy search algorithm for your teammates when they don't know exactly what they're searching for, but they know a few letters or something in the name itself. Searching with wildcards is one of the most underutilized features in Excel. The reason why I like it is because it really gives you and your teammates a way to search for data but not knowing the exact spelling or order of the characters in the list. I think it's a much better way of looking for data and you want to set something up that's easy for your teammates to use. Just an example, we're going to count the number of addresses below that end with the letters street, St, using a COUNTIF function. You'll see these addresses below. Some of them end with south, some of them end with drive, Dr. We see that a few that have St, St St St. You could do a variation of the mid and find function, which we've done and which I've shown in previous classes. But wildcards really make this a much easier exercise. You can Google for all different wildcards available. But I'm going to show you the most common ones that I use when I need to search for data and I don't know how the data is laid out specifically in the list. We're going to write a basic COUNTIF function, which we've all done before. We're just going to go through our range here, select that. The criteria here is instead of doing equals some specific value or whatever, or greater than or less than some value, we're going to write double quote, then the star St, double quote. We get the number four. You can quickly eyeball this and see there's four addresses with the letters street at the end. What's actually happening here is Excel is going through this entire list. This star basically tells Excel, I don't care what comes before the letters St, as long as it ends with the letters St. Then I want to count that in my COUNTIF function. If St is somewhere in the beginning or in the middle, it won't count it. This star basically tells Excel any characters, any numbers, letters before St, it's cool. I want to include it in my COUNTIF function. You can see here if I put in Rd here, it would only include this one specific address with Buttermilk Road. If I put Dr. here, then it has only two addresses that have Dr. at the end. The star one is one my favorite ones because it's like doing a fuzzy search on the list when you don't know exactly how things are spelled. You don't know what's going to come before the letters or after letters, but you know that without a doubt it has to end with St in this case for street, because that's where you're interested in, in terms of your dataset. Now we can also use other functions using these wildcards. We're going to sum up the number of sales for these customer IDs. We're only going to look at the customers that have nine characters in their customer ID using the SUMIF function. You'll notice that some of these have 1,2,3,4,5,6,7,8, 9 characters, but other ones have 10 characters because they might have an extra letter in there customer ID. We only want to sum up the sales that have customer IDs with nine. Maybe the customers that have nine customer IDs are like the old legacy customers, something like that. One way you could do this, of course, is we're going be doing a length of the cell. Knows how that's nine and there's a few of 10. Then you can sum the customer IDs that have a specific number, in this case nine. But with the wildcards again, I want to stress how important it is to use wildcards in certain situations. Is that you don't have to do this extra formula on the side. This question mark wildcard tells Excel only include cells that have exactly that number of characters in the cells. I'm going to write SUMIF. The range is going to be this list of customer IDs. Did that by accident. Now the criteria is going to be question marks. Again, you put the number of question marks you want to tell Excel, here are the exact number of characters in the cell that I want to include in my criteria. We're going to do 1,2,3,4,5,6,7,8, 9. This tells Excel only pull back the values that have nine characters, not 10, not eight, just nine, comma. The sum range would just be the sales and we have 14,914. You could double-check this by, notice how we did the count a over here. We could do equals SUMIF, just doing this double-check our work. SUMIF if this is equivalent to nine, and then sum this up, we get the same number, 14,914. Using a combination of the question mark wildcard and the star wildcard, this allows you to do some pretty powerful searching across your data. I'll just Google to see some of the top wildcards that you can use and the combinations. There are some really cool combinations you can do. But using a combination of the star and question mark wildcards are probably the most common ones that I use to search my data. To show you how this might work for your team, we have our same data and now we have address plus customer plus sales. We're going to do a fuzzy search to VLOOKUP. This is how you might actually do something for your teammates, which is your teammates only know the street name in the address. They want to be able to quickly type in a street name and see what the number of sales are for that given street names. They only know the word Buttermilk, or they only know the word Ross Clark, or they only know the word Montgomery. But they don't know the address number or the suffix which is street, drives, circle or whatever. We're going to write a fuzzy search allowing our teammates to enter in something like they might on Google where they don't know the complete search query and they want to get back some smart results. An example might be equals VLOOKUP. The VLOOKUP value here we're going to do is double quote, star, Buttermilk, star, double quote. We're going to look up in this table array, which is just from here to here. We're going to pull back the sales number, which is 3,0. Normally when you're doing a VLOOKUP, you're always looking up a specific value. You want to match through a specific match. But in this case, I'm telling Excel, I want to pull back any addresses that have the word Buttermilk in them. It doesn't matter what comes before or after. That's why I have star before the Buttermilk and after the Buttermilk. This means I don't care what the address number is, I don't care what the road street is. I just want to know which addresses have the word Buttermilk in them and that comes out to 239 right here. This allows my teammates to do a fuzzy search again, within my VLOOKUP formula, when they don't know the exact address, which is 321 Buttermilk Road. Now you can make this a little more flexible. I believe you can actually make this as a reference and I think what you can do here is actually just do a star. Let's say we have the word Buttermilk here. I think you can actually delete this and do star, that star. Maybe if l put the quote around that. This may not work either. I have to look into how you can do this. Maybe you have to do a ampersand with the cell reference. That's how it will work. If I wanted to make this more of a flexible search for my teammates, I might say something like enter address, road name here. Then they can type in things like they only know, Montgomery, which is this address right here, and it pulls back 1562, maybe they only know Florence. It's like a more flexible way of searching for stuff without having to know the full address again. Play around with the different wildcards. Google how you might be able to combine them. But I would say the question mark, the star, wildcards are the ones that I use the most to do fuzzy search so that I can make my tools and models in Excel more flexible for my teammates. 6. Find Optimum Inputs W/ Goal Seek: [MUSIC] When you have a model with multiple inputs, sometimes you just want to tweak one input to maximize profit, minimize cost, or maybe find the break-even point within your model or your business. The Goal Seek function feature is perfect for this because you can tell Excel to optimize one specific cell using one input in your model. In this lesson, we'll pretend to be a ridesharing company like Uber or Lyft to figure out what is the optimum number of rides that we need to have in our system to break-even. When you're building out a model, sometimes you want to be able to find the most optimum input to maximize profit, to find the break-even point without having to do a bunch of trial and error. We're going to look at this fictitious model for how a ridesharing company, let's say like Uber or Lyft, might model out their profits. Let us try to understand quickly what the D19, this total profit number represents. We have a few assumptions here, the number of rides that this ridesharing company has to do in their market. Their take rate is how much of each ride the ridesharing company takes from each fare. The total fair per ride, lets say the average is 15 bucks. That means the take rate for this fictitious company is simply 25 percent times 15 which is 3.75. Let's say they're entering a new market, like a new city to launch their ridesharing capabilities. They might have fixed marketing costs of $100,000, and their cost per ride would be simply a total fare per ride minus the take per ride which is their total net profit. The total profit here is simply 100,000, [NOISE] investment in their marketing efforts plus their take rate, which is their profit in each ride, multiply it by the number of rides that are being done. You can see here if there's only 100 rides then they have a net loss 99,625. This increases, let's say to 2,000. Their net loss is a little less. What is the break-even point for this example? Is it 5,000 rides? No, it's way too much. With little, is it 50,000? That's a little too much. You can see how I could do a bunch of trial and error to figure out what this number should be to get to a zero. Because I want to find the break-even number of rides that will give this fictitious ridesharing company a break-even of $0. We're adjusting the number of rides here in D10 and we're adjusting the total profit, but we want to be able figure out the precise number of rides to get this to break-even. Instead of guessing with trial and error, we're going to use this function called the Goal Seek, which has a feature that you can use for your models. You can go to the "Data" tab, go to "What-If-Analysis", and then go to "Goal Seek". This works when you only have to maximize or optimize one specific variable. In this case, it's going to be total profit. We're going to cell which is the cell or value we want to optimize, which is D19. We want to set this value to zero because that's going to be the point at which we're at break-even. How are we going to get to this zero break-even point? By changing this input, which is number of rides. We're not going to change the take rate or any of these other assumptions, just number of rides because this is how our model is built out. Once you have these parameters set, hit "Okay". You'll see that Excel quickly comes up with the solution, which is 26,667 rides to be exact, to hit a break-even point of $0 when we have a fixed marketing cost of $100,000 and your take is 3.75 per ride. If you add one more here, you'll see how this net profit now is $5, so it's a little too high. This is a real interesting way of either trying to find the break-even point or perhaps you want to find the total number of rides to reach $1 million in profits. We could try doing that really quickly by going to "Data", "What-If-Analysis", "Goal Seek". We can set, again, the cell D19. Instead of setting it to zero, we'll set it to one million by changing the cell D10 which is number of rides. Let's see how many rides it takes to get to a profit of $1 million, and that's going to be 293,333 rides. When you're working with your team and trying to collaborate to find an optimal solution, instead of doing trial and error, you can use this advanced data feature, which is Goal Seek, to find the optimal assumptions or inputs to maximize profits, to find break-even, and other metrics that you want to achieve for your team. 7. Complex Optimizations W/ Solver: Building off of the previous lesson where we used Goal Seek to optimize one input and maximize one output, sometimes you'll have more advanced use cases, and your teammates tell you there are other constraints in your model that you have to account for. When you have multiple variables and multiple constraints you need to take into account as you're finding the optimal inputs for your model, you can use the advanced solver feature in Excel, which is an ad-in. We're going to pretend we're still this ride-sharing company, and we'll have multiple constraints and multiple other variables to account for in order to maximize our profit for our company. For more advanced models where you have multiple constraints you need to work under, and you want to maximize a bunch of different inputs, you can't use the Goal Seek feature, which we've talked about on lesson five, you're going to have to use a more advanced feature called Solver. First, before we get into using Solver on our model here, we're going to quickly in Step One, take a moment to understand what the model looks like. Again, using our ride-sharing company as an example, we have a few assumptions here, the take rate is 25 percent. Now we have an assumption of the total number of assignable drivers is the total number of drivers that Uber or Lyft or whatever, may have at their disposal to work in their company to provide rides to customers. There's a total of 300,000 drivers who have signed up to drive for our ridesharing company, and the total number of rides that each driver can do per day is 10. These are just three assumptions that we have in our model. Now if we look at this list of cities, these are potential cities that our ridesharing company might want operate in. This column, the rides requested per day, this is the maximum number of rides that are requested for that day. If we have an assumption of 10 rides per driver per day, that means the drivers needed in each of these cities is simply the rides requested per day divided by 10. This, you'll see is the total number of drivers needed. The total number is actually 386,000, which is more than the number of assignable drivers that we have for all of our customers. We have our supply and demand issue here. We also have these inputs of the average fare per city. You can see this is sorted in descending order. Well, it is sorted in descending order. New York has the highest average fare, going down with Mumbai being the lowest. If we assume this 25 percent take rate, which is our ridesharing company's profit per ride, we simply take 25 percent, multiply it by the average fare, and then we can get the profit per ride. Now our goal is to enter in the number of drivers assigned here to maximize total profit. Look here, if I put 100 here, we get a profit of 469, which is simply 100 times the profit per ride. If I put another 100 here, we get 336 because the fare is lower, and we get a total profit here, 805. If you were to do this by hand or manually, you would look at the constraints in this model, which is, well, we know we only need 45,000 drivers here. One temptation is to say, well, if we have 300,000 drivers available, why don't we just put them all in the highest fare city and we can get a total profit of 1.4 million? Well, the problem is, we don't need 300,000 drivers in New York, we only need 45,000. The maximum number of drivers that we can put into this market for New York is simply 45,000. This is the maximum profit we can take from New York which is 210,000. Now you might go down the line and see, okay, well, our next highest profitable city, actually looks like it's going to be London, 4.39. We're going to try to maximize the number of drivers we want in this city, which is 37,400. Then the next highest profitable city looks like it's going to be Sao Paulo, so we might do 20,000 here. We can just keep on going through the list until we get to a maximum number of drivers assigned, which is going to be our constraint here, which is 300,000. This is going to be obviously very slow to do especially with a long list of cities. But I don't want to do this manually by going through each city and finding which city has the most profit for my ridesharing company, and then trying to plug in the maximum number available drivers needed to maximize the profit. We want to figure out what our maximum profit is given these constraints in the system. Now that we understand this model really quickly, let's look at Step Number Two. We actually just did this a little bit. We did through trial and error, adding drivers to the driver's assigned column to see how this affects total profit. We quickly discovered that we can't simply just assign 300,000 drivers to the highest profitable city, because we don't need 300,000 drivers in New York. We only need 45,000 as the maximum. Instead of doing this manually, one city by one city to maximize our profit, we're going to use the Solver feature. If you don't have Solver yet in your Excel, you'll see this under the Data menu, and you'll see Solver here. If you don't have that, click on the "Tools" menu in your Excel, it's actually outside of the window here, but you should have a Tools tab in your Excel. Then go to Excel add-ins, and you should see Solver add-in right here. If you don't see it, just take it off right here, hit, "Okay." Then you'll see Solver show up under the Data tab in the ribbon. Now we're going to click on the "Solver" tab to help us calculate the optimal number of drivers to put in the drivers assigned column. Now before we get into the Solver menu, there's a few things that we want to look at here to figure out how to set this up. We want to set the objective to the total profit, which is going to be this. Well, but change the number of drivers assigned, which is these cells right here. The constraints include drivers assigned must be less than or equal to the drivers needed, which is simply this number, has to be less than 300,000. The total driver's assigned also must be less than or equal to assignable drivers. That means this number cannot be greater than this number, and also these numbers all has to be integers. That's a lot of talking. Let's just take a look to see what this means using the actual Solver menu. This might show up a little small on the screen, but let's go through this one by one. We have some objective. We want this objective, which is cell I28, that's our total profit of all of our cities after we've assigned drivers. We want to simply maximize that number. We can set this to some number. In this case, we just want to find the optimal maximum number that we can use, that we can calculate given the constraints in the model. Now, we set our objective to be I28, which is our total profit, and we need to tell Excel what are the labels we're going to pull to get to that maximum profit. That's going to be this form right here, changing variable cells. It's going to be from H17 through H27. These are the drivers we want to assign to each different city. We're going to be changing all these cells or rather Solver is going to be changing all the cells using the algorithm. Now we're going to put in the constraints within our model. This is the most important part of Solver, because we have to tell Excel what are the constraints we're working with in order to get to this maximum profit. Let's start adding constraints. The first constraint we talked about is the number of drivers assigned. This has to be less than or equal to the total number of drivers needed. Because remember, we can't put more than 45,000 drivers in New York, more than 35,000 in Los Angeles. That's what you say, H17 through H27 has to be less than or equal to E17 through E27. We're going to add them to the constraint. We're going to say, the driver's assigned this total number right here, which sums up all the drivers assigned in that column, it has to be less than or equal to the total number of assignable drivers, which is 300,000. If we recall, in our total market analysis, we need more drivers than we actually have, so we have to figure out how to best optimize this small pool of 300,000 drivers, and add. The final constraint is that these numbers, Excel is going to put this through some algorithm. We want to make sure that doesn't come up with some fractions or decimals, because there's no such thing as like half of us driver, one-third of a driver. We're going to say these numbers all have to be integers. Just saying that this is an integer. We have those three constraints in our system, hit "Okay." Now our Solver menu is pretty much all set up. We have our objective, we have the cells we're changing, which is number of drivers assigned. Then we have these three constraints. Now a few things here we want to look at. You probably want to check this off, make unconstrained variables non-negative. This simply means that these variables that we're changing have to be above zero. It doesn't really make sense to have negative 500 drivers assigned, so we want to check this off. Typically, you'll want to use the GRG linear solving method or algorithm. This handles most situations, linear and non-linear situations. Leave that as default. You can click on "Options" here. There's a whole bunch of other options you can do, like, Ignore Integer Constraints. We obviously want to keep that on check because we want to keep these constraints. A GRG linear, use multi-start, you click on that to find more accurate answers. But usually, speaking in this model like this, you can just leave all these menu items as is, hit "Okay". We have our Solver set up. Let's try to see if Solver can figure out the total number of drivers to assign for each different city. Hit "Solve" and wow, that was pretty fast. Solver found a solution. We're going to hit "Okay." Let's just hit "Close", and let's see what Solver came up with. As expected, it tried to maximize the cities with the most profit like New York, it put 45,000 there, 35,000 Los Angeles. These are all the maximum. But you can see as you get down to the lower profit cities, it only assigned 33,600 drivers to Sao. It actually said, we're going to put in zero drivers in Hong Kong and zero in Mumbai because those are our least profitable cities, in order to maintain this maximum number of drivers to just 300,000, which we have the financing assumption over here. That is how you can use Solver on your team, to basically solve a complex optimization problem where you have multiple constraints, you're changing multiple levers or multiple cells here, which are these cells, and you have some number you're trying to hit. It could be a maximum, it could be a minimum, it could be a certain value. In this case, we know that given our constraints in our system of 300,000 drivers, and these drivers needed, the total profit we can make in this scenario is just over a $1 million. That's how you can use Solver, a much more advanced feature in Excel to prevent you having to do trial and error, and going through this manually, and just having Solver do the hard lifting to find these numbers. 8. Final Thoughts: Thank you so much for taking this class on advanced Excel features and functionality. I hope you'll walk away with more critical and analytical thinking skills when it comes to evaluating and analyzing your data in Excel. The most important part about some of these features and functions in Excel is knowing the specific use cases and niche scenarios in which to use them. But when you learn them, you can then make a business case to bring back to management or to C-suite on whether or not to invest in a specific business or investment opportunity. Please post your questions and thoughts in the discussion forum of the class. I'll be happy to provide you feedback and also take screenshots of your class project as you're working on them in the project section of this class. I hope to see you in one of my other Excel classes here on Skillshare.