Excel Agility: Dealing with Duplicates
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Business Skills, Business Administration, Taxation and Accounting, Finance
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
For those users who don’t know how to deal with duplicates in their Excel data, this enlightening course will be of great benefit. Excel expert David Ringstrom, CPA, 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 Excel 2016. He draws your attention to 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.
Your Benefits of Attending:
- Using the Reapply command to remove items that are no longer duplicated within a list.
- Using the MATCH worksheet function to identify differences between two different lists.
- Employing pivot tables to count the number of times an item appears in a list.
- Duplicating the formatting of one chart into a second chart.
- Copying dates within an Excel worksheet in the manner you want.
- Resolving situations where data appears more than once within a pivot table.
- Using Excel’s Advanced Filter feature to identify duplicates within a list.
- Eliminating duplicates from a list with just a few mouse clicks.
- Duplicating columns, rows, or cells within an Excel worksheet.
- Unlocking Data Validation cells in anticipation of protecting a workbook.
- Creating a Data Validation rule that prevents users from entering the same data more than once in a list.
Learning Objectives:
- Recall how to employ the MATCH function to identify differences in two different lists.
- Identify how to use a pivot table to quickly ascertain how many times duplicates appear within a list.
- Define how to visually identify duplicate records with the Conditional Formatting feature.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience Working with Excel Spreadsheets Is Recommended
Advance Preparation:
None
Introduction
Excel Versions 00:00:24
Duplicating Excel Worksheets 00:03:03
Mouse Trick to Duplicate Worksheets 00:05:06
Duplicating Columns, Rows, or Cells 00:07:29
Format Painter 00:13:59, 00:16:23
Copy Formatting to a Chart 00:18:46
Copying Dates 00:29:04
Remove Duplicates Feature 00:33:48
Advanced Filter - Duplicates in Place 00:37:14
UNIQUE Function (Office 365 Only) 00:44:15
=UNIQUE 00:44:31
Using SORT/UNIQUE Together 00:48:58
=SORT 00:49:04
Office Insider Program 00:51:14
Resolving Pivot Table Duplicate Values 00:52:00
Resolving Pivot Table Duplicate Values 00:54:42
Counting Text Instances in a Pivot Table 01:02:40
Create a Pivot Table 01:02:52
Introduction to COUNTIF/COUNTIFS 01:04:31
=COUNTIF 01:05:01
=COUNTIFS 01:05:06
Conditional Formatting - Duplicates 01:10:04
Filter by Color 01:13:01
The Reapply Command 01:15:08
Remove Conditional Formatting 01:17:06
Data Validation - Prevent Duplicate Entries 01:20:13
Unlocking Data Validation Cells 01:26:20
Protecting the Worksheet 01:28:09
Comparing Two Lists 01:29:30
=MATCH 01:29:45
Introduction to VLOOKUP 01:35:31
=VLOOKUP 01:35:37
SUMIF Function 01:40:08
=SUMIF 01:40:16
Introduction to SUMIFS 01:43:23
=SUMIFS 01:43:30
- Arrange All Command 00:06:33
- Chart Area 00:19:41
- Conditional Formatting 01:17:07
- Control Key (CTRL) 00:05:19
- COUNTIF 01:05:01
- COUNTIFS 01:05:02
- Format command 00:04:30
- Format Command 00:04:30
- Format Painter 00:13:59,00:16:23
- Hidden menu 00:07:30
- MATCH 01:29:45
- Move or Copy Command 00:04:31
- Pivot Table 00:52:38
- Plot Area 00:19:57
- SUMIF 01:40:08
- UNIQUE Function 00:44:54
- VLOOKUP 01:35:37
Arrange All Command: The Arrange All command provides you with a number of options for arranging multiple workbooks on screen simultaneously, depending on your particular needs. You can access the Arrange All command by selecting VIEW?Arrange All.
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.
Chart Area: The white outside margin of the chart
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)
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home 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.
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.
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.
Plot Area: The plot area in a chart or graph in spreadsheet programs such as Excel and Google Sheets refers to the area of the chart that graphically displays the data being charted.
Remove Duplicates: This feature first appeared in Excel 2007. This Data tab command allows you to reduce a list of items to a list of unique constituents. This action required the Advanced Filter command in Excel 2003 and earlier.
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.
UNIQUE: =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.
