Use Conditional Formatting in Excel with Formulas to Highlight Errors, Blanks and Duplicatesby Avantix Learning Team | Updated April 7, 2021 Show
Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) Conditional formatting is a great tool in Excel and if you combine it with formulas, you can do some amazing things. In this article, we'll use some simple examples to show the power of conditional formatting. Conditional Formatting appears on the Home tab in the Ribbon in the Styles group. You can apply conditional formatting using a formula and a new rule. Below is a sample of a rule with a formula: Recommended article: 15 Microsoft Excel Keyboard Shortcuts to Speed Up Formatting Do you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses > Highlighting errors using conditional formattingYou can use conditional formatting to highlight errors in a range of cells. In example below, there are errors in column C: To highlight the errors using conditional formatting:
Highlighting blanks using conditional formattingYou can use conditional formatting to highlight blanks in a range of cells. In the example below, there are blanks in column C: To highlight the blanks using conditional formatting:
Highlighting duplicates using conditional formattingYou can also use conditional formatting to highlight duplicates in a range of cells. In the example below, there are duplicates in column A: To apply conditional formatting to highlight the duplicates:
These are just a few examples of conditional formatting with formulas. You can use all kinds of other formulas in rules to format cells. Subscribe to get more articles like this oneDid you find this article helpful? If you would like to receive new articles, join our email list. More resourcesHow to Use Flash Fill in Excel (4 Ways with Shortcuts) How to Freeze Row and Column Headings in Excel Worksheets 3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells How to Replace Blank Cells with a Value from the Cell Above in Excel Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways) Related coursesMicrosoft Excel: Intermediate / Advanced Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools Microsoft Excel: Introduction to Visual Basic for Applications (VBA) VIEW MORE COURSES > Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you. Copyright 2022 Avantix® Learning Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners. Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at Which tab is used for conditional formatting?On the Home tab, click Conditional Formatting. Point to Highlight Cells Rules, and then click Text that Contains. Type the text that you want to highlight, and then click OK.
Which group of Home tab contains the format option in Excel?The Home tab contains the most commonly used commands and options from the Format, Design, Filter, and Report groups. It is shown in the following image. The Format group displays a menu of output formats and buttons to toggle between creating a report (default) or a chart.
Where is conditional formatting in Excel?Find cells that have conditional formatting. Click any cell. ... . On the Edit menu, click Find, and then click Go To.. Click Special, and then click Conditional formats.. If you want to locate only cells with the specific conditional format of the cell that you selected in step 1, under Options, click Same.. |