Excel Agility: Dealing with Duplicates
Please see below for additional instructions and information regarding this program.
If you’re tired of dealing with duplicates that show up frequently in your spreadsheets and you don’t know how to identify and manage them, you won’t want to miss this live webcast presented by Excel expert David Ringstrom, CPA. He covers a variety of topics, including identifying duplicate data visually, determining differences between two different lists using the MATCH function, applying the SUMIF function to summarize data based on a single criterion, and much more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) 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.
Microsoft 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
• Duplicating columns, rows, or cells within an Excel worksheet.
• Using the Reapply command to remove items that are no longer duplicated within a list.
• Using the COUNTIF function to determine the number of times an item appears in a list.
• Removing duplicates from a list with the new UNIQUE function.
• Filtering list entries based on colors you apply manually or with Conditional Formatting.
• Identifying duplicates in a list using Conditional Formatting.
• Learning the mouse trick that lets you quickly make a copy of an existing worksheet.
• Copying dates within an Excel worksheet in the manner you want.
• Nesting dynamic array functions such as SORT and UNIQUE together.
• Applying formatting to multiple locations within a worksheet by way of the Format Painter feature.
• Resolving situations where data appears more than once within a pivot table.
• Resolving situations where data appears more than once within a pivot table.
• Define how to visually identify duplicate records with the Conditional Formatting feature.
• Identify which command allows you to refresh a list as you correct duplicates.
• Apply the MATCH function to identify differences in two different lists.
- Please Ask Questions 00:01:47
- Excel Versions 00:03:16
- Duplicating Excel Worksheets 00:04:06
- Mouse Trick to Duplicate Worksheets 00:07:20
- Duplicating Columns, Rows, or Cells 00:09:18
- Format Painter 00:14:06
- Copy Formatting to a Chart 00:17:32
- Using Fill Handle to Populate Dates 00:23:13
- Remove Duplicates Feature 00:27:30
- Advanced Filter - Duplicates in Place 00:30:18
- UNIQUE Function (MicroOffice 365 Only) 00:34:49, 00:39:32
- Using SORT/UNIQUE Together 00:44:59
- Office Insider Program 00:47:59
- Resolving Pivot Table Duplicate Values 00:50:22
- Resolving Pivot Table Duplicate Values (cont’d) 00:54:30
- Counting Text Instances in a Pivot Table 00:56:55
- COUNTIF/COUNTIFS Introduction 01:03:20
- Conditional Formatting - Duplicates 01:07:47
- Filter by Color 01:11:23
- The Reapply Command 01:13:20
- Remove Conditional Formatting 01:15:05
- Data Validation - Prevent Duplicate Entries 01:16:43
- Unlocking Data Validation Cells 01:29:29
- Protecting the Worksheet 01:24:35
- Comparing Two Lists 01:27:06
- VLOOKUP Introduction 01:29:29
- VLOOKUP Duplicate Data Trap 01:33:01
- SUMIF Function 01:35:52
- Introduction to SUMIFS 01:37:45
- Presentation Closing 01:41:00
- Advanced Filter 00:30:18
- Cell 00:09:20
- Chart Area 00:18:18
- Column 00:09:19
- Conditional Formatting 00:36:37, 01:07:51
- Control Key (CTRL) 00:24:47
- COUNTIF 01:03:22
- COUNTIFS 01:03:23
- Data Validation 01:16:54
- Fill Handle 00:23:17
- Format Command 00:04:38, 00:06:00
- Format Painter 00:14:07
- LET Function 00:48:10
- MATCH 01:27:23
- Move or Copy Command 00:04:44
- Office 365 00:03:18
- Pivot Table 00:34:56, 00:50:29
- Row 00:09:19
- Sort Function 00:45:01
- SUMIF 01:35:57
- SUMIFS 01:37:46
- Text to Columns Wizard 00:54:44
- UNIQUE Function 00:39:45
- VLOOKUP 01:29:34
- XLOOKUP 01:33:19
Advanced Filter: Use an Advanced Filter in Excel to create a list of unique items, or to extract specific items to a different worksheet.
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Chart: In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that can bring more understanding to the data than just looking at the numbers. A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts.
Chart Area: The white outside margin of the chart
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Control Key (CTRL): A Control key is a modifier key which, when pressed in conjunction with another key, performs a special operation (for example, Ctrl+C)
COUNTIF: Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition. For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.
COUNTIFS: The COUNTIFS function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. The COUNTIFS function allows you to stipulate multiple criteria, hence the plural.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
Fill Handle: The little notch in the bottom right-hand corner of the selected cell or block of cells. You can drag the fill handle to copy the contents to other cells, double-click to copy contents down a column, or right-drag to reveal a hidden context menu.
Format Command: When we format cells in Excel, we change the appearance of a number without changing the number itself.
Format Painter: The Format Painter copies formatting from one place and applies it to another. For example, if you have written text in Word, and have it formatted using a specific font type, color, and font size you could copy that formatting to another section of text by using the Format Painter tool.
LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.
Move or Copy Command: When you move or copy a cell, Excel moves or copies the cell, including formulas and their resulting values, cell formats, and comments.
Office 365: Office 365 combines the familiar Microsoft Office desktop suite with cloud-based versions of Microsoft's next-generation communications and collaboration services—including Microsoft Exchange Online, Microsoft SharePoint Online, Office for the web, and Microsoft Skype for Business Online—to help users be productive .
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
Sort Function: =Sort - Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
UNIQUE Function: =UNIQUE - The Excel UNIQUE function returns a list of unique values in a list or range.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra 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. To learn more about David, you can view his LinkedIn profile and follow him on ... View Full Profile
Aurora Training Advantage 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.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or firstname.lastname@example.org.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.