Maximize Excel: Conditionally Formatting Data​

 

Date: 12/02/2024
Time: 12:00 PM - 2:00 PM EST
CPE Credit: 2 hours

Conditional Formatting—one of the most powerful features in Excel—is sometimes the most frustrating. In this enlightening presentation, Excel expert David H. Ringstrom, CPA, delves deep into the nuances of Conditional Formatting. David explains a variety of techniques, from simple color-coding to more sophisticated techniques, such as highlighting every other row, formatting a set of cells based on amounts in other cells, and more.
 
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 webcast.
 
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.

Topics Covered

• Preserving key formulas using hide and protect features.
 
• Using the Reapply command to remove items that are no longer duplicated within a list.
 
• Jump-starting data visualization with the Quick Analysis feature.
 
• Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
 
• Pasting a table as a raw list elsewhere in a workbook.
 
• Combining the COUNTIF function with Conditional Formatting to highlight the second and further instances of entries on a list.
 
• Using Conditional Formatting to identify unlocked cells into which data can be entered.
 
• Highlighting amounts greater than a specified amount with Conditional Formatting.
 
• Formatting certain words within a column of text with Conditional Formatting.
 
• Identifying cells that contain specific text.
 
• Using a custom number format to hide the underlying cell values when applying the Data Bars conditional formatting to one or more cells.
 
• Explaining how to use the MOD function in Excel to return the remainder from a division operation.

Learning Objectives:

• Recognize the Conditional Formatting feature that enables you to create a heat map.
• Name the submenu beneath with Filter by Cell Icon appears.
• Recall what "protect" argument of the CELL function returns if a cell being referenced is unlocked