Microsoft SATVs Are Expiring —Take Full Advantage and Act Now!

closeClose

Creating and Analyzing Database Using Microsoft Excel 2007 and 2010

  • Tuition USD $266 List Price $295
  • Reviews star_rate star_rate star_rate star_rate star_half 2348 Ratings
  • Course Code 50450
  • Duration 1 day
  • Available Formats Classroom

This 1 day course provides students with the knowledge and skills to use advanced features in creating and analyzing databases. Participants will learn how to sort and manage data in lists; filter and query data; apply lookup and database functions. Participants will also learn how to analyze and evaluate the information in databases by creating pivot table and pivot charts.

Skills Gained

  • Working with Databases
  • Using AutoFilter
  • Working with Advanced Filters
  • Lookup Formulas
  • Exporting and Importing Data
  • Creating/Revising PivotTable

Who Can Benefit

This course is intended for Information workers who have at least a year experience in using Microsoft Office Excel 2003/2007/2010.

Prerequisites

  • An intermediate usage of Microsoft Office Excel 2003/2007/2010 for at least 1 year.

Course Details

Outline


Module 1: Working with Databases
This module explains how to make use of Excel to create a sample database format.
Lessons

  • Creating a Database
  • Modifying a Database
  • Sorting Records by Multiple Fields
  • Using Data Validation
  • Validating Data using a List
  • Creating a Custom Error Message
  • Removing Data Validation
  • Creating Subtotals in a List
  • Removing Subtotals from a List

Lab 1: Working with Databases
  • Creating and Modify Database
  • Using Data Validation
  • Creating, Removing Subtotals
After completing this module, students will be able to:
  • Creating and Modify Database
  • Using Data Validation
  • Creating, Removing Subtotals

Module 2: Using AutoFilter
This module explains how to use AutoFilter to get their desired details from Excel List.
Lessons
  • Enabling AutoFilter
  • Using AutoFilter to Filter a List
  • Clearing AutoFilter Criteria
  • Creating a Custom AutoFilter

Lab 1: Using AutoFilter
  • Enabling AutoFilter
  • Using AutoFilter to Filter a List
  • Clearing AutoFilter Criteria
  • Creating a Custom AutoFilter
After completing this module, students will be able to:
  • Use AutoFilter to get their desired details from Excel list.
  • Create Custom AutoFilter

Module 3: Working with Advanced Filters
This module explains how to make use of the advanced filter to set criteria range and copy the result to another location in Excel ranges.
Lessons
  • Creating a
  • Using a
  • Showing All Records
  • Using an Advanced And Condition
  • Using an Advanced Or Condition
  • Copying Filtered Records
  • Using Database Functions

Lab 1: Working with Advanced Filters
  • Set Criteria range for the advanced filters.
  • Copying Filtered record to another location in Excel ranges.
  • Use Database function for calculating required results.
After completing this module, students will be able to:
  • Set Criteria range for the advanced filters.
  • Copying Filtered record to another location in Excel ranges
  • Use Database function for calculating required results
  • Resolve common application compatibility issues

Module 4: Lookup Formulas
This module explains how to make use of Vlookup, Hlookup to retrieve desired items in Excel Tables.
Lessons
  • Using VLookup
  • Using HLookup

Lab 1: Lookup Formulas
  • Make use of Vlookup and Hlookup
After completing this module, students will be able to: Lessons
  • Using VLookup
  • Using HLookup

Module 5: Exporting and Importing Data
This module explains how to import and export Excel data to text formats. It also shows how to import data from the web.
Lessons
  • Exporting Data to Other Applications
  • Exporting to XML
  • Importing Data from Text Files
  • Changing Properties
  • Importing Data from Other Applications
  • Removing the Query Definition
  • Importing Dynamic Data from the Web
  • Copying a Table from a Web Page

Lab 1: Exporting and Importing Data
  • Import and export data to Text format
  • Import data from other applications
  • Import data from the Web

Module 6: Exporting and Importing Data
After completing this module, students will be able to:
  • Import and export data to Text format
  • Import data from other applications
  • Import data from the Web

Module 7: Creating/Revising PivotTables
This module explains how to use determine the source needed to create its PivotTable/PivotChart report
Lessons
  • Creating a PivotTable Report
  • Adding PivotTable Report Fields
  • Selecting a Page Field Item
  • Refreshing a PivotTable Report
  • Changing the Summary Function
  • Adding New Fields to a PivotTable Report
  • Moving PivotTable Report Fields
  • Hiding/Unhiding PivotTable Report Items
  • Deleting PivotTable Report Fields
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report
  • Publishing PivotTable Reports to the Web

Lab 1: Creating/Revising PivotTables
  • Determine the source needed for its PivotTable
  • Create the PivotTable
  • Adding/removing Fields for the PivotTable
  • Creating Page Field Report
  • Creating a PivotChart Report
  • Publishing PivotTable Report to the Web

Module 8: Creating/Revising PivotTables
After completing this module, students will be able to:
  • Determine the source needed for its PivotTable
  • Create the PivotTable
  • Adding/removing Fields for the PivotTable
  • Creating Page Field Report
  • Creating a PivotChart Report
  • Publishing PivotTable Report to the Web

How do I get a Microsoft exam voucher?

Pearson Vue Exam vouchers can be requested and ordered with your course purchase or can be ordered separately by clicking here.

  • Vouchers are non-refundable and non-returnable. Vouchers expire 12 months from the date they are issued unless otherwise specified in the terms and conditions.
  • Voucher expiration dates cannot be extended. The exam must be taken by the expiration date printed on the voucher.

Do Microsoft courses come with post lab access?

Most Microsoft official courses will include post-lab access ranging from 30 to 180 calendar days after instructor led course delivery. A lab training key in class will be provided that can be leveraged to continue connecting to a remote lab environment for the individual course attendee.

Does the course schedule include a Lunchbreak?

Lunch is normally an hour-long after 3-3.5 hours of the class day.

What languages are used to deliver training?

Microsoft courses are conducted in English unless otherwise specified.

This was an excellent and very informative class. I will recommend it to others. Thank you.

The class provided by ExitCertified was well put together, with a great instructor and thorough course materials.

The course material and instructor were very good. easy to follow, lab was setup nicely and was able to complete most of the lab material.
The Koretex App is absolute garbage and very cumbersome to use on a tablet/ipad. A PDF file would be 100 times better than that atrocious app.
As a recommendation this class should be 5 days instead of 4 as some chapters had to be rushed.

The course is well organized, and I would recommend it. The cadence can be faster.

Very good except that often the labs did too much for you without explaining

0 options available

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links above. We can also contact you when the course is scheduled in your area.

Contact Us 1-800-803-3948
Contact Us
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward