In Excel, you can use conditional formatting to rearrange the cell content as you wish. For example, you can colour-code cells or replace numbers with text. In this practical tip, we will introduce you to some of the possibilities you have with this tool.
Conditional Formatting Excel: Use 2 and 3-colour scale
Conditional Formatting in Excel allows you to colour code cells to quickly get an overview of the values. One possibility is the 2 and 3 colour scale, where the individual values are displayed as a colour gradient.
- Select a range in the Excel spreadsheet and click on “Conditional Formatting” and “New Rule…” at “Start”.
- For the rule type “Format all cells based on your values”, select “2-colour scale” as the format style.
- For the type, select “Lowest value” for Minimum and “Highest value” for Maximum.
- Change the predefined colours, if desired, and finish with “OK”.
- For a 3-colour scale, determine the formatting for the average value.
Graphical representation: Via bars or symbol sets
To get an overview of the numbers entered, you can also use bars for formatting and thus create a kind of diagram.
- Select the item “Data bars” in the entry “Conditional formatting” after selecting the cells.
-
You can now select one of the templates. If you want more options, click on “More rules”. In the new window you can then customise things like the colour fill or the frame.
- Displaying using symbol sets is also quickly done. To do this, click on “Symbol sets” under “Conditional formatting”.
- Here too, you can choose between one of the templates or adjust the formatting via “Additional rules”.
Elevate cells with specific value
In addition to graphical representations, you can also highlight cells with specific value. For example, all numbers below the value 1,000 can be marked with the colour red.
- Select the range in the table and click on “Rules for highlighting cells” in the “Conditional formatting”.
- Select the item “Smaller than” and enter a desired value such as “1,000”. In this menu, however, you can also make other rules such as “Greater than” or “Between” for highlighting.
- Using the drop-down menu to the right, you can set the colour with which the corresponding cells are to be highlighted. Confirm your selection with “OK”.
- Click “OK” twice to create the rule
Display numbers as text
If you have predefined texts that occur again and again, you can also use conditional formatting to save time and prevent typing errors.
- Select the area in the Excel sheet to which the formatting should apply and create a new rule via “Conditional formatting”.
- Click on “Format only cells that contain” at the top of “Select rule type”.
- Select “Equal” next to “Cell value” in the rule description and enter a value to be replaced to the right of it. For example, the number 1.
- On the left sidebar click on “Custom”.
- In the text box under the “Type:” entry, enter the text to replace the number in the cell. Click “OK” twice to complete the whole thing.
- If you now enter the number 1 in a cell that is in your defined range, the text you entered will appear instead. In exactly the same way, you can define other texts for other numbers.
- Select the area of the table to which the formatting should apply and create a new rule. For “Select rule type”, select the item “Use formula to determine cells to be formatted”.
- Now enter the desired formula. In our example: “=A1☻TODAY()”.
- Click on the button “Format…” and select red as the colour in the new window.
- Confirm by clicking twice on “OK”.
Formula for determining the cells to be formatted
You can also use conditional formatting to format any value to which a particular formula applies. In this example, all Excel data that is still in the future is marked in red.
