Excel Accountant: Dealing with Duplicates

Learning Objectives

• To recognize duplicating Excel worksheets and Mouse Trick for the same.
• To Define how to apply the Advanced Filter feature and the Remove Duplicates feature.
• To recognize the UNIQUE function and other features available only on Office 365 only.
• To identify how to use a pivot table to quickly ascertain how many times duplicates appear within a list.
• To explore Data validation tricks for preventing duplicate entries and unlocking validation cells.

Course Description

Identifying duplicate data and managing it within spreadsheets has always been a challenge for most of us, especially in comparatively complicated spreadsheets. Excel expert David Ringstrom, CPA brings here this presentation educating on how to identify such duplicate data and manage it within the spreadsheets, which will certainly prove beneficial to a huge extent. 

In this Online CPE Excel Course, David has covered a variety of subjects, including the identification of 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.

Excel expert David will demonstrate each technique at least twice, firstly via a step-wise PowerPoint presentation and secondly in a subscription-based MS Office 365 version of Excel also he will draw your attention to differences in the older versions of Excel (i.e. 2019, 2016, 2013, etc.) in his presentation and detailed handouts. He also provides an Excel workbook that includes most of the examples he uses during the webcast.

David brings to your attention the subscription-based product 'Office 365' which provides new feature updates as often as monthly as against the perpetual licensed versions of Excel such as Excel 2019, Excel 2016, and so on, which do not update and include the latest features.

Key Topics Covered: 

• Using Fill Handle to Populate Dates
• Advanced Filter - Duplicates in Place
• Using SORT/UNIQUE Together
• Office Insider Program
• Resolving Pivot Table Duplicate Values
• Conditional Formatting – Duplicates
• Data Validation to Prevent Duplicate Entries
• VLOOKUP Function

Overview

• Duplicating Excel Worksheets

  4 mins
• Mouse Trick to Duplicate Worksheets

  6 mins
• Format Painter

  14 mins
• Remove Duplicates Feature

  34 mins
• Using SORT/UNIQUE Together

  48 mins
• COUNTIF/COUNTIFS Introduction

  65 mins
• Data Validation - Prevent Duplicate Entries

  82 mins
• VLOOKUP Introduction

  95 mins
• SUMIFS Introduction

  101 mins