Excel/Excel Spreadsheet


I would like to change a cell's fill color based on the results of a formula in a different cell.

I have 12 columns (months) each has a numerical value, or "M", for missing data.  The 13th column contains the SUM formula to determine the annual total for the 12 months (cell O3). I am then, in a different column (Q3), using COUNTIF to determine how many months have missing data ("M"), then based on this value I would like to change the fill color of the cell containing the annual Total (O3) to say green (no missing data), yellow-green (1 Missing month), yellow (2 missing months), Orange (3 missing months) or red (4 or more missing months) based on the COUNTIF value. I can get the Q3 column to change colors using conditional formatting, but I can't figure out how to get it to apply to the O3 cell.

Sorry for the delay in replying - I've been away from my Office 2007 machine for a bit and needed to refer specifically to that for you - I'm assuming you ARE on office 2007 or later as otherwise you will be restricted to 4 levels of colour (3 conditions and the base format of the cell).  In 2007, on the home ribbon, use the conditional formatting option then new rule (make sure you are on cell O3 when you do this) - at the bottom of the list you have the option to go for a formula - this is the one you want - for the formula enter


(replace 4 with whichever value you want, and add the format you want)

OK this and repeat as many times as you need rules - you can then auto-fill this down.

I hope that this gives you the information you need but do let me know if I can help further.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 About.com. All rights reserved.