Excel Search Tip #2: Set Search Criteria
When you are searching in Excel, sometimes the data you are looking for may not be in the sheet you are currently in. Sometimes you are looking for cells with only the data you are searching for and nothing else. Other times you might want to match the case of the search criteria.
When using Excel Search by hitting CTRL+F, click the Options button below the search box. This will open a number of search options available to you to help filter out unwanted results, or expand to find all the results you’re looking for.
- Within: This option lets you select whether or not you want to search within the active sheet, or within the entire workbook. If you are unsure which worksheet the data you are looking for is in, you can select “Workbook” to allow Excel search to look throughout the entire file. the entire file.
- Search: This option allows you to search by rows or by columns. This sets the direction that you want to search, but does not limit the results just to the individual active row or column. If you want to search down through columns, select By Columns. If you want to search right across by rows, select By Rows. Bonus: Hold down the SHIFT key while clicking Find Next to search up columns, or left across rows (reverses direction of the search).
- Look In: Determines whether or not to return results in a formula or the cell value. For example if you are searching for the number 11, a cell with the formula =11+2 would appear in the search results if the “Formulas” option was selected. On the other hand a cell with the formula =10+1 would show up in the search results if the “Values” option was selected.
- Match Case: If you are searching for a string written with specific capitalized or lowercase letters, select the Match Case box. With this option selected, searching for Company will only return cells that contain the word Company. Cells with the word company will not be returned in the results because the capitalization does not match.
- Match Entire Cell Contents: Searches and returns an exact match of characters specified in the search criteria.
- Format: Allows you to search for cells with a particular format. For example, if you want to find any cells with a Yellow background, click Format, click the Fill tab, click the Yellow background color option and click OK. If you only want to find all cells with a particular format regardless of that cell’s value you can leave the “Find What” search criteria field blank. Otherwise if you want to find all cells with a particular format and that contain a specific value, enter the value in the “Find What” search field and select the criteria you are looking for. In addition to background colors, you can search for cells with a specific number format, alignment, font variations, borders, etc.