Excel 2016 for the Mac: Managing and Analyzing Data

2h 16mIntermediate2025-02-10

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 the program'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
Multiple-key sorting
Sorting by background color, font color, or icon
Sorting based on the order of a custom list or in random order
Creating a top-10 list with values or percentage
Creating single and multiple level subtotals from a sorted list
Creating specialized filtered lists using Advanced Filter
Quickly eliminating duplicate rows from lists
Counting unique items using an array formula
Using SUMIF and COUNTIF functions

Skills covered

Microsoft Excel for MacSpreadsheetsData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)

Concepts

0. Introduction

01 - Welcome

1. Sorting Data

02 - Sort concepts and Sort menu options
03 - Multiple-key sorting
04 - Sort from AZ and ZA menu icons
05 - Sort based on data order in custom lists
06 - Sort by background color or font color
07 - Sort left-to-right columns
08 - Sort data in random order

2. Filtering Data

09 - Filter single- and multiple-column text
10 - Numeric filters
11 - Date filters
12 - Text filters
13 - Top 10 (value or percent) option
14 - Create custom filters
15 - Copy and sort filtered lists
16 - Recognize standard filtering limitations

3. Advanced Filter

17 - Advanced Filter for complex OR criteria
18 - Advanced Filter for specialized filters
19 - Advanced Filter to create unique lists

4. Creating Automatic Subtotals in Sorted Lists

20 - Single- and multiple-level subtotals
21 - Expand and collapse displays with symbols

5. Eliminating Duplicate Data

22 - Remove Duplicates command
23 - Identify duplicate data
24 - Count unique items using an array formula

6. Data Analysis Tools

25 - SUMIF and COUNTIF and related functions
26 - Database functions
27 - Use tables for formatting and data-handling features

Conclusion

28 - Next steps