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

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

    Select Column C without header

  • Go to Home tab, Conditional formatting, New rule.

    New Rule in Conditional Formatting

    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

    Enter OR formula

  • Go to Format, select yellow color under Fill, and press OK.

    Select yellow color under Fill

    Select yellow color under Fill

  • All Food & Travelling items are marked yellow.
    Conditional formatting applied

    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

=WEEKDAY. Find the Day Number