Conditional formatting in Excel – How to

In Excel, you can use conditional formatting to redesign the cell contents as you like. For example, you can display a completely different text or change the font. The powerful toolset includes many options as well as predefined rules. This article shows the most important conditional formatting rule types and explains them using examples.

Conditional formatting in Excel

Format all cells based on their values

This Excel rule type provides graphical options, for example, to visually decorate numerical values. This makes it easier for you to get an overview of your data.

  1. Select an area in the Excel spreadsheet and click on the “Start” tab.
  2. Now select “Conditional Formatting” and then click on “New Rule …”.
  3. In the new window, select the type “Format all cells based on their values”.

Colorize cells that exceed or fall below a value

Values ​​that do not fit within a specific area can easily be located with Excel. With this function, the cells that do not comply with a value are marked in color.

  1. Select an area in the Excel spreadsheet and click on the “Start” tab.
  2. Again, go to “Conditional Formatting” and then to “New Rule …”.
  3. Instead, select “Classic” to configure that the cells below or above a value are color coded.

2 and 3 color scale

You can add expressiveness to the meaning of your values ​​using a gradient. For example, on a scale of one to ten, show how high the radiation is in an area.

  1. In the lower part of the window you now choose the format style “2-color scale”.
  2. As type, select “minimum value” for minimum and “maximum value” for maximum.
  3. If necessary, change the predefined colors and close with “OK”. For a 3-color scale, you can simply set the formatting for the mean value.

bar

You can also use bars for formatting. For example, graph your spending per month in Excel.

  1. Just select “Data Bar” as the style.
  2. As Type, select “Minimum” and “Automatic”.
  3. Shape the bars below and close with “OK”.

symbologies

For example, you can use the symbol sets to enter the signal strength of a network in percent using the appropriate symbols as a graphical tool.

  1. At the bottom of the window, choose the Style Sets style.
  2. As a symbol type, select the signal strength display.
  3. For the first option “if value:” set this to “>”. The rest you can leave that way. Clicking on “OK” completes the Excel rule.

Format only cells that contain

This format type is particularly useful for highlighting individual special values. For example, all numbers below 0 can be formatted with the color red.

  1. Select the area in the table for which you want to apply the formatting and create a new rule with the type “Format only cells containing”.
  2. Set the following option: “Cell value less than 0”
  3. Click on the button “Format …” and select red in the new window as color.
  4. Click OK twice to create the rule

Represent numbers as text

Do you have predefined texts that occur again and again, this type is ideal for it. This not only saves you time, but also prevents typos.

  1. Select the area in the Excel spreadsheet that you want to apply the formatting to and create a new rule with the “Format only cells containing” type.
  2. Set the following option: “Cell value equal to 1”
  3. Click on the “Format …” button and select the “Numbers” tab in the new window.
  4. On the left side of the sidebar, click on “Custom”.
  5. In the text box under the label “Type:” enter the following: “Hello World!” – the quotation marks are mandatory.
  6. Click “OK” twice to complete the whole.

If you enter the number one in a cell that is in your defined area, the text “Hello World!” Appears instead. Right now, you can specify additional texts for other numbers.

Use formula to determine the cells to format

As the title implies, this type reformats every value to which a particular formula applies. In this example, all Excel data that is still in the future will be highlighted in red.

  1. Select the area in the table for which you want to apply the formatting and create a new rule with the type “formula for determining the cells to format”.
  2. Enter the formula = B2> TODAY ().
  3. Click on the button “Format …” and select red in the new window as color.
  4. Confirm by clicking “OK” twice.

This article shows only a few uses of conditional formatting. The function can be used extremely versatile and with a little routine you can already achieve great results. This guide is for Excel 2010.

Conditional formatting in Excel – How to
Rate this post