Conditional Formatting Updates in Excel 2010

If you’re upgrading to Excel 2010, you’ve probably noticed that the Conditional Formatting feature has changed quite a bit.

Now you can apply more than 3 rules, and there are fancy features like data bars and icon sets.

Conditional Formatting Icon Sets in Excel 2010
Conditional Formatting Icon Sets in Excel 2010

Intro to Conditional Formatting

On my Contextures site, I’ve updated my Intro to Conditional Formatting page, to show Excel 2010 instructions. I’ve also  made a new video to show the steps n the new version of Excel.

Note: If you’re looking for Excel 2003 instructions, they’ve been moved to this page: Excel 2003 instructions.

Video: Excel Conditional Formatting Rules

To see the steps for creating two conditional formatting rules in Excel 2010, you can watch this short video.

I show how to add conditional formatting rules that colour a cell, to make the high and low values stand out on an Excel worksheet.

You can enter the minimum and maximum values on the worksheet, and edit them there, to make the conditional formatting settings easy to adjust later.

Video: Sort Based on Conditional Format Icon

When you are working with lists in Excel, use the built-in Table feature, to enable sort and filter commands, and other powerful features.

In the table, you can use drop down arrows in the heading cells, to sort and filter the data

If you add conditional formatting icons, or if you color the cell or the font, you can also sort and filter by those colors.

Watch this short video to see the steps for adding cell icons, and sorting by the selected cell’s icon.

To get the Excel sample file, go to the Sorting in Excel page on my Contextures site:

Download the Sample File

To see the sample used in this video, and other conditional formatting examples, you can visit the Conditional Formatting Introduction page.

___________________

0 thoughts on “Conditional Formatting Updates in Excel 2010”

  1. Hello,
    I hope you can help. I have 3 cells in a row. D4 (form number), E4 (case number), and F4 (score between 1-100).
    I have set up conditional formatting for E4 successfully to change the color of the cell based on the value contained within it. 0-60, red; 61-70, orange; 71-80, yellow, 81-90, light green, and 91-100 dark green.
    Fantastic, but now I want to match the conditional formatting of cells D4 and E4 to F4. So that based on F4’s number those cells change color too.
    Logically, here’s what I have in my 5 formula bars for my 5 rules in conditional formatting:
    =”e4=f4=>90″
    =”e4=if(f4=>81,f4=70,f4=61,f4=<69)"
    ="e4=f4=<60"
    But nothing happens. Please help. How do I get cells D4 and E4 to match the color conditional formatting for cell F4?

  2. I have a couple of columns on a spreadsheet where I would like for the numbers on one column to transfer to another cell when the other column numerical columns are highlighted. In the examples below; EX1 is the original view, in EX2- When I highlight the cells under “Total”, I would like to see the numbers from the “S” column transpose to the last cell in “Total”. Is this a Conditional Formatting issue? Can you help?
    EX 1 W/P
    71
    WPS Total S
    12 9 2
    10 11 3
    21 17 4
    32 30 5
    18 18 6
    7 5 8
    EX 2 W/P
    71
    WPS Total S
    12 9 2
    10 11 3
    21 17 4
    32 30 5
    18 18 6
    7 5 8
    456

    1. @Alex, this isn’t a conditional formatting issue, but you could use a command to put the selected cells at the bottom of the list.
      Copy the selected cells, then select the cell where you want to put them.
      Then on the Ribbon’s Home tab, click the Paste command, then click the Transpose command.

  3. I am trying to set up conditional formatting which will count how many times a name has been entered into a defined table. The table will be limited to about 6 unique names. Just a simple +1 each time a name is entered. Names come from a drop down menue to avoid spelling diferences. I would love help on the conditional formatting for the name count.
    Any suggestions will be appreciated.

  4. Dear madam
    i want to format one cell with conditional format and condition is if cell value exceeds – it should show red tag if its 0 it should show yellow and more than zero green, can u please help me urgently.??? thanks.

  5. Is there a way to use conditional formatting in conjuction with data valadation. I have a dropdown list that has four dropdown options and I would like the row highlighted in a differenct color based on the selection. Is it possible?

Leave a Reply to Wanda Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.