Excel Intermediate 2: Manage Data in Lists and Tables

In this three-hour session for Mac and Windows users, you will import external data and create queries using Power Query to bring in data from external sources. You will sort lists by multiple columns, generate automatic subtotals on sorted data, including multi-level subtotals. 

Finally, you will convert data into a table, apply automatic formatting and totals and use AutoFilter to show only rows that meet your criteria.

Prerequisites

This class is suited for people who have taken Excel Intermediate 1 or been using Excel (any version) for a year or more and want to learn Excel’s powerful tools for managing and summarizing long lists of data.

Outline

Importing Data

• import data from text files
• modify queries using Power Query Editor
• refresh the results of a query

• sorting tables by one or many fields
• generating sub- and grand totals automatically
• use outlining to show only sub- and grand totals
• creating multi-level subtotals

• convert a normal list into an Excel table
• name and design a table
• write formulas that use table structured references
• use Filter to find records
• create custom Filter criteria

• creating the three needed ranges
• entering simple filter criteria
• filtering the list in-place and to the Extract range
• entering complex filter criteria

• use DCOUNT to count rows that meet criteria
• use DSUM to sum rows that meet criteria