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

Introduction

• Become efficient and effective with your data in Excel

1. Data Preparation

• Structure data for optimum usage in Excel
• Convert lists to tables
• Use formulas in tables

2. Sort Data

• Sort concepts and Sort menu options
• Multiple-key sorting
• Sort from menu icons
• Sort based on the order of data in custom lists
• Sort by cell color, font color, or cell icon
• Sort columns
• Sort data in random order

3. Create Automatic Subtotals in Sorted Lists

• Set up single- and multiple-level subtotals
• Expand and collapse displays quickly with grouping and outlining symbols

4. Filter Data

• Filter single and multiple columns
• Text filters
• Date filters
• Numeric filters
• Top ten (value or percent) option
• Custom filters and wildcards (? and *)
• Copy and print filtered lists
• Recognize the limitations of standard filtering
• Filter table data interactively using slicers

5. Advanced Filtering Tools

• Advanced Filter for complex OR criteria
• Advanced Filter for specialized filters
• Advanced Filter to create unique lists from repeating field data
• Add the AutoFilter button for selection filtering

6. Eliminate Duplicate Data

• Remove Duplicates command
• Use formulas to identify duplicate data and number of unique entries
• Use new Excel functions to count unique entries and create unique lists

7. Data Analysis Tools

• SUMIF, COUNTIF, and related functions for quick data analysis
• Explore more database functions
• Use the AGGREGATE function to bypass errors and hidden rows

Conclusion

• Next steps