Excel Automated Dashboard (15 hrs)

Course Code: CRS-N-0040895

An Excel Dashboard provides insight, analysis and alerts. Participants will acquire the knowledge to create an interactive dashboard that allows them to filter data and switch views.

  • Apply statistical and logical function to summarise data
  • Analyse large amount of data through Pivot Table
  • Automate repetitive task with macros
  • Build and assemble dashboard
  • Create form control to filter dashboard

Auditing tools and range name

  • Jumping to a named range
  • Assigning names
  • Using range names in formulas
  • Creating range names from headings
  • Applying range names
  • Deleting range names
  • Using range names in 3-D formulas
  • Displaying / removing dependent arrows
  • Displaying / removing precedent arrows
  • Showing formulas

Statistical and logical functions

  • Using the COUNTIF function
  • Using SUMIF function
  • Using AverageA function
  • Using IF function
  • Using IsError function to avoid error display
  • Creating multiple conditions using nested IF
  • Using logical function and / or within IF

Lookup and reference formulas

  • Using Vlookup to find specific data
  • Using Hlookup to find values in rows
  • Using Match and Index to retrieve data

Conditional formatting

  • Creating conditional formatting
  • Editing conditional formatting
  • Adding conditional formatting
  • Custom number formats

Creating Pivot Tables

  • Creating a Pivot Table report
  • Adding Pivot Table report fields
  • Selecting a report filter field item
  • Refreshing a Pivot Table report
  • Changing the summary function
  • Adding new fields to a Pivot Table
  • Moving Pivot Table report fields
  • Using expand and collapse buttons
  • Deleting Pivot Table report fields
  • Creating report filter pages
  • Formatting a Pivot Table report
  • Creating a Pivot Chart report

Recording macros

  • Recording a macro
  • Assigning a shortcut key
  • Deleting a macro
  • Adding a macro to Quick Access toolbar
  • Deleting a macro button from QAT

Using macros button

  • Using macro button
  • Create macro button
  • Formatting a macro button
  • Rename a macro button
  • Copy and re-assign macro button
  • Moving / sizing a macro button
  • Deleting a macro button

Working with Dashboard

  • Creating Excel Dashboard
  • Applying range names
  • Create Pivot Chart
  • Create combo box to filter chart
  • Create dynamic Pivot Chart title
  • Create macro to update Pivot Chart
  • Create dynamic chart with checkbox
  • Create links

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

This course is designed for CXOs, Managing Directors, General Managers, Project Managers, Data Warriors and professionals in Finance, Sales, Marketing, Engineering and IT that deal with large volume data and require advanced techniques to convert data into actionable information.

Prerequisite

Participants must be users of Microsoft Excel 2007/2010/2013/2016 for at least 1 year.

Duration : 2 days (15 hrs)
Time : 9:00am – 5:30pm

Course Fee : $680.00
Nett Fee Incl. 7% GST : $727.60
SDF Support* : $30.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
  • Apply statistical and logical function to summarise data
  • Analyse large amount of data through Pivot Table
  • Automate repetitive task with macros
  • Build and assemble dashboard
  • Create form control to filter dashboard
Course Contents

Auditing tools and range name

  • Jumping to a named range
  • Assigning names
  • Using range names in formulas
  • Creating range names from headings
  • Applying range names
  • Deleting range names
  • Using range names in 3-D formulas
  • Displaying / removing dependent arrows
  • Displaying / removing precedent arrows
  • Showing formulas

Statistical and logical functions

  • Using the COUNTIF function
  • Using SUMIF function
  • Using AverageA function
  • Using IF function
  • Using IsError function to avoid error display
  • Creating multiple conditions using nested IF
  • Using logical function and / or within IF

Lookup and reference formulas

  • Using Vlookup to find specific data
  • Using Hlookup to find values in rows
  • Using Match and Index to retrieve data

Conditional formatting

  • Creating conditional formatting
  • Editing conditional formatting
  • Adding conditional formatting
  • Custom number formats

Creating Pivot Tables

  • Creating a Pivot Table report
  • Adding Pivot Table report fields
  • Selecting a report filter field item
  • Refreshing a Pivot Table report
  • Changing the summary function
  • Adding new fields to a Pivot Table
  • Moving Pivot Table report fields
  • Using expand and collapse buttons
  • Deleting Pivot Table report fields
  • Creating report filter pages
  • Formatting a Pivot Table report
  • Creating a Pivot Chart report

Recording macros

  • Recording a macro
  • Assigning a shortcut key
  • Deleting a macro
  • Adding a macro to Quick Access toolbar
  • Deleting a macro button from QAT

Using macros button

  • Using macro button
  • Create macro button
  • Formatting a macro button
  • Rename a macro button
  • Copy and re-assign macro button
  • Moving / sizing a macro button
  • Deleting a macro button

Working with Dashboard

  • Creating Excel Dashboard
  • Applying range names
  • Create Pivot Chart
  • Create combo box to filter chart
  • Create dynamic Pivot Chart title
  • Create macro to update Pivot Chart
  • Create dynamic chart with checkbox
  • Create links
Learning Methodology

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

Who Should Attend

This course is designed for CXOs, Managing Directors, General Managers, Project Managers, Data Warriors and professionals in Finance, Sales, Marketing, Engineering and IT that deal with large volume data and require advanced techniques to convert data into actionable information.

Prerequisite

Participants must be users of Microsoft Excel 2007/2010/2013/2016 for at least 1 year.

Course Details

Duration : 2 days (15 hrs)
Time : 9:00am – 5:30pm

Course Fee : $680.00
Nett Fee Incl. 7% GST : $727.60
SDF Support* : $30.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 28, 2020 - January 29, 2020 (9:00 am - 5:30 pm) Closed
February 17, 2020 - February 18, 2020 (9:00 am - 5:30 pm) Closed
March 16, 2020 - March 17, 2020 (9:00 am - 5:30 pm) Closed
August 31, 2020 - September 1, 2020 (9:00 am - 5:30 pm) Register Now
September 21, 2020 - September 22, 2020 (9:00 am - 5:30 pm) Register Now
October 22, 2020 - October 23, 2020 (9:00 am - 5:30 pm) Register Now
November 30, 2020 - December 1, 2020 (9:00 am - 5:30 pm) Register Now
December 28, 2020 - December 29, 2020 (9:00 am - 5:30 pm) Register Now