Excel courses

EXCEL tutorial for Business analytics


Télécharger EXCEL tutorial for Business analytics

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Essential Excel for Business Analysts and Consultants?

Practical guide

Excel is a powerful yet a bit overwhelming tool that every business  analyst and consultant will use extensively 

>300 built-in functions

Pivot Tables

Built-in functionalities

Visual Basic

Analytical Tool Pack

I will show you want you should know to be effective using 80/20 rule. You will learn also how to use them effectively

~ 30 built-in functions

Essential Pivot Tables

Essential builtin functionalities

Essential Visual Basics

Essential

Analytical Tool Pack

If you follow my suggestion you will significantly improve your speed of work in analyzing data with Excel

§ Improved speed of working   10 x

§ You will manage to reuse

significate share of your Excel                                                                      40%

for new purposes

§ You can master or need things

in short time                                                                                                  1 week

This presentation will show you the most useful side of Excel that you need to be a great and efficient Business Analyst or Consultant. I will try to achieve it in shortest possible time

What I will show in this presentation is a part of extensive on-line course where I show you in details how to do it in Excel

I will talk about 8 different things in this presentation

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic

Below you can find the list of functions and functionalities that you should master

Basic functions

Financial / Mathematical

Others


§  SUMIF / SUMIFS § COUNTIF / COUNTIFS § HLOOKUP § VLOOKUP § MATCH § SUMPRODUCT § IF § AND / OR § IFERROR § AVERAGEIF § LEFT / RIGHT / MID § FIND § CONCATENATE § YEAR / MONTH / DAY § ROUND / ROUNDUP / ROUNDDOWN § TODAY § VALUE § WEEKDAY

§  RAND / RANDBETWEEN § MOD § NPV § IRR § ABS § MAX / MIN § CORREL

§  Pivot § Slicer § Relative addresses § Formats § Hyperlink § Remove Duplicates § Filters § Sorting § Data Validation § Trace Dependents / Precedent § Analysis Tool Pack 


Agenda

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


You quite often have want to merge 2 data set or on the basis of one data set assign categories to a second one 

                           Merging data

                           Assigning categories

In most cases you  can merge data from 2 different data sets (sheets) using  VLOOKUP in combination with other function

§ Assigning 1-1. For example using this you can assign to

VLOOKUP                          specific car his brand (from other table) on the basis of the

Exact match                                    registration number as ID)

§ Assigning on the basis of interval condition. For example

VLOOKUP                         using this you can assign to all shops between 0 and 500 sq



Approximate match                            m of space the category small

VLOOKUP + MATCH

VLOOKUP + HLOOKUP

VLOOKUP

+ a list of all options

§ Assigning categories to data set on the bases of 2 criteria. For example you can use it to show the probability of startup failing depending on money invested and the business model they are using

For more criteria than 2 you have to create first a list of all possible options and then assign to them the values. Below example for estimating the probability of success for a startup

100

200

100

200

100

200

100

200

112

212

122

222

111

211

121

221

Option number

7%

16%

11%

20%

9%

12%

14%

15%

What is the probability of success of your startup?

What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


As a business analyst or consultant you will quite often get data that lack consistency. There are number of problems you can face with cleaning data. Below the most often occurring examples


Lack of assigned categories

Different Names

Different Units

Joining 2 data points into 1

Dividing 1 data points into 2

§ Warsaw Shopping Mall              § Warsaw

§ Warsaw Shopping Mall           & § Warsaw Shop Mall  

§ 100 000 USD      & [‘000 USD] § 100

§ Asen         § Gyczew    § Asen Gyczew

§ Asen Gyczew         § Asen        § Gyczew


To tackle low quality an lack of consistency of data you have to use a bunch of functions that operate on text as well as some built-in functionalities of Excel that will make the cleaning process easier

SEARCH

LEN

Filters

LEFT

CONCATENATE

Ctrl + H

RIGHT

IFERROR

Remove

duplicates

MID

VLOOKUP

Pivot Table

You should first make sure that the data you are working on are consistent. This will help you speed up analyzes and make sure that you are recommending the right actions

§ 80% of the workload

What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic




IF function is universal yet it is too complicated and time consuming to be used in many situations

Example with IF function

Example without IF function

=IF(G6<=Target!D7;Target!E7;IF(AND(G6>Target                            =VLOOKUP(G6;Target!C:E;3;1)

!C8;G6<=Target!D8);Target!E8;IF(AND(G6>Targ et!C9;G6<=Target!D9);Target!E9;Target!E10)))

Luckily in most cases you can use shorter options to replace the IF function

I will show you how to replace it with other simpler functions on using the motivation system of sales rep as an example

What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


There is bunch of useful functions and functionalities that you have to master to speed up

INDIRECT

R1C1 reference style

Regression

NPV, IRR

Grouping / Ungrouping

Histogram

