The Select by Value & Color add-in offers several tools to quickly highlight 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:
When you want to quickly see all cells with identical records in your table, select a cell with the value you're looking for, click the Select by Value / Color icon on the Excel's toolbar (the Ablebits Tools tab, Search group) and pick the first option in the list: 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:
You will see a confirmation with the number of identical cells.
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.
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 simultaneously by using the tools one after the other.