Dealing with Duplicate Data in Excel

Author: David H. Ringstrom

See how to locate and manage duplicate data by utilizing the Conditional Formatting feature. The class also addresses summarizing data based on a single criterion and/or a partial match and applying worksheet functions to sum or count duplicates for more streamlined use.

Publication Date: July 2018

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

Topics Covered

• Learn the mouse trick that lets you quickly make a copy of an existing worksheet
• Create an in-cell list by way of Excel's Data Validation feature
• Identify duplicates in a list using Conditional Formatting
• See how to quickly duplicate a group of two or more worksheets
• Use the COUNTIF function to determine the number of times an item appears on a list
• Use Conditional Formatting to color-code your data, identify duplicates, and apply icons
• Understand why numeric data may appear in a pivot table more than once and how to correct the problem
• Use the SUMIFS function to sum values based on multiple criteria
• Use the Reapply command to refresh a list as you correct duplicates.
• Filter list entries based on colors that you apply manually or with Conditional Formatting
• Improve the integrity of spreadsheets with Excel's VLOOKUP function
• Use a wildcard character with SUMIF to summarize data based on a partial match

Learning Objectives

• Recognize and 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
• Identify which keyboard shortcuts to use to work more effectively in duplicating data
• Recognize which feature enables you to transform numbers stored as text into values
• Describe the COUNTIF function
• Recognize the Reapply command
• Identify the arguments for VLOOKUP function
• Recognize how to apply formatting to multiple ranges without repeatedly clicking on Format Painter
• Describe where to locate Excel commands within Excel
• Differentiate SUMIF's arguments

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