PLEASE CLICK HERE to view upcoming event. Information here is outdated

9 FEATURES TO BECOME MICROSOFT EXCEL EXPERT

25-26 Apr, 2022, Remote Online Training - Public

IPA Training is Registered with

Venue Details

Remote Online Training - Public


Contact us

FOR COURSE DETAILS

Juliany,

Office: 03 2283 6109

Mobile: +60 122281247

juliany@ipa.com.my

Phoebe,

Office: 03 2283 6100

Mobile: +60 193637822

phoebe@ipa.com.my

Bee Teng,

Office: 03 2282 6112

Mobile: +60 172566121

beeteng@ipa.com.my

FOR CUSTOMISED IN-HOUSE TRAINING

Jane,

Office: 03 2283 6101

Mobile: +60 129418251

jane@ipa.com.my

Keith,

Office: 03 2283 0000

Mobile: +60 162272735

keith@ipa.com.my

ADDRESS

A-28-5, 28th Floor, Menara UOA Bangsar,
No.5, Jalan Bangsar Utama 1,
59000 Kuala Lumpur

FOCUSING ON
  • Understanding Data in Microsoft Excel
  • Discover the Daily used Functions at Work
  • Check your Reports in seconds
    Prepare Calculations without Writing any Formula
  • Data Management
  • Analyzing Data using PivotTable & PivotChart
  • Most Popular: Logical and Lookup Functions
  • Analyze Data using What-IF-Analysis
  • Automate process using Macro Recording
COURSE OEVRVIEW
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. Looking for a few cool tricks to impress your boss the next time he calls you in to do an urgent Excel project? The tricks in this course will ensure the data in your chart is accurate to avoid any costly screw-ups. You’ll also save a ton of time – perhaps even a few hours. You might even be able to leave the office on time. For one way or the other, we all work with numbers. When you want to record, analysis and save such numeric data, you will become a Superhero in Microsoft Excel in your organization.
AFTER ATTENDING THIS COURSE YOU WILL RETURN TO YOUR JOB…
  1. Working with Excel confidently.
  2. Preventing common mistake in Microsoft Excel Reporting.
  3. Preparing reports with time given & meeting all the dateline given.
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

MUST KNOW ABOUT MICROSOFT EXCEL

  • Start with the Essential Knowledge
    - Excel Data Type
    - How does this information affect us?
    - A Quick Fix
    - Understanding Microsoft Excel Date
    - Quick Questions
  • Understanding Excel Number Formatting
    - Date Format
    - Number Format
    - Custom Number Formatting
  • Writing Formula using Excel References
    - Relative Reference
    - Absolute Reference
    - Mixed References
  • Using Name as Reference (Name Range)

10:45

DAILY USED FORMULAS AND FUNCTIONS

  • Essential Arithmetic Formula and Functions
  • COUNT Functions
    - COUNT Function
    - COUNTA Function (Count All)
    - COUNTBLANK Function
    - COUNTIF Function
  • DATE Functions
    - Insert Excel Date
    - Countdown between 2 date
    - Find out the completion date based on number of days
    - Find out the duration between two dates
    - Adjust an existing date to a future date by number of months
  • TIME Duration Calculations
    - Normal punch card calculation
    - How to deduct an hour from the duration between Time in and Time out
    - Calculate the consultation Fee based on Hourly Rate
  • Data Cleansing using Text Functions
    - Using “&” symbol or Concatenate Function
    - Using LEFT and RIGHT Functions to create a new Information
    - Convert the text to Capital Letters
    - Using Function to Split information to multiple columns
    - Change all Uppercase text to Sentence case
    - Removing additional spaces within a Text
1:00 Lunch
2:00

REPORT CHECKING FEATURES

  • Conditional Formatting
    - Find out which month achieved the highest sales
    - Find out the months that achieved the given target
    - Highlight the rows of data that meet the target
  • Data Validation
    - Circle the cell that does not meet the criteria given.
    - Data Entry control using Data Validation feature
    - Build a drop-down list using Data Validation List
  • Formula Auditing
    - Trace Precedents
    - Trace Dependents


 

COMMON DAILY COPY AND PASTE OPERATION

  • Paste Operation
  • Paste Transpose
  • Paste Column Width
5:00 End of Course
DAY 2
9:00

DATABASE MANAGEMENT

  • Sorting Data
    - Sorting data in ascending or descending order
    - Sorting data according to month or day order
    - Sorting data using our own priority order
    - Perform multiple level of sorting
  • Using Subtotal to generate Quick Summary
  • Applying common AutoFilter
    - Applying AutoFilter to show the records we want.
  • Using Custom Filter to search for data
    - Use Wildcard to filter the records
  • Advance Filter
    - Setting up before using the Advance Filter
    - Filter with Multiple sets of Criteria and paste it in another location
10:45

DATABASE ANALYSIS & REPORTING

  • A quick interactive report/summary using Database Functions
  • Generate Report using PivotTable
    - Understand the PivotTable Elements
    - Field List and Layout
    - Value Field Settings
    - Show Value as…
    - Grouping Data
    - Filtering report using Slicer
  • Transform a PivotTable Report to PivotChart
    - Insert a PivotChart as easy as 3 steps
    - Impress your boss with a beautiful and interactive PivotChart


1:00 Lunch
2:00

ADVANCED POPULAR FUNCTIONS

  • Applying Logical Functions
    - Logical Function (IF, AND & OR)
    - Using Logical Function to Suppress Error from appearing in the answer
  • Lookup Functions
 

DATA ANALYSIS USING WHAT-IF

  • Using Goal Seek to achieve the goal by changing one variable.
    - Solution One: Extend the loan period
    - Solution Two: Reduce the loan amount
  • Data Table
    - Calculate the Break-even point using One Input Data Table
    - Find out the best offer for bulk purchase
  • Scenario
    - Adjust our Costing to meet the Customer Budget

  AUTOMATE YOUR OPERATION USING MACRO
  • Macro Recording
  • Assigning Macro to Button

5:00 End of Course