How to Apply Conditional Formatting in Excel

2024 Author: Samantha Chapman | [email protected]. Last modified: 2024-02-02 02:12

Do you sometimes need to know when you are in or out of budget? Do you want to choose an important date from a long list? Excel's conditional formatting feature can help you do all of this and more. While not easy to use, knowing the basics can help you make more sense of the project you're working on.

Steps

Step 1. Enter all your details or download a sample file here

This is useful, because it will be easier to understand conditional formatting by practicing on data you already have. While you can apply conditional formatting to blank cells, it's easier to see its effects by using existing data.

Step 2. Click on the cells you want to format

Conditional formatting allows you to change font styles, underline and color. Using conditional formatting, you can also apply erasures, borders and shadows on the cells. However, you will not be able to change the font or font size of the cell contents.

Step 3. Click on "Format"> "Conditional Formatting" to start the process

In Excel 2007 you can find it under "Home"> "Styles"> "Conditional Formatting".

Step 4. Click on "Add >>" to use two conditions

For this example, two conditions are used to see how they interact. Excel allows three conditions per cell. If you only need one, skip the next step.

Step 5. Click "Add >>" again to set another condition, or click "Delete

.. and choose which ones to remove.

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

Step 7. Leave the condition as is (in other words, leave the first drop-down menu as "Cell Value"), if the condition is based on the current cell

If it is based on others, change the first drop-down menu to "Formula". For instructions on "Formula", go to the next step. For instructions on "Cell, do the following:

• Choose the appropriate topic type using the second drop-down menu. For conditions between a lower and a higher value, choose "included" or "not included." For single-valued conditions, use the other arguments. In this example we will use a single value with the "greater than" argument.

• Determines what value should be applied to the argument. For this example, we will use the "greater than" argument and cell B5 as the value. To select a cell, click the button in the text field. Doing so will minimize the conditional formatting window.

  

  Step 8. With "Formula" you can apply conditional formatting based on the value of one or more cells

  After selecting "Formula", all the drop-down menus will disappear and a text field will appear. This means that you can enter all the formulas you want using the Excel ones. For the most part, just use simple formulas and avoid text or lines of text. Remember that the formula is based on the current cell. For example, think about this: C5 (current cell) = B5> = B6. This means that C5 will change formatting if B5 is greater than or equal to B6. This example could also be used in "Cell Value", but it serves to make you understand. To select a cell in the spreadsheet, click the button in the text field. You will minimize the conditional formatting window.

  For example: imagine you have a worksheet with all the days of the current month listed in column A; you will have to enter data in the spreadsheet every day; and you will want the entire row associated with today's date to be highlighted in some way. Try this: (1) Highlight your entire data table, (2) Select conditional formatting as explained above, (3) select "Formula" and (4) enter something like = $ A3 = TODAY (). Column A contains your data and Row 3 is your first row of data (after the titles). Note that the "$" symbol must be in front of the A, but not in front of the 3. (5) Select your formats.

  

  Step 9. Click on the cell that contains the value

  You will notice that you have automatically placed dollar symbols ($) before the column and row designations. This will make the cell reference non-transferable. This means that if you apply the same conditional formatting to other cells with copy and paste, they will all refer to the original cell. To disable this feature, simply click in the text field and delete the dollar sign. If you don't want to set a condition using a cell in your sheet, simply type the value in the text field. You can also enter text, based on the topic. For example, don't use "greater than" as an argument and "John Doe" in the text field. - In this example, the whole condition, expressed in words would be: "When the value of this cell is greater than the value in cell B5, then …"

  

  Step 10. Apply the formatting type

  Keep in mind that you will need to make the cell stand out from the rest of the sheet, especially if you have a lot of data. But you will also want the sheet to look professional. In this example, we want the font to be bold and white and the shadow to be red. To get started, click on "Format".

  

  Step 11. Select what type of font changes you would like to apply

  Then click on "Borders" and make the desired changes. In this example, there are no edge changes. Then click on "Schemes" and make the desired changes. When you are done, click on "OK".

  

  Step 12. A preview of the format will appear below the arguments and values

  Make the necessary adjustments to get the look you want.

  

  Step 13. Move on to the second condition (and third, if any) and follow the previous steps again (starting with # 6)

  You will notice in the example that the second condition also includes a small formula. This takes the value of B5, multiplies it by 0, 9, and applies formatting if the value is less than that reference.

  

  Step 14. Click on "Ok"

  Now you are done with 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 will be applied.

  2. One of the formats you have chosen will appear because one of the conditions is met.

     Advice

     • You can apply formatting to other cells as well by highlighting the ones that have the formatting you want and copying it. Then select the cells you want to apply it to, perform a Paste Special and select "Formats".
     • A very useful application of this feature is to use it to identify inventory items that fall below an alarm level. Example: Bold a row or cell when the inventory value is less than a specific quantity.
     • Practice applying conditional formatting to data that you have a backup copy of or don't care about losing.
     • One feature that Excel does not have is the ability to do a "copy - paste special - values" to conditional formatting so that the formatting is copied but the equations disappear. This would save the memory occupied by the equations. Below is a VBA (Visual Basic for Applications) macro that does exactly this, copying the files to Word and then back to Excel; note this procedure is reserved for more advanced users who have experience with VBA macros:

     Fast and effective VBA macro that can be adapted to small data sets

     Public Sub FormatMyRow () 'Conditionally formats rows of data from red to yellow to green. '' There are 21 rows of data to format, excluding titles' Column 4 (D) was a percentage value that ranged from 0% to 100%. For i = 2 To 22 'Get the value of column 4 intVal = Cells (i, 4). Value' Establish temporary red and green RGB values … If intVal> 0.35 Then intRed = 200 Else intRed = Int (intVal * 510) If ((intVal - 1) * (-1))> 0.65 Then intGrn = 255 Else intGrn = Int (((intVal - 1) * (-1)) * 255) 'Add 100 to RGB values to make colors more pastel. intRed = intRed + 100 intGrn = intGrn + 100 'Reports RGB values beyond 255 to 255 … If intRed> 255 Then intRed = 255 If intGrn> 255 Then intGrn = 255' Apply RGB color to each of the 11 columns … Note that the 'RGB blue is fixed at 100… For j = 1 To 11 Cells (i, j). Interior. Color = RGB (intRed, intGrn, 100) Next Next End Sub
     

     • Conditional formatting can also be used to shade each odd line. You can find the information on the Microsoft website
     • You can apply the same formatting to an entire column or row. Click on the "Format Painter" button (it looks like a yellow brush) and then select all the cells you want to apply conditional formatting to. This will only work if the condition values don't have dollar signs. Remember that cell references should be checked carefully.
     • These steps work with Excel 97 or later.

     Warnings

     • In versions prior to 2007, there is a limit of three conditional formats per cell. In Excel 2007, this limit is removed.
     • Don't choose hard-to-read formatting. Orange or green backgrounds may look good on your computer, but the data will be more difficult to read when printed on paper.