Data Manipulation (Excel 2010/13/16) (8 hrs)

Course Code: OA-DMET-04

In this course, participants will learn the methods and approaches relevant to different types of data analysis projects. The chapters focus on preparing data for critical reporting, protecting sensitive data and automating repetitive data through the application of Excel.

  • Prepare data for critical reporting
  • Protect sensitive data
  • Automate repetitive data

Getting data into Excel

  • Importing data from Access
  • 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

Highlight critical data

  • Applying conditional formats
  • Changing a conditional format
  • Adding a conditional format
  • Creating a custom conditional format
  • Using data bars
  • Adding sparkline

Adding names for frequently used data

  • Working with range names
  • 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
  • Creating 3-D range names
  • Using 3-D range names in formulas

Using combination charts to present result

  • Adding and removing gridlines
  • Formatting gridlines
  • Formatting an Axis
  • Changing the Axis scaling
  • Formatting the data series
  • Adding data from different worksheets
  • Using a secondary Axis
  • Changing data series chart types
  • Adding a trendline
  • Creating a chart template
  • Applying a chart template

Adding security to data

  • Unlocking cells in a worksheet
  • Protecting a worksheet
  • Unprotecting a worksheet
  • Creating allow-editing ranges
  • Deleting allow-editing ranges
  • Assigning a password
  • Opening a password-protected file
  • Removing a password

Introduction to What-if Analysis

  • Using the Scenario Manager
  • Creating a scenario
  • Displaying a scenario
  • Editing a scenario
  • Creating a scenario summary report
  • Using goal seek

Automating repetitive task

  • Defining macros
  • Running a macro
  • Using a shortcut key
  • Opening the Visual Basic Editor window
  • Using the Visual Basic Editor window
  • Recording a macro
  • Assigning a shortcut key
  • Deleting a macro

Add interactivity to worksheets

  • Using a macro button
  • Creating a macro button
  • Copying a macro button
  • Formatting a macro button
  • Moving / sizing a macro button
  • Deleting a macro button
  • Adding a macro to the Quick Access Toolbar
  • Changing a QAT macro button image
  • Deleting a macro button from the QAT

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

This course is highly beneficial for professionals such as Managers, Data Analysts and Executives who need to apply advanced analysis techniques to manage larger and more complex data sets.

Prerequisite

Participants must have a basic understanding of MS Excel 2007/2010.

Duration : 1 day (8 hrs)
Time : 9:00am – 6:00pm

Course Fee : $400.00
Nett Fee Incl. 7% GST : $428.00

(A course in partnership with COMAT)

Key Benefits
  • Prepare data for critical reporting
  • Protect sensitive data
  • Automate repetitive data
Course Contents

Getting data into Excel

  • Importing data from Access
  • 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

Highlight critical data

  • Applying conditional formats
  • Changing a conditional format
  • Adding a conditional format
  • Creating a custom conditional format
  • Using data bars
  • Adding sparkline

Adding names for frequently used data

  • Working with range names
  • 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
  • Creating 3-D range names
  • Using 3-D range names in formulas

Using combination charts to present result

  • Adding and removing gridlines
  • Formatting gridlines
  • Formatting an Axis
  • Changing the Axis scaling
  • Formatting the data series
  • Adding data from different worksheets
  • Using a secondary Axis
  • Changing data series chart types
  • Adding a trendline
  • Creating a chart template
  • Applying a chart template

Adding security to data

  • Unlocking cells in a worksheet
  • Protecting a worksheet
  • Unprotecting a worksheet
  • Creating allow-editing ranges
  • Deleting allow-editing ranges
  • Assigning a password
  • Opening a password-protected file
  • Removing a password

Introduction to What-if Analysis

  • Using the Scenario Manager
  • Creating a scenario
  • Displaying a scenario
  • Editing a scenario
  • Creating a scenario summary report
  • Using goal seek

Automating repetitive task

  • Defining macros
  • Running a macro
  • Using a shortcut key
  • Opening the Visual Basic Editor window
  • Using the Visual Basic Editor window
  • Recording a macro
  • Assigning a shortcut key
  • Deleting a macro

Add interactivity to worksheets

  • Using a macro button
  • Creating a macro button
  • Copying a macro button
  • Formatting a macro button
  • Moving / sizing a macro button
  • Deleting a macro button
  • Adding a macro to the Quick Access Toolbar
  • Changing a QAT macro button image
  • Deleting a macro button from the QAT
Learning Methodology

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

Who Should Attend

This course is highly beneficial for professionals such as Managers, Data Analysts and Executives who need to apply advanced analysis techniques to manage larger and more complex data sets.

Prerequisite

Participants must have a basic understanding of MS Excel 2007/2010.

Course Details

Duration : 1 day (8 hrs)
Time : 9:00am – 6:00pm

Course Fee : $400.00
Nett Fee Incl. 7% GST : $428.00

(A course in partnership with COMAT)

Course Application

Details Registration
February 4, 2020 - February 4, 2020 (9:00 am - 6:00 pm) Closed
August 28, 2020 - August 28, 2020 (9:00 am - 6:00 pm) Register Now
October 29, 2020 - October 29, 2020 (9:00 am - 6:00 pm) Register Now
December 14, 2020 - December 14, 2020 (9:00 am - 6:00 pm) Register Now