There are many things you can do with Conditional formatting preset conditions in Excel. If you don’t already know about conditional formatting, you should read Trump Excel’s ultimate guide to using conditional formatting in Excel.
If you have a large dataset, it can be difficult to identify trends and pick out key information. But with conditional formatting preset conditions in Excel, it becomes a cup of tea.
The example below shows a dataset that different conditional formatting preset conditions have been applied to, to emphasize and easily identify key information.
In this post, I will share with you the steps to follow in applying conditional formatting preset conditions in Excel.
What is Conditional Formatting?
Conditional formatting is one of Excel’s powerful features. It enables you apply special formatting to cells that meet specific criteria.
There are a number of preset conditions that can be applied and you can also create your own conditional formatting preset conditions using Excel formulas.
With conditional formatting, you use colours to emphasize and highlight cells or rows in excel worksheet. It is a very useful Excel tool for data visualization, as well as to detect issues and identify trends in a dataset.
How To Apply Conditional Formatting Preset Conditions in Excel
There are a number of conditional formatting preset conditions in Excel. They are:
1. Apply Highlight Cells Rules
Now, let us see how they can be applied to this dataset in Excel worksheet.
1. Apply ‘Highlight Cells Rules‘ Preset Conditions
The ‘Highlight Cells Rules’ is used when you want to change the appearance of the cells based on specific conditions.
So this is applied when specific cells or cell ranges are highlighted to emphasize specific information or unusual pattern.
In this example above, all the cells that have Female (F) in the Customer Gender column is highlighted. The conditional formatting is applied using the Text contained in the cell to find the cells in the dataset.
There are also other ways you to apply conditional formatting using Dates, Duplicate values, or values with specific rules such as Less than, Greater than or Equal to.
To do this;
1. Open your data worksheet and select the cell range or column to apply the rule. In this case, we select column D(D1:D200).
2. Go to Home tab -> Conditional Formatting
3. Go to ‘Highlight Cells Rules’ and select ‘Text that Contains’.
4. In the ‘Text That Contains’ dialog box, enter the text in the field and select a colour option.
For this example, we enter ‘F’ and select ‘Light Red Fill with Dark Red Text’.
5. Click ok.
Now, all the cells in the rows that are Female (F) will be highlighted in red.
2. Apply ‘Top/Bottom’ Conditional Formatting Preset Conditions
This is another preset condition in Excel. This condition allows you to find and select the cells that contain the top or bottom range values. It can be specified by a number, average or percentage and can be applied to only cells with numeric values.
Using the same dataset, let’s say we want to find out the states where the top 10 highest revenue was made.
To do this:
1. Select the cell range in the Revenue column (i.e P2:P200)
2. Go to Home tab -> Conditional Formatting -> Top/Bottom Rules
3. Select ‘Top 10 Items’.
4. In the ‘Top 10 items’ dialog box, select the colour fill you want by clicking the dropdown arrow.
Note that the Top/Bottom rule is set to 10 by default. However, you can change the number in the ‘New Formatting Rule’ dialog box.
5. Click ok.
You will now see that the cells with the top 10 highest revenue values have been highlighted. And Excel updates it as new figures are added to the dataset.
3. Apply ‘Data Bars‘ Conditional Formatting Preset Conditions
You can also apply data bars to a cell range to indicate the amount of value in the cells. This is done using bar lengths to relate to the values in a cell.
This way, you can easily compare the values in that cell range.
In the dataset, you can easily see the cells or rows with higher Order Quantity and the cells with very low Order Quantity.
To apply or add data bars to a cell range in Excel worksheet, you can go to my earlier post on how to create data bars in Excel.
4. Apply ‘Colour Scales‘ Conditional Formatting Preset Conditions
Colour Scales are applied when you want to create a heat map in Excel. It is similar to data bars as they both relate to the values in a selected range of cells.
But instead of using the length of bar to represent the cell values, colour scales use colour gradients like this:
Applying colour scales in Excel’s conditional formatting feature colours the background of the cells to highlight the different categories of items contained in a cell range.
To learn how to apply colour scales, you can go to the post on how to create a heatmap in Excel or check out this Microsoft Support page.
5. Apply ‘Icon Sets‘ Conditional Formatting Preset Conditions
Using ‘Icon Sets’ rule is another way to relate data in a cell range and call attention to key data. Icon sets are used to apply colourful icons to the data as seen in the column above.
Here, icon sets are applied to the Cost column to indicate the increase or decrease in cost price of the products.
To do this,
1. Select the cell range for Cost column (i.e N2:N200)
2. In the Home tab, click on Conditional Formatting -> Icon Sets
3. And select from the variety of options under Directional, Shapes, Indicators or Ratings icons.
You will see that the Icon Sets formatting rule is now been applied to the column in your worksheet.
I hope you found this post useful. Don’t forget to share your thoughts in the comment section below.