Conditional Formatting and Custom Format – Use More than Three Colors

Conditional formatting (in pre Excel 2007 versions) had a limitation of only allowing for upto three conditions for formatting cells. A quick way to include more than three conditions when using conditional formatting is to use the custom format feature in tandem with it. Conditional formatting can be used to specify the first three conditions and then upto three additional conditions can be specified using custom format – giving us 6 different colors to work with.


Conditional Formatting to specify three conditions to color numbers

This part is easy. All we need to do is to open up the conditional formatting box and start specifying the conditions that we want to use and colors to go along with them. Just bear in mind that conditional formatting takes precedence over custom format when it comes to using colors. So if you end up specifying the same condition for both, the one that was specified in the conditional formatting will be used.


Custom Format to specify three additional conditions to color numbers

Custom format has the following format:


You can specify either one or more expressions (upto a maximum of 4) as a custom format for a cell. Each of the expression is a combination of a conditional statement (which when evaluating to true triggers the format) followed by the actual format to use when the condition evaluates to true. The first three expressions are reserved for numbers. If the fourth is not specified and is left blank, the application knows that the first three expressions are to be used for custom formatting numbers only.

So lets use the Custom format feature (ctrl+1) and write something like:



Well what does that do? By using this expression you are telling the application to custom format selected cells in the following manner –
– When the cell value is greater than -15, use the color whose value in the color index is 36
– When the cell value is greater than -40, use the color whose value in the color index is 40
– When the cell value is anything else, use the color whose value in the color index is 38

So that helps us take care of three colors. The flip side is that since custom format works only on numbers, you will not be able to format the cell fill color – which also means that we will only be able to format numbers.

conditional-formatting-custom-format-to-use-more-than-three-colorsOnce we have both of the steps completed, we are free to use upto 6 different colors for six different conditions. While the conditions specified in conditional formatting can be made dynamic by specifying formulas instead of absolute numbers, the conditions in the custom format are more or less static and that could be a bit of a drawback of using this approach.

You can download a sample worksheet with a example of conditional formatting with custom format to use more than three colors here or click on the button below:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Sara wrote:

    That’s a wonderful trick. Never thought of using the two together like this !

  2. smart defrag 6.0.1 key wrote:

    Blogs have building commenting system which
    allows readers to comment about your website. Spontaneous
    and raw ideas in blogging may also be a means of awakening a link of ideas which are not often brought out.
    Many successful bloggers have shared their secrets on the way to build a multi million dollar blogging empire.

  3. Fortnite HACK AIMBOT wrote:

    To promote your blog, you are able to post articles
    to directory websites that permit you to put a link at the end of each article in your blog.
    All of Apple’s new Macs come bundled with applications from Apple including
    i – Life, i – Tunes and the Safari browser.
    In 1998, I worked for starters in the major businesses that printed the Yellow Pages.

  4. xoracle download wrote:

    It’s enough for you to run most apps, as well as perform little bit of multi-tasking.
    The software offered are of every kind imaginable – from games along with
    other applications that help you are making essentially
    the most of one’s Mac. This articles gives you a three step simple process to secure a Free
    Mac – Book.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel