# Essential Excel for Business Analysts and Consultants

Master the formulas and tricks that will save you time and make you as efficient as consultants in McKinsey, BCG, Bain

4.4/5

## Essential Excel for Business Analysts and Consultants

### What you’ll learn

You will master the most crucial functions and features of Excel
Understand the main challenges in analyzing data in Excel
Analyze complex business problems in Excel
You will master essential Excel shortcuts that will help you drastically speed up your work in Excel
You will learn how to structure Analysis in Excel for your work as Management Consultant or Business Analyst

### Requirements

Basic Excel
Basic knowledge of economics or finance

### Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 3 What to do if a blurry image appears

Lecture 4 How to find additional resources

Lecture 5 How to make the most out of the course

Section 2: How to merge data from different sources

Lecture 6 How to merge data from different sources

Lecture 7 VLOOKUP – Introduction

Lecture 8 VLOOKUP with approximate match – Introduction

Lecture 9 What you can use the VLOOKUP for?

Lecture 10 Basic VLOOKUP usage

Lecture 11 Some comments on basic VLOOKUP

Lecture 12 How to assign categories using VLOOKUP?

Lecture 13 How to use HLOOKUP?

Lecture 14 The logic of MATCH function

Lecture 15 Assigning categories based on 2 criteria using MATCH and VLOOKUP function?

Lecture 16 Assigning categories based on 2 criteria using HLOOKUP and VLOOKUP function

Lecture 17 Assigning categories based on 2 criteria using VLOOKUP only?

Lecture 18 How to assign categories based on 3 and more criteria using VLOOKUP?

Lecture 19 How to assign categories using SUMIFS function – Basic Usage

Lecture 20 Assigning customers to segments – Case Introduction

Lecture 21 Assigning customers to segments – Data Available

Lecture 22 Assigning customers to segments – Solution

Lecture 23 The logic of INDEX function

Lecture 24 INDEX & MATCH function – Introduction

Lecture 25 INDEX & MATCH function – Exercise – Case Introduction

Lecture 26 INDEX & MATCH function – Exercise – Case Solution

Section 3: Example of analysis requiring merging data

Lecture 27 Fuel usage analysis using the vlookup function – intro

Lecture 28 Fuel usage analysis using the vlookup function – tips

Lecture 29 Fuel usage analysis using the vlookup function – solution

Section 4: Cleaning and unifying data

Lecture 30 What problems you may face with data

Lecture 31 How to assign categories using string related functions?

Lecture 32 How to divide 1 data into 2?

Lecture 33 How to join 2 data into 1?

Lecture 34 How you can standarize data?

Lecture 35 Example of unifying data – Saturating existing markets – Introduction

Lecture 36 Saturating existing markets – Data for the case study

Lecture 37 Saturating existing markets – Number of Stores

Lecture 38 Saturating existing markets – Stores by concepts

Lecture 39 Saturating existing markets – Concept & Space

Lecture 40 Saturating existing markets – Solution in Power Point

Lecture 41 Saturating existing markets – WaterFall Graph

Section 5: How to use IF function and what you can do instead?

Lecture 42 Why NOT to use IF function

Lecture 43 How to use IF function?

Lecture 44 How to use VLOOKUP instead of IF function?

Lecture 45 How to use IFERROR function?

Lecture 46 How to use MAX and MIN instead of IF function

Section 6: Pivot Tables

Lecture 47 Pivot tables – introduction

Lecture 48 How to use pivot tables?

Lecture 49 How to use pivot charts?

Lecture 50 How to combine pivot tables with regular calculations?

Lecture 51 How to use slicers?

Lecture 52 How to replace pivot tables with SUMIFS and COUNTIFS

Lecture 53 Going from general to specific with pivots

Lecture 54 Example of analyses using pivot tables- Analysis of sales channels for FMCG

Section 7: Other useful functions and tools in Excel

Lecture 55 Other useful functions and tools in Excel

Lecture 56 Conditional Formating

Lecture 57 How to use indirect function

Lecture 58 Histograms

Lecture 59 Regression – introduction

Lecture 60 Regression – example

Lecture 61 Data validation

Lecture 62 How to use SUMPRODUCT function

Lecture 63 Using solver – Planning the flow of finished goods – Introduction

Lecture 64 Using solver – Planning the flow of finished goods – Excel – First Option

Lecture 65 Using solver – Planning the flow of finished goods – Excel – Second Option

Lecture 66 When can I retire – Introduction

Lecture 67 When can I retire – No passive income – Excel Basic

Lecture 68 When can I retire – No passive income – Go seek function

Lecture 69 When can I retire – With passive income

Lecture 70 What will be the effect of the price increase – Introduction

Lecture 71 What will be the effect of the price increase – Solution – Impact on coffee only

Lecture 72 What will be the effect of the price increase – Solution – Full impact

Section 8: Examples of analyses

Lecture 73 Examples of analyses in Excel

Lecture 74 Sales channel analysis – case in FMCG

Lecture 75 Efficiency of marketing activities

Lecture 76 Model of E-commerce

Lecture 77 Expansion strategy into other countries – Case Introduction

Lecture 78 Expansion strategy into other countries – Data for the case study

Lecture 79 Expansion strategy into other countries – Which function to use

Lecture 80 Expansion strategy into other countries – Solution in Excel

Lecture 81 Expansion strategy into other countries – Solution in Power Point

Lecture 82 How to create a waterfall chart

Lecture 83 How to create scatter plot with Names

Lecture 84 Data Visualization

Section 9: Basic VBA usage – example

Lecture 85 Logistics system simulation – introduction

Lecture 86 Logistic system simulation – tips how to do it

Lecture 87 Logistic system simulation – final solution

Section 10: Essential Excel shortcuts

Lecture 88 Excel shortcuts – Why it makes sense to learn them

Lecture 89 Essential shortcuts – Introduction

Lecture 90 No mouse rule

Lecture 91 How to format cells using shortcuts – Text – Part 1

Lecture 92 How to format cells using shortcuts – Text – Part 2

Lecture 93 How to format cells using shortcuts – Numbers

Lecture 94 Copying of cells with shortcuts

Lecture 95 Selecting rows and selecting columns

Lecture 96 Adding and removing rows and columns

Lecture 97 Grouping rows and columns

Lecture 98 How to preserve your results or go back to previous ones

Lecture 99 Going fast around the Excel with shortcuts

Lecture 100 Selecting area with shortcuts

Lecture 101 Search and replace data

Lecture 102 More advanced shortcuts – Introduction

Lecture 103 Shortcuts hidden in Functional Keyboards

Lecture 105 Working with data in a table

Lecture 106 How to see formulas

Lecture 107 How to reach the menu with shortcut

Section 12: Conclusions

Lecture 108 Bonus Lecture