How to Apply Conditional Formatting in Excel
By Wikihows

Do you ever need to know when you're over or under budget? Want to pick out the important data from a huge list? Excel's conditional formatting can help with all of this and more. While it is a little difficult to use, knowing the basics can help you make sense of whatever project you are working on.

Steps

1. Input all of your data or download a practice file here. This is useful because conditional formatting is best understood by testing it on data you already have. While you can apply conditional formatting to empty cells, it is easiest to see if the formatting works by using pre-existing data.

2. Click on the cell you want to be formatted. Conditional formatting only allows you to change font style, underline, color, and strike-through as well as borders and shading. You cannot change the font or the font size.

3. Click "Format" then "Conditional Formatting" to begin the conditional formatting process.

4. Click "Add >>" to use two conditions. For this example, two conditions are used to see how each one plays off the other. Excel allows up to three conditions per cell. If you only need one condition, skip the next step.

5. Click “Add >>" one more time to set another condition, or click “Delete..." and choose which condition to remove.

6. Determine if your first condition is based on the value in the current cell, or if it is based on another cell or group of cells in another part of the worksheet.

7. Leave the condition as is (in other words, leave the first drop-down as “Cell Value Is"), if the condition is based on the current cell. If it is based on other cells, change the first drop-down to “Formula Is." For “Formula Is" directions, go to the next step. For “Cell Value Is" directions, do the following:

Choose what kind of argument works best using the second drop-down box. For conditions between a low setting and a high setting, choose “between" or “not between." For conditions using a single value, use the other arguments. This example will use a single value using the “greater than" argument.

Determine what value(s) should be applied to the argument. For this example, we are using the “greater than" argument and cell B5 as the value. To select a cell, click the button in the text field. This will minimize the conditional formatting box.

8. For “Formula Is" you can actually apply conditional formatting based on the value of another cell or cells. After selecting “Formula Is," all the drop-downs disappear and you are left with a text field. This means you can type in any formula you want using Excel’s formulas. For the most part, you want to stick to simple formulas and avoid text or text strings. Keep in mind that the formula is based on the current cell. For an example, think like this: C5 (current cell) = B5>=B6. This means that C5 will change formatting when B5 is greater than or equal to B6. This example can actually be used in “Cell Value Is" but you get the idea. To select a cell in the worksheet, click the button in the text field. This will minimize the conditional formatting box.

9. Click the cell that contains the value. You will notice that it automatically places dollar signs ($) before the row and column designations. This makes that cell reference non-transferable. This means if you were to apply the same conditional formatting to other cells through copy/paste, they will all reference the original cell. To turn this off, simply click in the text field and delete the dollar signs. If you do not want to set a condition using a cell in your sheet, simply type the value into the text field. You can even enter text, depending on the arguments. For example, don’t use “greater than" as the argument and “John Smith" in the text field. You can’t be greater than John Smith...well, you could, but--oh, never mind. In the example, the whole condition, if you were going to say it out loud, would read something like this: “When this cell’s value is greater than the value in cell B5, then..."

10. Apply the type of formatting. Keep in mind that you want to offset the cell from the rest of the sheet, especially if you have lots of data. But you also want to make it look professional. For this example, we want the font to become bold and white and the shading to become red. Click “Format..." to begin.

11. Choose what type of font changes you would like to make. Then click “Border" and make any changes there. This example does not make border changes. Then click “Patterns" and make changes there. At whatever point you are finished making the formatting changes, click "OK."

12. A preview of the format will appear under the argument and values. Make changes as needed until the formatting appears the way you would like.

13. Move on to the second (and third if you’ve got it) condition and follow the above steps (starting with Step 6) again. You will notice in the example that the second condition also includes a small formula (=B5*.90). This takes the value of B5, multiplies it by 0.9 (a.k.a. 90%) and applies formatting if the value is less than that.

14. Click "OK", Now that you have finished all your conditions. One of two things will happen:

  • No changes will appear. This means that the conditions are not met, so no formatting was applied.
  • One of the formats you selected appears because one of the conditions has been met.

Tips

  • You can apply the same formatting to a whole row or column. Click the “Format Painter" button (looks like a yellow paintbrush) and then select all the cells that you want to apply the conditional formatting to. This only works if the value of the condition does not have those dollar signs. Just keep in mind that the cell references should be double-checked.
  • You can also apply the formatting to other cells by highlighting the cell that has the formatting you want and copying it. Then select the cells for it to be applied to, and do a Paste Special and select 'Formats'.
    You may want to attempt conditional formatting on data that doesn't matter or won't get lost if you make a mistake.
  • These steps work with Excel 97 or newer.
  • Conditional formatting can also be used to shade every other row. Information can be found at the Microsoft website http://support.microsoft.com/kb/268568/en-us?spid=2513&sid=280
  • One very useful application of this feature is to use it to identify inventory items that fall below desired stock levels. EXAMPLE: Bold a row or cell when inventory value is lower than a specified quantity.

Warnings

  • Do not choose formatting (like orange background and green foreground) that is difficult to read. Always keep in mind that it may be easier to read and understand data on the computer screen than it is when the same information is printed out.
  • There is a limit of three conditional formats per cell.

The images at this page were uploaded before the licensing information templates were created. Therefore, it is under the assumption that the original uploader granted the correct license.

It is believed that these images are licensed under the Creative Commons licenses, the GNU Free Document License or some other license that will allow wikiHow to display and share the work freely with others. The original license and creator's name can be found at the source URL mentioned above. Many thanks to the original creator that freely licensed their work!

Close window