Excel Intermediate 2: Queries, Subtotals and Tables

In this three-hour session for Windows users only, 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

Using Queries to Import Data

• create queries to import data
• import multiple tables into the Data Model
• PivotTable report on Data Model tables
• import data from text files

Summarizing Data with Subtotals

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

Using Data in Tables

• converting data into a table
• applying tables styles and totals row
• using AutoFilter to find records
• creating custom AutoFilter criteria
• referencing table elements in formulas