Manipulating Data Effectively with Microsoft Excel

22-23 Sep, 2022, Remote Online Training - Public

IPA Training is Registered with

Learn from the Best


Kelvin Ng Choo Kent is a highly experienced digital arts instructor and master trainer with more than 17 years ofexperience in the Office Automation Consultation, Graphic Design, Animation and Print Production arena. He is a graduate of Malaysian Institute of Art (MIA) and holds the distinction of being certified as Adobe Certified Expert (ACE), Microsoft Office 2016 Master Specialist MCP & MCT, Certified Practitioner of Neuro-Semantics, Certified Practitioner of Neuro-Linguistic Programming & Certified Practitioner of Hypnosis & Time-Lines.

Venue Details

Remote Online Training - Public

Contact us

Juliany,
M - 012 2281 247
juliany@ipa.com.my

Phoebe,
M - 019 363 7822
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Yvonne,
M - 012 201 1247
yvonne@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my

FOCUSING ON
  • References & Names | COUNT Functions
  • Text Functions | Date Functions
  • Logical Functions | Lookup Functions
  • Database Functions | Paste Special | Print Setup
  • Sorting and Filtering Data | Reporting with PivotTable
  • Linking data | Report Checking Features
  • Advance Charting | What-If-Analysis
  • Macro Recording| Security Settings
INTRODUCTION
Microsoft Excel is a software for storing numerical data and analyzing them. It is one of the most flexible and commonly used applications in Office. Whatever career path you choose, you will probably have to use Excel to achieve your professional goals, some of which may happen every day. For one way or the other, we all work with numbers. When we want to record, analysis and save such numeric data, Microsoft Excel comes in handy. At this intermediary level, it provides an overview of the Excel forms and features used every day for preparing reports and analytics for our managers.
COURSE LEARNING OUTCOMES (CLO)
  1. Distinguish Excel Data thoroughly & preventing common mistakes in Excel Reports.
  2. Perform accurate and consistent Reports using the right Formula or Functions.
  3. State the Logic in the Formula by using Logical Functions to automate outcome.
  4. Present information & Summarizing Data accurately quick and easy.
  5. Rewrite accurate and consistent Reports using the right Formula or Functions.
  6. Able to print final report in proper setup and presenting professionally.
  7. Able to apply Logic in the Formula by using Logical Functions to automate outcome.
  8. Able to get information & summarize Data accurately quick and easy.
WHO SHOULD ATTEND
Personnel who use Microsoft Excel to analyse and prepare management reports, especially Administrative, Operational and Middle Management Staff.
METHODOLOGY
  • Guided Learning, group-paced, hands-on classroom training with real life examples for business applications.
  • Challenging exercises are set based on practical applications for typical organizational functions such as HR, Admin, Production, Sales etc.
DAY 1
9:00

Excel References  

  • Consolidating Data Using a 3-D Reference Sum Function
  • Mixed References within Formulas


 

 Excel Named Ranges

  • Naming Cell Ranges
  • Removing a Named Range
  • Named Cell Ranges and Functions

 

 

Excel Statistical Functions

  • COUNT Function
  • COUNTA Function
  • COUNTIF Function
  • COUNTBLANK Function
10.45

Excel Text Functions

  • Left function
  • RIGHT Function
  • MID Function
  • TRIM Function

 

 

Excel Time & Date Functions

  • Inserting the Current Time and Date
  • TODAY Function
  • WORKDAY Function
  • NETWORKDAYS Functions
  • EDATE & EOMONTH Functions

 

1:00 Lunch
2:00

Excel Logical Functions

  • IF Function
  • AND Function

 

 

Excel Lookup Functions

  • VLOOKUP Function
  • HLOOKUP Function

 

3:45

Excel Database Functions

  • DSUM Function
  • DAVERAGE Function
  • DMAX Function
  • DMIN Function
  • DCOUNT Function

 

 

Paste Special Options within Excel

  • Using Paste Special to Add, Subtract, Multiply & Divide
  • Using Paste Special ‘Values’
  • Using Paste Special Transpose Option

 

 

Excel Setup & Printing Issues

  • Worksheet Margins
  • Worksheet Orientation
  • Worksheet Page Size
  • Headers and Footers
  • Scaling Your Worksheet to Fit a Page(S)
  • Printing Titles on Every Page
  • Previewing a Worksheet

 

 

5:00 End of Course
DAY 2
9:00

Sorting and Filtering Data

  • Sorting Data by Multiple Columns at the Same Time
  • Using AutoFilter
  • Sub-Totalling
  • Removing Subtotals


 

Pivot Tables

  • Creating and Using a Pivot Table
  • Filtering and Sorting Data within a Pivot Table
  • Automatically Grouping Data in a Pivot Table and Renaming Groups
  • Manually Grouping Data in a Pivot Table and Renaming Groups

 

 

Linking & Embedding

  • What Is Embedding and Linking?
  • Linking Data within a Worksheet
  • Linking Cells between Worksheets within a Workbook
  • Linking Data between Workbooks
  • Linking Data from Excel to a Word Document
  • Linking an Excel Chart to a Word Document
  • Updating, Locking and Breaking Links
10.45

Validating

  • Data Validation - Whole Number
  • Data Validation - Decimal Number
  • Data Validation – List
  • Removing Data Validation
 

 Auditing

  • Tracing Precedent Cells
  • Tracing Dependent Cells

 

1:00 Lunch
2.00

Charts 

  • Creating a Combined Line and Column Chart
  • Adding a Secondary Axis to a Chart
  • Changing the Chart Type for a Particular Data Series
  • Re-Positioning Chart Title and Legend
  • Modifying Chart Axis Scales
  • Formatting an Axis to Display Using Commas

 

 

Input Tables 

  • One-Input Data Tables
  • Two-Input Data Tables 

 

 

 Scenario    

  • Scenario Manager
3.45

Macros  

  • Record macro
  • Assigning a Macro to a Button
  • Deleting Macros

 

 

Passwords & Security Issues

  • Adding ‘Open’ Password Protection to a Workbook
  • Adding ‘Modify’ Password Protection to a Workbook
  • Password Protecting Cells and Worksheets

 

5:00 End of Course