Excel 2016: Managing and Analyzing Data

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