How to select cells by value or color

How to select cells with a certain value

To see all cells with identical records in your table, select a cell with the value you're looking for, go to the Ablebits Tools tab > Search group, and click Select by Value / Color > All Cells with the Same Value:

You'll instantly get a confirmation with the number of found cells and see them highlighted in your worksheet:

How to spot cells with the same background color

Select a cell with the necessary fill color, go to Select by Value / Color and pick the second option, All Cells with the Same Background, to use color as a condition for highlighting cells in the data range:

This will select all cells with the same fill color and display a notification with the selected cells count.

How to identify cells with the same font color

There is also a one-step tool for highlighting cells with similar font formatting. Pick a cell with the necessary font color and choose the third option in the Select By Value / Color list: All Cells with the Same Font Color:

All identical cells will be selected in your table, so you can modify their color or perform any action you need.

How to find special cells

This tool lets you highlight cells by type and specify the range and conditions for the search.

1. On the Ablebits Tools tab, in the Search group, click Select by Value / Color > Select Special Cells:
   
2. You will see the Select Special Cells pane:
   

   By default, the add-in highlights the entire data range as the search area. You can edit the range in the add-in pane manually or pick it in your Excel worksheet. Also, you can use the Expand selection icon (blue in the screenshot above) to get the whole table selected automatically.

3. Use the Select cells containing dropdown list to specify the type of cells to be highlighted:
   

   Some options are plain and simple: you can select all comments, blanks, or objects (e.g. images and charts) in your spreadsheet. Or highlight all cells that have conditional formatting applied by picking the Conditional formats option.

   Constants and Formulas have advanced options and you can check all those you need:
   
   The options let you select all cells with constants that are Text, Numbers, Logicals (True/False), and Errors, or all the cells with formulas that return Text, Numbers, Logicals, or Errors.

4. Tick all the options and press Select. The add-in will select the found data and you will see a confirmation message with the number of highlighted cells.

How to select cells with the specified values

1. On the Ablebits Tools tab, in the Search group, click Select by Value / Color > Select by Value:
   
2. The Select by Value pane will appear:
   

   The add-in will scan the entire data range by default. You can limit the range in your sheet or use the field at the top of the pane to modify the range for the search. Also, you can use the Expand selection icon (blue in the screenshot above) to get the whole table highlighted automatically.

3. Choose from three kinds of values you are looking for: Numbers, Text, or Dates.
4. Use the Select cells that are dropdown list and the fields below to specify conditions:
   

   If you choose Numbers or Dates, the conditions to pick from are the following:

   • If you want to find certain numbers, dates, or values, pick the equal condition.
   • To select all records except the one you enter, choose the not equal option.
   • Identify all values that are above or below a certain level by using the greater or less conditions.
   • Pick In the range or Out of the range criterion to limit the top and bottom values for the selection or keep some values intact.
   • Find the minimum or maximum records by choosing the respective maximum or minimum option.
   • Retrieve unique values by selecting unique.

   If you are looking for Text values, the list of conditions to choose from is even longer:
   

   Here you can also select those cells with text that begin/end or contain/do not contain certain values.

5. Enter the corresponding value or range or click the up and down arrow to change it. If you work with text and numbers, you can enter them right into the field below the criterion. If you work with dates, click on the field to pick one from the drop-down calendar or change it manually:
   
6. Click Select to let the add-in perform an advanced selection of cells according to your criteria.

How to perform complex search

With the Select by Value & Color add-in, you can highlight cells by three main condition groups: Type, Value, or Format. However, you can narrow the selection by applying the conditions section by section.

Thus, if you have a complex task like "select cells with a green background containing formulas that return True or False values, you can split the conditions into groups and apply them one after the other:

1. Select a cell with the green background in your table and run All Cells with the Same Background to instantly select all similar ones.
2. Keeping the first selection, run the Select Special Cells tool, pick formulas with logicals, and click Select to apply the second level of conditions to the previously highlighted cells.

This way lets you quickly identify all cells that meet several conditions by using the tools one after the other.