Format values based on whether they appear in another column

05 Jul 2018
functions

How to use conditional formatting to highlight cells that appear in another list.

Introduction

You have a whole range of data (multiple columns and rows), and you wish to highlight cells based on whether they appear in another list (such as absent students)? No problems, custom conditional formatting is here to help!

Method

Let us assume that the list upon which you want to match is in column A of your sheet, and the data you want to highlight (where a duplicate is found), is in columns B-Z.

Select this range (columns B onwards), and apply a conditional format (Format -> Conditional formatting…). Select Custom Format from the ‘Format cells if…’ dropdown, and then enter the following formula (including the initial = symbol).

IF(MATCH(B1,$A$1:$A,0)>=0,TRUE,FALSE)

Then select the format you wish to apply to the cells, and those cells that match a value in your A column will be highlighted.

Easy!

Tagged Google, Sheet

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)