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
Instructor
David H. Ringstrom
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches over 200 webinars each year. His Excel courses are based on over 30 years of consulting and teaching experience. David’s catchphrase is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. He's currently writing a book titled "Exploring Excel's Hidden Treasures". To learn more about David, you can view his
LinkedIn profile and follow him on
Facebook or
Twitter (@excelwriter).