Skip to content Skip to site navigation Skip to service navigation

Data Mining with Excel: Diving into Advanced Filter and Database Functions

Class Sessions

Date Location Cost
  • Mon Jul 26, 9:00 am to 4:00 pm
Live Online $400

Class Code

ITS-1261

Class Description

Effective immediately in response to COVID-19, all Technology Training classes will be delivered online until further notice.


In advance of each session, Tech Training will provide you with a Zoom link to your class, along with any required class materials.
 



Dive deep into Excel's powerful advanced data-mining tools, with an emphasis on building query criteria ranges to find and extract and/or perform calculations on precise data subsets.

 
Abstract
This class is designed for experienced Excel users. See below the topics to view a list of proficiencies one must be familiar with in order to get the most out of this class.

Topics:

  • Comparing AutoFilter and Advanced Filter
  • Comparing database functions to conditional statistical functions
  • Understanding the criteria range
  • Working with Excel Tables and using table structured references in formulas
  • Using in-cell drop-down (data validation) lists to simplify building and changing criteria ranges
  • Using logical comparative operators
  • Using AND and OR criteria logic
  • Using wildcard criteria
  • Using multiple functions in complex calculated criteria, including AVERAGE, VLOOKUP, ISNA, NOT and LEN
  • Using Advanced Filter to extract a data subset rather than filtering data in place
  • Using filtering techniques to prove complex database function results


Prerequisite skills for this class

This class is designed for experienced Excel users. To get the most out of this class, participants should be proficient in and comfortable with at least seven out of the following 10 Excel skills:

  • Copy and paste worksheet data and formulas
  • Filter and sort worksheet data
  • Use the SUM and COUNT functions
  • Use the IF function
  • Use the VLOOKUP function
  • Use the SUBTOTAL function
  • Use conditional functions such as SUMIF and SUMIFS
  • Create formulas with comparative operators such as =, >, < and <>
  • Create formulas with multiple, nested functions
  • Use named ranges and/or Excel tables

 

 


 

University IT Technology Training classes are only available to Stanford University staff, faculty, students and Stanford Hospitals & Clinics employees. A valid SUNet ID is needed in order to enroll in a class.