Course Description
In this course, Excel expert David Ringstrom, CPA, shows you how to find and manage duplicate data within your spreadsheets. He explains how to identify duplicates by way of the Conditional Formatting feature; summarize data based on a single criterion and/or a partial match; and apply worksheet functions, such as SUMIF, COUNTIF, and MATCH, to sum or count the instances of duplicates.
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 course.
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:
Practitioners seeking to identify and manage duplicate data in Excel.
Level:
Intermediate
Topics covered:
- Copying dates within an Excel worksheet in the manner you want
- Crafting self-resizing formulas with the new Spilled Range Operator in in Excel 2021 and Microsoft 365
- Creating a Data Validation rule that prevents users from entering the same data more than once in a list
- Duplicating columns, rows, or cells within an Excel worksheet
- Duplicating Excel worksheets in two different ways
- Eliminating duplicates from a list with just a few mouse clicks.
- Filtering list entries based on colors you apply manually or with Conditional Formatting
- Identifying duplicates in a list using Conditional Formatting
- Learning the mouse trick that lets you quickly make a copy of an existing worksheet
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet
- Removing duplicates from a list with the new UNIQUE function
- Using the COUNTIF function to determine the number of times an item appears in a list
Learning objectives:
- Apply the Data Validation feature to create an in-cell list
- Define how to visually identify duplicate records with the Conditional Formatting feature
- Identify which function can be used to improve the integrity of spreadsheets
Review our course policies and procedures page for further information