Color numbers based on the value result in Microsoft Excel





There are two ways to color numbers according to criteria:

  • Custom formatting for a number with criteria
  • Conditional formatting

Using custom formats for numbers with criteria
Your options for coloring a number are not limited to displaying negative numbers red. You can color positive and/or negative numbers in any color you wish (do not get excited – the number of colors is limited to eight, and most are difficult to read). Do this by adding the name of the color to the number format in brackets.

Example: [BLUE] #,##0 ;[RED](#,##0)

A positive number is displayed in blue; a negative number is displayed in red; and 0 is displayed in blue (since there is no third section, 0 receives the format of the positive number by default).

Add a condition to the formatting, and have each section be displayed in a different color: [BLUE] [>5000]#,##0 ;[RED](#,##0); #,##0 Screenshot // Color numbers based on the value result in Microsoft Excel
Color numbers based on the value result in Microsoft Excel



9 thoughts on “Color numbers based on the value result in Microsoft Excel

  1. “Hi,

    A friend of mine is working on a spreadsheet. She is inputting contact information and in one particular column she is adding phone numbers. In that column, she added an email address and it automatically turned into a link (it was underlined and the type turned red). Now when she attempts to type a phone number in that same column, different cell, it turns red and is underlined. What is happening here and how can she eliminate whatever formatting is occurring????
    Thanks!”

  2. “Hi Gayle,

    Select the cell, click on Format – Cells.

    Change it to General or whatever format you prefer.

    Alternatively, use the format painter icon to copy the format of another phone number that is already how you want it to look.

    HTH,

    Alan.”

  3. “Hey Alan,

    Thanks, but we already tried that and it didnt work. Eversince she put the email address into the column, everything now in that column turns red and is highlighted as if it were a link.

    Gayle”

  4. Gayle, clieck on the cell that has the hyperlink (red and underlined) , it will go to the web site . GO back to that cell. Then on the menu bar , go to Insert, Hyperlink. In the window that opens up , go to the bottom left where there is an option “Remove Link”, select that. Your cell will not have that hyperlink anymore.

  5. Gayle, click on the cell that has the hyperlink (red and underlined) , it will go to the web site . GO back to that cell. Then on the menu bar , go to Insert, Hyperlink. In the window that opens up , go to the bottom left where there is an option “Remove Link”, select that. Your cell will not have that hyperlink anymore.

  6. A friend of mine is working on a spreadsheet. She is inputting contact information and in one particular column she is adding phone numbers. In that column, she added an email address and it automatically turned into a link (it was underlined and the type turned red). Now when she attempts to type a phone number in that same column, different cell, it turns red and is underlined. What is happening here and how can she eliminate whatever formatting is occurring???? 

  7. “Select the cell, click on Format – Cells.

    Change it to General or whatever format you prefer.

    Alternatively, use the format painter icon to copy the format of another phone number that is already how you want it to look. “

  8. Thanks, but we already tried that and it didnt work. Eversince she put the email address into the column, everything now in that column turns red and is highlighted as if it were a link.

  9. Gayle, clieck on the cell that has the hyperlink (red and underlined) , it will go to the web site . GO back to that cell. Then on the menu bar , go to Insert, Hyperlink. In the window that opens up , go to the bottom left where there is an option “Remove Link”, select that. Your cell will not have that hyperlink anymore.

Leave a Reply

Your email address will not be published. Required fields are marked *


8 − = three

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>