How to search a value in all cells

Finding a value in excel may be necessary for excel at some point but now the problem comes in when the worksheet you have is large let us say an enterprise. If you decided to do it manually it will not only be tiresome but also time-consuming. This guide will give you a quick and easy way to find data in excel.

Using FIND function

The position of a specific character or substring within a text string can be returned by using the Find function in excel. The syntax used is

FIND(find_text, within_text, [Start_num] )

The 2 arguments at the start are required while the last one is optional.

find_text– the desired character or substring to be found.

within_text-The range within which the search is done. It is usually given information of cell reference but the string can be typed directly in the formula.

Start_num– this is an optional argument that specifies from which character the search begins. If it is not included the search starts from the 1st character of the within-text string.

If the find_text character is not found by the Find function a #VALUE! Error is given as feedback.

Using Go To

Go To command can easily be used to quickly find cells that contain a specific type of data such as formulas and select them. The Go-To tool may also be used to identify cells that only satisfy particular criteria, such as the final cells in a spreadsheet with data or formatting.

To use the Go tool to search values in a cell

1. Start by doing either of the following;

  • To search through all the worksheets for a specific cell, click on the cell.

 

  • Otherwise, if you want to search for a specific cell within a specific area, you will need to select the row, column, or range of your choice.

 

2. On the Home tab, in the Editing group, click on Find & Select and the Go-To. You can also press the keyboard shortcut key combination Ctrl + G.

 

3. Once you have this click on special.

4. A Go-To Special dialog box will appear. Click on one of the options below depending on what you desire.

 

 

Click To select
Comments Cells that have comments.
Constants Cells that have constants.
Formulas Cells containing formulas and their type are below.
Blanks Blank cells.
Current region An entire list.
Current array An entire array
Objects Graphical objects
Row differences All cells in a specified row that differ from the active cell.
Column differences In a given column, all cells vary from the active cell.
Precedents Cells in the active cell are referenced by the formula.
Dependents
  • To find only cells that are referenced directly by formulas click Direct only.
  • To find all cells that are referenced directly or indirectly by the cells in the selection click All levels
Last cell The last cell on the worksheet that contains information
Visible cells only Visible in a range including concealed rows or columns.
Conditional formats Cells that solely have conditional formats are applied.
Data validation
  • To search for all cells that have conditional formats applied, click All
  • To seek out cells that have constant conditional formats because the presently elect cell, click Same