Excel Intermediate 2: Manage Lists and Tables of Data
Class Details
Length of class: three hours
Price per person: $129
Version covered: for Office 365
Description
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
Summarizing Data Using Subtotals
- 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
Using Data in Tables
- 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
Advanced Filtering
- creating the three needed ranges
- entering simple filter criteria
- filtering the list in-place and to the Extract range
- entering complex filter criteria
Database Functions
- use DCOUNT to count rows that meet criteria
- use DSUM to sum rows that meet criteria
Upcoming Dates
Wednesday, November 27, 2024
8:00 AM - 11:00 AM
Thursday, December 19, 2024
12:00 PM - 3:00 PM