IT07A11 - MODERN APPROACH TO ANALYSING BUSINESS DATA USING MICROSOFT EXCEL (SF) by NTUC LearningHub

What's In It for Me

• Familiarise with the use of tables in Microsoft Excel
• Find out more about Microsoft Excel’s functions such as the conditional and lookup functions
• Learn the basic concepts of data preparation using Power Query
• Discover useful concepts of data modelling using Excel Data Model
• Create calculated columns and measures using Power Pivot
• Explore data visualisation using Microsoft Excel

Course Overview

This course is intended for anyone who wants to stretch their data analysis capabilities using Microsoft Excel starting with a block of data to a table of data, going beyond just functions and formulas, and picking up more advanced capabilities leading to data preparation, data modelling and data visualisation using power query, power pivot, pivot charts, conditional formatting, slicers, timelines and dashboards.

Course Objectives

• Able to collect data from relevant data sources and select appropriate visualisation techniques to interpret and convey findings
• Able to organise data in a table format to display information to reflect trend and correlations
• Able to transform and contextualise data according to requirements to be displayed dynamically
• Able to represent data into a proper data model that will in due course lead to data visualisation
• Able to process data using tools and techniques for data display and visualisation purposes
• Able to utilise visual objects, charts and dashboards for data interpretation and presentation to stakeholders

Pre-requisites

• It is recommended that the participant would have used or have basic working knowledge of Microsoft Excel.
• Hardware & Software
  This course will be conducted as a Virtual Live Class (VLC) via Zoom platform. Participants must own a zoom account and have a laptop or a desktop with “Zoom Client for Meetings” installed. This can be downloaded from https://zoom.us/download

Course Outline

• Collect the Data
  • Steps to use MS Forms
  • Create and Remove a Table of Data
• Look at the Data
  • Total row of a Table
  • IF, IFS and nested IF conditions
  • Lookup functions
• Prepare the Data
  • Power Query
  • Merge and Append Queries
• Model the Data
  • Create a Excel Data Model
  • Create, manage and delete relationships
  • Data Hierarchy
• Process the Data
  • Using Power Pivot
  • Calculated Columns
  • Measures
• Visualise the Data
  • Conditional Formatting
  • Pivotcharts and Sparklines
  • Timeline and Slicer
  • Dashboard

Certificate Obtained and Conferred by

Awarded Statement of Attainments (SOA)

Upon completion of the course and assessment, students will also be awarded with the following ICT SOA: ICT-DIT-3006-1.1 Data Visualisation

NTUC Learninghub Certification of completion

Upon meeting 75% attendance and passing the assessment, participants will receive a Certificate of Completion from NTUC Learning Hub.