Apply Conditional Formatting in Excel

Do you ever need to know when you are over or under budget? Want to pick out an important datum from a huge list? Excel's conditional formatting feature 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 the cell you want to format. Conditional formatting allows you to change font style, underline, and color. Using conditional formatting, you can also apply strike-through as well as borders and shading to the cells. However, you cannot change the font or the font size of the contents in the cell.
  3. Click "Format" > "Conditional Formatting" to begin the conditional formatting process. In Excel 2007 this can be found under "Home" > "Styles" > "Conditional Formatting".
  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 need only 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:
    • Select what kind of argument works best using the second drop-down box. For conditions between a low setting and a high setting, select “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.
    • For example: Imagine you have a spreadsheet with all the days of the current month listed down in Column A; you need to enter data in this worksheet everyday; and you would like the entire row associated with today's date to light up in some way. Try this: (1) Highlight your entire table of data, (2) Select conditional formatting as explained above, (3) Select "Formula Is" and (4) Enter something like =$A3=TODAY() where Column A contains your dates and Row 3 is your first row of data (after your headings). Note that you want the dollar sign in front of the A but not in front of the 3. (5) Select your formats. [1]
  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 this 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. To begin, click “Format..."
  11. Select 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 (aka 90 percent) 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:
    1. No changes will appear. This means that the conditions are not met, so no formatting was applied.
    2. One of the formats you selected appears because one of the conditions has been met.

Tips

  • These steps work with Excel 97 or newer.
  • You may want to attempt conditional formatting on data that doesn't matter or won't get lost if you make a mistake.
  • One feature lacking in Excel is the ability to apply a "copy - paste special - values" to conditional formatting in such a way that the formatting is copied but the conditional formatting "equations" disappear. This would save the memory occupied by the equations. Below is a Visual Basic for Application (VBA) macro that does this by copying the Excel data to Word (which uses the HTML format) and then copies it back to Excel; But note, this is for the more advanced user who has some experience with VBA macros:

Here's a quick and dirty VBA macro that can be adjusted for small datasets (jp_johnny)

  • 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.
  • 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. 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."
  • 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

Warnings

  • Do not choose formatting that is difficult to read. Orange or green backgrounds may look sharp on your computer screen, but it will make the data harder to understand when it's printed out on paper.
  • On versions earlier than 2007, there is a limit of three conditional formats per cell. In Excel 2007, this limit is removed.

Related Articles

Sources and Citations

  1. ↑ Highlight Entire Row with Conditional Formatting at Better Solutions.com, Access Date: 2008-08-05.
Retrieved from "https://kipkis.com/index.php?title=Apply_Conditional_Formatting_in_Excel&oldid=272447"