To visually highlight table cells that have something in common - fully or partially coinciding values, the same font, background, etc. - in Microsoft Office Excel there is an option called "Conditional Formatting". It allows the user to indicate to the spreadsheet editor which matches should be identified in the cells, how strictly the identity check should be carried out, how to highlight matches and other parameters of this operation.
It is necessary
Tabular editor Microsoft Office Excel 2007 or 2010
Instructions
Step 1
Start Excel and load the desired table into it. Highlight the range of cells in which to search for matches - it can be a single column or row, a group of rows / columns, some region in a table, or even several unrelated regions.
Step 2
Expand the Conditional Formatting drop-down list in the Styles group of commands on the Home tab of the Excel menu. Go to the Cell Selection Rules section and select Duplicate Values. As a result, a dialog box will open in which you need to fill in only two fields.
Step 3
If you want to mark all cells of the selected area, the contents of which are repeated in it at least once, leave the default value “repeated” in the left drop-down list. There are only two options in this list, the second - "unique" - allows you to highlight cells that do not have duplicates.
Step 4
In the right drop-down list, select one of the options for highlighting the found cells. It contains six options for filling the background, changing the font color and borders of the found table elements. If none of the options suits the design of your table, select the bottom item - "Custom format" - and use the settings window that opens to design your style of the matching cells.
Step 5
Click OK and Excel will mark the matching cells according to the parameters you specified.
Step 6
The described method allows you to select all cells that have duplicates, but the conditional formatting option can be used in another way. If you need to highlight in the table only duplicates containing a specific value, then in the "Cell selection rules" section of the "Conditional formatting" drop-down list, select another item - "Equal". After opening the dialog, click on the cell, all duplicates of which you want to identify, and its address will appear in the left field. The rest of the actions in this case will not differ from those already described. If it is necessary to highlight in a different color matching cells with a different value, repeat this action, specifying the address of another cell.
