Wouldn’t it be nice if you could do a Google search on your spreadsheet?

Well, you can! And in this video I’ll show you how!

Download FREE Hands-On Exercises

Full Video Transcript:

Has it ever happened to you that you knew a specific phrase or even a specific number but you couldn’t seem to locate it on your spreadsheet?

Stop fumbling around and use this shortcut I’m about to teach you. It’s kind of like having a Google search on your spreadsheet.

You may think you know how to use Find but what I want to show you today is a way to really take advantage of this built-in feature, to locate exactly what you’re looking for without having to fumble around and just struggle.

So what you want to know is how to get to that Find box. That Find pop-up is CTRL + F, you may have already done this before. If I’m looking for an “X” and I hit ENTER right now, it’s going to go to the find next but if I keep going I can actually go to all the instances of it that way. Fundamentally, this is what it’s all about but there’s a lot of different ways to take it to the next level, which I’m about to show you.

So for example, if I go to the options, there’s a whole world of more advanced ways to go about this. You can do things like search for a specific format, we’re not gonna get into the details of it but I recommend that you explore this on your own time.

The cool thing is to search not just within the workbook, or not just within the worksheet, but the entire workbook, so for here, if I keep it within the sheet, it’s going to localize it to this worksheet only. You can search by rows, meaning going left to right and then down, like a book, or you can do it by columns, where it goes first up and down and then to the next column. Very cool way to speed up the search, if you have a lot of stuff.

You can also look within the formulas, within the value, within the comments, again, explore this on your own, match the case. I mean it’s crazy.

Really cool thing to do also, is to do a Find All, what that does is it gives you a list of all the different instances that that character or that string of characters appears. And you can see them all here, if I were to change it now to, say, within the entire workbook and I do Find All again, notice what happens, it gives me all the details. The cover worksheet has an “X” in it, in this cell, like, it literally lists everything out.

So I recommend that you explore this on your own, in detail. You can also go to the replace, and find every time there is an “X” and replace it with a “Y”, stuff like that, so it’s really, really cool.

To navigate this window, this is where it gets also pretty juicy, is to hit the TAB key and you’re going to jump to the next section of this pop-up itself. So TAB will go next, SHIFT + TAB will go to the previous, and if you’re on a checkbox, you want to hit the SPACE to activate or turn off the checkbox itself. ESC will essentially close the window without doing anything, and ENTER will actually save it or make that action take effect, and that’s what you want to keep in mind in terms of navigating this pop-up window.

Now, if you’re on a Mac, it’s a little bit different, I want to show you how this works. Anyone on a Mac can use the COMMAND + F but what that does is it gives you this little search box, and it’s not nearly as powerful as we just saw.

So instead, don’t hit COMMAND + F, resist the temptation, just hit CTRL + F and this is what you get instead, the same kind of options, not as much detail, but you still have more control than you did before. So that’s the nuance for Mac users, do not use COMMAND + F like you’d think to, use CTRL + F and you’ll get this more advantageous view.

I’ve created some exercises to help you reinforce all of these concepts that we learned, all these shortcuts. So again, what you want to do is go to that worksheet and find, here’s the hint, find the numbers. So I want to move the Xs, I’ll go ahead and do CTRL + X on the PC or COMMAND + X on the Mac and now, I’m going to find, CTRL + F, and look for the number one on this sheet only. And hit the next one, first it’s here, next it goes here, there’s one in there but that’s not what I wanted and now it goes to the one, which is somewhere on the sheet. We don’t know where, that’s why we’re using the find option here.

So go ahead and then you found it, close this up, go to the cell and CTRL + V on the PC or COMMAND + V on the Mac to paste, and go ahead and do that for all of the Xs for this exercise.

Don’t forget to visit test.excelshir.com, where you can download these exercises, along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.

Thanks for watching, and I’ll see you next time. And I’ve said it before I’ll say it again, share the Excel love.