50450-B: Creating and Analyzing Database Using Microsoft Excel 2007 & 2010

Course Modality

Instructor-led (classroom)

Course Level

Intermediate

Course Time

1 day

Course Language

English

Course Overview

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

Prerequisites

Before attending this course, students must have:

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

Intended Audience

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

Why The DataTech Labs ?

Self-Paced Online Video

A 360-degree learning approach that you can adapt to your learning style

A 360-degree learning

Engage and learn more with these live and highly-interactive classes alongside your peers

24/7 Teaching Assistance

24/7 Teaching Assistance Keep engaged with integrated teaching

Online Practice Labs

Projects provide you with sample work to show prospective employers.

Applied Projects

Real-world projects relevant to what you’re learning throughout the program

Learner Social Forums

A support team focused on helping you succeed alongside a peer community

Skill Covered

After completing this course, students will be able to:

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

Get In Touch

Course Curriculum

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 : Practice 1

After completing this module, students will be able to:

  • Creating and Modify Database
  • Using Data Validation
  • Creating, Removing Subtotals

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 : Practice 2

After completing this module, students will be able to:

  • Use AutoFilter to get their desired details from Excel list.
  • Create Custom AutoFilter.

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 Criteria Range
  • Using a Criteria Range
  • Showing All Records
  • Using an Advanced And Condition
  • Using an Advanced Or Condition
  • Copying Filtered Records
  • Using Database Functions

Lab : Practice 3

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.

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 : Practice 4

After completing this module, students will be able to:

  • Make use of Vlookup and Hlookup

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 External Data Range Properties
  • Importing Data from Other Applications
  • Removing the Query Definition
  • Importing Dynamic Data from the Web
  • Copying a Table from a Web Page

Lab : Practice 5

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

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 : Practice 6

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

Reviews

Get in touch, enquire now!


By submitting this form, I consent to the processing of the personal data that I provide The Data Tech Labs Inc. in accordance with and as described in the Privacy Policy.

© 2020 The Data Tech Labs Inc. All rights reserved.

TDTL-arrow
[glt language="Arabic" label="Arabic" image="yes" text="yes" image_size="24"]
[glt language="English" label="English" image="yes" text="yes" image_size="24"]
[glt language="French" label="French" image="yes" text="yes" image_size="24"]
[glt language="German" label="German" image="yes" text="yes" image_size="24"]
[glt language="Hindi" label="Hindi" image="yes" text="yes" image_size="24"]
[glt language="Marathi" label="Marathi" image="yes" text="yes" image_size="24"]
[glt language="Spanish" label="Spanish" image="yes" text="yes" image_size="24"]

Microsoft Power Platform App Maker

Designing & Implementing Azure AI Solution

Microsoft Azure Administrator

Developing Solutions For Microsoft Azure

Microsoft Azure Architect Design Exam

Implementing Azure Data Solution

Administering Relational Databases On Microsoft Azure