{"id":1153,"date":"2022-11-30T16:16:37","date_gmt":"2022-11-30T16:16:37","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=1153"},"modified":"2024-02-15T16:50:52","modified_gmt":"2024-02-15T16:50:52","slug":"how-to-apply-conditional-formatting-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-apply-conditional-formatting-in-excel\/","title":{"rendered":"How to apply Conditional Formatting in Excel?"},"content":{"rendered":"

Conditional Formatting in Excel is a powerful tool that allows users to visually highlight important trends, patterns, and outliers in their data. By applying formatting rules based on specified conditions, such as value ranges or specific criteria, users can quickly identify and analyze key insights within their spreadsheets. Whether it’s highlighting cells with the highest or lowest values, identifying duplicates, or flagging data that meets certain criteria, Conditional Formatting empowers users to make informed decisions and effectively communicate their findings. With its versatility and ease of use, Conditional Formatting in Excel is an essential feature for anyone looking to gain deeper insights from their data.<\/p>\n

How to use Excel’s conditional formatting? <\/strong><\/h2>\n

There are several ways you can apply conditional formatting in your worksheet.<\/p>\n

Conditional Formatting from the HOME menu tab<\/strong><\/h2>\n

\"Conditional<\/p>\n

Highlight cells based on certain value\/text<\/strong><\/p>\n

\"Conditional<\/p>\n

Highlight cells that have GPA of 3.50 and higher<\/p>\n

Step 1:<\/strong> Select the GPA column i.e. Col B and select the Highlight Cells Rules<\/strong> then Select Greater Than\u2026<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 2:<\/strong> Follow the next step to highlight GPA values greater than 3.50<\/p>\n

\"Conditional<\/p>\n

How to clear or remove conditional formatting?<\/strong><\/h2>\n

Step 1:<\/strong> Select range from B2:B12<\/p>\n

\"Conditional<\/p>\n

Step 2:<\/strong> Select \u2018Clear Rules\u2019 and then select Clear Rules from Selected Cells. You may choose to remove the conditional formatting from the entire sheet by selecting the second option.<\/p>\n

\"Conditional<\/p>\n

Highlight a Text<\/strong><\/p>\n

Highlight here all FAIL from the Result column.<\/p>\n

Step 1:<\/strong> Select the Result column i.e. Col C and then select the \u2018Text that Contains<\/strong>\u2026.\u2019 rule<\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>Type Fail<\/strong> in the box shown below and select Light Red Fill with Dark Red Text<\/strong> from the other box and click OK.<\/strong><\/p>\n

\"Conditional<\/p>\n

Find the Duplicate value i.e. same GPA number appears multiple times<\/strong><\/h2>\n

Step 1: <\/strong>Select column B and click on Duplicate Values.<\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>Follow the steps as shown in the picture below.<\/p>\n

\"Conditional<\/p>\n

Step 3:<\/strong> Here GPA 3.45 and 4.50 appeared multiple times and were highlighted in dark red.<\/p>\n

\"Conditional<\/p>\n

You can play around with other Cell Highlighting Rules<\/p>\n

\"Conditional<\/p>\n

Highlight Top\/Bottom 10 (10%) <\/strong><\/p>\n

Step 1: <\/strong>Select the column you want to highlight, click on Top\/Bottom Rules<\/strong> and then access the Top 10 Items (or%) \/ Bottom 10 Items (or%).<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>The dialogue box will open depending on what you choose. Suppose you clicked on the Top 10 Items<\/strong>; a dialogue box would then appear. Now follow the steps below and click OK<\/strong>.<\/p>\n

\"Conditional<\/p>\n

This only functions for cells that contain a numeric value. If you choose to highlight the Top 10 items or the Bottom 10 Items in a dataset with fewer than 10 cells, all of the cells will be highlighted.<\/p>\n

Highlight Errors\/Blanks<\/strong><\/p>\n

You can easily locate and highlight cells that contain mistakes or are empty by using conditional formatting in Excel.<\/p>\n

Let’s say we have the dataset as displayed below which has a blank cell (A6) and errors (A5 & A7)<\/p>\n

Step 1:<\/strong> Select column A, and click on Conditional Formatting >> New Rule.<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>Select Use a formula to determine which cells to format<\/strong> and in the area labeled “Edit the Rule Description<\/strong>,” enter the formula shown below. Now click of Format.<\/p>\n

=OR(ISBLANK(A1),ISERROR(A1))<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 3: <\/strong>To highlight blank or incorrect cells, choose your desired format and click OK. <\/strong>All the cells that are empty or contain errors would be instantly highlighted.<\/p>\n

\"Conditional<\/p>\n

Creating Heat Map<\/strong><\/p>\n

A heat map can be made, with the cell with the greatest value being colored green and the color shifting from green to red as the value lowers. Values in the GPA column range from 2 to 5. Green is assigned to 5 while red is assigned to 2.<\/p>\n

Select the GPA column and click Conditional Formatting >> Color Scales and chose one of the color schemes. Immediately the formatting will be applied in your datasheet.<\/p>\n

\"Conditional<\/p>\n

Highlight Every Other Row Column<\/strong><\/p>\n

To highlight every other row and columns, follow the steps below.<\/p>\n

Step 1: Select A2-C12<\/strong> and click on Conditional Formatting >> New Rule. <\/strong><\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>Select Use a formula to determine which cells to format<\/strong> and in the area labeled “Edit the Rule Description<\/strong>,” enter the formula shown below. Now click of Format.<\/p>\n

=ISODD(ROW())<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 3:<\/strong> Choose your desired format and click OK. <\/strong>It will highlight the alternate rows in the data set.<\/p>\n

\"Conditional<\/p>\n

A similar method may be applied in several situations. Simply apply the appropriate formula in the conditional formatting. Here are a few instances:<\/p>\n

=ISEVEN(ROW())<\/strong> highlights alternate even rows.<\/p>\n

=ISODD(ROW())<\/strong> will be used to highlight alternative add rows.<\/p>\n

=MOD(ROW(),3)=0<\/strong> will be used to highlight each third row.<\/p>\n

The various conditional formatting methods in Excel that visually improve the presentation of a dataset include data bars, color scale, and icon sets. In these methods, data is represented by bars, color combinations, icons etc.<\/p>\n

Apply Data Bars Conditional Formatting<\/strong><\/p>\n

Follow the steps bellow for applying data bars conditional formatting.<\/p>\n

Step 1<\/strong>: Select GPA column and click on Conditional Formatting >> Data Bars<\/strong> and select any of the Gradient Fill.<\/strong><\/p>\n

\"Conditional<\/p>\n

Step 2: <\/strong>Data bar is applied on the selected column\u2019s data.<\/p>\n

\"Conditional<\/p>\n

Apply Color Scales Conditional Formatting<\/strong><\/p>\n

Follow Step 1 from Apply Data Bars and then click on Color Scale and chose any of the color scale you want. It will immediately be applied to your worksheet data.<\/p>\n

\"Conditional<\/p>\n

Apply Icon Sets Conditional Formatting<\/strong><\/p>\n

Follow Step 1 from Apply Data Bars and then click on Icon Sets<\/strong> and chose any sets of icons you want based on the data on your worksheet. It will immediately be applied to your worksheet data.<\/p>\n

\"Conditional<\/p>\n

Application of\u00a0<\/strong>Conditional Formatting in Excel<\/h2>\n