How to select cells by value or color

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:

How to highlight cells with the selected value

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:
How to find similar cells.

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

How to spot cells with the selected background

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:
Select cells with the same background color.

You will see a confirmation with the number of identical cells.

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:
Find Excel cells by 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. Find the Select by Value / Color icon on the Ablebits Tools tab in the Search group and click on the Select Special Cells option:
    How to select special cells in Excel.
  2. You will see the Select Special Cells pane:
    Highlight special cells in Excel.

    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 drop-down list to specify the type of cells to be highlighted:
    Find Excel comments, constants, formulas, blanks, objects.

    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.

    Tip. Constants and Formulas have advanced options and you can check all those you need:
    Select cells by type in Excel.
    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 TextNumbersLogicals, 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. Find the Ablebits Tools tab, Search group, Select by Value / Color icon. Click on it and choose Select by Value:
    Start Select by Value.
  2. The Select by Value pane will appear:
    Select values or dates that meet your criteria.

    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 drop-down list and the fields below to specify conditions:
    Select cells that meet a certain condition.

    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:
    Select text in Excel by conditions.

    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:
    Pick dates from the drop-down calendar.
  6. Click Select to let the add-in create an advanced selection of cells according to your criteria.

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 simultaneously by using the tools one after the other.

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.