Maximize Excel: Dealing with Duplicates
Presented by David H. Ringstrom, CPA
 
Duration: 100 minutes

In this presentation, CPA and author of "Microsoft 365 Excel for Dummies" David Ringstrom explores practical techniques for identifying, removing, and preventing duplicate data in Excel. You will learn efficient ways to duplicate and copy worksheets and ranges, remove duplicate records using Excel’s built-in tools and modern functions, summarize duplicate values with COUNTIF and SUMIF formulas, and visually highlight duplicates with conditional formatting. The session concludes with strategies for preventing duplicate entries through data validation, helping you maintain cleaner and more reliable spreadsheets.
 
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 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.
 
Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.
 
Who should attend:
Excel users who work with lists, reports, or imported data and need reliable methods to find, summarize, and prevent duplicate entries.
 
Topics typically covered:
 
• Duplicating Excel worksheets in two different ways.
 
• Removing duplicates from a list with the UNIQUE function in Excel 2021 and later.
 
• Counting occurrences of an item in a list using the COUNTIF function.
 
• Using SUMIF to total values for specific matching criteria.
 
• Removing Conditional Formatting rules when they are no longer needed.
 
• Using Excel’s Advanced Filter feature to identify duplicates within a list.
 
• Eliminating repeated values while preserving unique records.
 
• Learning the mouse trick that lets you quickly make a copy of an existing worksheet.
 
• Applying formatting to multiple locations within a worksheet by way of the Format Painter feature.
 
• Copying dates within an Excel worksheet in the manner you want.
 
• Resolving situations where data appears more than once within a PivotTable.
 
• Enabling worksheet protection so that users cannot inadvertently remove Data Validation.
 
Learning objectives:
 
• State which Data Validation Error Alert prevents users from overriding the data validation rule assigned to a cell.
 
• State the arguments used within VLOOKUP.
 
• State what SUMIF returns if a match cannot be found.