We all know how to use ‘Find” to search out a particular value in worksheet.
Sometimes, it’s helpful to actually highlight the values Excel finds.
Take this example. Some goofball entered B for billions in a table of numbers. So you need to find and highlight all of those cells.
You first execute a normal Find operation, looking for any instance of the letter B. Be sure to click “Find All”.
While still in the dialog box, press Ctrl+A on the keyboard. This will not only select every found instance in the dialog box, but it will select/activate all instances on your spreadsheet.
Once all the cells are selected, you can apply a cell color to have it take effect on all the selected cells.
This technique comes in handy when you want to do something like highlight all the external links found in the cells of your workbook. Instead of looking for the letter ‘B’, you simply find all the instances of ‘['.
Why the open bracket? Because external references use brackets [ ] to enclose the source workbook. (Note that this won’t find the external links hidden away in Objects or Named Ranges)
RELATED STUFF
Good stuff! But it would be useful if you could indicate where something depends on a facility that’s only available in v2007 – as with ‘Find All’ in the Find dialog. In this particular case, could one not use conditional formatting with a Formula test?
OMG!
This is one of the things I was looking for since ages!
Great tip and great blog (even if I’m italian and bacon is NOT really my fav food! :p )
Roy: The ‘Find All’ button can be found in all versions of Excel. I suppose you could use conditional formatting to do something similar. But that would require me to think. Any when I have a reasonable alternative like this one, I choose not to think. Besides, I’ve got blog pages to fill man.
Bacon Bits is the Atkins Diet for Excel!
I’m like Francesco — these are the kinds of tips and secrets that I’ve been looking for for ages!
Keep the blogs coming!
Mike,
Found your blog yesterday. Love it! The tips you are posting solve those niggly little problems that keep coming up when we say “I wish Excel could do ….”. Can’t find help on some of those without knowing the exact terminology to search for – “niggly little problems” just doesn’t work in the search engine!
Thanks!
Will you be posting any tips for the 2003 version access?
Arlyn: My goal is to share tips that will apply to all versions of Excel and Access. If I do share something related to only one version, I’ll note that.
Awesome tip, especially since I know that goofball, he use to work in my office. These tips are very helpful, I hope you keep them coming.
Thanks so much for this great blog. I’m sure you’ll run out of cool tips/tricks eventually, but so far, I’m impressed….
I know exactly where I can use this tip. We get a monthly statement from our main supplier. Their reference numbers can appear in many different spots. Being able to quickly scan an entire sheet for yellow cells would make doing a “one off” search so much quicker.
Usually when I’m in that report, it’s because something has gone wrong. Maybe I should use red as my colour of choice in this case, since I’m already “seeing red”?
datapig: it’s early in the morning, and I might be dazed and confused, but there really isn’t a Find All button on the Find dialog in Excel 2000 (yes I do have 2007, it’s on my laptop). ???
Roy: You may have gotten me this time. I actually don’t have Office 2000, nor have I looked at it in five or so years. I believe you. I should amend my comment to say that these tips have been tested in Office 2003 and 2007. No guarantee with earlier version.
Good job you’re watching…now go buy yourself a pint!
very cool! I don’t know how to narrow down my search, though. For example, if I want to Find All “015″s and exlude anything else which contains “015″, can I do that? Thanks…
Christi: While in the find dialog, select Options. Then place a check next to ‘Match entire cell contents’. This will limit the search to cells that which ONLY contain 015.
This tip is really cool. I wish I had known it earlier. It’s just like hidden secrets waiting to be xplored in excel.
Monsieur Data Pig, you are a doll! I’d overlooked this! Thank you!!!!
Hello…back again! I found a way to use this in a daily report…but I wanted to record a macro for it. Unfortunately, it will not record the Find All/Alt+A (Select All) functions. Is there a way to incorporate these steps into a macro? All it does is record the color change on a given cell (which I was using as sort of a touch stone)…Thanks.
Christi: When Find All has to be done via VBA, it gets hairy and looses much of it’s coolness. See Andy Pope’s comments at this link: http://excelusergroup.org/forums/t/264.aspx
Great Bacon!
How can I make the COUNTIF function work if there are symbols (“<") in my list of numbers.
@RGquail,
Can you show us a sample of the kinds of value in your cells (by the way, what range are you looking to count?) including those with a < symbol in it and any non-numbers? Also, tell us what it is you are trying to count (numbers? < symbols? something else?).