Blog featured image find values

The problem

Have you ever had trouble trying to find where data is located in Excel? When you are pretty sure 100% positive the value you are searching for is there somewhere!?

You may have a lot of data on the sheet, so you look around the sheet for a while, then you use the standard Find function via Ctrl+F.

Unfortunately and out of character, the Find function fails to locate the data you’re after. This will have left many of us scratching our heads at times. The good news is there is a simple solution that is easily missed.

Before I explain the solution we need to understand the real problem, so here is the why:

  1. It may be that the data you wish to find is actually the result of a formula.
  2. It may be that the data is actually in a different sheet altogether.

Solution

So here is an example of a very simplistic spreadsheet:

Find data basic spreadsheet

What we want to find is the number 24.

But what we need to keep in mind is if we switch to formula view (Ctrl+` )[the latter is the key top left of most keyboards, usually underneath Esc] Excel shows this:

Find data formulas

As we can see above, the number 24 is created by a formula, and here lies the problem. So when we do a Ctrl+F to find 24 we get this warning:

Find error

Let’s take a minute to digest what the warning message actually says, specifically “click Options for more ways to search.” So let’s do just that and click Options:

Find Options

Now this opens up more dropdown selections and checkboxes to control the way in which we wish to search:

Find Values

If we then switch the “Look in” section to Values this will look at the result, rather than the formula. When we click to Find Next it will go straight to the cell.

Find result

Exceptionally easy I’m sure you’ll agree.

You can even try it on text. In the examples given above you can try finding the word “Text” both looking in Values and Formulas and see the difference in results. Look in: Values will pick up both cells A1 and A2 as Values, effectively meaning the resultant value of the cell, where as Formulas will only pick up A1 as it looks at the formula itself before it is calculated.

Other uses of the Find Options:

  • One of my personal favourites is the option – Within. This defaults to Sheet i.e. the current active sheet of the file you are in. However, you can switch this to Workbook so that Excel will find the data anywhere in the active Workbook (file). A real time saver considering without this you would be running the Find function on each sheet one at a time, until you find what you’re looking for.
  • Look in also allows you to search Comments.
  • Matching case checkbox can also be handy if you want to cleanse your data.

Well that’s all for this blog I hope you’ve enjoyed reading. If so, please do leave a comment below and share with friends via the social share buttons.

If you haven’t already, please signup to the newsletter. Only subscribers will receive the free and exclusive courses from us here at Exceptional Excel.