spot_img

Conditional formatting in Excel

In this lesson you can learn how to use conditional formatting. Conditional formatting is a change in cell size provided that any condition. For example Excel can change color of all cells whose value is greater than 100. Conditional formatting is very useful for working with Excel, so now you’ll learn how to use them.

Basic formatting

I prepared a table with the results of the exam.

Excel Conditional Formatting Table Results Exam

You want to highlight the extreme results. The best results are above 90% mark in green, and below 30% in red. How to do it?

Conditional formatting button is at the center of the Excel ribbon.

/Excel Conditional Formatting Button Ribbon

Start with the best students. All results above 90% want to paint the green. Click on the button Conditional Formatting on the ribbon and select Greater Than….

Excel Conditional Formatting Format Rule Greater Than

A dialog box appears. The entry requirement of 90% and selecting the format of a custom list to determine the green fill.

Excel Conditional Formatting Greater Than Rule Green Fill

Similarly do with next conditions. For results less than 30% choose to fill the red. The results look as follows:

Excel Conditional Formatting Green Red Rules

Excel automatically formats all cells according to the given condition.

Advanced formatting

Other (example) formatting options presented in the following figure.

Excel Conditional Formatting Examples

I marked the first column with blue stripes. The higher the number in the cell, the longer the formatting toolbar. This option can be turned on in a way that I showed in the picture.

Excel Conditional Formatting Rule Solid Fill

The second column formatting benefited from the color scale. Only the first scale – the highest values are green and red the lowest.

Excel Conditional Formatting Green Yellow Red Rule

Results in the third column formatted sets of icons. I think this is a good way to spice up the sheet, but for use at work is not the best idea.

Excel Conditional Formatting Rule Icon Sets

There are many more possibilities. Try them all at home.

Template

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell