## Highlight Rows of Data in Excel with Conditional Formatting Formulas

In this post I will explain how to format certain rows in Excel with formatting that matches a condition set you a formula.

First lets study the sample dataset in the screenshot below.

Observe that the first row of data is row 2 because we have a header row. So that means I will need to start off selecting the dataset with row 2. Then I will choose the conditional formatting button from the home tab and click new rule. When the new rule dialog box displays, I will click “Use a formula to determine which cells to format”.

Lets say that I want to highlight an orange row for all records that sales were sold in a “store”. Column C has either “store” or “online”. It will be important to write the formula using the first row of data that references the cell of that row. The formula will be =\$C2=”store”. The reason for the dollar sign in front of column C is that is will absolutely reference column C. I do not want a dollar sign in front of row 2 because I want that formula to go down relative to all the other rows till the end of my dataset. Not certain you understand the difference between \$A\$1 and A1 then check out my video post explaining when you use absolute vs relative.

Pick a format fill and then click ok.

Here is what I get. All the rows containing “store” will show up in orange.:)

Let me do another example

Ok in this next lesson I will format all the rows that are Fridays. I can format the dates to long date format just to peek at the days of the week to help the screenshots.

First I will select the dataset and then click the conditional formatting command to add a new rule. I will use the WEEKDAY() function which results in a value 1 to 7.

Sunday=1

Monday = 2

Tuesday =3

Wednesday =4

Thursday=5

Friday=6

Saturday=7

My formula will be =WEEKDAY(\$A2)=6.

And my result is that all rows will turn green when the date is a Friday.

For more cool things you can do with dates use the DATEDIF formula. Details here.