Dealing with Duplicate Data in Excel (Completed)

Date: Wednesday, May 31, 2017
Instructor: David H. Ringstrom
Begin Time:  12:00pm Pacific Time
1:00pm Mountain Time
2:00pm Central Time
3:00pm Eastern Time
CPE Credit:  2 hours for CPAs

Excel expert David H. Ringstrom, CPA, focuses on showing you how to identify and manage duplicate data within your spreadsheets in this live presentation. A variety of topics will be covered, including identifying duplicate data visually by way of the Conditional Formatting feature, using a pivot table to determine how many duplicates are in a list, and applying worksheet functions like SUMIF or COUNTIF to sum or count instances of duplicates.

In addition, David demonstrates how to use the MATCH worksheet function to help you identify differences in two different lists, and how VLOOKUP stops searching for duplicates after an initial match in found within a list. He even shows you how to use Excel’s Data Validation feature to create a rule that ensures users can’t enter the same data more than once in a given area of a spreadsheet.

David demonstrates techniques by using the version of Excel in use by the majority of attendees for this presentation. Typically, this means he teaches from Excel 2010 or 2013, but his handouts identify differences in Excel 2007, 2010, 2013, and 2016 when needed. His webcasts are fast-paced, and attendees often are surprised at the amount of ground he covers in a session. He welcomes participants’ questions, so come ready to pick his brain. David’s detailed handouts, with numbered steps, serve as reference material you can utilize going forward. He also provides an Excel workbook that includes a majority of the examples he uses to demonstrate techniques during the presentation.

Who Should Attend
Practitioners seeking to identify and manage duplicate data in Excel.

Topics Covered

  • Create a data validation rule that prevents users from entering the same data more than once in a list
  • Eliminate duplicates from a list with just a few mouse clicks
  • Filter list entries based on colors that you apply manually or with Conditional Formatting
  • Identify duplicates in a list using Conditional Formatting
  • Improve the integrity of spreadsheets with Excel's VLOOKUP function
  • Learn how VLOOKUP stops looking after it finds an initial match within a list
  • Learn the mouse trick that lets you quickly make a copy of an existing worksheet
  • Understand why numeric data may appear in a pivot table more than once, and how to correct the problem
  • Use a wildcard character with SUMIF to summarize data based on a partial match
  • Use Conditional Formatting to color-code your data, identify duplicates, and apply icons
  • Use Excel's COUNTIF function with Conditional Formatting to highlight the second and beyond instances of entries on a list
  • Use the COUNTIF function to determine the number of times an item appears on a list
  • Use the MATCH worksheet function to identify differences between two different lists
  • Use the Reapply command to refresh a list as you correct duplicate instances
  • Use the SUMIF function to summarize data based on a single criterion
  • See how to instantly determine the number of duplicates in a list with a pivot table

Learning Objectives

  • Recognize and apply the Advanced Filter feature and the Remove Duplicates feature
  • Describe how to visually identify duplicate records with the Conditional Formatting feature
  • Identify how to use a pivot table to quickly ascertain how many times duplicates appear within a list

Level
Intermediate

Instructional Method
Group: Internet-based

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

Program Prerequisites
Experience Working with Excel Spreadsheets.

Advance Preparation
None

">
 Chat — Books Support