Subscribe e-Newsletter
    Member Login
    Course Information
    Email
    Pass
    Forget password? Click here
    Event Profile
    Date Feb 27 - 28, 2018
    Time 9:00am to 5:00pm
    Venue Mandarin Orchard Singapore
    333 Orchard Road
    Singapore 238867
    Fee
    7% GST will apply
    SGD 700.00
    For Member
    SGD 665
    NoteBreakfast, two tea breaks and buffet lunch will be served; complimentary car parking coupon will be provided upon request
    Other Date(s)1) Jun 28 - 29, 2018
    2) Sep 24 - 25, 2018
    3) Dec 10 - 11, 2018
    Trainer
    Activity
    You may reach us via
    T: (65) 9879 6267
    E: info@ccisg.com
    To register via fax, fill in This Form and fax to (65) 6310 5430.
    Data analysis is a process of inspecting, cleaning, transforming, and modelling data with the goal of discovering useful information, suggesting conclusions, and supporting decision making. The use of powerful Excel data analysis tools allow you to speed up your day-to-day work as well as assist the decision makers in making sound decision.

    Participants are required to bring a laptop installed with Microsoft Excel 2013 or 2016.
    Objective
    This 2-days course will train participants how to import external data using Microsoft Query, prepare source data for the use of data analysis, convert dates and perform date calculations, create dynamic tables and charts that can be updated automatically using a button, lookup and extract data from a table, consolidate multiple set of data into a summary worksheet, perform What-if Analysis with Scenario Manager, Goal Seek and Data Table, using new features such as Flash Fill, Recommended Charts. Recommended PivotTables and new Charting Tools.
    Outline
    Chapter 1: Linking and Consolidating Data
    1.1 Building Link Worksheets Formula
    1.2 Consolidating Data from Multiple Worksheets
    1.3 Building 3-D Reference Formulas
    1.4 Building Link Workbooks Formula
    1.5 Consolidating Data from Multiple Workbooks
    1.6 Editing Link to Multiple Workbooks

    Chapter 2: Using What-If Analysis
    2.1 Using Goal Seek
    2.2 Using Scenario Manager
    2.3 Using Data Table

    Chapter 3: Cleaning Up Data Using Functions
    3.1 Split Content of a Cell into Different Columns
    3.2 Convert Dates with Text to Column Wizard
    3.3 Perform Date Calculations -> EDATE, EMONTH, WORKDAY, NETWORKDAYS
    3.4 Copy Characters -> LEFT, MID, RIGHT, FIND
    3.5 Convert Text Case -> UPPER, LOWER, PROPER
    3.6 Using Flash Fill

    Chapter 4: Using Advanced Functions to Analyse Data
    4.1 Using Relative and Absolute Cell References
    4.2 Naming Cells and Ranges
    4.3 Using Database Functions -> DSUM, DCOUNT, DMIN, DMAX, DAVERAGE
    4.4 Using Conditional Logic Functions -> COUNTIFS, SUMIFS, AVERAGEIFS
    4.5 Using VLOOKUP And HLOOKUP Functions
    4.6 Using INDEX and MATCH Functions

    Chapter 5: Using Data Validation
    5.1 Understanding Data Validation Settings
    5.2 Understanding Data Validation Messages
    5.3 Using Data Validation to Restrict Data Entry
    5.4 Creating Drop-Down List

    Chapter 6: Implementing Protection
    6.1 Protecting Worksheet
    6.2 Protecting Workbook Structure
    6.3 Protecting Workbook

    Chapter 7: Using Excel Table
    7.1 Creating and Formatting Table
    7.2 Sorting and Filtering Data
    7.3 Delete Duplicate Record Rows
    7.4 Adding Total Row to Excel Table
    7.5 Creating Dynamic Chart from Excel Table
    7.6 Formatting Chart with New Charting Tools

    Chapter 8: Using Conditional Formatting
    8.1 Understanding Conditional Formatting
    8.2 Applying Duplicate Values Rule
    8.3 Applying Top/Bottom Rules
    8.4 Applying Data Bar Rules
    8.5 Applying Icon Sets Rules
    8.6 Managing Conditional Formatting Rules
    8.7 Deleting Conditional Formatting Rules

    Chapter 9: Using Advanced Filter To Copy Data
    9.1 Filter The List in Place
    9.2 Copy Filtered Records to Another Location
    9.3 Copy Unique Records to Another Location

    Chapter 10: Importing External Data
    10.1 Import Data from Text File
    10.2 Import Data from Access File
    10.3 Import Data using Microsoft Query

    Chapter 11: Creating Slicer-Driven Reports
    11.1 Using Recommended PivotTables
    11.2 Filtering Data in PivotTable
    11.3 Formatting Numbers in PivotTable
    11.4 Sorting Values in PivotTable
    11.5 Summarizing Values by Functions
    11.6 Customizing the PivotChart
    11.7 Creating A PivotTable From Another PivotTable
    11.8 Creating and Formatting Slicer
    11.9 Connecting PivotTable Report
    Who should attend
    • This is a fast pace advanced level course and is not suitable for beginners who use Excel occasionally.
    • This course is for frequent Excel users who wish to learn how to increase their productivity with effective and time saving data analysis skills
    • Participants must have an intermediate level Excel knowledge and a few years working experience using Excel.
    Testimonials
    The workshop was well-planned and designed. Well-versed in the subject taught.
    National Healthcare Group Pte Ltd

    One of the most useful course I have attended; my teammates and I will try to sign up for similar courses conducted by Ms Valene. She is an excellent trainer, She is patient, explains concepts well and paces the class well. Excellent facilitator.
    Ministry of Trade and Industry

    Trainer's teaching is excellent.
    National Environment Agency

    The workshop is very good, practical examples and exercises. One of the best Excel courses I've ever attended thus far. The trainer is very knowledgeable and have very good rapport with the group.
    Health Promotion Board

    Perfect pace. I have learnt a lot - Everything is useful. Enjoyed your lessons, Valene. Thank you.
    Lion Global Investors Limited

    Learnt an in depth with regards to excel as my works uses a lot of excel. Overall is good. Trainer is very knowledgeable and experience.
    Ministry of Law

    Pace is fast, but I am able to follow and I have achieved outcome of the course! The workshop is good for managing excessive data! Trainer is very knowledgeable and experience. Able to address all questions posed.
    National healthcare Group
    Valene Ang's Profile
    Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has more than 19 years of experience in corporate IT training and course materials development.

    Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.

    Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc...
    Privacy Policy  |  Terms of Use
    Copyright © 2018 CCISG Pte Ltd  |  ACRA Reg No: 201207591D