Managing and Analyzing Data in Excel (Office 365/Excel 2019)

Managing and Analyzing Data in Excel (Office 365/Excel 2019)
Managing and Analyzing Data in Excel (Office 365/Excel 2019)
English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 30m | 505 MB

Large amounts of data can become unmanageable fast. But with the data management and analysis features in Excel, 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 2019 and Excel for Office 365. He covers sorting, adding subtotals, filtering, eliminating duplicate data, working with tables, and using functions to isolate and analyze data. With these techniques, you’ll be able to better manage your lists and extract the most important information from them, in the shortest amount of time.

Topics include:

  • Prepping data for analysis
  • Multiple-key sorting
  • Sorting based on custom lists
  • Creating single- and multi-level subtotals
  • Using text, numeric, and date filters
  • Filtering tables using slicers
  • Using the Advanced Filter
  • Eliminating duplicate data
  • Using SUMIF and related functions for quick data analysis
Table of Contents

1 Become efficient and effective with your data in Excel

Data Preparation
2 Structure data for optimum usage in Excel
3 Convert lists to tables
4 Use formulas in tables

Sort Data
5 Sort concepts and Sort menu options
6 Multiple-key sorting
7 Sort from menu icons
8 Sort based on the order of data in custom lists
9 Sort by cell color, font color, or cell icon
10 Sort columns
11 Sort data in random order

Create Automatic Subtotals in Sorted Lists
12 Set up single- and multiple-level subtotals
13 Expand and collapse displays quickly with grouping and outlining symbols

Filter Data
14 Filter single and multiple columns
15 Text filters
16 Date filters
17 Numeric filters
18 Top ten (value or percent) option
19 Custom filters and wildcards ( and )
20 Copy and print filtered lists
21 Recognize the limitations of standard filtering
22 Filter table data interactively using slicers

Advanced Filtering Tools
23 Advanced Filter for complex OR criteria
24 Advanced Filter for specialized filters
25 Advanced Filter to create unique lists from repeating field data
26 Add the AutoFilter button for selection filtering

Eliminate Duplicate Data
27 Remove Duplicates command
28 Use formulas to identify duplicate data and number of unique entries
29 Use new Excel functions to count unique entries and create unique lists

Data Analysis Tools
30 SUMIF, COUNTIF, and related functions for quick data analysis
31 Explore more database functions
32 Use the AGGREGATE function to bypass errors and hidden rows

33 Next steps