Look Who it Was Random video

Excel - Conditional Formatting for 4 Rules & Color Column A Based on Column D Values - Episode 635

617 views · Published 26 March 2009 · 3:10 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Conditional Formatting for 4 Rules & Color Column A Based on Column D Values.

Welcome back to the MrExcel netcast. In today's video, we will be discussing a topic that I rarely bring up - conditional formatting. Up until Excel 2007, it was a feature that was difficult to use. However, a question came in and I thought it was worth addressing. The question was, how can we color Column A based on the values in Column D, when there are four different values in Column D and conditional formatting can only handle three of them? Well, in this video, I will show you a workaround to achieve this.

First, let's take a look at the data set we will be working with. We have four different values in Column D and we want to color Column A based on these values. The first thing I'm going to do is select all of Column A and choose an orange color as the default. This means that if none of the other conditions are met, the cells in Column A will be colored orange. Next, we will select all of the cells in Column A and go to Format, Conditional Formatting. Normally, we would use the "cell value is equal to" option, but in this case, we want to use the "formula is" option.

Now, we need to write a formula that will work for the first cell in Column A. To do this, we will use the formula =$D2, which will freeze the column reference to D, but allow it to move as we copy the formula down to the other cells. Next, we will specify the condition for this formula, which in this case is "equals 1". Then, we will choose the color red for this condition. We will repeat this process for the other two conditions, using the formula =$D2=2 for the second condition and =$D2=3 for the third condition. Finally, we will click "Add" and use the formula =$D2=4 for the fourth condition, and choose the color orange. This is essentially tricking Excel into giving us four conditional formats.

One thing to note is that most people are not aware that conditional formatting can be based on a value in another cell. This is because you have to change the "cell value is" drop-down to "formula is". Additionally, in Excel 2003 and before, we were limited to only three conditions. However, by using this workaround, we can achieve a fourth condition. So, there you have it - a way to use conditional formatting to color cells based on values in another column. I hope you found this tip helpful. Thanks for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Todays question deals with conditional formatting. How can you have four rules? How can you have the color of column A be based on values in Column D? Episode 635 answers all.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction to Conditional Formatting
(00:21) Addressing a common question about coloring cells based on values in another column
(00:42) Using a workaround for having four different values in the data set
(01:04) Using the "Formula Is" option in Conditional Formatting
(01:28) Applying different colors for each value
(02:02) Additional tips and tricks for using Conditional Formatting
(02:50) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding multiple conditions in Conditional Formatting
Changing cell color based on specific values in Excel
Coloring cells based on values in another column
Conditional formatting in Excel
Excel 2007 Conditional Formatting
Handling four different values in Conditional Formatting
Trick to have four Conditional Formats in Excel
Using Formula Is in Conditional Formatting
Using green color for specific values in Conditional Formatting
Using orange color as default in Conditional Formatting
Using red color for specific values in Conditional Formatting
Using yellow color for specific values in Conditional Formatting


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152295/

More from this channel