Microsoft Excel logo

Course duration

(we will customise to your learning requirements and timeframe)

Target audience

This course is designed for those who need to know how to use Microsoft Excel as a database to manage and manipulate data. Participants should have completed the Excel Introduction training course or have a good understanding of Microsoft Excel.

Download the PDF Course Outline.

Course Objectives

At the end of the course participants will be able to:

  • Create databases in Microsoft Excel
  • Do’s and Do nots of creating databases in Excel
  • Perform calculations on data by using functions and formulas
  • Sort and Filter a database in Excel
  • Work with Pivot Tables and Sub Totals to generate calculations

Rules for Creating a Database

  • Elements of a Database
  • Create an Excel Database
  • Do’s
  • Don’t’s
  • Modifying the Database Structure

Sorting

  • 2 click sorting
  • Multiple column sorting
  • Sort by colours
  • Sort by cell icon

Filters

  • Understanding filters
  • Turn on auto filter
  • Filter text fields
  • Filter number fields
  • Filter date fields
  • Filter by colour
  • Filter by cell icon
  • Select filtered data
  • Totals and filters
  • Multiple field filters
  • Advanced filter
  • Remove filters

Calculating your Data with Subtotals

  • Understand Subtotals
  • Create Subtotals
  • Create multiple levelled Subtotals
  • Remove Subtotals

PivotTables

  • What is a Pivot Table
  • Understand how PivotTables summarise data
  • Create a PivotTable
  • Work with the Pivot Table layout
  • Change designse
  • Sort and filter a PivotTable
  • Refresh a PivotTable
  • Change the data source
  • Group data by row labels
  • Format numbers within a Pivot Table
  • Create calculated fields
  • Create and work with Pivot Charts