Introduction

Microsoft Excel is an important tool for data analysis. It helps companies accurately assess situations and make better business decisions. This course helps you unlock the power of your organization's data using the data analysis and visualization tools built into Excel. Author Curt Frye starts with the foundational concepts, including basic calculations such as mean, median, and standard deviation, and provides an introduction to the central limit theorem. He then shows how to visualize data, relationships, and future results with Excel's histograms, graphs, and charts. He also covers testing hypotheses; modeling different data distributions; calculating the covariance and correlation between data sets; and calculating probabilities, combinations, and permutations. Finally, he reviews the process of calculating Bayesian probabilities in Excel. Each chapter includes practical examples that show how to apply the techniques to real-world business problems.

Training Objectives

At the end of this training program, you will learn:

• Data Cleaning, the first phase of Data Analytics.

• Data Analytics and how we can do it using excel.

• Process raw data into meaningful data by various phases of Data Analytics: Data cleaning, Data Analysis, and Data Visualization.

• Understand each phase in detail and learn how you can take critical decisions using data in a business.

• How to manage data using Data Validation.

• Understand what Pivot tables are used for and how they are used.

• The types of functions you can use in Excel for Data Analytics to save your time and effort.

• How to Sort and Filter options.

• Understand how VLOOKUP and HLOOKUP functions work for finding specific information in a spreadsheet.

• How you can use conditional formatting in the cells to fulfilling the main purpose of data analysis.

• Understand how you can represent your data for better clarity and decision-making purposes.

• Miscellaneous Analytical Tools

Target Audience

This course is intended for:

• Data analysts

• Spreadsheet authors

• Excel power users

• Finance managers

• Decision makers

• Financial controllers

• Junior accountants

Training Outlines

Day 1: Introduction to Data Analytics & Power BI

• Data analytics fundamentals and Microsoft ecosystem overview
• Roles and responsibilities of a data analyst
• Overview of Power BI products and services
• Getting started with Power BI Service

Day 2: Data Preparation & Transformation

• Connecting to multiple data sources (Dataverse, data flows, etc.)
• Understanding connection methods and performance implications
• Data cleaning, profiling, and transformation techniques
• Handling data errors and optimizing performance
• Lab: Preparing and transforming data in Power BI Desktop

Day 3: Data Modeling & DAX Fundamentals

• Introduction to data modeling concepts
• Working with tables, relationships, dimensions, and hierarchies
• Creating and managing relationships and cardinality
• Introduction to DAX and filter context
• Creating measures, calculated columns, and calculated tables
• Basic KPIs, rankings, and time intelligence

Day 4: Reporting, Visualization & Dashboards

• Designing effective and interactive report layouts
• Building visualizations and enhancing user experience
• Drillthrough, slicers, bookmarks, and conditional formatting
• Creating dashboards and real-time monitoring
• Sharing, publishing, and managing Power BI content
• Lab: Designing and enhancing Power BI reports and dashboards

Day 5: Advanced Analytics, Security & Optimization

• Advanced analytics: forecasting, outliers, and AI visuals
• Decomposition Tree and Key Influencers analysis
• Model optimization and performance tuning
• Dataset management and refresh processes
• Power BI security and Row-Level Security (RLS)
• Workspace management, collaboration, and app publishing