Highlighting duplicates over multiple rows using conditional formatting

Question

I'm trying to create a Google spreadsheet to organise a seating plan. I've laid out the page with 11 separated smaller tables in a kind of grid format (for easier reading, as you can see it all without scrolling). Like this:

      Table 1       |      Table 2       |      Table 3       |
 Seat | Name | Diet | Seat | Name | Diet | Seat | Name | Diet |
    1 |      |      |    1 |      |      |    1 |      |      |
    2 |      |      |    2 |      |      |    2 |      |      |

I'm trying to create a conditional format to highlight cells where a name appears more than once.

So I created a conditional format with a custom formula as follows (the range covers the all tables):

COUNTIF($A$1:$O$42, A1)>1;

and I set the range to only the Name cells on the page.

However when I purposely set a duplicate the cells are not highlighted.

I thought that maybe my formula was wrong, so I put into a cell and pointed it at a duplicate cell and it returned TRUE.

So I'm at a loss a how to get it working.

miken32 · Accepted Answer · 2025-06-06 21:27:57Z

1

Try this formula applied to range A3:O

=AND(ISTEXT(A3),COUNTIF($A$3:$O$42, A3)>1)

Example sheet is here: https://docs.google.com/spreadsheets/d/1rRbZorcbw5IZoTTNCZUTHGSyW18gHxeFF1O6lTaE35A/edit?usp=sharing

edited Jun 6 at 21:27

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Aug 25, 2015 at 13:46

Chris Hick

3,0961 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RedXian Over a year ago

Thanks. This worked for me, however, selecting the whole page also highlighted cells in the Diet column as well as all the table headers. When I tried to apply the conditional formatting to only the Name cells I got an Invalid Formula error. Why would changing the range do that? As it stands, I've had to leave it applied to the whole page and create a second conditional format that removes the highlight on the diet column and table headers

Chris Hick Over a year ago

I've changed the range on the formula sheet back to A:O and used the formula =AND(ISTEXT(A1),arrayformula(A1<>{"seat","Name","Diet"}),COUNTIF($A$1:$O$42, A1)>1)

Chris Hick Over a year ago

I think the invalid error you were getting is because my previous formula started at row 3 (to avoid the values in row 2) so cannot be applied to a whole column starting at row 1. The revised formula caters for the values in row 2 by using an arrayformula instead though.

Collectives™ on Stack Overflow

Highlighting duplicates over multiple rows using conditional formatting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related