Creating and Analyzing Database (Microsoft Excel 2010/2013/2016) (7.5 hrs)

Course Code: CRS-N-0040876

This in-depth course introduces Microsoft Excel users to the advanced features in creating and analysing databases. Participants will learn how to sort and manage data in lists; filter and query data; apply lookup and database functions. They will also learn how to analyse and evaluate the information in databases by creating PivotTable and PivotChart.

  • Learn about the advanced features in creating and analysing databases
  • Able to analyse and evaluate information in databases

Working with databases

  • What is an Excel database?
  • Creating a database (Excel table)
  • Modifying a database (add a new record / add a new field)
  • 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

Using AutoFilter

  • Enabling AutoFilter
  • Using AutoFilter to filter a list
  • Clearing AutoFilter criteria
  • Display top ten records
  • Creating a custom AutoFilter
  • Create a custom filter using wildcards
  • Disabling AutoFilter

Working with advanced filters

  • 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

Lookup formulas

  • Using VLookup
  • Using HLookup

Exporting and importing data

  • Exporting data to as text file
  • Importing data from text files

Refresh data

  • Changing external data range properties
  • Removing the query definition
  • Importing data from other applications
  • Importing dynamic data from the web
  • Copying a table from a web page

PivotTables

  • Get answers with PivotTables
  • Create the PivotTable
  • Create the layout
  • Rearrange the layout
  • Filter your data
  • Changing the summary function
  • Creating a page field report
  • Formatting a PivotTable report
  • Creating a PivotChart report

Participants will benefit from the hands-on practical sessions during the course.

This course is suitable for information professionals who possess advanced working knowledge in Microsoft Excel and wish to use other high-level features in creating and analysing databases.

Prerequisite

Participants must be an advanced user of Microsoft Excel 2010/2013/2016.

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $350.00
Nett Fee Incl. 7% GST : $374.50
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Key Benefits
  • Learn about the advanced features in creating and analysing databases
  • Able to analyse and evaluate information in databases
Course Contents

Working with databases

  • What is an Excel database?
  • Creating a database (Excel table)
  • Modifying a database (add a new record / add a new field)
  • 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

Using AutoFilter

  • Enabling AutoFilter
  • Using AutoFilter to filter a list
  • Clearing AutoFilter criteria
  • Display top ten records
  • Creating a custom AutoFilter
  • Create a custom filter using wildcards
  • Disabling AutoFilter

Working with advanced filters

  • 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

Lookup formulas

  • Using VLookup
  • Using HLookup

Exporting and importing data

  • Exporting data to as text file
  • Importing data from text files

Refresh data

  • Changing external data range properties
  • Removing the query definition
  • Importing data from other applications
  • Importing dynamic data from the web
  • Copying a table from a web page

PivotTables

  • Get answers with PivotTables
  • Create the PivotTable
  • Create the layout
  • Rearrange the layout
  • Filter your data
  • Changing the summary function
  • Creating a page field report
  • Formatting a PivotTable report
  • Creating a PivotChart report
Learning Methodology

Participants will benefit from the hands-on practical sessions during the course.

Who Should Attend

This course is suitable for information professionals who possess advanced working knowledge in Microsoft Excel and wish to use other high-level features in creating and analysing databases.

Prerequisite

Participants must be an advanced user of Microsoft Excel 2010/2013/2016.

Course Details

Duration : 1 day (7.5 hrs)
Time : 9:00am – 5:30pm

Course Fee : $350.00
Nett Fee Incl. 7% GST : $374.50
SDF Support* : $15.00

* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.

(A course in partnership with COMAT)

Course Application

Details Registration
January 3, 2020 - January 3, 2020 (9:00 am - 5:30 pm) Closed
March 2, 2020 - March 2, 2020 (9:00 am - 5:30 pm) Closed
September 2, 2020 - September 2, 2020 (9:00 am - 5:30 pm) Closed
November 11, 2020 - November 11, 2020 (9:00 am - 5:30 pm) Register Now