Create Conditional Formatting Rule In Excel 2010

Excel 2010 has various predefined conditional formatting styles and rules which enable users to apply styles, formatting and rules instantly on the specified data cells but sometimes it becomes frantic need to apply formatting and style over spreadsheets. For this Excel 2010 has an option to create a new conditional formatting rule which eventually abets users to mark out data cells in desired way.

Launch Excel 2010 spreadsheet on which you want to apply rule.

table

Now select the spreadsheet portion on which you need to apply rule, navigate to Home tab and from Conditional Formatting options, click New Rule.

new rule

From New Formatting Rule dialog, under Rule Type, select Format only cells that contain and from under Format only cells with options select Blanks, as shown in the screenshot below. When done, click Format.

blanks

Upon click, Format Cells dialog will appear, now select the Pattern Style and Color, to mark out blank cells in your datasheet. From the left side you can select different colors for the selected pattern. Navigate to different tabs and to change the Font, Border for the blank cells. Now Click OK.


format cells dialog

It will bring you back to the New Formatting Rule dialog, here you can see the preview of the changes made. Click OK to mark out all the selected cells with specified formatting styles.

preview

You will see all the blank cell filled with the specified formatting style, as shown below.

blank cellsa

For editing the rule you can always go to Conditional Formatting options and hit Manage Rules.

manage rules

You may also be interested in our previously reviewed guides on Using Color Scales in Excel 2010 & Icon Sets in Excel 2010.

Advertisement
  • Charles Lunsford

    I am trying to changes background of a cell based on a value of another cell. I have not been able to do that. Does anyone there been able to do this?

    • Cris

      You have to create a new rule, and select the last option (“use a formula to…”)

      Then you should write:

      =Formula(boolean)value

      For instance, you want to make your cell background change if cell A1=2

      You will write:

      =$A$1=2

      Then you go to format (underneath where you write) and choose the color of the background you’d like when the rule has ben met

      Regards

  • Jessie White

    Is it possible to “lock” cell formatting so that only authorised people can modify the rules?

  • Frank

    I would like to conditionally format a cell based on the value itself and another, for example if A1=W and A20 is blank. Is this possible?

  • Miamakenzie

    I am trying to highlight the lowest number in a set, but only if it is the only one, not multiples?

  • me

    but how am i supposed to know when to use the ” or $ or () or , like where is there a book on it??????????

    • me2

      Come on now, like you’d really read the book

  • me

    for instance i want the word “name” in cell a1 to be red ,and when someone enters their name in cell a2 i want for cell a1 to turn black. how do i do this?

  • Heather Stewart

    Is it possible to lock a cell so nothing can ever be typed in it?

  • steve

    I want to make a formula that when in a cell when the dates get put in, a different cell to make numbers for example when 6/19/14 -8/20/14 goes in a cell 2.2 goes into a another one then anything before 6/19/14 makes the cell in same row go 2.0