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.

data

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.

rules manager

Pick a format fill and then click ok.

rules managere

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

excel conditional formatting rows with 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.

fridays

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.

Friday = 6

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

friday rows green

 

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

Download my Excel keyboard shortcuts or Income and Expenses budget.

 

 

 

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at 5dollardinners.com.
This entry was posted in Excel and tagged . Bookmark the permalink.

One Response to Highlight Rows of Data in Excel with Conditional Formatting Formulas

  1. Pingback: SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s