185 Madison Ave. at 34th St., Suite 1104, New York, NY 10016 • 212-684-5151
Create/Analyze Database in Excel
Create/Analyze Database in Excel

Price: $345.00

Course Code: ED | Hours: 8

Creating & Analyzing Databases Training Using Excel 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 and apply lookup and database functions. Students will also learn how to analyze and evaluate the information in databases by creating PivotTables and PivotCharts

Upon successful completion of this course, students will be able to:

  • Work with databases
  • Use AutoFilter
  • Work with Advanced Filters
  • Apply Lookup Formulas
  • Export and import Data
  • Create and Revise PivotTables

Prerequisites:

Students are recommended to take the following course or possess equivalent knowledge:

Next Classes:

Related Classes:

See Also:

Prerequisites:

Students are recommended to take the following course or possess equivalent knowledge:

Next Classes:

Related Classes:

See Also:

Creating & Analyzing Databases Training Using Excel Outline

Lesson 1: Working with Excel Database

• An Excel Database
• Create a Database (Excel Table)
• Modifying Database (Add A New Record)
• Modifying Database (Add A New Field)
• Sort By Custom List
• Using Data Validation
• Validate Text Length
• Create a Custom Error Message
• Validate Data Using a List
• Set Maximum and Minimum Values
• Remove Validation Rules
• Using Subtotal
• Creating Subtotal
• Use Outlines in Subtotal
• Apply Multiple Subtotals

Lesson 2: Using AutoFilter

• Enable AutoFilter
• Use AutoFilter to Filter Data
• Clearing AutoFilter Criteria
• Display Top Ten Records
• Create a Custom AutoFilter
• Create a Custom Filter Using Wildcard

Lesson 3: Working with Advanced Filters

• Create Criteria Range
• Use a Criteria Range
• Use an AND Condition
• Use an OR Condition
• Copy Filtered Record
• Use Database Functions

Lesson 4: Using Lookup Formulas

• Use Vlookup to Find Specific Data
• Use Hlookup To Find Values in Rows

Lesson 5: Exporting and Importing Data

• Export Excel Data As Text File
• Import Data From Text File
• Refresh Data
• Change External Data Range Properties
• Remove Query Definition
• Import Data From Other Applications - Part 1
• Import Data From Other Applications - Part 2
• Import Dynamic Data from the Web
• Copy a Table From A Web Page

Lesson 6: PivotTables

• Get Answers with PivotTables
• Use the PivotTable Wizard
• Create a PivotTable Layout
• What Goes Where
• Filtering Our Data By Product
• Rearrange The Layout
• Format a PivotTable Report
• Create a PivotChart Report