Finding data is very useful when you are dealing with a lot of data on a worksheet. Find feature helps you to find data using different criteria, such as "Match case" and "Match entire cell contents". You can also find data inside cells as well as in the comments and formulas.
Look at the example below. We have there a few people who live in different countries and are of different ages. Each country has its own ISO number.
At the bottom of the table there is a "=SUM(E3:E8)" formula to calculate the sum of the years of all people in the table. There is also a comment inserted into the cell B3 (Austin's name) by Jack, which says "Oldest person".

Let's say you want to find people living in the USA. If you want to open "Find and Replace", be sure that you are in the "Home Tab" and then click "Find & Select" icon. The keyboard shortcut is "Ctrl-F".

In the "Find and Replace" window you can start searching for strings inside the worksheet.

As soon as you start clicking "Find Next" button you will notice that it searches not only inside ISO Codes but also inside Countries and Names: Austin, USA, us, USA, us, Australia,

Find Options

You need to use more advanced searching options to specify additional criteria. Click the "Options >>" button to show a few criteria you can choose from.

  • Within
    You can search data inside one sheet or entire workbook
  • Search
    Search data by columns or by rows
  • Look in
    You can search for data inside Formulas, Values and Comments. In our example in the cell E9 we have sum of the ages of all people in the example. The value is 156, and the formula used to calculate this value is "=SUM(E3:E8)". If you search for "SUM" you will find it only when you choose "Formulas". If you search for "156" you will find it if you choose "Values". If you choose "Comment" option you can search only in comments.
  • Match case
    If you have fields "USA" and "usa" inside your sheet, and you enter into a search field string "us", Excel will return both, if this option is unchecked and only "usa" if the option is checked.
  • Match entire cell contents
    Let's suppose you have four cells "USA", "usa", "US", "us". If you enter "us" into search field and check this option, Excel returns "us" and "US". If you want to return only "us", then check both: "Match case" and "Match entire cell contents".

In order to have a complete list of all search results, click button "Find All". Click one position from the list, and Excel will show you the location of the cell.
You can also search for data inside selected cells. You can select cells from B3 to B7 in order to search only for names.

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views