Essential Excel for Business Analysts and Consultants

A few words about your humble teacher

Here I will show you what to do if a blurry image appears

Here I will show you how to find additional resources attached to the coruse like Excel files, presentations, links etc.

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

During consulting projects, you will have to merge data from different sources to create a nice presentation. In this section, we will discuss how to do it in practice, and what excel functions you should use. We will use in this section extensively functions like VLOOKUP, HLOOKUP, MATCH, and many others.

In this lecture, I will show you how to use one of the most useful functions - the VLOOKUP.

In this lecture, I will show you how to use one of the most useful functions - the VLOOKUP. We will look at the additional value coming from the so-called approximate match

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

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

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

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

Another useful function is the so-called MATCH. We will see what it gives you.

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

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

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

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

To assign categories that are numbers you can also use Sumifs which works for many criteria. In this lecture, I will show you how to use it. Later on, I will use it to assign also categories that are not numbers but for example names

Now we will use the SUMIFS to assign to every person a segment that he belongs to. We will do it in 2 phases. First, we will assign a number that will represent a specific segment, and then using VLOOKUP we will change this into a name.

In this lecture, I will show you the data we will need to solve the case study.

In this lecture, we will solve the previously introduced case study.

Some firms use heavily INDEX function. Let's see how it can be used in practices

Now let's combine INDEX and MATCH functions.

Now let's combine INDEX and MATCH funciton

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

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

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

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

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

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

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

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

We will now try to check in practice how to find saturation point for a retailer. In this lecture I will introduce the case that we will work with for the next few lectures. You will be asked to find the saturation for grocery store operating in Poland

In this lecture we solve previously introduced case.

Here I will show you how to create a waterfall graph

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

We start with showing you how to use the IF function

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

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

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

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.

We start with basic usage of pivot tables

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

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

Slicers are a 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

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

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

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

In this section we will discuss other useful tools from Excel like Conditional Formating, indirect function, data validation, regression, go seek. solver and many more. They will help you deliver your work fast and efficiently

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

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

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

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

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

SUMPRODUCT is a great function that you can use to calculate faster the revenues of the firm by weighted average. I will show you how to do it in Excel.

Let’s have a look at car producer that is wandering which plant should supply to which country 

In this lecture I will show you how to use solver to find optimal solution to the case of planning the flow of finished goods between factories and markets 

Try to estimate at what age can Peter retire using his own savings. To estimate the age of retirement use the backward reasoning

In this lecture I will show you the solution to previously introduced case study

Future is pretty difficult to figure out. You can use scenario analysis or you check ALL the potential options and see which is optimal. I will talk about this technique in this lecture and when to apply it. The impact of the price change on your profit will depend on a few factors. I will discuss in this lecture on what you should take into account and how to calculate it .

In this lecture I will show you how to solve the case shown in the previous lectures

Here I will show you some examples of analyses using things we have learned so far

Here I will show you how to estimate efficiency of marketing activities

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

In this lecture I will start the case study that we will be solving for the next few lectures. Imagine that you are a Spanish fashion retailer and you want to figure out which countries you should enter. Therefore, you prepare a ranking of countrires that show attractivness and the size of each and every market.

Here I will show you how to create a waterfall chart

Here I will show you how to create a scatter plot with the names of the data points

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

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

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

I will explain you here how it makes sense to master the Excel shortcuts and how they will help you to become more productive

In the second section I will discuss essential shortcuts that you have to learn by heart to prepare fast analysis

In this lecture I will explain you why you should not use the mouse