Accessing the Go To Special options - Microsoft Excel Tutorial

- [Instructor] All right, let's talk about selecting special cell types using Go To 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. And that includes a ton of additional options that allow you to select certain types of cells or objects within the sheet. So you can select things like blanks, cells contain formulas versus constants, 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, cell E10 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 cells in a worksheet are user inputs, for instance. So let's jump into our Pro Tips Excel workbook and practice some of these Go To options. OK, 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 E10 will essentially retrieve the proper price from this matrix based on those selections. We'll also notice 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 we'll 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. We've got conditional formats, we've got constants, we've got formulas, and we have data validation cells. So let's see what this actually looks like. I'm going to select an empty cell over here and press Control + G to launch the basic Go To options and then click the Special button in the lower left to access those additional options. So starting pretty simple, let's go ahead and select Constants, which is any non-blank cell that's not a formula. When we press OK it's going to 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 the mouse. Now deselecting those. If we go back to Control + G, back to Special, we can do kind of the opposite and say, OK, now show me any cells containing a formula. In this case I want any sort of formula, so I'll leave all those boxes checked, press OK. And that highlights cell E10, this kind of merged cell 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, press OK, and 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 and keep the All option selected, press OK, and that's going to select cells B10 and C10, which are our two 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.