Today I got an interesting question from a customer using Excel for Mac 2011. She wanted to know if she could use Conditional Formatting to color a cell based on the contents of another cell.
In her case, she has notes about employees written in a cell, and wanted to call attention to their salary if there were any notes for that person. To do this,
- Click on the first salary cell that you would color if the row has a note.
- Click on Conditional Formatting.
- Choose New Rule.
- Drop down the Style option and change it to Classic.
- Drop down the next option and select Use a formula to determine which cells to format.
- Enter the formula as =(N3<>””), with N3 being the first cell with a potential memo in it. This tells Excel to use the formatting if the cell does not equal null (is not empty).
- Edit the formatting to your liking (text color, fill color).
- Close the dialog box.
- Click back on the cell.
- Use the AutoFill Handle (the little square in the bottom right corner of the cell outline) to replicate the cell down the entire column.
- Any of the cells that have notes will now turn colors.
Using Conditional Formatting to automatically highlight cells based on criteria is a powerful way to create dynamic spreadsheets!
Need to learn Excel quickly? Check out Alicia’s online course, Learn Excel in 3 Hours Flat. For only $59 you get lifetime access to the course.
I am trying to format the cells in the classic style formating the cells using a formula. The format I am using is =$A$2=”N” format with: light red fill with dark red text. Is there a way I can copy and paste my formula on each row in the spreadsheet without having to change the selected cell each time? I have tried ctrl + D and it does not work.
Hello and thank you for your tip.
I’m using Excel 2019 for Mac and wondering if you can help me.
Here you are talking about coloring a cell based on a different cell’s value.
I would like to do the same, but instead of a single character/value as the criteria, a character or value within a string.
IE, to color A2 if A1 contains the word “example” (when A1 contains a phrase like “For example, 4×4”).
I have some ideas but none of them worked so far. :-))))
Thanks in advance
Saul
Hi Saul, Yes, you can! In Conditional Formatting, go down to the bottom to create a New Rule. Make the Style “Classic.” Choose Format Only Cells That Contain, then Specific Text, then Containing. type in your string! That should do it.
Can I set a value of a cell based on text value of another cell with the same background color?
If you google it, I saw someone combine an IF statement with a Macro that assigns numerical values to cell fills. But there’s no native functions to do that.
If I wanted to edit a bank statement and color the amounts according to whether they are credit or debet (which is the next column). How could I do that?
Dragging the Autofill on the first amount left me with a column of the same amounts…
When you use the Autofill Handle, it will overwrite the content BUT immediately click on the little Smarttag that appears. It will have an option to “Copy Formatting Only”. When you click on it, you’ll get your numbers back, and just get the Conditional Formatting to change the colors.
Use the standard logical formulas and nest the conditions inside: =and(I5>35,L5<>“”)
(Hours are greater than 35 and there’s something in L5).
In case we wish to apply multiple conditions, then how does one use logical operators like “or”, “and” and “Not” ?