Question

Excel: Conditional Formatting - Here's How

Answer 1

preparation
The goal
Your Excel spreadsheet
Show bar graphs instead of values
1st step:
2nd step
3rd step:
Colorize every other row
1st step:
2nd step
3rd step
4th step:
5th step
Mark today's date
1st step:
2nd step
The final result

---

Would you like to use conditional formatting in your Excel documents? We'll show you how it's done using a clear example..

With the conditional formatting in Excel, rules can be defined as to how certain values ​​are to be visually emphasized. For example, important data can be highlighted in color so that they can be recorded immediately. In addition, your tables will be much more aesthetically pleasing if the design complements the data supplied in a meaningful way.

Here are more tips for dealing with conditional formatting in Microsoft Excel.

• preparation
• Show bar graphs instead of values
• Colorize every other row
• Mark today's date

preparation

The goal

The aim of this guide is to create a to-do list . The first step is to create an overview using different colors in the lines. Red lines mean that the entry is due today. All other lines should be highlighted alternately in blue and green. In addition, the importance of the entries should be visualized using a bar chart .

Your Excel spreadsheet

First of all, of course, you need to create a suitable document. In our example, we assume a simple to-do Lis-te with the columns " ToDo ", " Date " and " importance ". You are also welcome to use our sample list take over. To do this, copy the following text into the first cell of a new Excel document: ToDo Date Importance Birthday Isabelle 08/31/2018 100% Appointment at the dentist 05/12/2018 90% Renew ID card 05/09/2018 90% Complete report 05/15/2018 80% Pick up important documents 05/15/2018 .2018 75% Meeting May 27th, 2018 65% Conference Call June 30th, 2018 45% Feed the Fish May 9th, 2018 40% Write a Mail to Max May 20th, 2018 30% Milk Shopping May 9th, 2018 20% Tidy Desk May 16, 2018 10%

Show bar graphs instead of values

1st step:

First, the values ​​in the " Importance " column should be displayed as a bar. To do this, first select all the entries in this column by clicking on the column name .

2nd step

Under the " Start " tab, select the " Conditional Formatting " button . There are now various ready-made options for displaying the numbers graphically. In our case, however, we will adjust the display a little. To do this, go to the " Data bar " submenu and click on " More rules ... ".

3rd step:

Here you can define various settings such as a color gradient, frame or other nominal values. In this case, we opted for black data bars without the associated numbers. To do this, we tick " Only show bars " and change the color to black. You can accept our settings as shown in the picture. If you have other preferences, you can of course adapt them to your needs.

Colorize every other row

1st step:

Now we want to achieve that every other line is displayed in a different color. However, Excel does not provide any ready-made formatting options for this . Therefore you have to create your own rule. To do this, click on column A again . Then, with the [Ctrl] key pressed, still column B , so that both columns are selected. Go back to the " Conditional Formatting " menu and click on " New Rule ... ".

2nd step

A new window will appear in which you have to select a rule type. Select Use Formula To Find Cells To Format . There you can make the settings of your choice under " Format ... ". We decided to change the background color. Feel free to experiment with the various settings if you want to learn about the possibilities of conditional formatting in Excel. So that Excel can now assign when the formatting rule should come into effect, you must specify a formula. To do this, copy the following value into the formula field : = AND (REMAIN (LINE (); 2) = 1; NOT (ISBLANK ($ A1))) This formula checks, on the one hand, whether the current line number is odd (1, 3, 5, ...) and, on the other hand, whether there is any entry in this line. Excel's formatting rule is only applied if both properties are met. For a formula that formats all even rows, take a look at step 5.

3rd step

Now each line appears with an odd line number in the selected format. However, the column headings are now also affected by the rule . To exclude these two cells from the rule, select one cell and then while holding down the [ Ctrl ] key , select the other. Then click on " Conditional Formatting ", click on " Delete Rules " and then on " Delete Rules in Selected Cells ".

4th step:

Now we want to create a rule that affects all other lines and color them. To do this, create a new rule for columns A and B by selecting both columns again and creating a new rule via the menu.

5th step

A new rule of the type " Use formula to determine which cells to format " is to be created. Under " Format ... " we have selected a green color to highlight the line for our example. Copy this formula into the formula field : = AND (REMAIN (LINE (); 2) = 0; NOT (ISBLANK ($ A1))) This formula decides, similar to the first, whether a line is filled and whether it has an even line number . Lines that match both are selected for formatting.

Mark today's date

1st step:

Next, all entries due today should be marked in red. This marking should always be kept up to date. To do this, you have to create a new rule for the first two columns again.

2nd step

Select the rule type " Use formula to determine cells to be formatted " again. Under " Format ... " we have chosen a red color to highlight the line. Copy this formula into the formula field : = $ B1 = TODAY () This formula checks whether the date in the line corresponds to the current date. If this is the case, Excel's formatting rule is applied.

The final result

Now you are done. Here you can see the end result according to our instructions.

---