Skip to main content
  • Login
Cart
Exceljet logo Exceljet
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
Cart
  • Login
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
  • Login

You are here

Home › Videos › How to search in formulas and values in Excel

How to search in formulas and values in Excel

Transcript 

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.

Master Excel with practice. Download this worksheet (and dozens more) in our Core Excel course. Learn by doing.

Dave Bruns Profile Picture

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Microsoft Most Valuable Professional Award  

Exceljet Training

  • Excel 101
  • Excel Fundamentals
  • Conditional Formatting
  • Essential Formulas
  • Advanced Formulas
  • Pivot Tables
  • Excel Tables
  • Excel Shortcuts
  • Excel Charts
  • Course Bundles
Thank you, this is an excellent site to get an understanding of all the Excel formulas. Especially for brushing up or just rechecking the formulas.
Priti
More Testimonials

Get Training

Quick, clean, and to the point training

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas
Exceljet logo Exceljet
Quick, clean, and to the point

Resources

  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles

About Us

  • About
  • Testimonials
  • Topics
  • Contact
  • Donate

Newsletter Sign-up

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
© 2012-2025 Exceljet. Terms of use
  • Visit our Twitter page