Conditional formatting in Excel 2016

by Ali June 13, 2017 at 11:18 pm

Using conditional formatting in Excel 2016 is a very effective way to format the cell with the colors. whenever you use the conditional formatting the cells with the numbers are colored in the different styles. The coloring of the cells also has rules that s’ why we call it the Conditional Formatting. We put the condition and then apply the color. For example, color the cells between the 1 to 12. It is a condition or a rule for the formatting of the cell with numbers. Although the conditional formatting is just for highlighting the specific cells and the color formation looks very good and amazing. That is because sometimes a cell is fully colored sometimes half and sometimes quarter. In this article, I will show how to put conditions or create rules for the conditional formatting in Excel 2016.

At the first, you have to know how the conditional formatting works then you can put the condition and rules for the cells. You have to know that the conditional formatting work on the cells containing numbers like 12,43,4555, or any. Select the number then click on one of the conditional formatting options to format the cell. You will see more options after clicking on each option in the conditional formatting. First I will teach you how to apply conditional formatting with created rules and conditions. Continue reading the following paragraph for that.

Apply Conditional Formatting in Excel 2016 Without Rules/ Condition?

It depends on you that how many cells with the number you will select. One cell or many cells. After the selection of the cells go to the Home tab of Microsoft Excel and then find the Styles group and then click on the Conditional Formatting option. Look at the following picture for the instruction.

Conditional formatting in Excel 2016
Styles Group

After clicking on the Conditional formatting option a list of option will appear. The first two options are for creating the rules and putting conditional for the formatting. The three options in the middle are the effect that you can apply to the cells. you can click on any one of them. Color scale, data bars and icons set are the three groups.

Conditional formatting option list
Conditional formatting option list

The Data Bars: Select a data bar formatting to show the value in the cell. For example, the higher cell will be fully colored and the rest will be the according to the sequence. Suppose a cell value is 1000 which is higher than other cell values the cell will be fully colored and if a cell is 500 then the cell will be colored half. Look at the following picture for more clarification.

Data Bars in Microsoft excel 2016
Data Bars

Color scales: Apply a color gradient to a range of cells. the color indicates where each cell value falls within that range. Without the usages, it looks so amazing and beautiful. The following picture is an example.

Color Scale
Color Scale

Icon sets: you can choose a set of shapes whether directional shapes or other shapes to represent the value in the cell. There are many kinds of shapes that you can represent the cell value by. The shapes are directional, indicators, ratings, and shapes.

Icon set conditional formatting in excel
Icon Sets

Highlighting cells with rules

In this part, I am going to show you how to highlight the cell with the rules or putting a condition. Before I told you that from the conditional formatting options list the first and second option is for creating rules. So now I will explain some of the rules for highlighting cells.

Click on the first option then another list will appear. In this list, there are many rules that you may apply. You can see the rules in the following picture. I will explain some of the rules for you.

#1. Greater than: The first rule is greater than. Click on the rule. A small window will appear and from there you have to type the value. it will highlight the cells with the greater values than the value you type. At the first, you have to select the range or cell which you want to highlight with the (greater than) rule.

Greater than rule in Microsoft excel
Greater than rule

You can see the result of the rule in the following picture. You can see in the picture that the values higher than 750 is highlighted with a different color. In addition, you can select the highlighting color.

Microsoft excel 2016
Result of the (greater than) rule

#2. Less than:  Below the (Greater than) option there is the (less than) option which is opposite of the (greater than) option. it will highlight the cell which is lower than the written value. you may try it yourself.

#3. Text that contains: this option is very rare and very useful. It will highlight those word that contains the letter you type. Now I will tell how it works. After clicking on the option a new window will appear and from there you have to type letters that you want to highlight. The words that contain that typed letter will be highlighted automatically.

(Text that contain)Option in conditional formatting
(Text that contains)Option

More rules

Duplicate values: this option is also one of the useful rules. It will find the values that are duplicate in the range you select. First of all, select a range then click on this option. After clicking a new window will appear and from there select the duplicate option and then select the color. Click ok. You can see the duplicate values with the color that you select for the highlighting.

Duplicate values in Microsoft Excel 2016
Highlight Duplicate values

Note: You can also highlight the unique values that exist in the range you select. You can do such from the same window. Just select (Unique) instead of the duplicate option. select the color and then click on ok. The unique values are highlighted with the color you selected.

Top/bottom rules

These rules are really amazing and useful. There are different rules like you can highlight top 10 values or bottom 10 values. You can change the top value to top 3, top 4, top 7, or other. So I will explain some of the most useful ones. There is the list of the top and bottom rules in the following picture.

#1. Top Ten: The first option is top ten items. Click on the rule to open the new window. After you see the new window manage the top value and then select the color. It will highlight the top values with a color you select.

Conditional formatting in Excel 2016
(Top 10 items) option

Note: you can change the top 10 value to top 4, top 5, or any value.

#2. Bottom 10: This rule is the opposite of the top ten rule because it highlights the bottom 10 values or items. You can highlight the bottom values with this rule. Click on the rule then a new window will appear. Manage the bottom value then select the color. This much easy is to highlight the bottom values.

Bottom 10 values
Bottom 10 values

Here is a simple video of conditional formatting. Watch it on YouTube.

Conclusion (conditional formatting in Excel 2016)

Using conditional formatting in Excel 2016 is effective on the project you make. For highlighting the cells, it is so easy to use the rules from the conditional formatting in Excel 2016. Without the highlighting work, it makes your project looks so good. Using the top 10 rules make easy to find out the tops records from any range. I hope this article is helpful for all Excel users. We will be happy hearing from you all so provide us a feedback in the comment. You may ask any question regarding conditional formatting in Excel 2016.

Click here for YouTube tutorials

Click here for more Office tutorials.



Add Comment