Excel PRO TIPS Part 1: Productivity | Chris Dutton | Skillshare

Excel PRO TIPS Part 1: Productivity

Chris Dutton, Founder, Excel Maven

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (1h 45m)
    • 1. Course Structure & Outline

      1:58
    • 2. Setting Expectations

      1:45
    • 3. Productivity Tips Intro

      0:29
    • 4. Customizing the Footer

      4:03
    • 5. CTRL Shortcuts

      8:01
    • 6. ALT Key Tips

      6:53
    • 7. "Go To" Special Options

      4:36
    • 8. Removing Blank Rows

      3:17
    • 9. Data Validation Drop-Downs

      7:11
    • 10. Autofill & Flash Fill

      8:26
    • 11. Customizing the Ribbon

      6:53
    • 12. Splitting Text to Columns

      7:39
    • 13. Synchronous Scrolling

      2:58
    • 14. Extracting Unique Values

      4:06
    • 15. Named Range & Table References

      8:12
    • 16. Protecting Cells & Formulas

      8:09
    • 17. Multi-Level Row & Column Sorting

      10:54
    • 18. Advanced Filter Criteria

      9:27
12 students are watching this class

About This Class

Welcome to Excel Pro Tips for Power Users!

Please note that this is PART 1 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)

Transcripts

