Essential Excel for Business Analysts and Consultants
4.3 (113 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,393 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Essential Excel for Business Analysts and Consultants to your Wishlist.

Add to Wishlist

Essential Excel for Business Analysts and Consultants

Master the formulas and tricks that will save you time
4.3 (113 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
2,393 students enrolled
Created by Asen Gyczew
Last updated 9/2017
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3 hours on-demand video
  • 1 Article
  • 65 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • You will master the most crucial functions and features of Excel
  • Understand the main challenges in analyzing data in Excel
  • Get ready made analyses in Excel
  • Analyze complex business problems in Excel
View Curriculum
Requirements
  • Basic Excel
  • Basic knowledge of economics or finance
Description

What is the aim of this course?

Excel is the most often used first-choice tool of every business analyst and consultant. Maybe it is not the most fancy or sophisticated one,  yet it is universally understood by everybody especially your boss and your customers.

Excel is still pretty advanced tool with countless number of features and functions. I have mastered quite a lot of them during my studies and while working. After some time in consulting I discovered that most of them are not that useful; some of them bring more problems than solutions. On top of that there are features that we are taught at university that are not flexible and pretty time consuming.  While working as a business analyst I developed my own set of tricks to deal with Excel  I learned how to make my analyses idiot-proven and extremely universal.

I will NOT teach you the entire Excel as it is simply not efficient (and frankly you don’t need it). This course is organized around 80/20 rule and I want to teach you the most useful (from business analyst / consultant perspective) formulas as fast as possible. I want you also to acquire thanks to the course good habits in Excel that will save you loads of time.

If done properly, this course will transform you in 1 day into pretty good business analyst that knows how to use Excel in the smart way. It is based on my 12 years of experience as a consultant in top consulting companies and as a Board Member responsible for strategy, improvement and turn-arounds in biggest companies from FMCG, SMG, B2B sector that I worked for.  On the basis of what you will find in this course I have trained over 100 business analysts who now are Investment Directors, Senior Analyst, Directors in Consulting Companies, Board Members etc.

I teach step by step on the basis of Excel files that will be attached to the course. To make the best out of the course you should follow my steps and repeat what I do with the data after every lecture. Don’t move to the next lecture if you have not done what I show in the lecture that you have gone through.

 I assume that you know basic Excel so the basic features (i.e. how to write formula in Excel) are not explained in this course. I concentrate on intermediate and advanced solutions and purposefully get rid of some things that are advanced yet later become very inflexible and useless (i.e. naming the variables). At the end, I will show 4 full blown analyses in Excel that use the tricks that I show in the lectures.

To every lecture you will find attached  (in additional resources) the Excel shown in the Lecture so as a part of this course you will also get a library of ready-made analyses that can, with certain modification, be applied by you in your work.

 

Why I decided to create this course?

I have done number of courses showing you how to analyze data in Excel. Yet, I have noticed that some students lack the fluency of operations in Excel. This course is designed to fill in the gap and help you fully appreciate my other courses for business analysts and consultants. It can be used also as standalone course that will help you to be smart in Excel

 

In what way will you benefit from this course?

The course is a practical, step by step guide loaded with tones of analyses, tricks, hints that will significantly improve the speed with which you do the analyses as well as the quality of the conclusions coming out of available in your company data. There is little theory – mainly examples, a lot of tips from my own experience as well as other notable examples worth mentioning. Our intention is that thanks to the course you will know:

  1. How to use Excel in a smart way to be able to analyses data fast and efficiently
  2. How to draw conclusions from analyses – chosen examples of analyses
  3. How to be efficient in your work as analyst?
  4. How to build Excels that it is understandable for you and your team, even after some time

You can also ask me any question either through the discussion mode or by messaging me directly.

 

How the course is organized?

The course is divided currently in 6 sections and will be adding new section to address other important issues. Currently you will find the following sections:

  • Introduction. We begin with little intro into the course
  • How to merge data from different sources. Quite often as an analyst you will have to combine data from different sources. In this section I will show you how to do it using VLOOKUP function and others to make sure that you can easily combine data from different tables into the desired analysis
  • Cleaning and unifying data.  50% of your time will be lost on setting the data right so it can be used for analyses. In this section you will learn how to clean and unify data fast
  • How to use IF function and what you can do instead? Most of analyses require you to make them dependable on something. For this you can use IF function which gets messy with more complicated formulas. I will show you how to avoid the mess and in most cases not use IF function
  • Pivot Tables. As a business analyst you will have to look at data on different levels. I will show you how to do it with Pivot Tables, Pivot Charts and what to do instead.
  • Other useful functions. In this section I will show you other useful function that you should master
  • Example of analysis. Here we show full analysis from start to end including drawing conclusions. This will show you want should be the end result
  • Tools for analyzing data. On top of Excel function you have available other tools in Excel that we will discuss in section. Most of them save you a lot of trouble in analyzing data
  • Being faster with Excel. If you spend 6-8 hours a day with Excel you have to be fast not to waste your valuable time. Here I will show you how to do it
  • Visual Basic – main things you should know. Sometimes it is much easier to do something in Visual Basic than go via typical Excel formulas. For some cases Visual Basics creates new opportunities. Here I will show you the most useful elements of Visual Basic.

 

We will be adding new sections in the coming months

 

You will be able also to download many additional resources

  • Excels with analyses shown in the course
  • Links to additional presentations and movies
  • Links to books worth reading

 

At the end of my course, students will be able to…

  • You will master the most crucial functions and features of Excel
  • Understand the main challenges in analyzing data in Excel
  • Perform the analyses in a very effective manner

Who should take this course? Who should not?

  • Business analysts
  • Researchers
  • Controllers
  • Consultants
  • Small and medium business owners
  • Startups founders

 

What will students need to know or do before starting this course?

  • Basic Excel
  • Basic knowledge of economics or finance

 

 

 

Who is the target audience?
  • Business analysts
  • Consultants
  • Researchers
  • Controllers
  • Small and medium business owners
  • Startups founders
Compare to Other Excel Courses
Curriculum For This Course
49 Lectures
03:02:20
+
Introduction
3 Lectures 07:44

Excel is still pretty advanced tool with countless number of features and functions. I have mastered quite a lot of them during my studies and while working. After some time in consulting I discovered that most of them are not that useful; some of them bring more problems than solutions. On top of that there are features that we are taught at university that are not flexible and pretty time consuming.  While working as a business analyst I developed my own set of tricks to deal with Excel  I learned how to make my analyses idiot-proven and extremely universal

I will NOT teach you the entire Excel as it is simply not efficient (and frankly you don’t need it). This course is organized around 80/20 rule and I want to teach you the most useful (from business analyst / consultant perspective) formulas as fast as possible. I want you also to acquire thanks to the course good habits in Excel that will save you loads of time.

Preview 04:15

A few words about your humble teacher

About Me
01:51

Here I give you some tips how you can get the best out of the course

Preview 01:38
+
How to merge data from different sources
10 Lectures 28:35

In this lecture we show you what you can use the VLOOKUP function for. It is one of the most widely used function that simplifies a lot of things

Preview 01:37

We start with basic VLOOKUP Usage just to show you the flavor and basic construction of VLOOKUP.

Preview 03:20

We start with basic VLOOKUP Usage just to show you the flavor and basic construction of VLOOKUP.

Preview 01:58

VLOOKUP can be used to assig categories. This helps you easily by creating rules divide data into segments, cohorts and analyze them, draw conclusions

Preview 02:58

HLOOKUP is a cousin of VLOOKUP. It is not that often used independently but has some serious application when combined with VLOOKUP

How to use HLOOKUP?
01:37

In this lecture I will show you how to assign categories using VLOOKUP and MATCH. You have the categories described in the matrix and you want to use them

Preview 04:00

In this lecture I will show you how to assign categories using VLOOKUP and HLOOKUP. MATCH can be used as alternative to the HLOOKUP

Assigning categories based on 2 criteria using HLOOKUP and VLOOKUP function
02:44

When you have more than 2 categories you have to resort to a number of tricks and the VLOOKUP function. I will show you on the basis of 2 criteria how to do it

Assigning categories based on 2 criteria using VLOOKUP only?
04:16

We continue with the example from previous lecture and show you how to approach cases when you have 3 criteria. This method can be scaled to any number of criteria

How to assign categories based on 3 and more criteria using VLOOKUP?
04:46
+
Example of analysis requiring merging data
3 Lectures 13:48

In this case we will analyze what is the fuel usage for different cars, what it depends on and how to optimize it

Preview 04:08

In this lecture we will give you some tips how to approach the case and show you the usage of the proper Excel functions

Fuel usage analysis using the vlookup function - tips
06:37

In this lecture we solve the case with you, show you the outcome, interpret it and give you examples how the results can be presented in terms of Excel and slides

Fuel usage analysis using the vlookup function - solution
03:03
+
Cleaning and unifying data
6 Lectures 20:23

In many cases the data you have to work with are far from ideal.  I will show you what you can do when you want to join or divide data point, how to unify and standardize it

Preview 02:19

In many cases you have create categories / tags on the basis of the name of the data point. I will show you here how to do it automatically

How to assign categories using string related functions?
03:11

Sometimes you want to divide 1 data point into 2 seprate ones. I will show you in this lecture how to do it

How to divide 1 data into 2?
02:54

Sometimes you want to join 2 data points into 1. I will show you in this lecture how to do it

How to join 2 data into 1?
01:23

In this lecture I will show you how to standardize data points to make analyses much easier

How you can standarize data?
04:01

If you work for a retailer one of the most useful analysis is to check whether there is still potential to growth and how big it is. In this lecture I will show you 2 ways in which you can estimate how many shops you can open in a specific location, city, province. One approach will be concentrated solely on the number of shops whereas the second one will also take into account the size of shops

Example of unifying data - analysis potential of growth for a retailer
06:35
+
How to use IF function and what you can do instead?
5 Lectures 13:42

IF function can be very problematic. I will show you in this lecture why is not always the best choice

Preview 01:56

We start with showing you how to use IF function

How to use IF function?
01:42

In many cases IF can be replaced successfully with VLOOKUP. I will show you how to do it and what are the benefits

Preview 03:45

In some cases you need to use IFERROR. It can be used as a replacement for IF or independently

How to use IFERROR function?
02:11

Sometimes make sense to use MIN and MAX instead of IF. I will show you how and when to do that

How to use MAX and MIN instead of IF function
04:08
+
Pivot Tables
8 Lectures 22:15

Pivot Tables help you group data and analyze them fast. You can go from general to specific within seconds thanks to pivot tables. I will show you in this lecture how to use pivot tables, what you can use instead

Preview 01:24

We start with basic usage of pivot tables

How to use pivot tables?
05:28

A cousin of pivot tables is pivot chart. I will show you how to use the pivot charts and how does it relate to pivot tables

How to use pivot charts?
02:39

It is not easy to get data from pivot tables. I will show you how to do it without any complications and special formulas

How to combine pivot tables with regular calculations?
02:17

Slicers are nice add-on to pivot tables and pivot charts that enable you fast filtering without any knowledge of pivot tables. They can be successfully used to create i.e. dashboards

How to use slicers?
02:07

Sometimes you need more Excel like to build on the basis of this. I will show you how to get the same results without pivot tables

How to replace pivot tables with SUMIFS and COUNTIFS
03:17

Pivot tables enables you to go from general to specific. I will show you how to do it 

Going from general to specific with pivots
01:57

Most producers / brand owners operate using many sales channels: wholesalers, own shops, e-commerce, marketplace etc. It makes sense to check how profitability looks across channels and what can e done to improve overall situation. I will show you how it cane be done with a simple Excel analysis and a pivot table. I will go also try to demonstrate what kind of conclusions can you draw and what should be your next steps, on the basis of the results you are getting form the analyses

Example of analyses using pivot tables- Analysis of sales channels for FMCG
03:06
+
Other useful functions and tools in Excel
6 Lectures 22:10

Conditional Formating helps you data be more understandable to people. This is great tool for creating dashboards. I will show you how to use them

Conditional Formating
04:42

Indirect function enables you to  make very complicated function with flexible area to which it relates. I will show you how to do it

How to use indirect function
03:12

Histograms help you see the frequency of data occurrence and to see what is the distribution. I will show you how to calculate it using build-in tools in Excel

Histograms
02:49

Regression model is a powerful tool you can use to analyze data, predict things, find relation between different phenomena. I will show you how to use it in Excel

Regression - introduction
02:23

Regression model is a powerful tool you can use to analyze data, predict things, find relation between different phenomena. I will show you how to use it in Excel

Regression - example
06:03

You can limit the freedom of people in the input of data. Using data validation you can for example you can create a list from which they will have to choose defined options. I will show you how to do it

Data validation
03:01
+
Examples of analyses
4 Lectures 27:10

Most producers / brand owners operate using many sales channels: wholesalers, own shops, e-commerce, marketplace etc. It makes sense to check how profitability looks across channels and what can e done to improve overall situation. I will show you how it cane be done with a simple Excel analysis and a pivot table. I will go also try to demonstrate what kind of conclusions can you draw and what should be your next steps, on the basis of the results you are getting form the analyses

Sales channel analysis - case in FMCG
03:06

Efficiency of marketing activities
08:19

If you work for a retailer one of the most useful analysis is to check whether there is still potential to growth and how big it is. In this lecture I will show you 2 ways in which you can estimate how many shops you can open in a specific location, city, province. One approach will be concentrated solely on the number of shops whereas the second one will also take into account the size of shops

Potential growth by regions - case in retail
06:35

Here we show how to model the business model of e-commerce


Preview 09:10
+
Basic VBA usage - example
3 Lectures 25:57

In this case we will do the simulation of the whole logistics system and show you how to choose from many options

Logistics system simulation - introduction
04:57

Here we will give you tips how to calculate the cost of the whole system and how to prepare for the simulation

Logistic system simulation - tips how to do it
09:53

We present here final solution to the case. We show how to do the simulation of logistic costs for 8 different options in 5 minutes

Logistic system simulation - final solution
11:07
+
Conclusions
1 Lecture 00:47
Bonus Lecture: What's next for you?
00:47
About the Instructor
Asen Gyczew
4.2 Average rating
988 Reviews
21,463 Students
23 Courses
Expert in performance improvement, turnaround and startups

Expert in performance improvement, startuping and turning-around companies with significant experience both in management as well as in supervision of medium size companies (EUR 20 – 40 million) as well as startups.

Specialties:

Functional experience: performance improvement, strategy development, startups, intrapreneurship, post-merger integration and cost reduction, due diligence;

Industry experience: SMCG (domestic appliances, furniture, ceramic tiles), FMCG (cosmetics, juice, meat), retail (electronics, convenience shops, pharmaceuticals, DIY, vending), B2B (aluminum system, plywood, alcohol, reagents, loading systems), services (MRO, consulting, outsourcing)

Countries: Poland, Serbia, Russia, Ukraine