30 Mar - 3 Apr 2025
Sharm El-Sheikh (Egypt)
Hotel : Sheraton Sharm Hotel
Cost : 3850 € Euro
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.
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
This course is intended for:
Data analysts
Spreadsheet authors
Excel power users
Finance managers
Decision makers
Financial controllers
Junior accountants
Data Cleaning
Resize columns
Understanding the data set
Rename column
Convert data type general to short date
Trim function and Proper function
Remove duplicates
Add symbols to column
Flash fill pack size
Extract the first word
Find and replace inconsistent names
Remove outliers
Missing date
Phases of Data Analytics Project
Data Discovery
Data Preparation
Model Planning
Model Building
Communications Results
Operationalize
Data Validation
Select The Cell for Validation. Select the cell you want to validate.
Specify Validation Criteria.
Select the Criteria.
Select Condition.
Input Message.
Custom Error Message.
Pivot Tables
Introduction to Pivot Tables
The Data Model
Power Pivot
Root Cause Analysis
Comparative Analysis
Pivot Charts and Slicers
Functions - Data Analytics using Excel
The INDEX functions
The MATCH functions
Text functions
The LEFT function
The RIGHT function
The MID function
The TRIM function
The LEN functions
The FIND and SEARCH functions
The REPT function
The CONCATENATE and & functions
The REPLACE function
The VALUE functions
The UPPER, LOWER, and PROPER functions
The CHAR functions
The CLEAN function
The SUBSTITUTE functions
Dates and date functions
CUMPRINC and CUMIPMT functions
Time and time functions
The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions
The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions
The OFFSET functions
The INDIRECT function
Sort and filter
Sorting in Excel
Filtering data and removing duplicates
Lookup Functions
Conditional formatting
Data Visualization using excel
Data Visualization Best Practices
Excel Chart Formatting and Customization
Customizing Chart Elements, Layouts & Styles
Configuring Chart Formats
Changing Chart Types & Adding a Secondary Axis
Excel Chart Customization
Mastering Basic Excel Charts and Graphs
Bar & Columns Charts
Histograms & Pareto Charts
Line Charts & Trendlines
Area Charts
Pie Charts, Donuts & Race Tracks
Scatter Plots
Bubble Charts
Box & Whisker Charts
Waterfall Charts
Funnel Charts
Stock Charts
Heat Maps with Conditional Formatting
Surface & 3D Contour Charts
Geo-Spatial Mapping with Excel Power Map
Common Excel Combo Charts
NEXT level Demos
Creating Custom Image Overlay Charts
Using Binary Values to Highlight Time Periods
Adding Scroll & Zoom Functionality Using Form Controls
Creating Interactive Area Charts to Show Changes Over Time
Building a Dynamic Excel Dashboard
Dynamic Chart Formatting Based on Values
Dynamically Highlighting Segments of Data
Building a Custom Excel Pacing Chart
Visualizing Percentages Using Array Functions
Advanced Excel Data Visualization Techniques
Statistics: Hypothesis Testing and Regression
Types of Data
Fundamentals of Sampling
Distributions
Introduction to Hypothesis Testing
T-tests
Chi-Squared
Test for Normality
ANOVA
Simple Regression
Multi-Variate Regression
Forecasting
Factor Forecasting - Regression
Factor Forecasting - Monte Carlo Simulation
Time Series Forecasting - The Simple Moving Average
Time Series Forecasting - Parameter Tuning
Time Series Forecasting - Auto Regression
Time Series Forecasting - Time Series ARMA
Miscellaneous Analytical Tools
Scenario Analysis
Data Tables
Introduction to Excel Solver Tool
Excel Solver Tool - The Backpack Problem
Excel Solver Tool - The Mixing Problem
Excel Solver Tool - The Traveling Salesman Problem
Practice & Solutions