1. Course Structure & Outline: everyone chris dot in here and welcome to Microsoft Excel Pro Tips for Power Users. Now, for those of you who don't know me, I'm a Microsoft certified expert and bestselling instructor with students in over 185 countries. Now this course is not an introduction to excel. It's not about comprehensive deep dives or about showing off cheesy, impractical hacks, discourses about featuring some of most powerful and effective tools used by Excel professionals and sharing them through crystal clear demos and unique real world case studies. We'll cover more than 75 tools and techniques grouped into six categories. Productivity, formatting formulas, visualization, pivot tables and analytics. I'll show you how to apply advanced formatting rules, create an audit complex formulas, build your own interactive visuals and used powerful tools like gold seek solver and even Monte Carlo simulation models. As part of the course, you'll receive downloadable pdf slides to keep on hand as a helpful reference, along with a project file containing every single demo and data set that we cover, ranked by difficulty organized by category and hyperlinked for quick and easy access. So if you're ready to work smarter, boost productivity and become an absolute excel power user. This is the course for you. Sign up today and get lifetime access to high quality video content. Downloadable resource is project files, of course, quizzes and one on one expert support, all backed by a 30 day, 100% guarantee. Thanks for watching help. See there. 2. Setting Expectations: All right now, before we dive into the course content, just want to take a minute to set some expectations. Number one. I'll be using office 3 65 pro plus for PC. What that means is that what you see on your screen may not always match what I'm showing on mine. And that's especially true if you're using a different operating system if you're on a Mac or if you're using an older version of Excel Number two. This course is not designed to serve as a gentle introduction to excel. These pro tips are intended to showcase very specific, often very advanced tools and techniques. If you're looking for deeper dives or more comprehensive reviews, check out my other Excel and Power beyond courses, which dive into formulas and functions, charts, graphs, pivot tables, power query and more. Number three. Unlike most of my courses, which tend to follow very clear, project based work flows, this one's actually nonlinear. That means that you can explore the content in any order that you choose. I've designed each of these demos to be self contained units so you can skip ahead or focus on the specific areas that you'd like. So if you work in Data Analytics or be I, you may want to spend more time in the formulas and analytics tips, whereas more casual day to day users might spend more time focusing on general productivity tips. So think of this is kind of a choose your own adventure style course. Now last but not least, if you have questions, have comments or feedback. Let's talk. If you need support along the way, feel free to post a question or reach out directly, and I'd be happy to help. We've got a lot to cover, so let's dive in. 3. Productivity Tips Intro: all right, so we're gonna kick things off with some general productivity tips, which are all about increasing efficiency. Now. The demos in this section generally trend on the easier side. You'll see a lot of one and two star tips like shortcuts, flash ville and workbook customization. But we'll also talk about some more advanced tools as well, like custom sorting and filtering. Keep in mind you can work through the course in sequence or jump straight into specific demos. Let's do it. 4. Customizing the Footer: All right. So I want to start with a pretty simple productivity tip. This is a one star tip, very basic, very easy to do. And it's about customizing your workbook footer stats. So the Excel Footer bar, which lives right beneath the sheets or tabs, typically shows some basic metrics like some average and count of a selected range of values, you know? So maybe you've got a workbook like this with some weather data. You've got conditions, temperature, wind speed, you're to select column, See, you'll see that footer bar right beneath. Those worksheet tabs show some of those basic metrics like the average, the count and the sun. And this is pretty helpful for just very quick spot checks. Kind of calculate those metrics without having to actually write some average count or count a functions. Now what? I didn't realize for a long time that you can actually right click that footer bar toe access additional options like this. So that allows you to not only activate additional stats like the numerical count, the max or the men, but also activate or enable other types of notifications. So if you're using a flash fill tool, which will cover. In another tip, you can show the results of your flash ville. You can indicate if you've got caps, lock number, lock or scroll lock enabled. And one of the most common ones is showing when a macro is in recording mode. If you're using Vehbi, eh? So the ones we're gonna look at here are these six down at the bottom, and all you need to do is check the box and it will add them to your foot or bar so that instead of just the basic three, you can also show numerical count men and Max So common use cases here, like I said quickly generating some of these summary statistics without using a single formula. And another common one is adding that status indicator that displays while you're macros are in recording mode. So with that, let's head to her pro tips workbook and let's actually practice this in excel. All right, so once you've opened up your Excel Pro tips file, go ahead and navigate to the first Blue tab. Custom footer stats. Remember, any time we're having trouble navigating to a particular demo or tab, I can always go back to the 1st 1 table of contents. Find the lecture and link straight to it. So here we are. We've got the weather data that I mentioned earlier got a year's worth of data from 2016 and this is captured from the Boston Massachusetts weather station where I'm located. I've got conditions, temperatures and Fahrenheit, wind speed in MPH and precipitation in inches. Now, if I wanted to learn more about average daily temperatures, for instance, I could write functions like Count a Count Max men average or some. Or it could simply select the entire column and take a look down here, my foot or bar so you could see the average temperature after all days was 53.14 degrees Fahrenheit. Got a count of values is 3 66 which, you might notice, is counting the header row as well. So text or numbers and I've got the some, which doesn't really make sense when I'm looking at temperature values, but I right click this footer. Here's that menu I talked about earlier, and I've got these additional stats that I can activate by simply clicking on each of them . So I want to add a new miracle count minimum and a maximum. And there you go. Now I'm showing all six of those summary stats right here in the Footer, so you'll notice that my original count 3 66 now shows. In addition to that, a new miracle count of 3 65 which excludes that text header road got a minimum temperature of zero and a max of 86 F. So there you go really quick way to do some very basic data analysis without using pivot tables, filtering, sorting or writing functions, customizing the Excel Footer Bar. 5. CTRL Shortcuts: all right. Time to talk about quickly and efficiently, navigating through worksheets, using control shortcuts. Now, this is another one of our very basic one star productivity tips. But that doesn't mean it's not valuable and important. In fact, these simple control shortcuts can save you a huge amount of time in the long run. Now, I'm not gonna talk through every single shortcut option that Excel has to offer. Instead, I'm going to share with you the ones that I tend to use most frequently. So here are a few of my favorites. Start with control Arrow. Those up, down left, right, arrows on your keyboard. What those will do is jump to the edge of a contiguous range of cells. That means arrange with no blanks in between every autumn. Accurate news command arrow for the shortcut. And at the same time, you could hold shift and not just jumped to the last cell, but also create a selection at the same time. So, for instance, if you've got a simple range of data like this, got data and sells a one through d 12 and we're selecting sell A to what we could do is use control our right to jump to the last contiguous cell in Row two could use control down to jump to the last contiguous cell in column A. Or we could use home and end tools to jump to the top, left or lower. Right? So control end would take us to that lower right cell, which in this case is D 12. Now keep in mind. Like most things and excel, there are many ways to accomplish the same thing instead of control. And you could also use control Arrow right, followed by a narrow down. Now, if we add the shift key in here instead of control Arrow right, something like control shift Arrow right won't just jump to sell the two. It will create a selection containing cells eight to through D two similar way Control shift arrow down. It's gonna select the entire contiguous range in column. A and control shift end is going to select the entire range of rows and columns, so that would be similar to using something like control A. To select all of the values, although in this case we're excluding the headers now, a couple other quick control tips that I use quite a bit Control page up and page down. Help you kind of navigate and scroll between tabs or worksheets. You're on a Mac Euronews option arrow to do that, um, and then controlled G launches something called the go to menu, which contains a list of all named cells, ranges or tables in your worksheet and allows you to jump straight to them. So common use cases here first selecting large tables or cell ranges without having to do any sort of manual scrolling and second, identifying the last active cell in a worksheet. Now this is an important one, because when I see a lot of users do is accidentally apply formatting well beyond the range of cells that they actually need. So you may accidentally apply self fill to an entire column, which extends that filled down to about a 1,000,000 rose That's gonna do is it's gonna bloat your file size unnecessarily. So control End is a great way to kind of see cases where that might be happening. Last but not least, here in the lower right, I've got shortcuts to the full list of both window and max shortcuts on the office support websites So with that, let's jump to excel in actually practice using some of these control shortcuts. All right, so here we are. Excel Pro Tips workbook. I'm on the second Blue tab here, Control and all shortcuts and what we have is a larger sample of movie data. Got titles and column, a release. Dates, genres, languages. Some metrics like revenue, budget and profit extending all the way up to call him p here. And then if we scroll down, we've got several 1000 rows. Looks like about 3700 rose. So quite a bit more data. Still not huge, but certainly more than the 12 rows that we looked at in our sample. And let's say that we want to grab some of these values and maybe copy them or analyze them elsewhere. Um, what a lot of users will do is select a field or value like, oh, to in this case and start scrolling down like this. And if this looks familiar to you, I want you to stop and think about how we can use control shortcuts here, toe automate this process and make it much, much more efficient. So what I can do here without changing anything I've selected down to looks like Road 3 50 Just gonna hold control. Hold shift, press arrow down. That's gonna take me all the way down to the last cell in that column, which is row 37 26. So I was able to select all of those values with one click of the button and then similarly , control arrow up. It's just gonna jump me all the way to the top back to Row one. Now you've heard me use this expression a few times is contiguous range and again, that's a range that does not contain any blanks. Because when you're using these control, shift in control shortcut tools, Excel is going to see a blank as the end of the range that you're looking to select. So to give an example, let's look at column G here, the rating column and kind of apply that same shortcut to select all the values in this column. It could press control shift arrow down, but notice that this time it stops at Road 3 17 That's because cell G 3 18 is a blank looks like Maryland Hodgkiss is ballroom dancing, which I'm personally not familiar with does not have a rating in the data set so that control shortcuts stopped right here in Rhode 3 17 So bottom line here is to be careful when you're using these shortcuts so that, you know you don't think Okay, I've done the shortcut, Grabbed all of my rating values. I'm good to go because, in fact, you've missed about 90% of them due to that blank row. So just be careful when using these tools, make sure that you're grabbing all of the selections that you need now, quickly showing you the home and and options I can select any cell in here press control and home That's gonna jump me right Top left, Active cell. Same thing Control end is gonna jump me to p 37 26. Which, as you can see, is that lower right corner of my range. Now, this pro tip workbook is a great opportunity to practice the control page up, page down tabbed navigation shortcuts. So if I hold control and press page up, it's gonna jump me one tab to the left to custom footer stats. I press page up again. It's gonna movement to the left again and then same case with page down. It's going to take me to the right. So I'm navigating through this workbook very, very quickly Tabai tab, which in this case is going to be very, very helpful since we have dozens and dozens of demos and tabs in this file on a final control shortcut that I want to share with you is controlled G, which is gonna pop up this go to menu containing all of those names, cells and tables and ranges in the worksheet. And from here, you know, I could say, Alright, what what's this interest rate cell or named range to select it press OK is gonna jump me straight to that cell. In this case, it's on my scenario Manager Tab, which is a demo that will cover in the analytic section of the course. And it's a cell that we've named interest rate here as part of this mortgage payment calculator. So very efficient way to navigate directly to that cell and then one final little bonus tip . If I want to get all the way back to beginning here for the first time in the worksheet, instead of clicking and holding that arrow in the lower left. I can hold control and click once That's gonna take me all the way back to my start. So back where I started controlling all shortcuts and there you have it using control shortcuts to efficiently navigate through your workbooks. 6. ALT Key Tips: all right. Next pro tip that I want to cover is how to quickly access your ribbon controls using something called Ault Key tips. And just like our control shortcuts, this is a great way to efficiently navigate through your workbooks and worksheets. So how this works, Basically, you press and release the altar key toe, activate ribbon shortcuts. Kind of like this. Now, what this does is allows you to access or navigate through any of those menu options, using simple keystrokes without even touching your mouse. So, as you might imagine, there are hundreds and hundreds of different ault key tips because you can essentially access any tool in the entire Excel application from any workbook menu. So I'm gonna share with you the ones that I use most frequently. And what I'd suggest is that you think about you know, the three or four tools that you use on a regular basis. Most frequently learn the Ault key tips for those tools and commit those two memory. There's no way you're gonna memorize dozens or hundreds of these all key tips, but even just learning a handful are really gonna help you work more efficiently. So let me give you an example of one of my favorites, the paste special as values key tip all to H Fyvie. So continuing with this visual here by simply pressing and releasing Theobald, Key Excel has displayed these key strokes that will allow me to drill deeper into the ribbon. So if I press H after the out, keep, that's gonna drill me into the home tab. And, as you can see, it now displays a completely new set of keystrokes to drill even deeper. So from here, if I press V, that's gonna drill me into my paste menu and then finally within that paste menu one more key stroke of the letter V and I've got my pay special as values, and this may seem like a lot of work may seem like a lot to memorize, but trust me, it's a muscle memory thing. Once you've done it a few times, you'll find yourself doing it almost automatically like second nature. So that's pay special as values. All th vv Some of the other ones that I personally use quite a bit, all to 80 will add or remove filters from a range all t h e f will clear any formatting and ault envy inserts a pivot table. Now if you're a Mac user, got bad news for you at this point in time. Ault Key tips are not available for Max. This is a PC only Windows option. Common Use cases again, Really, it just comes down to quickly accessing tools or commands that would normally require multiple clicks like that. Pay special example. Let's jump into a pro tip workbook and practice some of these all turkey tip shortcuts. All right, so here I am in the second blue productivity tab that Control and Ault shortcut worksheet. And as you can see here, I've got a range of values. I've got some headers and Row one, but I don't have filters on those headers. So first thing I want to show us how it could use all key tips to quickly add those headers . Now one option is to actually manually click the data ribbon and select filter here. Another option is the control key tip, which is all too a T, and what that's done is simply apply the filters right there to row one. So let's do that again a little bit slower so you can see what's happening. My press Ault displays my key tips to drill down one level deeper into my main tabs and know that I want the filter option in my data tab. So pressing A drills me into my data tab options and then from within the data tab, my filter tool is labeled with a T suppressing T activates that option. In this case, I already had my filters applied, so it simply removes them. But going the other direction, Ault A. T adds them to that row. So again you can access virtually any tool in the Excel ribbon using these all shortcuts. Now, to give you a second example, let's scroll to the right here. We've got some metrics. Their movie titles got revenue, budget and profit, and you'll notice here and Colin P. These profit values are calculated by simply subtracting the budget from the gross revenue value and if we wanted to hard code, these values turned them from formulas into Ron values. We need to use the pay special as values option again. The manual approach would be home copy paste special as values. In this case, the all key tip is gonna make that process quite a bit faster so I can select the entire column P control, See to copy all those values. And then you remember that shortcut Ault H V V. And all of a sudden now I've got raw values no longer formulas here in this column. So doing that without having to even touch my mouse made it a lot faster and a lot easier, especially because that's a command that I use quite a bit. And now from here, let's say I want to actually analyze and explore some of this data using a pivot table, which is hands down one of my favorite tools in Excel for exploring information, you could navigate manually to the insert tab, click pivot table. Or you could use the Ault N V option to accomplish the exact same thing press. Okay, you created a new worksheet with your pivot table based on that data. Now we don't need this Pivot this point. That's right. Click and delete that tab. I've said this before, but there are often many approaches to solve the same problem or accomplish the same task in excel, and I'm gonna show you one more example, and that's formatting this range as a table. And in fact, they're about three different ways that we can do that. For one, we can navigate to the home tab with some cell in a range selected. We can use the format as table option and simply choose a table format to insert it. Press cancel their, but that's option one. Option two can use an altar key tip in this case Ault h T. To do the same thing. Press enter toe, actually insert the table itself, and then the last option. You could use a control shortcut here. Simply use control T and get to the same place. So that's a good example of three different ways to arrive at the same solution. And there you have it. So again, think about the most common tools and techniques that you use an excel. Learn those all key tips and really practice using them until they're committed to memory. It will save you a ton of time and help you work more efficiently and excel 7. "Go To" Special Options: All right, let's talk about selecting special cell types using Go two options. Now, if you saw the control G shortcut demo, you'll know that that launches the default Go to Options Dialog box kind of like this. Now it will list out any named cells, ranges or tables that exist in your workbook. But what you may not have realized is that there's a special menu right here in the lower left that includes a ton of additional options that allows you to select certain types of cells or objects within this sheet so you can select things like blanks. Cells contained formulas versus constant cells with conditional formats or validation rules like drop downs, things that would be very, very hard to select or isolate otherwise without this tool. So in this case, we've selected formulas in the go to special box. What that's going to do is navigate to any cell and highlight any cell or cells that contain a formula in that active worksheet. So in this case, sell E 10 contains an index match function that's the one that gets highlighted. Common use cases here again, quickly identifying or highlighting all cells containing formulas. You can select and delete all objects in a worksheet with one click instead of manually control, clicking them one by one. Or you can identify cells that have validation rules like drop downs applied so that you know which sells in a worksheet. Our user inputs, for instance. So let's jump into our pro tips Excel, workbook and practice Some of these go to options. Okay, so once you've opened up your Excel Pro Tips workbook, go ahead and go to the third Blue tab. Go to Special Options tab and you'll see this product matrix here, and what we have here is a range or matrix containing prices based on five different product types and five different sizes. We've also included some data validation drop downs so that users can select any combination of product and size. And this index match function here in E 10 will essentially retrieve the proper price from this matrix. Based on those selections, I also noticed we have some conditional formatting applied that actually highlights the proper cell based on these selected values from the drop downs. Now, if you want to learn more about these index and match functions, or how to build a tool like this. Make sure to check out the advanced formulas and functions course where will actually build this calculator from the ground up. Now this is a great tool to demonstrate those go to special options because the sheet contains all sorts of different cell types. Got conditional formats. We've got Constance got formulas, and we have data validation cells. So let's see what this actually looks like. Gonna select an empty cell over here, impressed, control G tow, launch the basic go to options and then click the special button in the lower left toe. Access those additional options. So starting Pretty simple. Let's go ahead and select Constance, which is any non blank cell. That's not a formula. We press OK, it's gonna highlight any of those cells that fit that criteria, which in this case, is this selection here you can see this would be a very difficult selection to make manually. It's a great way to kind of isolate just those cells with one click of a mouse. Now, de selecting those, we go back to control G Back to special can do kind of the opposite and say Okay, now show me any cells containing a formula. This case I want any sort of formula. So leave all those boxes checked, Press OK and that highlights sell E 10 this kind of merged sell here, which contains my index and match function. Similar story if we want to isolate something like conditional formats, go to special all the way down at the bottom of the right. You've got conditional formats here. Yes, OK, there you go. It's selected all of the cells containing conditional formatting rules and then last but not least, let's de select that. Go back into our control. G Go to options. Click Special. Now let's look at any cell containing data validation. Keep the all options selected. Press OK and that's going to select cells. Be 10 and see 10 which are to drop down data validation cells. So there you have it, using the go to special options to highlight specific types of cells in a sheet 8. Removing Blank Rows: all right for this tip. I want to show you guys had a ring. Move all of the blank rows in a worksheet in one. Go. Now, this sounds like something that should be pretty easy to do, but it can actually be quite challenging without using the right tools. So in this case, we're gonna use the go to special options, which recovered in the last demo and select blanks to get rid of all of those pesky blank rows. So control G is gonna launch your basic go to options. And then again, that special button is gonna allow us to highlight or isolate specific types of cells within the sheet. So in this case, we're going to use the blanks option, and that's gonna select all of those blank rows within the active range. And from here, the only thing left to do is actually delete those blank rows you can do in a few different ways. First you can navigate to the home tab pressed, delete, and then delete, cheat, Rose kind of like this, you could use an altar key tip, which in this case would be ault h d r. Or you can use a control shortcut control plus than minus, followed by shift cells up all of those air gonna accomplish the same thing, which is getting rid of all of those blank rows in your range. So common use cases cleaning up raw data by quickly eliminating those extra or blank rows without having to do any manual selections or use other types of tools, like the query editor. So let me show you how this works. All right, So I'm in the Excel Pro Tips workbook and in the removing blank rows productivity tab. And as you can see here, I've got four columns of data got dates, product. I do use product names and quantities, and obviously I've got these intermittent blank rows here that are kind of disrupting my range. And if I wanted to turn this into a nice, clean, contiguous range of values, there are a few different approaches I could take. Ah, lot of users will go the manual route and start actually right clicking and deleting individual rose to get rid of them. You can also use sorting and filtering tools. In this case, I want to show you that go to special approach, which is really, really handy. So I'm gonna press control. G Got any cell kind of in this range Selected Doesn't have to be the full range or anything like that. And from here, my basic 02 options. We're gonna drill into special options and quickly blacks button here now in a press. OK, see that it's automatically identified and highlighted all of the blank rows on Lee within my active range My range that contains values. And from here, the only step left is to actually delete these rows. And again, there are a few ways to do it in use, control shortcuts, all key tips. This case already here in my home tab just gonna go over to delete in the cells group and delete those sheet rose. And all that will do is ship the values right up and give me this nice, clean, contiguous range of values. So really helpful. Tool control special Remove blank rows. So really helpful. Shortcut here using the go to special options to highlight blanks and delete them from your sheet 9. Data Validation Drop-Downs: all right for this pro tip. I want to talk about one of my favorite Excel tools, data validation and specifically had a used data validation to create drop down lists within cells. Now, quick summary here, data validation is all about limiting the values that a certain Selcan, except so you can say, I only want this cell to take whole numbers or positives or negatives or date ranges or data types like text or values. You can access that menu from the data tab. Click data Validation. Now, within that data validation menu, you've got an option called a list and the list option. It's the one that I use most frequently. That's what's gonna allow you to create that drop down menu containing a specific set of items. And you can either type those items directly in the dialogue box, separated by commas, or you can reference a cell range containing your list. Now, in this case here, we've manually type them in. We've got three different values that this particular Selcan take 5% 10% or 20% and when we press OK, it's simply creates that drop down containing those three items, and those items only now you can also customize the input messages that users see when they select a cell with data validation or the error alerts that pop up if someone enters an invalid value. We're gonna have some fun with that in the demo in just a minute Now. Common use cases for one. Like I said, creating formula based models that have these variable controlled inputs. That's probably the most common use case for data validation list options. You can also prevent users from entering invalid values that might break of formula or yield meaningless results. I'm so you can limit to just decimals. Just whole numbers, positives, negatives, etcetera. So without further ado, let's head to our excel workbook and practice building some data validation. Drop down lists. Okay, So if you have your Excel Pro tips workbook open, you're gonna navigate to the data validation list Blue tab in our productivity tips section , remember that you can always use the table of contents tab to find the tip and go ahead and click link to jump straight to the tab. And in this case, what we're looking at here is a property calculator, and this is something that I have actually built and used myself to evaluate the cost of different properties based on loan terms. And what we're doing here is entering an information about a property you know, the purchase price and the tax rate, and then allowing the user to input three different values, a down payment percentage, an interest rate percentage and a term length in terms of the number of years of the loan. And based on those inputs, we have all sorts of calculations that determine the loan amount, that closing costs and the monthly expenses which all boil down to these calculated cells here, the cash to close and the monthly expenses. So you can see that for this made up property here that costs $599,000. If we were to put 10% down, get 5% interest rate in our alone and pay off that loan over 30 years, we would need $71,880 to close on that property estimated, and we would expect to pay $3718 a month now. You can also see what happens if we change this to 20% down. Now you can see that our monthly payment is only 3397 but we require more cash to close. The bottom line is that we have these formula driven outputs based on these user determined inputs. So this is a great opportunity for us to use data validation to fix these inputs into certain categories or lists of values. So let's start with the down payment sell here. We're gonna go into the data tab data validation, and instead of allowing any value in that, so we're gonna allow a list, and again we could have the list exist in cell somewhere and reference that cell range or weaken. Type them in right here. In this case, I'm gonna type them in because we only have three values 5% 10% or 20%. These air common down payment amounts. You can customize this, however you see fit. But for now, let's stick with these three and press OK now, Once we've done that, it's created this drop down, and now we have those three options and those three options only for that down payment percent sell. Let's go ahead and do the same thing for interest rate data validation list and will create a range of realistic interest rates. Maybe it's 3% 3.5% 4% 4.5% and 5%. So these five values for this particular cell press okay? And there you go And then last but not least, term length. Let's just due to term length here in a list, either 15 year alone or a 30 year loan. So two items in that list purse OK? And now what we've done is created a fixed set of variable inputs that users can use to explore our different model outputs. And they can select any combination of those three values based on the guidelines that we've created using data validation Now, last but not least, let's practice customizing some of those pop up messages so you can go back for a down payment cell back in the data validation. We've got these two additional tabs input message and error alert, so the input message is gonna show up. Once a user selects that cell, you can give it a title like down payment and a message like select a value. I didn't press OK And now when a user select that sell, they're going to see that pop up that indicates to them. Okay, this is a user input cell. I can click the drop down and select one of the values here, so certainly not necessary. But a nice option to use if you'd like another one, which is really funny is the air alert. And you can customize this however you see fit, you can change the style of the icon like a warning sign. For instance, this is gonna pop up a box in cases where users try to enter an invalid value into the cell . So a value that breaks the rules of our data validation list so we could do a title like Hey, and an error message like, Yeah, stick to the list, pal. And obviously, you know, do whatever you want, But whatever message in here that you like press OK, and as long as the user sticks to the list, they're all good. But if they try to go rogue and say OK, actually want to put down 8% they're going to see that pop up says Hey, stick to the list, pal. And then they know. Okay, Did something wrong. I've got to choose one of these items, and there you go. So there you have it. Data validation. Drop down lists. One of my favorite tools in Excel. 10. Autofill & Flash Fill: all right for this pro tip. Want to talk about populating values with tools called Auto Fill and Flash Ville? And this is a pretty basic productivity tip but incredibly powerful once you learn how they work so quick. Summary. Here you got your basic auto fill options that allow you to copy sells ads. Sequential Siris of values, Phil values with or without underlying formats or applied date intervals. If you selected a date specific cell, so all you need to do is drag or double click the lower right corner of a cell toe. Access these auto fill options. So here's what one example that might look like that save typed the date 11 2018 to a cell . Drag that down six rows because that was the date field. You've got these dates specific. Auto fill options. Dan Ally to fill those roles by day, like January 2nd 3rd 4th or, in this case by month. January 1st February 1st March 1st, etcetera. Another example. Creating an index. Siri's. Simply type the number one. Drag it down 10 rows juice fill Siri's that will tell Excel not to copy that first value over and over and over but to apply sequential Siri's from 1 to 10. Now these air both pretty simple, straightforward examples of these auto fill options. But if we want to do something a little bit more custom or complex, see at the bottom of both of those pop up lists. Got another option called Flash Phil. Flash Ville is much more powerful and flexible because what it does is actually identifies patterns based on a given value or set of values that you provide and then uses those patterns to populate an entire column. Now I'm gonna be honest with you guys. I normally avoid these tools like this that just kind of feel to me like cheesy shortcuts. But I've been playing with flash vill a lot more and have been incredibly impressed by the capabilities. So let me show an example Here, let's say you've got an email address and column G. You want to define it? User name and column K. We're defining that user name as just the text component of the email prior to the at symbol. So Jennifer J. McGrath, 34 turns into Jennifer J. McGrath. Chad S. Lewis, 51 turns into Chad s Lewis out. This is something that would be very, very challenging to do with self formulas, even nesting functions like left and search, because the number of numerical digits could potentially change from road row with flash vill. That's not an issue. Excels. Able to identify exactly what you're looking for. Apply that pattern down to all of those rose. I'm going to show you another similar demo in just a moment. So common use cases here plying values to thousands of rose without dragging or copy pasting feeling formulas without overriding formatting, extracting text that would otherwise be very difficult or even impossible to isolate with formulas or quickly creating sequential index columns are calendar tables. So let's roll up our sleeves, jump into excel and practice, working with some of these auto fill and flash ville options. All right, so once you're in your Excel Pro Tips workbook, head to the auto fill and Flash Phil productivity tab and you'll see a list of users. We've got first names, last names, addresses, city state, zip, email, telephone and date of birth. Now this is fake artificial data, so please don't try to dial these numbers Have no idea who you're gonna actually reach. Basically, what we want to do here is populate some additional columns we wanted index value to uniquely identify each row. You want to pull a user name out of the email field at a user I D column, which is just the numerical component of the user name and then the birth year as just a two digit date based on column I. So let's start with our index couple ways we could do this. Gonna start by just typing a one here to identify our first row. And then when we select that one and double click the lower right corner by default, it's copied that value over and over because Excel doesn't really know what else to do it that value. But by clicking into the auto fill options, we can simply select Phil Siri's, and now we get that sequential list of unique values to create our index. Now, one other tip here, if I control Z to undo that, another option is to actually provide a second value here. Basically, what I'm doing is providing excel with another clue, another bit of context to tell it how to populate this column. And now and I select both the one and the two and double click. Now it fills my entire Siris in one go because behind the scenes XLs saying if you're following a one with a two, my best guess is that you're gonna follow the two with a three a three with a four and so on and so forth. And in fact, that's exactly what I'm trying to do and it gets right. So next up, we've got a user named Colin, Colin K. And this time we're gonna define user name as everything up to the at sign. So before at Gmail, at a well, etcetera. And remember that when we want to do something like this, that's based on a pattern where we need to use the flash ville option. We need to start by providing Excel with a sampler, too. So what I'm gonna do is copy this first value in G two, paste it here into the user name column and just strip out the at Gmail. And that's the first example of a user name that I want excel to use to try to figure out the pattern that it can then apply to this entire column. So let's start with one and just double click, and you can see that it's not gotten it right yet. It thinks we maybe want to do a series of values with the same name we don't. In this case, we want something a little bit more complex, which requires Flash Phil. So it's select Flash Ville, and there you go. What it's done is populate all of those user names exactly correctly, even cases where you have three digits instead of two or one instead of three, and it's gone down and applied that pattern toe every single Rohner data. It's a really, really powerful tool. In this case. I could use a combination of left and search functions to return the string up to the outside. But then I'd be left with thousands of functions that process here in column K with every workbook change, and that's just not very efficient when I can accomplish the same thing using this flash fill tool. So next up we've got the user i D number, which is just the numerical component of the user name. So 99 51 71 17 etcetera and again. We can start by just providing one sample of what we're looking for. Double click Flash Phil. And there you go. It's worked perfectly. 96 for Joseph, 8 68 for Diane and so on and so forth. So another very quick way to apply that pattern. And then last but not least, we want the birth year, which is the two digit year based on the date of birth and column. I now first row for Jennifer McGrath. She was born in 1999. That would be 99 which you can see we might run into a problem here. We try to apply the flash fill option again because we get the same pattern that was identified in the user I D column. And this is a great example where we may need to provide one more input or one more fixed valuer clue that Excel can use to understand the proper pattern. So in this case wrote to, we want the year of birth to be 78 So instead of 51 which is based on the user name, we can just manually type the 78 and check this out without even changing our undoing anything. I'm just gonna press enter, and it's going to revise that whole pattern and apply it correctly. Toe all of my rose. So now we have the proper birth years, the proper user i d numbers as well as our index and user name. So hopefully this gave you a good example of why these tools can actually be really, really powerful when it comes to populating cells based on patterns. So there you go, auto fill and flash Phil tools. 11. Customizing the Ribbon: All right, everyone for this pro tip. I want to talk about customizing the workbook ribbon with your own tabs. Now, what a lot of Excel users don't realize is that you have full control to customize and personalize exactly what your ribbon looks like. So Excel has tools that allow you to add, remove or rearrange tabs and tools in your workbook ribbon, including the quick access toolbar and those quick access tools. They live above the ribbon above your main tabs, and by default they include options like save, undo and redo. So a couple ways to customize those quick access tools. You can head to your file options pain and click into the quick access tool bar tab that's gonna show you the list of tools included in that toolbar. Again, save, undo redo by default, and you can simply search for popular commands or tools and drag him into that list to customize the toolbar. But an easier way to do that is to simply right. Click any particular tool right from your ribbon and click that add to quick access toolbar option, and removing them is just a Z Z. You can right click from within the toolbar itself and choose removed. Now to customize the ribbon itself in your core tools and menus, you can head to file options customized ribbon and determine how to show hide or rearrange the tabs in your ribbon right from this window on the right. And what you can also do is actually create brand new tabs to consolidate your most commonly used tools. So if you use cases here again, consolidating those tools into a single custom tab or exposing other tabs like Developer, which may not be shown by default, so that's heading to excel and give this a shot. All right, here we are in our Excel Pro Tips workbook. I mean, the blue customizing the ribbon tab. And what we're gonna do here is just demonstrate how we can add or remove tools from our quick access toolbar here at the top of the window, and how we can customize our ribbon here by adding our own custom tabs. So, first and foremost, we want to add tools to the quick access toolbar. It's very, very simple. The easiest way to do it is to simply hover over the tool you want, like copy for instance, right, click and select. Add two quick access toolbar. Now that's added an icon to the toolbar up top that's centrally accessible. No matter which tab I'm currently in. Suppose that's why they call it the quick access to a bar again with Paste. Same story here, right click. Add to quick access toolbar. Now, if I change my mind and I want to remove some tools or go back to the default, I can simply right click from the toolbar itself and choose removed. Too. Simple is that now, as far as customizing our core ribbon and our tabs and tool categories here, there are few ways to access those options. The first is to choose file options and navigate to the customized ribbon tab within our options dialog box. There's a quicker way, which is just right, clicking any location within the ribbon itself and choosing customized the ribbon that will navigate straight to that tab within your options. And what this will show me is my current ribbon configuration. So which tabs in which groups of tools are currently visible and accessible in my ribbon. So right now I've got the home tab insert tab page layout, formulas, data and so on and so forth. So note this developer tab here for most new versions of Excel. You may see that box unchecked, So if you want to access the visual basic editor and play with VB a write macros insert form controls. You may need to access this customized ribbon option and check that developer box to make the town visible so we could drill into each of these tabs and actually customized the tool groupings within them. In this case, I don't really want to mess with my default settings. I just want to add a brand new tool or a brand new tab to my ribbon containing a custom group, and we can go ahead and click the new tab Custom right Click and rename it something. Ah, that makes more sense to us like favorites. And what I want to do here is just drop in a bunch of my favorite data and analytics tools so that they're more easily accessible in one place so I can navigate. This left pain here, and I'm looking at the popular commands can also look at all commands, which is a pretty overwhelming list Or you can look at all tabs, which shows kind of the current layout, um, and allows you to drill in select tools from within either the main tabs or the tool specific tabs like pivot table tools, table tools, spark line tools, etcetera. So in this case, it's going to start finding some of the tools that I want consolidate here in my favorites tab. So we start with insert. Definitely want to insert some tables here because I used pivot tables and tables quite a bit now within this grouping, I could insert just pivot tables alone. Oregon Select the group, which would insert all three of these options at the same time. Let's go ahead and add that to our favorites tab. And there you go. It's populated it right here can do the same thing with our spark lines. It's add spark lines that category, and why don't we go into data and getting transform? I want this get data option as well. It says they needed to be added to custom groups, so pick a tab, then click new group. So I just need to select that new group option and press ad. So there you go. I've got get data table tools, spark line tools. Obviously, you could continue to do this until you have a full set of tools that you'd like to access . But in this case, that should be just fine. Go ahead and press OK. And there you go. It inserted that new tab right Where is located in pain in our options menu. Right after home. And within that Favorites Tab, we've got our get data command. We've got our table tools which contained pivots, recommended pivots and tables. And we've got our spark line group as well. So really helpful tool for organization and quick access. But if you wanna undo that or remove your custom tab pretty simple, he can right click customize the ribbon, navigate to your new favorites or custom tab, whatever you named it. Simply right. Click. Remove it. Press OK? And there you go. I'm right back to where it started from. So there you go. Customizing the quick access toolbar and the Excel ribbon 12. Splitting Text to Columns: all right. The next pro tip that I want to cover is splitting out text strings using text to columns. Now, if you have data that's stored as either a text file a txt or a separated value file like a CSP comma separated values, then you can use text two column tools within Excel to split those values into columns and rows. In other words, you can convert it into a tabular format that you can then use for analysis. So I'm gonna show you a couple options here. Option one. If your data looks something like this, you have a TXT file right now in viewing it and note pad. Where you can do is simply open that text file from Excel tow. Launch the text to column Wizard. This will walk you through all the steps and then load that data delimited out into rows and columns. Second option is to open a C S V file into Excel, and what you'll see is something like this where all of the data lives In column A. It's just these really long strings of text condensed into that single column. And in cases like that, where the data lives in column May in Excel, All you need to do is use the text a column tool from the data menu, and you'll get to the same option. The same user interface where you can determine settings like specifying the delimit er, defining any text qualifiers like quotation marks and formatting or excluding columns right there within the dialog box. Either direction that you go, you're gonna end up in the same place, which is something like this. A properly formatted, tabular data set that you can work within Excel. So couple common use cases here first and foremost, transforming raw text based data into properly formatted tabular layouts for analysis, either in Excel or other data analysis tools and then second, splitting text strings without having to use any sorts of formulas or functions. So let's hop into our pro tip workbook and excel and give this a go. All right, so if you're following along with the course, go ahead and open up the Excel Pro Tips workbook, and you can find the text to Columns Blue tab, either by scrolling through the tabs or, as always, clicking the table of contents. Finding that text to columns demo and clicking the link to jump straight to it. And here you'll see data that was sourced from a C S V file. And what that means is that it's loading the data into a single column. There's nothing in columns. B c D E. It's just basically compressing all the information into a single text string in that one column and what we need to do is tell Excel to break the state out into columns defined by the positioning of each of these Comus, which in this case is our delimit er so to do this, I'm going to select the entire column A containing all of our data, head to the data tab and click text to columns. This opens up this wizard. It's dialog box that allows us to customize their settings in order to break this data out properly into columns. This is the CSB example. We would have arrived at this exact same dialog box had we pointed or navigated to a text file on her desktop, for instance. So from here we can determine what type of data we're working with in this case delimited. We have a character in this case a comma that separates each field so you can go ahead and press. Next. Make sure that your delimit er is not set to tab or anything else. This is a comma separated field you can choose to treat consecutive commas. Consecutive delimit er's as one. This case we don't want that option. We can keep the default text qualifier at two quotation marks, and you can see this preview here that showing how the delimit er is going to break out our columns and because we have column headers right there in the first row, it's pretty easy to spot check and confirm that this is indeed breaking out the data in the way that we'd expect. So from here, let's go ahead and press next. In this view, it can actually apply formatting to these columns or exclude a column entirely if we don't want to load it into columns. So 99% of the time General is going to be the right fit is going to allow Excel to convert fields that it recognises as numbers, two numbers, date values, two dates and any remaining values to text that will almost always get the job done. one thing to note that can sometimes be helpful if you receive data that contains dates in a different unusual format from your local settings are locale. You can use this date option to force that date into a different format that your version of Excel will recognize. Other than that, most of the time, this will give you exactly what you need just by using the general data format. So our destination and sell a one in this worksheet. That sounds fine. It's going press finish, and there you have it. We've broken out that one single string that had existed in column A into individual columns all the way out through column. Oh, and now our data is in a format that we can easily load into tools like pivot tables or the data model for further analysis. Now, a couple quick warnings when you're using this text to columns tool first and foremost, if you have values that already existed in these columns, B C D. E. F. This process that we just went through would overwrite those values. In other words, we wouldn't push those values out. Two columns P. Q. R. It would simply overwrite them. So, generally speaking, make sure there's nothing in the cells or the columns to the right of your raw source data . Before you use that text to columns tool to break it out. And then, second, you're gonna want to be very, very sure that your data is in the proper format and that you don't have issues like missing delimit er's or blank values. And I'm gonna show you why. That's an issue here in a press control Z twice to get back to my original format. Now let's just look at Roe to here, and what I'm gonna do is actually just delete the name of the AB, which is the second column Pacman Premium. And if I delete it but leave the space between the two comments and press OK, if we go through that same text to columns process next comma next finish. See, that's okay because we have a blank app name where I deleted it. But every other field is organized into its proper columns, you know, press undo again three more times. The issue is if you have cases like this, where one of your delimit, er's Comus, doesn't even exist now if we go through that process one final time, Text to columns next, next and finish. Now we've got an issue because that second row doesn't align with the others. Everything from the right of column A has shifted to the left because of that missing delimit er. So, as you can see, seemingly minor issues like one missing comma can actually create pretty major issues with the structure of your resulting data set. So let's go ahead and undo that change on Do again do. And now that we have our app, name back, back in original format, let's go ahead and run through it one final time and will keep the settings that we had been using First finish and there you have it. Now we've got a properly structured tabular data set. There you go. Text to columns 13. Synchronous Scrolling: all right. Our next pro tip sounds pretty fancy, but it's actually quite basic. We're gonna talk about comparing two files or excel workbooks using something called synchronous scrolling. And basically what synchronous scrolling is. It's a way to arrange to excel workbooks side by side and have the ability to scroll through them both at the same time. So we're gonna follow a few quick steps in this demo, we're gonna open up to workbooks that we want to compare. They're gonna be identical. We're gonna go into our view tab and activate view side by side as well a synchronised scrolling. And what that's going to do is allow us to scroll through both windows at the exact same time. So few common use cases here. It's good for spot checking workbooks to test for version control issues or make some quick , high level visual comparisons between files that share similar formats and layouts. Now a quick word of warning. I would not recommend using this tool as your primary or soul form of quality assurance, but it is a nice way to kind of do those quick preliminary spot checks. So it's jumping to excel and see what this looks like. All right, so here I am, many Excel pro tips workbook, and I'm in the blue synchronous scrolling tab in her productivity tips section. And what I've also done is create a copy of this tab, and I've named it as a second file called Temp PRECIP Data if you want to follow along, so I've got to work books open, which basically showed the exact same identical view. Now, from my pro tips file that can navigate to my view tab and click view side by side, and that's gonna rearrange the windows and kind of stack them together. And depending on your settings, this may look a little bit different. Mind kind of defaulted to this vertically stacked format, which is a little bit tough to read. I prefer kind of side by side with a vertical split. So to change that I can go to arrange all here in the view tab and instead of horizontal, gonna choose vertical press. OK, and there you go. That's a little bit more readable, and the next thing we don't want to look for is this synchronised scrolling button, and it's a little bit compressed here but it lives right beneath the view side by side button, and you want to make sure that's activated so shaded means it's activated. We're going to go. What that means is that if I scroll through this one worksheet in this one workbook, my other workbook is gonna scroll at the exact same time. And what's cool about this is that doesn't just apply to vertical scroll bars applies to horizontal as well, so you can really navigate through each sheet and kind of spot any differences between them . So it's really a simple is that to return back to my kind of standard full screen view, all I need to do is click the view side by side button again, and there you have it kind of right back where I started. So using synchronous scrolling to compare to workbooks in Excel 14. Extracting Unique Values: All right, This next tip is a really useful one. I'm gonna show you how to extract unique values using the advanced filter tool. Now in Excel, there are a number of different ways to remove duplicates. You can use the remove duplicates tool you can use. Advanced filters, can't even use pivot tables or power query all to accomplish the same end result. Now, I actually like the advanced filter approached the most because it's the most flexible. And what I mean by that is that it allows you to either filter the list in place or overwrite the original list, which is how the remove duplicates. Tool works by default. Or you can copy on Lee the unique values to a new worksheet location. So it's a have some data like this. I've got dates, product ideas, products, you know, maybe this is transaction data where product might sell, you know, on multiple days or even multiple times within the same day, meaning that that column C is likely to contain many duplicate values. Now I'm gonna show you how to extract on Lee the unique from column C using that advanced filter tool, which you can find in the data tab of your ribbon. And when you launch that filter tool, you can choose to either filter the original list in place or copy it to a new location. And the key is this check box at the bottom. Unique records Only. What that's going to do is produce a list like this one shown in column F that contains Onley, the unique from our original column C So really great tool Couple common use cases here for one, obviously just identifying unique while preserving your original list, which may live as part of a larger table or data range. And then second, it's a great way to create new look up or dimension tables containing Onley, your unique primary keys. Now, to learn more about what that means would look up or dimension tables are all about, check out my intro to Power Query, Power Pivot and Dax course that's gonna talk all about excels, data modelling and relational database tools so that let's take a look at a demo, actually practice extracting some unique using that advanced filter tool. Alright, So back in our Excel Pro Tips workbook, I mean the blue extracting unique values tab. And what we've got here is a list of a quantity of products sold. So we're looking at product names and I ds the quantity sold per day and scroll through Will. See, we've got a lot of different transactional records here, spanning about a month, about 20 days, but it's 5000 observations. So we know obviously that we've got some duplicates here in column C. And now the task is to turn that product list into a list of just unique. So I want to know Onley list of unique products that sold during this time period and again we can select. Call them, see head to our data tab, and you might be tempted to use this. Remove duplicates button. But remember that this is going to remove those duplicates in place. And we don't want to do that because we want to keep the quantities, keep the product and keep this transactional tabular data set in place and intact. Instead, we're gonna use this advanced option in our sort and filter tools, and from here, we're gonna say, No, we don't want to filter in place. We don't want to overwrite original data. I want to move it to a new location. And this is our list range. See one to receive 5000 and one, which is correct. And we want to copy that two new ranch. Let's say F one And here's the key. Check that unique records only box and press. OK, and there you have it. Once we do that, you can see with the control arrow down shortcut that our list ends right here at 14. 90. So a lot of those duplicates were removed, so they have a great way to use the advanced filter tool to give you a little bit more flexibility for extracting and identifying unique values. 15. Named Range & Table References: all right. Time to talk about simplifying formula references, using named ranges and tables. So getting a little bit more complex here. This is a three star productivity tip a little bit more advanced, a little bit more nuanced than some of the more basic tips that we've covered up to this point. But basically what we're gonna talk about here. It's how to use either named ranges or tables compared to Ross Cell ranges like a one through a 10 to simplify our formula references. So, for instance, let's say we're looking at data like this where you've got a holiday table containing holiday labels for each date and then some sort of transactional record as well. We basically want to populate a column like column see here that says, Given the date, look up the holiday and if it's a holiday, return the name. Otherwise return in. None. Now, rather than referencing E two through F 16 in our formula, we conform at that holiday table itself as a proper table and given a name like holidays. And once we've done that, we can simply reference that table name holidays right there within our look up table and This not only makes the formula more readable toe end users, but it also serves a few very important benefits. Number one. That table will now automatically ingest new rows of data. So if we want to add a holiday to that list, we don't have to redefine the cell range weaken, simply tack it on, and it will be absorbed into the table named holidays. Then we'll also have some additional table specific benefits, like formatting and filtering options and the ability to write more efficient, calculated columns versus the A one style cell formulas that were used to writing So couple common use cases here, number one. Replacing those raw cell references is a great way to make her formulas more readable and easier to interpret. Second, you can convert chart source data to a table or named range to enable automatic updates. So instead of sitting, a chart on top of a static data range can pointed to a dynamic table and last but not least , preparing data for analysis with Exelby I tools like the data model or power pivot. So let's jump into her pro tips workbook and see what this actually looks like. An excel. All right, So in your protest workbook, head to the named ranges and tables Tab. It's one of the last blue productivity tabs. You can either scroll to it manually. But remember, you've always got that table of contents in the first tab where you can simply jump to the link and land straight at the demo. So here we've got that same kind of demo that we introduced just moments ago. We got transactional records with dates, a certain volume of transactions. We want to populate this holiday label column. See, by looking up the date within this little look up table here and returning the name of the U. S. A holiday and we can use a number of different formulas to do that can use index and match this case. I'm going to use simple V look up formula. We're in the same equals. We look up and value we're looking up is the date and transactional room and the table array. This is where we're looking for that date and where we're gonna try to retrieve the holiday label. So if we just kind of go the standard route and select the raw cell range e to through F 16 . Go ahead and press F four to fix that reference in comin over to the next formula argument . The column index. So where does the holiday label live? Well, in this case, it lives in the second column in our range and then last argument here we want false or exact match so narrow down tavern in close the parenthesis and press enter and there you have it. January 1st is New Year's Day. We apply this down. What we'll see is an error and n a for any non holiday date. And we should see the holiday it name populate. Otherwise, so little quick tip here. If you want to get rid of these days, we're going to cover this in another tip as well. But give you a little bonus here. Just wrapped that entire function in if error. So if you get an error when you do this entire V look up comma value if error, we're just gonna put the word none in quotes, close the parenthesis and press enter. I'm gonna ply that down. No, I went through that very quickly. Now, at this point, we have our kind of wrong cell reference here, which isn't very helpful end users. And it's not dynamic, right? So if I add rows here, RV lick up is continuing to stop at Rose 16 and I will have no idea that we just added data to rose 17 18 19 etcetera. And we'll have that same problem even if we name this range something like holidays. So let's go ahead and select E to their F 16 that same range that we referenced. And to turn this into a named range. It's a simple is clicking the name box to the left of the formula bar and typing in a name right here. So call this holidays, press enter and now back in my formula can actually just go ahead and delete the table array and start typing holidays, and you'll see that it's populated right here with a special icon. That means it's a named range. In my workbook, I can press tab toe lock that in, you can see that it matched up with ourselves there and press enter double click to plan that formula down, and now at least we have improved the readability of the formula. But we haven't quite solved the problem because if I add another date here I in 1 2014 which obviously Chris's birthday, very important date. And then we scroll through to September here in our transactional table, you'll notice that 91 is still listed as no holiday, which is very sad. And that's because even though we named this range holidays, the range was defined toe end at Rose 16. So let's go ahead and delete my birthday will add it back in in just a moment. The other approach, which actually prefer, is to not format this range as a table. So easiest way to do that, to select any cell within it. Control T. Yes, we've got headers that looks like the correct range press. OK, they're ago, and now we've got a table containing her holidays go into our table tools tab. Give it a name, just like we named our named Range, consisting of cells. Let's call it holidays. Two, because we can't give it the same name. Press OK and now go ahead back into her formula. And now, if I leap yes, you'll see. You now have two options here, holidays and holidays to That's my table version can tab that one in press enter and fly it down. Now here's where the good stuff comes in. Now if I go ahead and add that additional holiday 91 2014 Chris's birthday see how it formatted it to kind of pull it into the table. And as we scroll down all the way to September, check it out. 91 2014 Chris's birthday. So I didn't have to modify the actual definition of the named range, but formatting it as a table. It automatically ingested that new row of information and then, like I hinted at before, we got some other benefits of tables. You know, one thing is that we've got these banded Rose makes it nice and readable. Another is that if you actually select a table and scroll down, note how the headers kind of stuck there. They froze right. There are column labels, which is great, especially when you have very large tables that might be hundreds or thousands of rose long s O to little benefits to tables. But really, that big one is automatically adding data as it gets placed in new rose. So there you have it. That's your crash course on simplifying formula references with named ranges and tables 16. Protecting Cells & Formulas: All right, guys. Time to talk about protection. I'm talking about workbook protection specifically. Now we've all heard this story. You've spent hours, days, maybe even weeks building this incredible, complex, intricate dashboard or data model in Excel. And then one day, Gary, the intern, strolls in he hard codes all your formulas. He deletes half the rose accidentally and then saves it as a flat CSP file. And all that work you just did is gone down the drain. So cases like that. I know it's a little bit extreme, and it's a fictional tale, but it's not that uncommon. And cases like that you've got to look back and say, You know what? I could blame Gary, Or I could have applied some workbook protection settings to prevent this from happening, And that's exactly what we're gonna talk about now. So when you look at the format cells dialog box, you're gonna see a protection tab that allows you to specify exactly how cells are going to behave once the worksheet is protected. So maybe you've got a simple report like this. In this case, I'm looking at baseball stats from 2010 through 2015 for a specific player, and I've created a data validation cell on Row two so that users can change the player name and view their metrics right there in my report. So I want my users to be able to change the selection in the drop down cell, but I don't want them to be able to edit or in some cases, even see the formulas in the other cells of the worksheet. So our protection tools, they're gonna allow us to set those exact settings to do we need. If I select that data validation, so can use the control. One shortcut to launch the format cells dialogue box. And from there you can navigate to the sixth and final tab in that view called protection. And in that protection tab, you'll see two different options locked and hidden. Locked means that users can view the cell contents, but they can't edit or change those contents. Now, by default, that will be set with a check mark so all cells will be locked by default. Hidden means that users can't even see the underlying formulas or references when they select a cell, so it's almost like an additional layer of protection on top of simply locking the cell. Now it's important to keep in mind is that thes protection settings have absolutely zero effect until you actually protect the sheet or protect the workbook. And this is kind of a nuanced thing that a lot of people don't quite understand. Everything in Excel is going to be locked by default. But because your workbook isn't protected, it has no impact on how you see or interact with those cells. The Onley time these protection settings come into play is when you protect that sheet or workbook. So what that means is that if you want to protect certain components of your workbook, your goal isn't to protect certain cells. It's to un protect the ones that you want users to edit. It's kind of coming at it from the other side. I'm gonna show you exactly what I mean. We go through the demo in just a second. So common use cases here, obviously preventing users from accidentally modifying sensitive content Gary two, obscuring underlying formulas or sell references from view again. That's that deeper level of protection so that users can even see the formula deriving the value and then last but not least, limiting user interaction to a specific set of inputs or cells. This is the way that I personally use self protection. Most frequently. It's one of building things, like models or dashboards, where I want users to be able to edit certain cells, but not others. So let's jump into Excel Workbook actually practice modifying some of these settings. All right. Now, if you've been following along with the course up to this point, go ahead and open up your Excel Pro Tips workbook and head to the Workbook Protection tab in the Blue Productivity Tips section. Now, for those of you who have taken my data viz with charts and graphs, course, this view should look pretty familiar. This is the baseball dashboard that we built in that course, and basically what we're doing is allowing users to select individual players and have this nice kind of clean report update dynamically to show six years metrics along with options to customize the views in the metrics that they're showing in this visual. So that's great. But in its current state, there's no protection applied whatsoever. So this is a pretty fragile worksheet when I mean by that, is that users anyone with this file can see the formulas that air deriving these values. Not only that, they can delete those values as well. So let's go ahead and undo that. Don't want to get our formula back. And what this means is that we need to apply some sort of protection to the sheet to prevent that from happening. So as a first past, maybe we head to our review tab and just click protect sheet. Now, by default, you won't have a password here. Let's not add one you can if you want. But these default settings basically say that once we protect the sheet, all that we want to allow users to do is select cells. Whether they're locked or unlocked, it can't edit them. It can only select them. So that sounds fine. It's press OK, and now if you tried to delete one of these values, you see this pop up saying that you're trying to change value on a protected sheet, so you've gotta unprotected and heads up. You might need a password to do that, Um, and that's fine because I don't want people deleting these values, but it also means that a user can even select a different player here. And the same goes with these data validation cells down here in row 14 as well. So as a next step, what I need to do is un protect the sheet and then target or isolate just the cells that I want users to continue to be able to edit. So, for instance, sell T to this drop down here. Let's right click format the cells. You could also use the control. One shortcut there and all we're gonna do is uncheck that lockbox and press. OK, do the same thing for these two cells here by the chart Control one unlocked. Okay, control, one unlocked. Here we go. Now, when we protect our sheet, keep the default settings. We still, you know, prevent users from editing the values. But now the user can actually go ahead and select a different player or customize their chart with any of the cells that we've enabled for editing by un protecting. So that's great. But I'm not quite all of the way there yet because maybe I don't want users to be able to see the underlying functions producing these values here. So even though a user can't edit or delete these values, they can still see the underlying formulas. So to fix that, we need to add that additional layer of protection. Let's go ahead and un protect again, select that whole range of cells containing those formula driven values. Control one. And all we're gonna do is at a check box to the hidden box and that will obscure those formulas from view. So it's press, OK, protect sheet. Okay. And now check this out. If you try to click on any of these content cells here in my report, all you see is a blank formula bar. So you've no idea kind of how these formulas air being derived, what source data they're coming from and so on. But those cells that are unprotected are still free to allow users to select certain players, and the data in those underlying hidden cells or locked cells can still update accordingly . So there you go. This is a much better, much more protected version of this report, and there you have it. That's your crash course on workbook protection settings. 17. Multi-Level Row & Column Sorting: all right for this pro tip. Want to talk about slightly more advanced sorting rules, specifically, how to apply multilevel row and column sorting. So for very simple cases, you can just use the column header options to sort individual fields one by one. It's a perfectly valid approach, but in cases where you want something a bit more complex, a bit more custom, you can use the actual sort tool within your data tab. And when you access that tool, you'll see a dialog box that allows for multi level sorting. And essentially, what you're doing here is creating on order list of sort rules that can accommodate multiple columns in your table. So in the example we're looking at here, what we're doing is sorting by a country field alphabetically from a dizzy and then within . Each of those countries were sorting on the provinces within those countries, also from a dizzy and then within each province were sorting by a numerical field called points largest to smallest. So now our tables being sorted based on logic from three columns at once as opposed to one at a time, and I'm gonna show you a great example of what this actually looks like in just a second. But this is kind of our default option. It's our sort, top to bottom. It's how most people sort your basically just sorting rose within each column. But that's not the only way to sort in excel. In fact, if you select the entire columns of your range, including the headers, you'll see another option, which is to sort left to right. And this basically allows you to sort the order of your columns instead of rose. And one thing in this example that you'll notice is that we're sorting by Row one just the first Rohner table. It's our header row in most cases, and we're not sorting on values or texts were not sorting alphabetically or large to smaller, small to large. We're sorting on cell color, which is really interesting. It's kind of hard to wrap your head around it first, but basically you've got other options. Besides cell values, you can sort based on color font color, even icon sets that come from conditional formatting rules. And basically, what Excel will do is it's gonna look at Roe one. See all of the cell color fills that exist in that room and give you options to say. And I want to put all of my cells filled with green to the left or move them all the way to the right or vice versa. It just gives you a bit more flexibility. You know, when those alphabetical or numerical sorting rules don't get the job done. So a little bit tricky to comprehend at first. But I'm going to show you an example of when this could be a really helpful tool. Now common use cases for one, applying more complex or custom rules to your tables. Ranges number two. Re arranging your columns, either alphabetically or by different rules like color or font toe. Organize large tables, especially tables that may have dozens or even hundreds of columns were cutting, and pasting may not be the most efficient approach. So with that, let's jump into excel and show you some great demos of what this actually looks like. All right, so here I am Excel Pro Tips workbook. I'm in the table of contents tab and we're getting towards the end of our productivity tip list and let's look at our row and column sorting demo This is a three star demo, so a little bit trickier, but still, you know pretty easy to comprehend. And let's go ahead and click Link to jump straight to that row and column sorting tab. Now here you're going to see all sorts of data about wines, and each row in this range represents an individual line. And we've got information attributes about that wine, the variety, what whiner it came from the province, the country of origin, as well as some quantitative or numerical fields like the point rating upto 100 the price per bottle. So really good table to practice some of these row and column sorting tools Now. First things first. Let's go ahead and add a filter to row one a couple of ways you can do this. Jump to the data tab. Simply press filter. I prefer the altar key tip approached a bit faster. Ault, 80 will do the exact same thing. And again, if we wanted some very simple, straightforward sorting logic here, like sorting all of our rose based on countries, we can use those headers to do that. So now we'll see. Argentina, Australia, Austria at the top of column c comply a similar rule to province, for instance, alphabetically a dizzy and we'll see, uh, all it all in Tejano. Um, all in Te'o all Sochi A. Um basically, our provinces are now sorted, but take a look. A column. See the country field That one's kind of been over written. It's re sorted based on the order of provinces. So it's kind of like a one and done kind of situation if you use these column headers to sort your table, So let's go ahead and get a little bit more complex here. What's going to data sort? And this is where we can access that multi level logic here. So in this case, let's use a similar example that it talked about in the slide. We're using some of these geographic fields and let's start high level with country. First, let's sort the country on values. A dizzy it doesn't really sent makes sense. We don't have cell colors or formatting here that we could use. So sell values is really the only option that really works in this case and now again within each country. So we're gonna have a chunk of rose for each country within that chunk of rose. How do we want to sort that chunk? That's why we add a level and we can sort those chunks, you know by province also a dizzy. And then within each province, we could go even deeper and sort, you know, the wineries or the wine varieties. In this case, let's go ahead and add a level and use one of our numerical fields this time, instead of looking at, you know the most or least expensive wines. Let's rank each province by the highest point rating, so we can go ahead and pick the points. Call him here, sell values and we don't want smallest to largest. We want largest to smallest. Since we're sorting top to bottom, I want to see the highest values up top press, OK, and there you go instantly. It's applied these multiple levels or layers of sorting rules, got her country's sorted first, and then note that within each country our provinces air also sorted alphabetically. Austria got your A's B's your sees your case, and then within each of these provinces, your point ratings are sorted. Lips looks like assorted on price. Accidentally, our prices are sorted alphabetically, and that's fine. If you wanted to change that, Ah, we can simply go back to our starting rules. Change price. Two points that looks better knows it's wondering what was going on there. So now within each province, here we see the points descending 90 to 91 90 87 so on. So we've created a much more ah sophisticated set of sorting logic here that we couldn't have achieved with just those headers alone. Eso That's Rose sorting. And that's kind of where 99% of people will stop pick. Remember that you can also apply column specific sorting as well. So what if we wanted to take these seven columns and rearrange the order of them, you know, without just cutting and pasting them manually? So, for instance, you know, maybe I want my metrics over here on the right. Maybe I want my columns to be sorted alphabetically, left to right. These are all options that I can accomplish or achieve using that sort tool. So the key here is to actually, instead of just selecting any cell within the range, I'm gonna select the entire column A by clicking the header when a hold shift press column g to make sure that I got the entire set of columns. And now when I go into my sort options, you'll see the dialog box looks a little bit different. Now it says row here instead of column and it says sort by row, one row to Row three roof. For now, you'll almost always want to choose your header row here, which in this case is Row one. That looks good. And if I press options, you'll see that now we're no longer sorting top to bottom because we're not sorting Rose. We're sorting left to right because we're sorting columns so press OK. We've got the same set of options here. Values sell color, font color or conditional formatting icons again because we only have a single cell fill color here. The only option that makes sense right now is cell values. So sorting Row one values A to Z will accomplish something like this. Where are column headers are now alphabetically country, which means with C points, price province y name, wine, variety and winery. So we've rearranged our seven columns into alphabetical order, and that's fine. That's not quite what I want. In fact, what I really want is to put my two numerical fields at the end of the list. So I can't accomplish that using any sort of value or text based rule, because points and prices always gonna fall kind of in the middle of an alphabetical sort order. And this is where something like sorting by color is a great tool. So all I need to do is select those two column headers that I want to reposition or move going to my home menu and give them a different kind of self. Phil. Let's give them a green Phil here instead and again select all of column A through all of column G holding shift Gonna go back in the data back in this sort. Now, instead of sorting on cell values, well, sort on cell color and take a look at this. It knows now that I've added a second fill color with this green, I'm gonna say, Give me all those green cells in row one and put them on the right for us. Okay? And there you go. Now it's put my to quantitative values in columns, F and G and the rest of my columns are sorted like they originally work. So really helpful options to use if you want more complex or more custom, a sorting criteria. 18. Advanced Filter Criteria: all right. Hey, everyone want to share a pro tip with you that I came across pretty recently that just completely blew my mind, and we're gonna talk about some very advanced filtering tools That, to be quite honest, I had no idea even existed in excel. I'm gonna call this a five star productivity tip. This is an expert level tip, definitely a little tricky to get used to. But once you understand how it works and what's going on, I think you'll find that this is an incredibly powerful option. So basically, we're dealing with advanced filters here, and we've used these before. You'll find the advanced filter option. All right there. In your data tab, we've used them to kind of pull out unique values from a list, paste them into a new workbook location. But what we haven't really played with is that criteria range condition within the dialog box. We've always kind of just left that blank and ignored it. And that range is basically just a range of cells right there in your worksheet, where you can manually input very complex custom filtering rules, and by doing so, you can create these intricate combinations of rules that you couldn't possibly replicate with any other standard filtering tools, even basic pivot tables. So let me show you an example of what I mean by that. In this case, I've created some filter criteria cells right above the table, arrange itself and sells a two throughout four, and you can see some of the criteria that I've entered there. So in column de genre, I'm telling Excel to filter my source table for Rose or the genre equals biography or action. I'm also applying a wildcard title filter with the word the followed by an asterisk that tells Excel. I want only the titles that start with the word the but note that that's on Lee on Row three, the biography wrote, so that wild card title Filter is not applied to the action genre Rose Onley Biography and then similar case for column I that I am Devi score. What I'm doing here is saying Show me all of the biography films that haven't IMDb rating greater than eight. But for my action films, I want to be a little bit more critical filtered those action films down only the ones with the rating greater than 8.5. So one important thing to note here is that you'll notice there's no filter row or there's no filters applied to the headers and roast seven. And that's because Excel isn't simply using, you know, header filtering options. It's actually iterating through the rows of the table and filtering down the ones that don't meet all of the criteria defined in my criteria range. And what that means is that it's very powerful and very flexible. But it can also be very slow, especially if you're dealing with very large tables or if you set very broad criteria that returns thousands or hundreds of thousands of rose. So common use cases here again, applying those customer complex rules that can't be replicated with your standard filter tools or pivots, or adding user facing criteria range, which helps to provide some transparency into how a table arrange is being filtered and what settings have been applied. So again, this one takes a little bit of time to get used to a little bit of experimentation to get to work. But let's jump into our pro tip workbook and actually work there a couple samples, All right, so here in our excel pro tip workbook Gonna head to your advanced filtering demo productivity tip section Go ahead and press link that will take you to your blue advanced filtering tab. And this should look familiar. It's just like the demo I showed you in this slide where we've got a source table which is really just a range of values starting here in rose seven and then I've manually created Ah , this space for my filter criteria right here above the table. And it's important to keep in mind that you do need to replicate your header row like I've done here in row two. That's gonna give Excel what it needs to apply these advanced filters. So to show you how this works, I don't need to apply filters to any headers here because this is gonna work a little bit differently. Remember, it's gonna actually literate through the rows in this range and hide the ones that don't meet the criteria that I said. So let's start simple. I want to return kind of small set of data. So let's say Onley, Show me the rose where genre equals biography, enter that in and now, with any cell selected in the range that I want to actually filter. Gonna go into data advanced. And remember, this is the criteria range that I care about. It's automatically identified the entire cell range that I want to filter. You can confirm by looking at these green marching ants extends all the way down through all the data that I want. So that looks good. Now my criteria range is going to equal Just row to through row three from a to also a l sorry. 82 through l three. And that should just about do it. You press. OK, there you go. Our source range down here has filtered down toe on Lee Show me Rose where biography is the genre and following that same process, we can add more filters here just like we would in a regular filter dialog box. So it can say, you know, I am Devi score greater than eight. We can say Onley, Show me the titles that start with the word the and the asterisk basically means I don't care what else comes after the word the just give me the ones that start with t h e right and then basically got a range defined already. So we select inside of our table press filter that's gonna unfiltered Everything going to advanced Got the range already set See that we've already kind of dropped our new values into that range that we've already to find Press OK, and there you go. So now biography films, starting with the word the that have IMDB scores greater than eight. So I've got the pianist C inside the imitation game the act Killing and the wolf of Wall Street thes five titles passed through all three of those criteria or those filters that we defined. Now we compress filter to clear that again. And in this case, we haven't done anything novel or groundbreaking at this point. Because if you want to see something, we can actually just replicate what we've done using the standard filters. We're gonna do a text filter that begins with that same thing, though with an asterisk s Okay, filter. I am baby scores with a number filter greater than 8% K And look at that. We've arrived at the exact same answer same five titles, just using those standard column headers, right? So we didn't really need to use the fancy advanced filter criteria for something like what we just did. So let's walk through an example of when we would need this advanced tool. I'm gonna go ahead and just unfiltered that take the filters off the header and we're back to where we started. And now let's add some criteria here. So when we add fields beneath existing ones, basically adding in or claws so if genres, biography or something else. So in this case, let's go ahead and add family as second genre. Go back into our advanced filter, and this time we need to change the criteria range. Since we had only extended it to Row three. Now we want to extend it to Row four because we've added family right there in that fourth row. So press OK, here we go. Now we have biography and family films. Note that these additional filters, the filter on the title and the greater than eight filter on the I'm to be score are only applying to the biography Rose because that's where those filter criteria settings exist. So we've got family titles that aerated under an eight, and we've got titles that don't start with the word the and this pinpoint level of control is exactly why this advanced filter tool is so powerful. And it's why we can do things like you know, instead of family, maybe want biography and action. But we only want action films that were released after 2005 and we want apply, and I am DB score to the action titles as well. But not greater than eight. Maybe want greater than 8.5 instead, right? And because now we have our filter criteria that accommodates through row, for all we do is press advanced. Okay, and there you have it. We've got biography films, action films that are on Lee released after 2005. Both of those action titles are rated greater than an 8.5. Meanwhile, the biography rose following completely different set of filter criteria. So there you have it. Advanced filters can be an incredibly powerful tool once you learn how to harness them,