MS Excel Database Features and PivotTables

Course Content

Lesson 1: Using Lists

1. Using Auto Fill to Enter Data
2. Using AutoComplete
3. Creating and Using Absolute Named Ranges
4. Working with Data Validation
5. Inserting and Deleting Columns or Rows
6. Freezing and Unfreezing Panes
7. Splitting a Worksheet Window Using Functions with Lists

Lesson 2: Using Table Features

1. Creating a Table, Formatting a Table
2. Sorting a Table, Filtering a Table
3. Working with the Total Row, Creating a Calculated Column

Lesson 3: Working with Data

1. Importing Data from a Text File
2. Exporting Data
3. Converting Text to Columns
4. Using Flash Fill, Using Text Functions
5. Connecting to Data in an Access Database
6. Creating a Web Query
7. Importing Web Data using a Saved Query, Working with Hyperlinks

Lesson 4: Creating and Working with PivotTables

1. Understanding PivotTables
2. Creating a PivotTable using Worksheet Data, an External Data Connection
3. Laying out a PivotTable on a Worksheet
4. Modifying PivotTable Fields
5. Using a Report Filter
6. Refreshing a PivotTable, Formatting a PivotTable
7. Using VLOOKUP to Add a Column to a List

Lesson 5: Enhancing PivotTables

1. Working with Summary Functions
2. Creating a Slicer, Calculated Field, Calculated Item & PivotChart
3. Applying Filters & Grouping Data
4. Using a Timeline