How to search in formulas and values in Excel

In this lesson, we'll look at the difference between finding what is stored in a cell and finding what is displayed in a cell.

Let's take a look.

When you use Find, there are three options for the "look in" setting on the Find tab: formulas, values, and comments. Comments are self-explanatory, so let's focus on formulas and values. You can think of formulas as whatever is stored in the cell—the underlying formula—and values as whatever is displayed in the cell.

The naming is a little confusing, so let's take a look at some examples.

Excel's default setting for "look in" is formulas, and this makes sense in most cases.  For example, we can easily search for and find names like Cecil...or Hana.  Or, we can search for a certain order number, like 23464.  But watch what happens when we search for $19.89, or the month March, abbreviated "Mar."

In both cases, Excel finds no match, even though we can see the values in the table. This is because Excel is searching formulas—what is stored in the cell—and not the values as displayed.

If we look at the Tax column, we can see this is actually a formula. So $19.89 is not actually stored in the cell G11.

If we look in the Date column, we can see these dates are stored in day/month/year format without the month name. In other words, what is displayed in the Date and Tax columns is different from what is being stored. To search for what is displayed, we need to change the settings for "look in" from Formulas to Values.

Now Excel will search values as displayed. We can find $19.89 and dates in March without any problem.