Does it sometimes happen that you need to know whether you have stayed within budget? Do you want to be able to quickly find relevant data in a huge list? The conditional formatting function of Excel can help you with this, among other things. It's not very easy to use, but knowing the basics can help you understand your projects better.
Step 1. Enter all your data or download a practice file here (in English)
This is useful because the best way to understand conditional formatting is to test it with data you already have. Of course you can use conditional formatting on empty cells, but it's easier to understand how it works by using existing data.
Step 2. Click on the cell you want to format
Conditional formatting lets you customize font style, underline, and font color. You can also strike out letters and apply borders and padding. However, you cannot change the font or font size of the cell contents.
Step 3. Click "Format" > "Conditional Formatting" to start the process
In Excel 2007 you can find this under "Home" > "Styles" > Conditional Formatting".
Step 4. Click "Add >>" to use two conditions
This example uses two conditions to see how one affects the other. You can apply a maximum of three conditions per cell in Excel. If you only need one condition, you can skip the next step.
Step 5. Click "Add >>" again to add another condition or click "Remove
.." and choose which condition you want to remove.
Step 6. Determine whether the first condition is based on the value in the current cell or based on another cell or group of cells elsewhere in the worksheet
Step 7. Leave the condition as it is (in other words, leave the first drop-down menu set to "Cell value is") if the condition is based on the selected cell
If it is based on other cells, choose "Formula is" in the menu. For an explanation of "Formula is", go to the next step. In case of "Cell value is" do the following:
Select a comparison operator from the second menu. For conditions between a low and a high value, select "between" or "not between". For conditions that use a single value, use the other arguments. This example uses a single value in combination with the "greater than" argument.
Determine which value(s) the argument should use. In this example, we'll use 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 window.
Step 8. In the case of "Formula is", you can apply conditional formatting based on the value of another cell or cells
After you select "Formula is" the drop-down menus will disappear and you will be left with a text field. This means that you can type any formula here using Excel's formulas. Usually it is good to use simple formulas and avoid text. Keep in mind that the formula is based on the selected cell. For example, think like this: C5 (current cell) = B5>=B6. This means that C5 changes the formatting if 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 minimizes the conditional formatting window.
- For example: Suppose you have a spreadsheet with all the days of the current month in Column A; you need to enter data in this worksheet every day; and you want the entire row related to today's date to light up in some way. try this: (1) Select the entire data table, (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 the headings). The dollar sign should be in front of the A and not the 3. (5) Select the format.
Step 9. Click on the cell that contains the value
You will notice that the dollar signs ($) are automatically replaced for the row and column names. This makes the cell reference non-transferable. That means if you were to apply the same conditional formatting to other cells by copy/paste they will all refer to the original cell. To turn this off, just click in the text box and remove the dollar signs. If you want to specify a condition that is not based on the value of a particular cell, you can just enter the value in the text field. You can even use text depending on the arguments. But you can't, for example, use "greater than" as an argument and "Piet Jansen" in the text field. You can't be bigger than Piet Jansen… Well, you can, but - well, never mind. In this example, the entire condition would sound like this if you said it out loud: "If the value of this cell is greater than the value in cell B5, then…".
Step 10. Choose the formatting type
Keep in mind that you want to be able to distinguish the cell from the rest of the worksheet, especially if you're dealing with a lot of data. But you also want it to look professional. In this example we are going to make the letters bold and the padding red. To get started, click "Format…".
Step 11. Select which type of font changes you want to apply
Then click on "Edge" and make your desired changes here. In this example we will not change the borders. Then click on "Patterns" and change what you want. When you're done making formatting changes, click OK.
Step 12. A preview of the formatting appears below the argument and values
Make changes to the layout until you are satisfied.
Step 13. Proceed to the second condition and (if you have one) the third and follow the above steps again, starting at Step 6
You will notice in the example that the second condition contains a small formula (=B5*.90). This takes the value of B5, multiplies it by 0.9 (or 90 percent), and applies formatting if the value is less than that.
Step 14. Click OK
Now that you're done with the conditions, one of two things will happen:
- Nothing changes. This means that the specified conditions have not been met, so no formatting has been applied.
- One of the formatting types you've selected appears because you met one of the conditions.
- These steps work with Excel 97 or later.
- You can apply the same formatting to an entire row or column. Click the “copy‐paste” start/clipboard/formatting button (looks like a yellow paintbrush) and select the cells where you want to apply conditional formatting. This only works if the condition's value does not have the dollar signs. Keep in mind that you are always checking the cell references twice.
- You can also apply the formatting to other cells by selecting the cell that contains the formatting you want and copying it. Then select the cells you want to apply it to, do Paste Special and select "Format".
- You may want to try conditional formatting first on data that doesn't mind if you make a mistake.
- You can also use conditional formatting to shade every second row. You can find information here:
- Conditional formatting can be very useful to keep an eye on your inventory, for example: make a cell or row bold if the value falls below a certain amount.
- As of Excel 2007, the limit of up to three types of conditional formats has been removed.
- Avoid formatting that is difficult to read. Orange or green backgrounds may look nice on your screen, but it will be hard to understand if you print it out.