Conditional Formatting in Excel

Conditional formatting is used to format or highlight cells or range of cells in Excel. It is easy, emphasizes the exceptional values, views data using data bars, color scales, and icon sets corresponding to particular variations in the data set. 

Conditional formatting changes the visuals of the cells based on the user-specified conditions. These conditions are applied to the range of cells and formatted only if the conditions are accurate; if the states do not fall under the right category, then the cell range will not be formatted. Many built-in functions can be created by the user, including by using a formula to evaluate true or false, such as:

Background color shading of cells

• Foreground color shading of cells

• Data bars

• Icons that have four different image types

• Values

Rules of Conditional Formatting

Conditional formatting rules are applied to automatically format rows and cells based on specific values that they contain. These conditionally formatted features do not change when the values in the cell change. Formulas are used to change the values in the cells based on the criteria when the cell value has to be changed. 

Create a Conditional Formatting Rule

• Click on Conditional Formatting in the toolbar to view the conditional formatting window. 

• Click on Add New Rule to create a new, blank conditional formatting rule. 

Setting the Conditions for the Rule

1) Select <set condition> to display the Set Condition window 

The specified condition set by the user determines whether the specified condition triggers the formatting. For example, if the condition user would want to apply a background color to all rows assigned, select the column named set to in the left pane and the mentioned criteria in the right pane. 

The criteria for conditional formatting will vary depending on the column type. For example, a checkbox column can use formatting based on whether the box is checked or not. For a date column, formatting is applied based on the date specified “in the past” or “in the next days” amongst the different options. 

A conditional formatting smartsheet looks at the numbers in the column formatted for percentage (for example, a % complete column) for the values between 0 and 1. The decimal values like 0.25 for 25%, 0.5 for 50% are used to analyze correct comparison instead of whole numbers. 

The rule is further customized. For example, if the user wants to mention a comments column containing the value “urgent”), select define custom criteria. 

Select the checkbox to apply to format when needs are NOT met to display formatting on rows that do not belong outside the condition. 

From the already considered example, selecting the checkbox would apply formatting to the rows NOT assigned to the mentioned condition. 

2) When the condition is finished setting, it is selected OK. The format rule is set to:

Click on the format to choose the formatting to apply if the specified condition is met from the Conditional Formatting dialog window. The formatting choices will appear within the dialog window. 

The Conditional Formatting in Excel course offered by Great Learning will help you understand the formatting techniques to create an easily understandable Excel sheet. You can segregate the values based on specific conditions, group them, and analyze them much easier using conditional formatting. This course is designed with care to cater to all the learning kinds across the globe. Enroll in Great Learning today to avail of the Conditional FormattingIn Excel certification course for free online. Happy Learning!