Conditional Formatting Excel (Formula for Beginners)

Conditional formatting is used to highlight data meeting specific criteria in different colors. This type of formatting let you apply the specific type of cell formatting that helps you in fulfilling your criteria. The formatting is mainly a color based formatting by which you can highlight, differentiate, and focused on particular data and information available in your spreadsheet. This article will help you get familiarized as how you can apply conditional formatting excel formula on given fields.

conditional formatting excel

Conditional Formatting Excel

Now, the data set has Student’s name and their marks in three subjects – Math, Science, and English. Let’s assume anyone who has scored more than 75 receive award and we want to highlight them in the different color.

Step1 – Select the data.

Step2 – Click on ‘Conditional Formatting’ from top ribbon under ‘Home’.

Step3 – Click on ‘Highlights Cells Rules’.

conditional formatting in excel

Step4 – Click on ‘Greater than’ icon. Following window will pop-up.

Step5 – In the field next to the ‘greater than’ field, you need to type 85 which is your desired value.

Step6 – Choose the drop-down box to change the color “Light red fill with a dark red text”

Conditional formatting excel formulas

Step7 – Click “OK”.

Similarly, you can apply the multiple conditional formatting formula rules on your worksheet and visualize the various trends and data pattern.

Keep in mind: Whenever you want to come out of the conditional formatting then you need to select the clear rules.

Conditional Formatting Excel 2018

The conditional formatting is used for various purpose like excel compare two columns and find duplicates in excel. After finding out the duplicate values, you can remove them easily. This is very important excel functions that you need frequently. In this kind of formatting, you can use colors, icons, and bars on different cells depending on the cell values. You can use this formatting on more than 1000 field. For example, you can use it for sales data. Using this conditional formatting excel formulas, the cells containing higher value than the desired one will be marked in different color.

Conditional Formatting with Formula

Excel comes with various predefined styles and formats that you can apply easily to your data.

  • Data Bars: If you select this kind of bars, then you will find that they are of the horizontal type. These horizontal bars are added to every cell and it looks like a bar graph.
  • Color Scales: With this preset, you can change the color of the desired cell depending on its value. Every color has its 2 or 3 variants. For example, in this table green color has three color variants. The number which has the highest value comes in dark green color options.

conditional formatting in excel tutorial

  • Icon Sets: The icon set will show you different types of icons and when you use it on your table then it will show the specific icon to every cell depending on the value.

Excel conditional formatting 1

How to use conditional formatting in Excel?

Step 1: To use the conditional formatting, you need to select the desired cell.

Step 2: Click on the conditional formatting icon and press on its drop-down menu.

Step 3: Choose the desired preset style that you want.

Step 4: Now you will find that the formatting has now applied to the selected cells.

Steps to use Conditional Formatting Excel

Let’s take another example, where you want to find whether you have sent greetings to your friends or not. To find out, you need to apply conditional formatting in excel.

  • Take the data set listed in the screenshot.

Conditional formatting in excel

  • First, you need to select the cells from A2 to A7 by dragging the corner.
  • Go home and select conditional formatting and the new rule.
  • In the rule box, find “Use a formula to determine the cells to format”.

Conditional formatting excel

  • In format values where this formula is true, you need to type A2>TODAY().
  • After doing so, you need to click on Format icon.

Format cells

  • You will find the Format Cells dialog box, where you need to choose the color option.
  • Click on Ok button to apply the formatting rule.

New formatting rule

By this way, you can apply conditional formatting in excel. You can also use conditional formatting if statement in the new rule if you want to apply any condition. Apart from the conditional formatting excel, you can also check the excel pivot table, vlookup, and other functions in our next posts.

You may also like the following Excel tutorials: