WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to use conditional formatting in excel to highlight cells

July 26, 2022
3.4K Views

Depending on the value of the cell, conditional formatting in Excel lets you highlight certain cells with a specific colour.

Highlight cell rules in excel online, 2016 and 2019

Follow the instructions below to highlight cells that are larger than a value.

1.Choose the A1–A10 range.

2.Click Conditional Formatting under the Styles group on the Home menu.

3.Click Greater Than in the Highlight Cells Rules box.

4.Select a formatting style and type in 80.

5.Click on ok.Result. The cells in Excel that are greater than 80 are highlighted.

6.Cell A1's value should be changed to 81.Result. Excel updates the format of cell A1 automatically.

7.Notably, you can also use this category (see step 3) to highlight cells that include specified text, dates (today, last week, next month, etc.), duplicates, or unique values, as well as cells that are less than, between, or equal to a value.Clear rules in excel:Do the following to remove a conditional formatting rule.Choose the A1–A10 range.

8.Click Conditional Formatting under the Styles group on the Home menu.

9.To remove rules from selected cells, select Clear Rules.

Top/Bottom in excel

1.Use the following procedures to identify cells that are above average.Choose the A1–A10 range.

2.Click Conditional Formatting under the Styles group on the Home menu.

3.Click Above Average, Top/Bottom Rules.

4.Decide on a formatting scheme.

5.Click on ok.Result. The average (42.5) is calculated by Excel, and the cells that are over this average are formatted.

Note that you can also highlight the top n things, top n percent, bottom n items, bottom n percent or cells that are below average using this category (see step 3).

Conditional formatting with formulas in excel

1.Use a formula to decide which cells to format to advance your Excel proficiency. Conditional formatting formulas must evaluate to TRUE or FALSE.Choose the A1–E5 range.

2.Click Conditional Formatting under the Styles group on the Home menu.

3.Click on new rule.

4.To choose which cells to format, click Use a formula.Type =ISODD into the formula (A1)Click OK after choosing a formatting option.

5.Result. Each odd number is highlighted in Excel.

Explanation: In the selected range, always write the formula for the upper-left cell. The formula is automatically copied to the other cells by Excel. As a result, the formula =ISODD(A2) is found in cell A2, and the same is true for cell A3 and subsequent cells.

Note: This above written article is an attempt to show you how to use conditional formatting in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                  

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.