| Price per person: | $129 |
| Length of class: | three hours |
| Version covered: | for Office 365 |
| Delivery format: | live over Zoom |
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.
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.
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