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.

 

 

 

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website at www.sequentiasolutions.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