All courses available in-class or via remote login

Excel table

Applying Conditional Formatting

June 05, 2017

Conditional Formatting in Excel Intermediate

I’ve been conducting Excel Courses for Excel Consulting for a couple of years now. Excel Intermediate is our most frequently offered course. In that course, Conditional Formatting seems to be the most favorite topic for most groups that I’ve come across. It’s easy to see why because:

  • It allows you to color code your data based on predefined conditions e.g. anything above $100,000, turns the cell green
  • It is dynamic i.e. even after defining your conditions, if the value changes, the colors will update automatically. In the above example, if the value fall below $100,000, the cell will no longer be green
  • It is an efficient way of Data Visualization as it uses color, cell background, font format etc. to convey additional meaning

 

I can go on and on regarding the benefits of Conditional Formatting but perhaps it deserves a separate blog. My focus in this blog is a specific way of using Conditional Formatting. It was an interesting question I got from a course participant:

How to apply Conditional Formatting on one column based on another?

Let me try to answer that using an example. Let’s take the following data set:



 

Problem Statement:

The objective is to highlight the car Model based on the Total values e.g. if the Total is above $100,000, we want the respective car Model cell to turn green. Here’s how we do it:

Step 1:

Highlight the car Modelsquarterly used vehicle sales excel

 

Step 2:

Go to Conditional Formatting -> New Rule -> Use a Formula to determine which cells to formatquarterly used vehicle sales excel

 

Step 3:

Type the following expression for the Formula: =I4>=100000 and then choose green Fill after clicking the Format button and then click OK. The Models above $100,000 ( 5 Series and X5) should turn green as a result.quarterly used vehicle sales excel

I hope it’s a useful tip for those of you who want to explore Conditional Formatting and how it could be used in a specialized way.

 

Danny (Danish)

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 21559 Reviews
Thank you for everything and sharing information for external sources. Wonderful Teacher -Power Apps Intermediate Brisbane
Natasha - Power Apps Intermediate, .
Great course, gave me lots of ideas on how to improve processes in my workplace. It also helped me move away from the SharePoint is the be all and end all mindset I have been in. Thank you! -Power Apps Intermediate Brisbane
Katie - Power Apps Intermediate, .
Excellent as always -Analysis & Dashboards Melbourne
Jonathan - Analysis & Dashboards, .
Thank you for all the hard work. You are a great Teacher and very helpful.. -Power Apps Beginner Perth
Natasha - Power Apps Beginner, .
great course!! -Power Apps Beginner Perth
Wayne - Power Apps Beginner, .
very informative, enjoyed course -Power Apps Beginner Perth
Shannon - Power Apps Beginner, .
Excellent fast paced training, I really enjoyed the training style. Jagg answered all questions and was very knowledgeable -SharePoint Site Owner Adelaide
Chevaun - SharePoint Site Owner, .
Good session and learnt lots -SharePoint Site Owner Adelaide
Melodee - SharePoint Site Owner, .
Great informative session -SharePoint Site Owner Adelaide
Mandy - SharePoint Site Owner, .
Very good presenter. Patient and knowledgeable, thanks! Good content for someone new to Sharepoint. -SharePoint Site Owner Adelaide
Tanya - SharePoint Site Owner, .
Session was very informative and all questions answered -SharePoint Site Owner Adelaide
Tim - SharePoint Site Owner, .
Bit fast paced for me but worth doing as an introduction to the Sharepoint platform -SharePoint Site Owner Adelaide
DAVID - SharePoint Site Owner, .
Comprehensive training -SharePoint Site Owner Adelaide
Victoria - SharePoint Site Owner, .
Productive day. Learned a lot about SharePoint (still another day to complete where more knowledge will be shared). Trainer spoke clearly, concisely and in a way that assists with learning -SharePoint Site Owner Adelaide
Natalie - SharePoint Site Owner, .
Jagg had to manage multiple competency levels and IT issues, but ensured everyone kept together so no-one left behind which was good. -SharePoint Site Owner Adelaide
Kelly - SharePoint Site Owner, .
Great overview. -Project Beginner Brisbane
James - Project Beginner, .
Really good with the fundamentals which is exactly what I was after -Project Beginner Brisbane
Shane - Project Beginner, .
Clear and concise delivery, easy to follow, good level for beginners. -Project Beginner Brisbane
Julia - Project Beginner, .
Very informative and helped with digging myself out of the holes that i kept finding myself in. -Project Beginner Brisbane
Luke - Project Beginner, .
Excellent teaching, Awesome course -Project Beginner Brisbane
Justin - Project Beginner, .

Trusted by Business

Procured by Government

Map of Australia