Excel 2016: Managing and Analyzing Data
Overview
Learn easy-to-use commands, features, and functions for managing and analyzing large amounts of data in Excel 2016.
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.
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.
Syllabus
Introduction
- Welcome
- Exercise files
- Structure data for optimum usage
- Converting lists to tables
- Using formulas in tables
- Sorting concepts and sort menu options
- Sorting from menu icons
- Multiple-key sorting
- Sorting based on the order of data in custom lists
- Sorting by cell color, font color, or cell icon
- Sorting columns (sorting left-to-right)
- Sorting data in random order
- Setting up single and multiple-level subtotals
- Expanding and collapsing with grouping and outlining symbols
- Single and multiple-column filtering
- Using text filters
- Using date filters
- Using numeric filters
- Using the Top Ten (value or percent) option
- Creating custom filters
- Copying and printing filtered lists
- Recognizing the limitations of standard filtering
- Filtering interactively using slicers with tables
- Using Advanced Filter for complex "OR" criteria
- Using Advanced Filter for specialized filters
- Using Advanced Filter to create unique lists from repeating field data
- Using the Remove Duplicates command
- Using a specialized array formula to identify data that's been duplicated
- Using an array formula to count the number of unique items in a list
- Using SUMIF, COUNTIF, and related functions for quick data analysis
- Using database functions including DSUM, DAVERAGE, DMAX
- Using the AGGREGATE function to bypass errors and hidden rows
- Next steps
Taught by
Dennis Taylor
Never Stop Learning!
