Excel 2016: Managing and Analyzing Data

3h 1mIntermediate2025-02-11

Authors

Dennis Taylor

Excel expert with 25+ years of experience

Course details

Large amounts of data can become unmanageable fast. But with the data management and analysis features in Excel 2016, you can keep the largest spreadsheets under control. In this course, Dennis Taylor shares easy-to-use commands, features, and functions for maintaining large lists of data in Excel. He covers sorting, adding subtotals, filtering, eliminating duplicate data, and using Excel's Advanced Filter feature and specialized database functions to isolate and analyze data. With these techniques, you'll be able to extract the most important information from your data, in the shortest amount of time.

Learning objectives
Recognize the default settings in multiple-key sorting.
Explain the benefits of a custom list.
Name three different ways you can sort in a sort dialog box.
Recall the function used to randomly sort a list.
Identify the filters that can be applied to cells containing dates.
Review appropriate uses of the Advanced Filter.

Skills covered

Data Resource ManagementMicrosoft OfficeOffice 365SpreadsheetsMicrosoft ExcelDatabase ManagementData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)

Concepts

0. Introduction

01 - Welcome
02 - Exercise files

1. Data Preparation

03 - Structure data for optimum usage
04 - Converting lists to tables
05 - Using formulas in tables

2. Sorting Data

06 - Sorting concepts and sort menu options
07 - Sorting from menu icons
08 - Multiple-key sorting
09 - Sorting based on the order of data in custom lists
10 - Sorting by cell color, font color, or cell icon
11 - Sorting columns (sorting left-to-right)
12 - Sorting data in random order

3. Creating Automatic Subtotals in Sorted Lists

13 - Setting up single and multiple-level subtotals
14 - Expanding and collapsing with grouping and outlining symbols

4. Filtering Data

15 - Single and multiple-column filtering
16 - Using text filters
17 - Using date filters
18 - Using numeric filters
19 - Using the Top Ten (value or percent) option
20 - Creating custom filters
21 - Copying and printing filtered lists
22 - Recognizing the limitations of standard filtering
23 - Filtering interactively using slicers with tables

5. Advanced Filter

24 - Using Advanced Filter for complex OR criteria
25 - Using Advanced Filter for specialized filters
26 - Using Advanced Filter to create unique lists from repeating field data

6. Eliminating Duplicate Data

27 - Using the Remove Duplicates command
28 - Using a specialized array formula to identify data that's been duplicated
29 - Using an array formula to count the number of unique items in a list

7. Data Analysis Tools

30 - Using SUMIF, COUNTIF, and related functions for quick data analysis
31 - Using database functions including DSUM, DAVERAGE, DMAX
32 - Using the AGGREGATE function to bypass errors and hidden rows

Conclusion

33 - Next steps