Excel Find Options
Recently I was looking through a spreadsheet to find the numbers that were in red text. Now, while that was rather visible, the spreadsheet was huge and there was a large number of rows between each red entry. While an earlier TechBits article showed how to sort by text color, that wasn't an option in this case since there were subtotals that would have been messed up by resorting the page.
Because I knew you could sort by color, I figured I could probably search by color too and I was right. There are a lot of extra Find options actually, you just don't see a number of them because they hide behind the Options button.
If you open the Find window in Excel and click on the Options button on the right hand side just above the Close button, you'll see a number of the Find options. The ones I've used most often have been:
- Match Case checkbox: Check this box and Find will only pick those entries that are entered in the same case as what you are searching. Normally searching for "Monroe" would get you "Monroe", "monroe" and "MONROE". If you check this box it will only get you "Monroe".
- Match entire cell contents checkbox: If you want just those entries that match what you're searching for and not match when what you're searching for is part of a larger entry. For example, if you're searching a list of titles to find the ones with a status of "Lost", a normal search would also match the titles like "The Lost Treasure" or "Lost in Space". Checking this box would only get items where "Lost" was the entire contents of the cell.
This time though I needed to search for a specific format. When you click on the Options button, the "Find what" box gets a couple of additions. The first is a format box. By default it will have "No Format Set". If you want it to search for a specific format, you need to use the Format button at the end of that line. There are two ways to choose a format to search.
- If you just click on the "Format" button it takes you to what looks like the regular format window. From here you can choose the format from the various tabs.
- There's an easier way if you already have a cell with the format you want to search for handy. Instead of having to find and select the format from the various tabs, you can click on the "Choose Format From Cell" button if you use the drop down arrow at the right of the "Format" button. This will change your cursor to a big plus sign with an eyedropper. Click on the cell with the format you wish to search for and that format gets automatically copied into the format box.
After that, just click Find All or Find Next to start your search.
Comments
You can follow this conversation by subscribing to the comment feed for this post.