The Select by Value & Color add-in offers several tools to quickly select all similar records in an Excel worksheet according to your criteria. Here you will learn how to use the utility. Choose the task and get started:
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:
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.
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.
This tool lets you highlight cells by type and specify the range and conditions for the search.
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.
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.
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.
If you choose Numbers or Dates, the conditions to pick from are the following:
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.
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:
This way lets you quickly identify all cells that meet several conditions by using the tools one after the other.