Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Excel 2016: Managing and Analyzing Data

via LinkedIn Learning

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.

Syllabus

Introduction
  • Welcome
  • Exercise files
1. Data Preparation
  • Structure data for optimum usage
  • Converting lists to tables
  • Using formulas in tables
2. Sorting Data
  • 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
3. Creating Automatic Subtotals in Sorted Lists
  • Setting up single and multiple-level subtotals
  • Expanding and collapsing with grouping and outlining symbols
4. Filtering Data
  • 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
5. Advanced Filter
  • Using Advanced Filter for complex "OR" criteria
  • Using Advanced Filter for specialized filters
  • Using Advanced Filter to create unique lists from repeating field data
6. Eliminating Duplicate 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
7. Data Analysis Tools
  • 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
Conclusion
  • Next steps

Taught by

Dennis Taylor

Related Courses

Reviews

Start your review of Excel 2016: Managing and Analyzing Data

Never Stop Learning!

Get personalized course recommendations, track subjects and courses with reminders, and more.

Sign up for free