Lesson 20: Conditional Formatting in Excel!

How to become masters in excel using Condition Formatting!

This video is a part of free Excel Training Singapore, where I show you how to use Conditional formatting in Excel from the basics and also cover a lot of advanced Features.

Conditional formatting in used to format a cell or range of cells based on a particular condition. When the given condition is proven to be true, then the cell is formatted, and if the condition becomes false, the cell is not formatted.

Features of conditional formatting in excel –

• Apply multiple conditional formats – while working with huge numerical data, this feature comes the handiest. This feature helps to highlight numbers greater than a specified number or less than that. Users can combine both options as well to display the output.
• Apply bar graphs to data – in excel conditional formatting, users can actually apply bar graphs to the numeric data cells. These graphs are displayed accordingly to the cell value. These can be filled with a single color or gradient effect as well.
• Sort data based on averages – any numerical data in excel can be sorted based on the average of the numerical data. This function highlights the numbers that are above average of the entire data set.

How to apply conditional formatting?

Conditional formatting enables users to highlight data which the user needs according to the given condition. This can be used to highlight data more than or less than a given number. To learn how to work with this, follow the following mentioned steps –

• Conditional formatting can be accessed in the tab bar in the home option. Click on home option, under the styles group, the option of conditional formatting appears.
• Prepare a set of data having various entries comprising of numeric data as well as alphabetical data. Select the numeric data part and hit on the conditional formatting option.
• Various option appears such as color services, data bar, icon sets, highlight data sets and many others. For instance, to highlight all numbers greater than 700 in the numeric list, select the highlight cell rules option.
• Another menu appears, select the option of greater than from the drop down list. A pop up menu appears. In the given space, enter the number with the greater numbers are to highlighted. Select the color in the with which the numbers are to be highlighted
• To change the color, click on the arrow that appears next to that box. A drop down menu appears. Either choose from the color choices or custom your own choice.

How to format cells using a formula?

The specific conditional formatting in excel enables users to filter out data based on a given condition. When the given formulas is fulfilled, the cells are highlighted else not . To learn how to use formula formatting, follow the following mentioned steps –

• Start by preparing a data comprising of various numeric and well as alphabetical data. Click on the conditional formatting in the styles group.
• Click on the new rule Option. A new rule dialogue box appears. From there select the last option that says use a formula to determine which cells to format .
• The good thing to do is to type the formula initially in any cell, check that if it’s working and then copy it to the dialogue box where the formula is to be entered.
• For instance, the formula is to be made to check that the given values are greater than 900 or not. Type the formula in any cell, mentioning the cell reference and hit enter. Drag down to all the cells and the output will be displayed as either true or false
• Now that the formula is working copy it in the new rule dialogue box and hit the format option. Specify the number, border, other formats and hit OK and also click OK in the rule dialogue box. The output will be displayed and the numbers greater than 900 will be highlighted in the selected color format as well.

How to use the manage rules option?

Manage rules option comes most handy when the given condition for cell formatting is to be edited or the formula used is to be changed. To learn how to do that, follow the following mentioned steps –

• Click on the Conditional formatting in the styles group and select the manage rules option which is displayed at the end of the menu drop down
• A condition formatting rules manager dialogue box appears. Various options such as add a new rule, delete rule, edit rule is shown along with the applied formula is displayed as well.
• The formula created is not constant as its value changes along with the cell references of the cell. To make it constant, write a situation in any random cell which here is US.
• Make a formula which returns true only when three cells are equal to each other.

Learn Microsoft Excel course in Singapore!

Microsoft excel is most widely used in all fields of work because of its easy understanding and dynamic outputs. To equip your firm with such Useful software, join an excel course in Singapore today!

In this Lesson, you will learn all about conditional formatting in Excel.

The video covers the following topics:

• Introduction to Conditional Formatting
• Using Formula in Conditional Formatting
• Advanced Examples (Highlight Every Nth Row, Creating Dynamic Search)

Conditional formatting is a great feature in Excel that allows you to highlight data points based on the specified conditions. It’s loaded with features and you can use it to apply a background color to a cell based on the value in it.

You can also change a lot of formatting (such as font type, font size, border, etc.) with conditional formatting.

Apart from it, conditional formatting has a rich set of icons that you can use to make your data more visual. For example, you can use it to apply tick mark and cross mark or traffic lights icons based on the cell value.

One of the most powerful aspects of conditional formatting is that you can use a custom formula to check cell value and apply formatting to it accordingly.

For example, if you want to highlight all the rows where the sales value is less than 100, you can do this by using a custom formula in conditional formatting.