Excel Database Functions and PivotTables

Excel is our most popular series classes. A green flag beside a date on the right means that a session has met the minimum enrollment levels and is running. However, it is common for dates for this course to fill several weeks in advance. Even if a date has a green flag, you might not be able to get into that session if it is full. To avoid disappointment, it is best to register for this class at least three weeks in advance.

Overview

Learn the essentials regarding managing data and creating PivotTables in Excel. Create tables; import, modify and analyze data; create and enhance PivotTables.

Target Student

• The course is for individuals who are responsible managing data and creating PivotTables in Excel.

Prerequisites

• Excel Introduction or equivalent skills.

Course Outline

Using Lists

• Using Auto Fill to Enter Data
• Using AutoComplete
• Creating and Using Absolute Named Ranges
• Working with Data Validation
• Inserting and Deleting Columns or Rows
• Freezing and Unfreezing Panes
• Splitting a Worksheet Window
• Using Functions with Lists

Using Table Features

• Creating a Table
• Formatting a Table
• Sorting a Table
• Filtering a Table
• Creating a Slicer
• Working with the Total Row
• Creating a Calculated Column

Working with Data

• Importing Data from a Text File
• Exporting Data
• Converting Text to Columns
• Using Flash Fill
• Using Text Functions
• Connecting to Data in an Access Database
• Creating a Web Query
• Importing Web Data using a Saved Query
• Working with Hyperlinks

Creating and Working with PivotTables

• Understanding PivotTables
• Creating a PivotTable using Worksheet Data
• Creating a PivotTable using an External Data Connection
• Laying out a PivotTable on a Worksheet
• Modifying PivotTable Fields
• Using a Report Filter
• Refreshing a PivotTable
• Formatting a PivotTable
• Using VLOOKUP to Add a Column to a List

Enhancing PivotTables

• Working with Summary Functions
• Creating a Slicer
• Applying Filters
• Grouping Data
• Using a Timeline
• Creating a Calculated Field
• Creating a Calculated Item
• Creating a PivotChart