Dealing with Duplicate Data in Excel

Author: David H. Ringstrom

Excel expert David Ringstrom, CPA, shows you how to find and manage duplicate data within your spreadsheets in this enlightening course. 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 course 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.

Publication Date: December 2023

Designed For
Practitioners seeking to identify and manage duplicate data in Excel.

Topics Covered

• 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
• Crafting self-resizing formulas with the new Spilled Range Operator 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
• Duplicating the formatting of one chart into a second chart
• Eliminating duplicates from a list with just a few mouse clicks
• Employing pivot tables to count the number of times an item appears in a list
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify
• Exploring the risks and benefits of participating in the free Microsoft Office Insider program
• Filtering list entries based on colors you apply manually or with Conditional Formatting

Learning Objectives

• Recognize and apply the Data Validation feature to create an in-cell list
• Describe how to visually identify duplicate records with the Conditional Formatting feature
• Identify which function can be used to improve the integrity of spreadsheets
• Identify under which menu within Excel's Home tab the Move and Copy Sheet command appears
• Identify the mouse action that reveals a hidden menu when you drag one or more columns or rows

Level
Intermediate

Instructional Method
Self-Study

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

Program Prerequisites
Experience Working with Excel Spreadsheets.

Advance Preparation
None