You have a list of expenses made during a holiday to the USA, and you want to highlight in yellow color – only the expenses made for Food and Travelling.
Conditional formatting with OR function can be used for this.
You could avoid using OR function, but then you would have to create 1 rule for each – food and travelling.
But if you had to include 5 things to be highlighted with the same color, you would have to create 5 different rules.
Rather, create 1 single rule for all together.
To understand conditional formatting basics, see this.
Logic
OR function enables us to mention all the conditions in a way that if any 1 of the conditions is true, conditional formatting will be applied.
Thus, OR (food, travelling) means if either “food” OR “travelling” is present in the cell, the conditional formatting will be applied.
Creating the OR formula inside Conditional formatting
Expenses Data
As you see that column C has the data of Category, we will that column while creating the OR formula within conditional formatting.
- Select the C column, without the header (do not select the 1st row).
Select Column C without header
- Go to Home tab, Conditional formatting, New rule.
New Rule in Conditional Formatting
- Select “Use a formula to determine which cells to format”.
- Enter the below formula.
=OR (C2=”food”, C2=”travelling”)
Enter OR formula
- Go to Format, select yellow color under Fill, and press OK.
Select yellow color under Fill
- All Food & Travelling items are marked yellow.
Conditional formatting applied
Understanding the OR formula
=OR (C2=”food”, C2=”travelling”)
- C2 is the cell from where all the entries of Categories are starting. Thus C2 is used to check both the conditions – food and travelling.
- Automatically, C2 becomes C3, C4 and so on, when Excel starts checking each of the value in column C, credits to relative referencing feature of Excel.
- Since both conditions are under OR, when either of them is true, conditional formatting will be applied.
- the ” ” are required as food and travelling are text values.
Keep Excel-ing!
If you found this interesting and helpful, read these too: –
Highlight unique values using Conditional formatting
Highlight WEEKENDS or WEEKDAYS using conditional Formatting
