Course summary: Use conditional formatting

Find duplicate values fast

Finding duplicate values can be time consuming and frustrating. Leave the stress behind by applying conditional formatting and letting Excel do the work for you.

  1. Select the range of cells you think might contain duplicates.

  2. Click Home> Conditional Formatting> Highlight Cells Rules> Duplicate Values.

  3. In the Duplicate Values box, make sure that Duplicate is selected in the first drop-down box.

  4. Then select the format for duplicate values in the other drop-down box. Click OK. All the duplicate numbers or text are highlighted.

Apply conditional formatting based on text in a cell

  1. Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.

  2. Click Home> Conditional Formatting> Highlight Cells Rules> Text that Contains. In the Text that Containsbox, on the left, enter the text you want highlighted.

  3. Select the color format for the text, and click OK.

Copy conditional formatting

If you have a conditional formatting rule you want to use for new data, copy the conditional formatting to the new data using the Format Painter.

  1. Click a cell that has the conditional formatting you want to copy.

  2. Click Home > Format Painter. The pointer changes to a paintbrush. Double-click Format Painter if you want to copy the formatting to multiple selections.

  3. To copy the formatting, drag the mouse pointer across the cells or ranges of cells that you want to format.

  4. To stop the formatting, press Esc.

See also

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.