It’s always good to “know your options” – this is especially true when it comes to being efficient executing your day-to-day responsibilities. Today, we’re going to explore the ‘Options’ button associated with the Find Function in Excel.
Knowing how to take advantage of the options available when searching for information in an Excel spreadsheet can be particularly useful, especially when dealing with large and complex spreadsheets.
Supercharge the Excel Find Function
Most of us have probably used the Find function in Excel, the binocular icon found under the Home ribbon in Excel 2007. So now it’s time to supercharge its use. These tips will increase your efficiency and effectiveness the next time you go to search for something in Excel.
First, get away from the mouse; you can use Ctrl + f to pull-up the Find function. This will save you the time of going to click on the Home ribbon, and then moving your mouse over to the binocular icon. Having to remember this simple shortcut key is well worth the time savings. DOWNLOAD A LIST OF SHORTCUT KEYS.
For example, to select everything – click on the first item > hold down the Shift button > go to the end of the list > click on the last item (you can also use the down arrow key). Or, maybe you just want to select a few results. Click the first one in the list you want, then holding the Ctrl key select the others.
Once you’ve selected the items you want, you can modify the items selected. One handy trick, especially if you’re interested in other data included on each row that you’ve selected, is to use the Fill option to highlight the cells a particular color. You can then use the Filter by Color option to just view the highlighted cells.
Lastly, and probably the least frequently used aspect of the Find function, is to use the Options button. When the Options are expanded, several additional search options are available.
You can expand your search from an individual Sheet to the entire Workbook, or change your search so that it goes By Rows (i.e., starts in Cell A1 then goes B1, C1, etc; then moves to the next row) or By Columns (i.e., starts in Cell A1 then goes A2, A3, etc; then moves to the next column). You can also narrow the search so that it’s only looking in Formulas, Values, or Comments.
Now to really supercharge the find function, use wildcards. Wildcards may be even less frequently used than the Options button, people just don’t think of them. Incorporating wildcards into your search can be a very necessary solution when looking for specific data. Here is a list:
| Wildcard | Description |
|---|---|
| * | Returns any combination of characters in place of “*” – for example, “1*6” would return “106”, “123456” or “s*t” would return “sit” and “spreadsheet” |
| ? | Returns any one character in place of “?” – for example “1?6” would return “106 or “126”, or “s?t” would return “sit” but not “spreadsheet” |
| ~* or ~? or ~~ | Adding the “~” allows you to search for a wildcard – for example, if looking for an asterisk (*) put a tilde (~) in front of the asterisk in the search |