Essential Excel for Business Analysts and Consultants?
Practical guide
>300 builtin functions  Pivot Tables  Builtin functionalities  Visual Basic  Analytical Tool Pack 
~ 30 builtin functions  Essential Pivot Tables  Essential builtin functionalities  Essential Visual Basics  Essential Analytical Tool Pack 
§ 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 online course where I show you in details how to do it in Excel
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
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 
Merging data
Assigning categories
§ Assigning 11. 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
 
 

§ 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 


Option number
7%  16%  11%  20%  9%  12%  14%  15% 
What is the probability of success of your startup?
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
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 builtin functionalities of Excel that will make the cleaning process easier
SEARCH  LEN  Filters  
LEFT  CONCATENATE  Ctrl + H  


 
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
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 
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)))
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 online 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 builtin module in Excel that you can use to achieve this. Go to Home > Conditional Formatting and pick one of the available templates
?_{?} =?+?_{1}?_{?} +?_{?}
?? =?0 +?1?1? +?2?2? +?3?3? +??
?_{1}
?_{2}
?_{3}
?_{0}
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
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 


For more you can go to my extensive online course where I show you in details how to do it in Excel
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 
Usage of colors  Shortcuts  
Consistency between sheets  No mouse  
Pyramid principle  Description  
1source 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
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
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
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 
For more check our online course on how to use the Excel as a Business Analyst and Consultant
Subscribe to our channels: