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
This course is available for "remote" learning and will be available to anyone with access to an internet device with a microphone (this includes most models of computers, tablets). Classes will take place with a "Live" instructor at the date/times listed below.
Upon registration, the instructor will send along additional information about how to log-on and participate in the class.