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

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

• Syntax of the lookup functions

• VLOOKUP
• HLOOKUP

Conditional formatting

• Conditional formatting
• Using conditional formatting to highlight each employee’s ratings
• Using conditional formatting to highlight outliers.

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