0

ecommerce/course.login

ecommerce/course.or, ecommerce/course.create_accountCreate an account in checkout page
Excel

Excel for Business Analysts

Learn the Functions and Formulas you need to perform detailed Data Analysis in Excel from Excel experts.

4.4
(337 ratings) 949 students



Enroll now


What you will learn

How to merge data from different sources using VLOOKUP, HLOOKUP, INDEX MATCH, and XLOOKUP
How to use IF, IFS, IFERROR, SUMIF, and COUNTIF to apply logic to your analysis
How to split data using text functions SEARCH, LEFT, RIGHT, MID
How to standardize and clean data ready for analysis in Excel
About using the PivotTable function to perform data analysis
How to display your analysis using Pivot Charts in Excel
Conducting a Linear Forecast and Forecast Smoothing in Excel
All about Histograms and Regression in Excel
How to use Goal Seek, Scenario Manager, and Solver to fill data gaps in Excel

Who should take this training

Prerequisites

  • Microsoft Excel 2016, 2019, 365

Target audience

  • Business analysts who are Excel users

About this training

We start by looking at how to take raw data and prepare it for analysis. After that, we look at a number of tools and functions that can be used to conduct analysis such as Pivot Tables, before moving onto how to display data in the most meaningful way using charts, Pivot Charts, Slicers and so much more. Finally, we move onto some more advanced techniques designed to aid forecasting and using existing data to predict future trends.

This course includes:

    This course was recorded using Excel from Office 365. It's also relevant to those using other, recent versions of Microsoft Excel including Excel 2013, 2016, and 2019.

Course Language : EN
On-demand video
Full lifetime access to videos
Downloadable resources
Assignments
Certificate of Completion
Enroll now

Training options

Only Videos

$ 30

  • Full lifetime access to videos
  • Downloadable resources
  • Certificate of Completion
  • Hours of Individual Coaching

Blended

$ 550

  • Full lifetime access to videos
  • Downloadable resources
  • Certificate of Completion
  • 10 Hours of Individual Coaching
Save 10%

Blended

$ 1040 $ 963

  • Full lifetime access to videos
  • Downloadable resources
  • Certificate of Completion
  • 20 Hours of Individual Coaching
Save 20%

Blended

$ 1590 $ 1272

  • Full lifetime access to videos
  • Downloadable resources
  • Certificate of Completion
  • 30 Hours of Individual Coaching

Course Content

1. Introduction
1. Introduction to Business Analysis 05:25 mins
2. The Basics
1. A Recap of Basic Formulas 12:42 mins
2. Absolute vs Relative Referencing 08:16 mins
Exercise 01 02:03 mins
Basic Functions
Absolute Referencing
Exercice 01
3. Merging and Looking Up Data
1. Merging data from different sources 02:48 mins
2. Looking up Information with VLOOKUP (Exact) 10:58 mins
3. Looking up information with VLOOKUP (Approx) 06:27 mins
4. How to use VLOOKUP with data that expands 10:01 mins
5. Using HLOOKUP 12:16 mins
6. Combining VLOOKUP and MATCH 12:26 mins
7. Using INDEX, MATCH and XLOOKUP 17:26 mins
8. Data Validation Lists with Lookups 08:30 mins
Exercise 02 06:24 mins
VLOOKUP Exact
VLOOKUP Approx 1
VLOOKUP Approx 2
VLOOKUP Expanding Data
HLOOKUP
VLOOKUP and MATCH
INDEX MATCH and XLOOKUP
Exercise 02
4. Making Better Decisions with IF
1. How to use the IF function 05:30 mins
2. More Examples of the IF Function 07:09 mins
3. Working with Nested Ifs 06:36 mins
4. The IFS function 08:47 mins
5. Error handling with IFERROR and IFNA 09:22 mins
6. Using MAX and MIN instead of the IF function 11:26 mins
7. Using SUMIFS and COUNTIFS 11:52 mins
Exercise 03 01:32 mins
MAX and MIN
IFERROR and IFNA
IF Statements
COUNTIF and SUMIF
Basic IF Statements
Exercise 03
5. Preparing Data for Analysis
1. Splitting data using text functions 16:14 mins
2. Using the Flash Fill command 04:40 mins
3. Concatenating data 09:04 mins
4. Standardizing data 09:01 mins
5. Formatting data as a table 09:46 mins
Exercise 04 02:28 mins
Flash Fill
Sales Data
CONCAT and TEXTJOIN
Sales Data.csv
ImportText.txt
Exercice 04 - Sales Data - Answer
Sales Data.txt
6. PivotTables
1. An introduction to PivotTables 03:16 mins
2. Creating a PivotTable 12:40 mins
3. Summarizing Data 13:53 mins
4. Calculations in PivotTables 15:13 mins
5. Using Slicers to filter information 09:56 mins
Exercise 05 01:54 mins
Calculations
PivotTables
Summarizing Values
Slicers
Exercise 05
7. Visualizing Data with Charts
1. Creating a Pivot Chart 10:38 mins
2. Formatting a Pivot Chart 14:26 mins
3. Using Sparklines 07:53 mins
4. A Basic Interactive Dashboard 14:46 mins
Exercise 06 02:52 mins
Pivot Charts
Formatting Pivot Charts
Sparklines
Interactive Dashboard
Exercise 06
8. Forecasting
1. Forecast Sheets 07:18 mins
2. The Forecast Function 07:25 mins
Exercise 07 01:35 mins
Forecast Sheets
Forecast Functions
Exercise 07
9. Additional useful tools in Excel
1. Conditional Formatting 11:29 mins
2. The INDIRECT function 13:29 mins
3. The OFFSET function 10:32 mins
4. Using Histograms 04:56 mins
5. Regression 06:41 mins
Exercise 08 02:13 mins
Conditional Formatting
INDIRECT
OFFSET
Regression
Sales Data - Conditional Formatting
HR Data
Frequency Distribution
Exercise 08
10. WhatIf Analysis
1. Goal Seek 07:43 mins
2. Scenario Manager 08:01 mins
3. Data Tables 07:13 mins
4. Solver 06:51 mins
Exercise 09 01:42 mins
WhatIfAnalysis
Exercise 09
11. Course Close
Course Close 00:45 mins
Quiz - Business Analysis Questions and Answers

Request more information

Similar courses

Join our mail list for news