Inside Excel's Conditional Formatting Feature

Author: David H. Ringstrom

Conditional Formatting is one of the most powerful, yet sometimes, the most frustrating features in Microsoft Excel. In this presentation, Excel expert David H. Ringstrom, CPA, delves deep into the nuances of Conditional Formatting. Techniques David covers range from simple color-coding to sophisticated techniques, such as highlighting every other row or formatting a set of cells based on amounts in other cells.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David will draw your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Office 365 is a subscription-based product that is sometimes updated as often as monthly with new features. The perpetual licensed versions of Excel have year numbers, such as 2019, 2016, and so on, have feature sets frozen in time and do not receive updates with new features.

Publication Date: January 2019

Designed For
Users who work with large data sets in Excel and want to learn ways to streamline their filtering and formatting tasks.

Topics Covered

• Filtering lists of data based on cell icons applied via Conditional Formatting
• Using the Reapply command to remove items that are no longer duplicated within a list
• Crafting custom Conditional Formatting rules that highlight alternating sets of rows, such as every other five rows
• Formatting certain words within a column of text with Conditional Formatting
• Removing Conditional Formatting when it's no longer needed within a spreadsheet
• Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting
• Illustrating numbers within a list by using the Icon Sets Conditional Formatting
• Avoiding frustration by understanding the nuances between Enter and Edit modes
• Gaining control of long lists of data by filtering instead of sorting
• Using Conditional Formatting to color-code your data, identify duplicates, and apply icons
• Using Conditional Formatting to identify unlocked cells into which data can be entered
• Controlling two or more Conditional Formatting rules by way of the Conditional Formatting Rules Manager

Learning Objectives

• Identify which menu the Conditional Formatting command appears on in Excel
• Describe the purpose of the Reapply command in Excel
• Identify and apply which Conditional Formatting feature fills cells with color sized commensurate with the underlying cell values
• Differentiate aspects of Excel's Quick Analysis Feature
• Describe the COUNTIF function
• Recognize and apply the benefits the Table feature offers

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with Excel Spreadsheets.

Advance Preparation
None