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.
Monday = 2
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.