Database Management with Microsoft Excel

Database Management with Microsoft Excel

This in-depth two-day course introduces Microsoft Excel users to the database features of the software application.
Participants will learn how to sort and manage data in lists; filter and query data; use outlines on worksheets, lookup and database functions.

Participants will also learn how to create and work with PivotTables, use data tables to make projections, audit formulas and data, protect contents of database and import and export data.

Program objectives:

What You Will Cover:

• Manage data in lists.
• Filter and query data.
• Use data outlines on worksheets.
• Use lookup and database functions.
• Use PivotTables to display and chart data.
• Use data tables & scenarios to make projections.
• Analyze data and audit worksheets.
• Work with Macros to automate database functions.
• Protect data for worksheets and workbooks.
• Import and export information and documents.

Day one:

Working with Data in Lists.

Understanding Data Lists.
Sorting Data.
Creating and Removing Subtotals.
Working with Data Forms:

• Accessing the Data Form.
• Entering Data into the Data Form.
• Moving Through Records in the Data Form.
• Editing and Querying Data in the Data Form.

Using Data Validation:

• Restricting Cells to Numbers, Dates, or Times.
• Restricting Cells to a List of Values.
• Displaying an Input Message.
• Displaying an Error Alert.

Filtering and Querying Data

Filtering Data:

• Using AutoFilter and Removing AutoFilter.
• Working with Filtered Data.
• Using AutoFilter with Top 10.
• Creating a Custom AutoFilter.
• Filtering with a Single Comparison Criterion.
• Filtering with Two Comparison Criteria.

Working with the Advance Filter

• Setting Up the Criteria Range.
• Examples of Criteria Ranges.
• Filtering Data with the Advance Filter.
• Displaying All Data.

Performing Web Queries

Using Outlines

Creating an Outline.
Hiding and Displaying Outline Levels.
Hiding and Displaying Outline Symbols.

Using Lookup and Database Functions

Looking Up Values in a Table.
Working with Database Functions:

• Counting the Occurrences of a Value.
• Finding the Sum of Specific Records.
• Finding the Average of Specific Records.

Auditing Worksheets

Finding Referenced Cells.
Finding Formulas that Refer to a Cell.
Auditing Formulas.

Download the complete brochure and registration form using the link below:

Database Management with Microsoft Excel