Courses

Course Overview

Microsoft Excel is a very widely used tool with many organizations and departments and being able to produce a quick analysis is an asset. This course provides participants with the knowledge and skills to work with and analyze data and work with business cases using Microsoft Excel. The objective is to familiarize the participants with the fundamentals of how to manage, transform and analyze data using the various functions in Excel.

Course Duration
2 days (16 hours)

Course Fee
$800 + GST

Medium of Instruction: English

Mode:
Webinar

Course Outline

MODULE 1: Basic Functions of MS-Excel

Excel is a tool which is widely used for data management and analysis using its powerful in-built functions. This module takes you through hands-on operations on some basic but extremely useful and common functions used everywhere. Some tricks and keyboard shortcuts will be shown while doing data analysis.

• Exploring Window and playing with cells

–     Understanding of ribbon bar

–     Named Ranges

–     Common keyboard shortcuts

• Formatting Cells, Freezing & Conditional Formatting

–     Changing cell values in different formats

–     Freezing vertical and horizontal panes

–     Conditional formatting using color scales

• Excel built-in functions with categories

–     Operations using Text Functions

–     Date, Days, Time Functions

–     Logical Functions and conditions

• Power functions and Statistical functions

–     Basic Math Functions

–     IF and Related Functions

MODULE 2: Analyzing Data using Excel

One of the important parts of data analytics is making sure data is in the most optimized form. Non-optimized data tends to slow down visualizations and makes the process messy. This module will teach a few concepts of fundamental data analysis techniques which can help to get things much faster.

• Instant Data Analysis
  • Quick Analysis Toolbar
  • Sorting data using Toolbar options
• Conditional sorting of data
  • Highlighting Value Cells
  • Sorting based on color
  • Differentiating data using icons
• Removing Duplicates while cleaning
  • Highlighting duplicate values
  • Removing duplicates in single/multiple columns
• Flash Filling of data
  • To fill data instantly
  • Flash Fill Option

MODULE 3: Referencing Data in Worksheets

When we have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. Using different referencing functions, we can get the data into a single working sheet. We will go through how the above can be ensured as part of this module.

• Cross-referencing concept
  • Importance of referencing data
  • Working on single/multiple worksheets
• VLOOKUP and Reference functions
  • Parameters in VLOOKUP
  • Using Named Ranges
• HLOOKUP function
  • Parameters in HLOOKUP
  • VLOOKUP vs HLOOKUP
• XLOOKUP function

MODULE 4: Data Transformation using Text-to-Column

Sometimes we need to transform the available data into required output format. Data require cleaning and must be in correct datatype format. Text-to-column feature simply helps in these operations.

• Operations using text-to-column
  • Splitting cell values
  • Correct date format

MODULE 5: Analysis in Excel Tables

Excel Table is a range of cells formatted to help you analyze data easily. Tables have styles to make it more understandable. Filters and Slicers can be easily applied. It is a fast way to do simple data analysis.

• Create Excel Tables
• Range vs Tables
• Summarizing Data
• Styling Tables
• Sorting and Filtering Tables
• Absolute References

MODULE 6: Getting Insights using Charts

Identifying the business problem is important before getting into data analysis. But we might have other unknown factors, which can be seen after visualizing the graphical results. Visualizations are as good as they are made to look. It is important to know how to format your visualizations to have them convey the right information to the stakeholders. In this module, we will cover the various formatting options available for different graphical options.

• Creating basic Charts
  • Using Recommended Charts Technique
  • Changing Data Range for Charts
• Style Charts with Design Tabs
  • Converting another Chart option
  • Combo Chart for complex data
• Fine Tune Chart Elements
  • Adding Chart titles and axes
  • Editing using Chart Attributes
  • Getting Insights by Displaying Values
• Creating a simple dashboard using various charts

MODULE 7: Accessing data using Pivot Tables

This module introduces one of the most powerful technique used in data analysis in Excel. A Pivot table is essentially a dynamic summary report of your raw data generated from a database. It can transform endless rows and columns of numbers into a meaningful presentation of data. Creating pivot charts using the pivot tables are much easier and efficient.

• Creating Pivot Tables
  • Using working sheet for Pivot table
  • Using another sheet for Pivot table
• Sorting and Filtering Pivot Tables
  • Column and Row Labels
  • Report Filter and Values Areas
• Formatting Pivot Tables
  • Shifting default position of values
  • Sorting and Displaying required data
• Creating Pivot Charts
  • Using Pivot table to create Pivot Charts
  • Changing Graphical representation runtime
• Hands-on exercises to analyze datasets using pivots

MODULE 8: Automation using Power Query

When the dataset is huge and there is need to do automation, Power Query comes into picture. Power Query is used to create data pipeline in any business operation.

• ETL Concept in Data Warehousing
  • Extraction of Data from Source
  • Data Transformation
  • Loading of Data into Table
• Process Automation
  • Set up data pipeline
  • Getting Data from APIs

Who should attend?

This course should be attended by those who want to learn data storytelling using excel as a platform. Applicable to students, working professionals and PMETs.

System Requirements

Windows OS installed with Microsoft Excel 2016 or above.

Mode of Assessment
Written and Practical