Data Analytics using Excel Course


Introduction

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.

In this course, you’ll develop employable data analyst skills, starting with the Excel basics, what it can do, and the data analysis steps you should follow.

Duration

5 Days

Who Should Attend

This course should be attended by those who want to learn data storytelling using excel as a platform

Course Objectives

At the end of this IRES training course, participants will learn:

·         An understanding of how to apply most commonly used statistical and lookup functions.

·         The ability to gather data and organize the data for analysis in business.

·         Knowledge in chart generation and interpretation.

·         The ability to build dashboards using PivotTables and Pivot Charts.

·         Skills in using Excel What-If Analysis and Data Analysis tools for generating different scenarios of business environment and aiding decision making.

Course Outline

Module 1: Basic Functions of MS-Excel

  • 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

  • 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

  • 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

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

Module 5: Analysis in Excel Tables

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

Module 6: Getting Insights using Charts

  • 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

  • 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

  • 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

Enroll for this Course

We are proud to offer this course in a variety of training formats to suit your needs.

IRES

Enroll for a Face-to-Face (In-Person) Class

We use the highest quality learning facilities to make sure your experience is as comfortable as possible.

Register Here
IRES

Enroll for a Virtual (Zoom) Class

Join a scheduled class with a live instructor and other delegates.

Register Here
IRES

Enroll for an Online Self-Paced Class

Keep track of your own progression throughout your course and ensure continuous improvement.

Register Here

Benefits of Taking a Course at IRES

LEARN

Our courses are carefully curated to keep you abreast of latest industry trends, technological advancements, and best practices. We employ a variety of teaching methodologies, including hands-on workshops, case studies, and interactive sessions, all aimed at fostering an engaging and effective learning environment. Our expert instructors bring a wealth of knowledge and real-world experience, providing our clients with insights that can be immediately applied in their professional lives.

NETWORK

Our courses serve as a vibrant platform for professionals to connect and engage with a diverse community of peers, industry leaders, and experts. By participating in our programs, you gain access to an invaluable network that spans across various sectors and geographical boundaries. This networking aspect is not just about forming professional relationships; it's about creating a supportive ecosystem where ideas, opportunities, and collaborations can flourish.

GROW

Our courses are designed to challenge and inspire professionals to step out of their comfort zones and explore new horizons. Through a combination of theoretical knowledge and practical application, our programs help professionals refine their existing skills and acquire new ones, making them more versatile and competitive.

FAQs & Course Administration Details:

This training can also be customized to suit the needs of your institution upon request. You can have it delivered in our IRES Training Centre or at a convenient location. For further inquiries, please contact us on Phone: +254 715 077 817 or Email: [email protected].
The instructor led trainings are delivered using a blended learning approach and comprise of presentations, guided sessions of practical exercise, web-based tutorials and group work. Our facilitators are seasoned industry experts with years of experience, working as professional and trainers in these fields. All facilitation and course materials will be offered in English. The participants should be reasonably proficient in English.
Upon successful completion of this training, participants will be issued with an Indepth Research Institute (IRES) certificate certified by the National Industrial Training Authority (NITA).
Payment should be transferred to IRES account through bank on or before start of the course. Send proof of payment to [email protected].
Accommodation and airport pickup are arranged upon request. For reservations contact the Training Officer. Email: [email protected] Phone: +254 715 077 817.