ADVERTISEMENT
How to use Conditional Formatting in excel | Format | Conditions
Note: If video does not play,
just refresh page and then Play video or Just Click on this text.
Why Conditional Formatting is Important In Ms Excel?
Conditional formatting helps make patterns and trends in your data more apparent. To use it, create a rule to format cells based on their value, like monthly temperature data with cell colors associated with cell values.
1. Highlight Cells with a Value Greater or Less than a Number
- Select the entire dataset.
- Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Greater Than.. or Less Than.
- Based on what option you select (greater than or less than), a dialogue box would open. Let’s say, you select the ‘Greater than’ option. In the dialogue box, enter the number in the field on the left. The intent is to highlight cells that have a number greater than this specified number.
- Specify the format to be applied to the cells that meet the condition by using the drop-down on the right. There are some existing formats that you can use, or specify your own format using the Custom Format option.
- Click OK.
This would instantly highlight all the cells with values greater than 5 in a dataset.
Note: If you wish to highlight values greater than equal to 5, you should apply conditional formatting again with the criteria “Equal To”.
2. Creating Color Scaling
A Color Scale is a visual representation of data where colors represent values in cells. For example, you can create heating visuals where the cells with the highest values are colored green, and the colors shift to red as the values decrease.
Here are the steps to create heat maps using conditional formatting in Excel.
- Select the data set.
Go to Home –> Conditional Formatting –> Color Scales, and choose one of the color schemes
Click the scaling icon to apply the format to your data set. You can choose from multiple gradients. If you are not satisfied with the existing color options, you can select more rules to specify the colors you want.