I've highlighted my duplicate text in column B, using Conditional Formatting. No I want to highlight the same field on column A (and afterwards delete all non highlighted texts), but I am not sure how to do it.

At this time, my excel file is like this:


and the expected result after this task is to have it like this one:

my goal

  • Just select your table and filter your column B (By color), once done delete the rows. – Scorpion99 Dec 1 '18 at 21:08
  • im not sure if this will end up matching my goal. Im on excel 2016 if it helps btw! – John Greco Dec 1 '18 at 21:20
  • My goal is to have all entries of the same row of the column A hightlighted aswell – John Greco Dec 1 '18 at 21:30
  • Ths can be done using a VBA code – Scorpion99 Dec 1 '18 at 21:40
  • Can you please share this code? – John Greco Dec 1 '18 at 21:42

In this example the sample data is in Cells D2:E7. Now you have already applied Conditional Formatting to Column E based on Duplicate Values. Make a note of the color or select a color from Custom Format.

Now in D2 put the following Conditional Formatting Rule. You have to select Formula this time.


Apply the same color that you opted earlier for Column E. Click OK and the using Format Painter apply the Formatting to rest of the cells in Column D.

enter image description here

Now your matching values shall be highlighted.

enter image description here

Now put a Filter on First Row. If you have a header select it else the first row of the table. Better to have a Header row to your data.

Now Filter by Color and No Fill. So all non duplicates will be shown and Duplicates shall be filtered. Select the rows and Delete.

enter image description here

enter image description here

Update: How to use Format Painter

After you have applied the Conditional Formatting Formula Rule to A1, click on A1 and go to Format Painter on Home Tab

Click the format painter. Now it will be attached to the cursor.

Now Click and Hold on A2 and just Drag it down up to the intended rows. The Formatting shall get applied to all applicable cells.

enter image description here

See the below sample animation on how to use Format Painter.

enter image description here

  • Thanks for the detailed answer. However, I've added =COUNTIF($B$1:$B$50,B1)>1 to a new rule and I ended up in an error "there is a problem with this forula" – John Greco Dec 1 '18 at 21:54
  • May be the List Separator in your Regional Settings is ; instead of , i.e. Semicolon instead of Comma. Try using ; therein – patkim Dec 1 '18 at 21:55
  • FYR... Windows --> Control Panel --> Region and Language --> Additional Setting --> List Separator. You have to change the formula to =COUNTIF($B$1:$B$50;B1)>1 in that case. – patkim Dec 1 '18 at 21:58
  • It worked now with no errors, but cells of A column are not hightlighted. – John Greco Dec 1 '18 at 22:19
  • Did you apply this rule first to only A1 and then used the Format Painter to apply it to all other applicable cells of Column A ? – patkim Dec 1 '18 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.