|
|
| Accelerate Excel: Dealing with Duplicate Data (170918N) |
Course CPE: 2 hours
Fields of Study: Practice Management–2 hrs
Program Level: Intermediate
Prerequisites: Previous training or research on subject matter being taught. Such persons are often at a mid-level within the organization, with operational and/or supervisory responsibilities.
Advanced Preparation: None
Delivery Method: Self-Study |
Item
Number: 17PPM0918
Shipping Weight: 0lbs. 0oz. |
| Price:
$147.00 |
|
|
|
|
Recording Date: September 18, 2017
Presenter: David Ringstrom
Program Description:
If you’re frustrated with duplicates showing up frequently in your spreadsheets and don’t know how to identify and manage them, you’ll want to attend this live webcast presented by Excel expert David Ringstrom, CPA. He covers a variety of topics, 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 shares information and tips on how you can:- Identify disparities between two different lists.- Create a data validation rule that ensures users can’t enter the same data more than once in a given area of a spreadsheet.- Make a copy of an existing worksheet by implementing an easy mouse trick.- Use a wildcard character to summarize data based on a partial match.- Refresh a list as you correct instances of duplicates. . . . and much more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 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 webcast.
Topics Covered:
- Creating a data validation rule that prevents users from entering the same data more than once in a list.
- Eliminating duplicates from a list with just a few mouse clicks.
- Filtering list entries based on colors that you apply manually or with Conditional Formatting.
- Identifying duplicates in a list using Conditional Formatting.- Improving the integrity of spreadsheets with Excel’s VLOOKUP function.
- Learning how VLOOKUP stops looking after it finds an initial match within a list.
- Seeing how to instantly determine the number of duplicates in a list with a pivot table.
- Learning the mouse trick that lets you quickly make a copy of an existing worksheet.
- Understanding why numeric data may appear in a pivot table more than once, and how to correct the problem.
- Using a wildcard character with SUMIF to summarize data based on a partial match.
- Using Conditional Formatting to color-code your data, identify duplicates, and apply icons.
- Using Excel’s COUNTIF function with Conditional Formatting to highlight the second and beyond instances of entries on a list.
- Using the COUNTIF function to determine the number of times an item appears on a list.
- Using the MATCH worksheet function to identify differences between two different lists.
- Using the Reapply command to refresh a list as you correct duplicate instances.
- Using the SUMIF function to summarize data based on a single criterion.
After completing this course, you will be able to:
- Select and apply the Advanced Filter feature and the Remove Duplicates feature
- Identify and define how to visually identify duplicate records with the Conditional Formatting feature
- Differentiate how to use a pivot table to quickly ascertain how many times duplicates appear within a list
For more information regarding refund or concerns, please contact our offices at (800) 677-2009.
The Consultants’ Training Institute (CTI) is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
|
|
|
