Duration: 5 days – 35 hrs
Overview
This comprehensive training course is designed to equip participants with the essential skills needed to perform data analytics using Microsoft Excel, covering both basic and advanced
functionalities. The course is structured to help learners effectively analyze and visualize data, making informed decisions based on their findings.
Participants will begin by mastering the fundamental features of Excel, including data entry, formatting, and basic formulae. The course will then progress to more advanced topics, such as
complex functions, pivot tables, data visualization, and Excel-based data modeling techniques. By the end of the course, participants will have a strong foundation in Excel and the ability to
leverage its advanced tools for powerful data analysis.
Objectives
• Master Microsoft Excel from Beginner to Advanced.
• Unlock dynamic formulas with IFS, VLOOKUP, INDEX-MATCH and many more.
• Get around Excel like a pro with an Excel keyboard shortcut game.
• Build stunning, professional-quality dashboards in MS Excel (from scratch!)
• Apply powerful techniques for transforming raw data into aesthetic, interactive visuals
Audience
- Business Analysts: Professionals who analyze data to help their organizations make informed business decisions and improve processes.
- Data Analysts: Individuals responsible for collecting, processing, and performing statistical analyses on data sets.
- Financial Analysts: Finance professionals who need to analyze financial data, create forecasts, and generate reports using Excel.
- Marketing Analysts: Marketers who require data-driven insights to optimize campaigns, analyze customer behavior, and track performance metrics.
- Project Managers: Managers who need to track project progress, manage budgets, and analyze project data to ensure successful project delivery.
- Operations Managers: Professionals focused on improving operational efficiency through data analysis and reporting.
- Human Resources Professionals: HR personnel who need to analyze employee data, track performance metrics, and create reports for decision-making.
- Sales Professionals: Sales teams looking to analyze sales data, track performance, and identify trends to drive sales strategy.
- Small Business Owners: Entrepreneurs who need to analyze business data, manage budgets, and make data-driven decisions.
- Students and Fresh Graduates: Individuals aspiring to build a career in data analytics or any business-related field and seeking to develop strong Excel skills.
- Administrative Staff: Office personnel responsible for data entry, report generation, and basic data analysis who want to enhance their skills.
Prerequisites
• Familiarity with using a computer and navigating the Windows operating system.
• Understanding of basic Excel functions, formulas, and data entry.
• A basic understanding of data and its role in decision-making is helpful but not required.
Course Content
Day 1 Introduction to Microsoft Excel
• Pre-Test #1 – Introduction to Excel
• Excel Proficiency Levels
• Importance of Microsoft Excel
• Components of Excel Window
• Excel Specifications and Limitations
• Excel Keyboard Shortcuts
• Excel Functions and Formulas
• Understanding Relative and Absolute References
• The Array and Flash Fill
• Data Validation
• Post-Test #1 – Introduction to Excel
Day 2 Basic Excel Functions
• Pre-Test #2 – Basic Excel
• Case Study #1
• CONCAT / CONCATENATE
• VLOOKUP / HLOOKUP
• TRIM
• MATCH
• INDEX
• INDEX-MATCH
• SUMPRODUCT
• MIN / MAX
• DATAVALUE
• TEXT
• SUMIFS
• AVERAGIFS
• COUNTIFS
• CEILING / FLOOR
• IF
• LEFT / MID / RIGHT
• NOW / TODAY
• COUNTA
• LEN
• AND / OR
• PROPER / UPPER / LOWER
• LEN
• Post-Test #2 – Basic Excel Functions
Day 3 Advanced Excel Functions
• Pre-Test #3 – Advanced Excel Functions
• Case Study #2
• IFS
• FILTER
• UNIQUE
• SORT
• SORT-UNIQUE-FILTER COMBO
• XLOOKUP
• OFFSET
• TEXTBEFORE / TEXTAFTER
• SEQUENCE
• TEXTJOIN
• IFERROR
• FIND / SEARCH
• NETWORKDAYS
• GETPIVOTDATA
• FORMULATEXT
• FORECAST.LINEAR
• Excel 365 New Functions
• Post-Test #3 – Advanced Excel Functions
Day 4 Performing Data Analysis
• Pre-Test #4 – Performing Data Analysis
• Introduction to Data Analysis
• The Consolidate Feature
• Perform What-If analysis using Goal Seek and Scenario Manager
• Forecast Data by using AND(), IF() and NPER() functions
• Calculate financial data using PMT() function
• Best Practices in writing Excel Formulas
• Error Handling Techniques
• Post-Test #4 – Performing Data Analysis
Day 5 Dashboard Making
• Pre-Test #5 – Dashboard Making
• Case Study #3
• Basic Charting
• Bar Charts
• Column Charts
• Pie Charts
• Line Charts
• Scatter Plots
• Sunburst Charts and Treemaps
• Advanced Charting
• Combination Charts
• Plotting Data using Secondary Axis
• Bullet Charts
• Waterfall Charts
• Sparklines
• Forecast Sheets
• Introduction to Pivot Tables
• Applying Conditional Formats to Pivot Tables
• Filtering and Linking Dashboards using Slicers
• Black Magic Dashboarding Techniques
• Understanding Custom number Formats
• Conditional Formatting of Chart Axes
• Display Emojis in your Dashboards
• Displaying indicators on charts
• Post Test #5 – Dashboard Making