MOD

Conditional formatting

Data Validation

For more you can go to my extensive on-line course where I show you in details how to do it in Excel


Sometimes you want to see in more graphical way the data or build a dashboard.

There is built-in module in Excel that you can use to achieve this. Go to Home > Conditional Formatting and pick one of the available templates


Sometimes you want to predict certain things using the available data

I nice way to  find relation and create prediction is to use the linear regression

?? =?+?1?? +??

There may be situation that the thing we want to predict depend on more than one variable

?? =?0 +?1?1? +?2?2? +?3?3? +??

?1

?2

?3

?0

I nice way to create prediction is to use the linear regression

There is built-in module in Excel that you can use to do linear regression. Go to Data > Data Analytics > Regression


A good way to look at data on a general level is to create a histogram that shows you the distribution of certain characteristics

Histogram

There is built-in module in Excel that you can use to do Histogram. Go to Data > Data Analytics > Histogram

What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


Pivot Tables enable you do to a lot of things

For some purposes can be replaced with other functions

COUNTIFS

Pivot Tables

SUMIFS

AVERAGEIFS

For more you can go to my extensive on-line course where I show you in details how to do it in Excel

What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


What rules should be used when building analysis in Excel

Usage of colors

Shortcuts 

Consistency between sheets

No mouse

Pyramid principle

Description

1-source  rule

Data source

Repetition of variables

Master sheet

Below the most important shortcut that you should use on PC (1/3)

Ctrl + 1

§ opens the table with formatting the cells

Ctrl + s

§ saves the file

Ctrl + c

§ uploads the cell for copying



Ctrl + v

§ copies the value of the cell

Ctrl + z

§ undoes a command

Ctrl + y

§ Go forward with the command previously undone

Ctrl + b

§ makes the text bold

Ctrl + u

§ underlines the text

Below the most important shortcut that you should use on PC (2/3)

Ctrl + i

§ makes the text italic

F2

§ edits the given cell

F4

§ enables to freeze the formula in the addresses

F9

§ changes the cell formula into value

Ctrl + a

§ select everything

Ctrl + f

§ turns on the search box - you can look for a string

Ctrl + h

§ replaces 1 string with another one

Ctrl + k

§ inserts hyperlink

Below the most important shortcut that you should use on PC (3/3)

Shift + space and afterwards Ctrl + Shift + "+"                      § adds a new row

Shift + space and afterwards Ctrl + "-"                           § removes the row

Ctrl + space and afterwards Ctrl + Shift + "+"                       § adds a new column

Shift + space and afterwards Ctrl + "-"                            § removes the column

Ctrl + Shift + "+“                                                    § lets you add new columns or rows

You select the rows (i.e. Shift + Space and arrow down or up                       § group a bunch of rows

to select the specific rows) and then Shift + ?lt + arrow Right

You select the rows (i.e. Shift + Space and arrow down or up    § ungroup a bunch of rows to select the specific rows  and then Shift + ?lt + arrow Left

You select the Columns (i.e. Ctrl + Space and arrow left or § group a bunch of columns right to select the specific columns) and then Shift + ?lt + arrow Right

You select the Columns (i.e. Ctrl + Space and arrow left or            § ungroup a bunch of columns right to select the specific columns) and then Shift + ?lt +

arrow Left

The most useful functions

Basic functions

Financial /

Mathematical

Others


§  SUMIF / SUMIFS § COUNTIF / COUNTIFS § HLOOKUP § VLOOKUP § MATCH § SUMPRODUCT § IF § AND / OR § IFERROR § AVERAGEIF § LEFT / RIGHT / MID § FIND § CONCATENATE § YEAR / MONTH / DAY § ROUND / ROUNDUP / ROUNDDOWN § TODAY § VALUE § WEEKDAY

§  RAND / RANDBETWEEN § MOD § NPV § IRR § ABS § MAX / MIN § CORREL

§  Pivot § Slicer § Relative addresses § Formats § Hyperlink § Remove Duplicates § Filters § Sorting § Data Validation § Trace Dependents / Precedent § Analysis Tool Pack 


What we will talk about in this course

Merging data from different sources

Cleaning and unifying data

IF and their alternatives

Other useful functions and functionalities

Pivot Tables

Being faster with Excel

Example of analysis

Elements of Visual Basic


You will find this section well developed in another presentation suggested below. Click on the cover below to go to the presentation

For more check our on-line course on how to use the Excel as a Business Analyst and Consultant

Check also business modeling in Excel


….and how to perform market research

Subscribe to our channels:

Check my extensive presentation on productivity hacks to see how you can me 10x more productive

If you need more detailed version on productivity hacks you can check our course on productivity hacks

Check my presentation on on-line models to understand them properly

For more check also my on-line course

Check my presentation on on-line models to understand them properly

Check my presentation on on-line models to understand them properly



12