YouTube Transcript:
Complete Excel Tutorial for Data Analysis in 4 Hours (with FREE Files)

Skip watching entire videos - get the full transcript, search for keywords, and copy with one click.

Video Transcript

View:

Microsoft Excel is by far the most

important and versatile software when it

comes to data

analysis in this lengthy and

comprehensive lesson we are going to

learn how to use Excel right from

scratch all the way up to setting up

formulas all the important Excel

functions analyzing the data using pivot

table feature of

Excel and finally including with a

comprehensive portfolio dashboard like

this using which you can analyze a call

center data and visualize the results in

a beautiful interactive manner like this

we are also going to learn how to use

the power query feature of excel to

automate and clean our data easily

welcome to Excel for data analysis video

let's go my name is chandu and I have

been using and teaching Excel for the

last 16 years let's start our lesson

with the basics of excel we can use

Excel to analyze the data track

information or update a project or

collaborate with others when you open a

blank Excel file this is what it

normally looks

like the screen itself is divided into

three main areas we have got the rib bun up

top and we have got this massive grid

area where you have got row numbers 3 4

5 like that and column numbers b c d k m

Etc the intersection of these things is

called a cell a cell is where you can

put any values you can type some numbers

put some text or put an image or anything

anything

else and the grid itself is divided into

sheets so right now we have got one

sheet but you can use this plus button

to add multiple sheets so typically when

you open Excel it might add one sheet or

it might have three sheets underneath

all of this we have got a status bar

that tells us what is going on at any

time as well as we can use this to zoom

in or zoom out of the spreadsheet as we

go through the lesson you will

understand how to access and use various

things on the screen so I'm not going to

bore you with details here instead what

we will do is we will open a sample data

file that I have prepared for you and

use that to understand Excel a quick

note here I have put a set of resources

for you one for each concept that we are

covering so introduction to excel there

is one file likewise there is files for

formulas pivots and the portfolio

project and power query sections so feel

free to refer to the link in the video

description to access all of these files

from my

website so here is my introduction to

Excel file this is an employee data set

we are going to use this same data set

later on as well so that you will

understand how this data set helps you

explore and understand various things

about the

data so here I have got some employee

IDs names gender Department salaries

what is the date on which that employees

started with us what is their fulltime

equivalent or FTE what kind of employee

they are and where they work

the first thing that we will understand

is whenever you look at some data like

this you may want to for example sort

this data or filter it for a specific

department or generally explore this

data and this is where the filter

functionality of excel is really handy

all you have to do is when you have data

like this select all of this data you

can use control shift down arrow to

select an entire column likewise control

shift right arrow to select an entire

row but when you have a grid like this

you can press control a a for all to

select all of this data you can also

data once the data is selected in the

home ribbon if you go into sort and

filter options you can apply a filter on

this data so once this is selected I'm

going to go here and apply a filter and

now that the filter are applied for

example I can look at all the employees

in our Chennai office so I can just

select that and when you click okay all

of the employees that are currently

working within our Chennai office of

India are selected when you are

filtering the data you can select a

single item or you can select multiple

items as well and then all of those

employees that satisfy any of those

conditions will come in you can apply

the filter on a single column or you can

apply them on multiple columns as well

so for example I can select multiple

locations and then I can say show me all

the female

employees Within These locations and we

will get a subset of the data like

this when you have number columns like

salary or date columns like start date

you can also apply special filters so

for example I can say salary and then I

can go into number filters and then I

can say greater than and I can type in a

number and see the employees that are

making more than that money so for

example let's take a look at all the

people who are making more than

80,000 now these are the employees and

once you apply the filters if you clear

filter on a specific column so for

example now that the salary and gender

filters are there I want to see who is

these people across the board so I can

go here and use the clear filter from

work location and now what we are seeing

is all the employees that are making

more than 80,000 and of female gender

type just as you can filter the data

using this filters you can also sort the

data for that first I'm going to clear

the filters when you have filters on

multiple columns you will have to go to

individual columns and clear the filter

which is a bit tedious alternatively you

can also go to home sort and filter and

then just select clear and all the

filters will be cleared for

you everything in Excel comes with a

shortcut so for example if you want to

use a shortcut to clear the filters you

can go here and hover your mouse on the

clear button and then if there is a

shortcut available it will show right

there for example clear doesn't have a

built-in shortcut but filter does if you

point your mouse on filter you'll see

that control shift L is the shortcut for

filtering the data but there is a way to

access the shortcuts for things that

don't have a Belin shortcut to do that

for example to clear the filters first

up I'm going to apply a filter so let

fil let's filter everybody who is in

Wellington New Zealand and now to clear

the filter as there is no shortcut here

only the the button says clear what we

can do is we can press alt button

briefly and you'll see that there are

shortcuts for every ribbon so for

example alt H would get us into the home

ribbon so we're going to say alt H and

once we are in the home ribbon now we

can press s to get into the sort and

filter s and then you can see that C is

for clear this is a powerful and elegant

way to learn Excel as you're using it

every time you need a shortcut you just

hold the ALT key briefly and then press

the keyboard sequence to get to the

button that you want so in this case we

want to clear so I'm going to press C

and bingo my data is now fully visible

like I said we can also filter the data

as well as sort the data with filters so

to sort the employees by their salary I

can select the salary column and then

say smallest to largest and I'll see the

employees sorted like this here is a

quick puzzle for you what if I want to

sort the employees but by gender first

and within the gender by salary right

now the Sorting is done in such a way

that everybody is sorted but instead

what we want is all the male employees

up top and then within the male their

salaries and then female employees and

within female their salaries how would

you do such two Lev sorting give it a

try our next thing that we want to do

when whenever you're looking at data

like this is you might be wondering if

there is any duplicate information in

this data for example there is duplicate

information in this data you can

actually see it right here van tawell

the employee has repeated three times

that name but if you look at the

employee ID itself there is two

instances of that ID this sort of a

thing is a problem when I'm trying to

analyze the data and I want to identify

if there is any employee IDs that are

duplicated to do that we are going to

select the employee ID column select

this entire column and from home ribbon

use the conditional formatting highlight duplicate

duplicate

values when you click okay all the

duplicate employees will be highlighted

in this pink color so you can easily

spot them what if you don't want to spot

them what if you want to filter them now

that the employees are highlighted you

can use the filter and use the color

option to filter all these colored

employees and we can see van tax Val is repeated

repeated

pratigyaa and Jamie Aeris and sachat

probal now these people the names are

different but they're sharing the same

employee ID which is also a problem it

kind of tells me that there is some data

quality issues here why two employees

have same ID but they are having

different names and if you look at their

salaries and departments and gender date

of join FTE everything is matching just

the name is different

so something wrong here and again this

kind of a thing is easy to identify

using the conditional formatting

highlight duplicate values option I'm

going to clear the filters on this and

to take away that color you can again

select this data go to home conditional

formatting clear rules from selected

cells I'm going to leave these

highlighting there but you can use this

option to clear the highlighting from

any selected cells or the entire

worksheet let's say looking at these

employees you want to understand who has

this question mark Department maybe

there's some data quality issues and we

are not getting the department name for

everybody to filter them you have two

ways of doing this one way is you can

select this and unselect everything and

select the question mark but I'm going

to show you a powerful and simple way to

do this which is just right click on the

value that you want to filter and go to

filter selected sales value this is

going to filter whatever you have

currently selected and here I can see

these three people are not flagged into

their correct departments again this

tells me that there is something fishy

going on with this data and maybe I want

to check up with our payroll department

to understand what is going on here

another thing that you may want to do

whenever you are analyzing the data like

this is for example you may want to see

all the top five salaries or bottom five

salaries or all the people who have

joined recently or something like that

so for example in the salary column we

have already sorted this so I'm going to

take the Sorting to my employee ID and

while looking at like this I want to see

who are our top 10 employees by the

salary so I can use the filter again and

go to number filters and use the top 10

option and say I want to see the top 10

items you can change these things by the

way and change from top to bottom if you

want to see the bottom 10 and once you

adjust this and click okay you will be

able to see the employees that are

making highest

salaries you can also apply special

filters for dates if you have a start

date you can use the date filters and

for example see which employees have

joined in the last week or last month or

last quarter or last year you can also

use the between option to select

employees that joined between two points

in time let's conclude this segment of

our Excel Lesson by building a quick

summary of our data for example I may

want to calculate what is the total

salary what is the total FTE or what is

the total count of employee IDs to

calculate the total of any column you

can select the entire column again the

shortcut is control shift down arrow and

once the column is selected you can use

the home ribbon and auto sum button in

fact if you place your cursor on it

you'll see the shortcut for this which

is alt equal to so once you select the

column if you press alt equal to you

will get the total added at the bottom

the column is too narrow to show it and

it will give you what is the sum you can

examine this formula to examine the

formula if you select the cell and press

f2 to edit it you'll see that it has

written a sum formula for us sum is this

much and you can apply some formatting

on it let's make it bold and let's fill

some color into it so we can see that

our total salary for this set of

employees is 4. .17 million likewise I

can do the same for FTE here I can

select this cell and press alt equal to

when I select a blank cell Excel

automatically identifies all the cells

above it and adds it up for me and then

tells me our total FTE is 51 let's do

that for this as well alt equal to and

unfortunately as these are text values

Excel hasn't tell told me what is the

count of employees it is trying to sum

but these are text values so it's not

going to work we are going to later on

cover how to add counts of employees or

how to count a number of male employees

or how to count the number of employees

that are in a specific Department Etc

using various powerful Excel functions

as you can see here doing some quick

analysis of excel is really simple in

the subsequent parts of this video we

are going to see an expanded set of this

data and understand how to use power

query to automatically clean and

manipulate this data and then how to use

Excel tables formulas and pivot tables

to analyze this data let's continue our

journey of all the features that are

available in Excel and powerbi power

query is the number one feature for me

why because it lets me save time and get

to my data quickly so in this video

Let's understand what power query is and

how to use it with two practical

examples the these are number one is a

web scraping example where we're going

to connect to a website and get the data

in a dynamic fashion and the second one

is we are going to connect to a local

network file and get the data from it

through these two examples we're also

going to uncover many of the powerful

and useful time-saving features of power

query let's go oh by the way this is

part of my free data analysis course

series on YouTube you can see the

previous Parts as well as the next Parts

by looking at the playlist that is

linked in the video description below or

going to the web page that I have set up

for the free data analyst course let's

go so what is power query power query is

a data cleaning and a data

transformation software it comes

prepackaged with both Excel as well as

powerbi and the approach of using this

software is exactly similar whether you

use it in Excel or powerbi you can also

use power query with the online powerbi

platform as well as fabric so today we

are going to look at two examples one is

a web scraping example and the second

one is a local network example so both

of these we're going to do through Excel

but you can also apply all of these

steps from powerb as well for our web

scraping example we are going to look at

the 2020 Summer Olympics medal table

data this is a publicly available data

set available on the Wikipedia

website if you go on this page you'll

see that there is a nice little table

that is available here let's say For an

upcoming data analysis project I would

like to get this data into Excel as a

neat table we can of course copy paste

this data but we would like to connect

to this website and get this data that

way if and when this website changes we

can and refresh and get the new data of

course the Summer Olympics medal table

from 2020 is not going to change but

once you know the technique you can

connect to a more live data set and get

the upto-date

information so let's go to the Excel

here I have already got the URL here but

you can also copy the URL from the

browser address bar and we'll go to the

data ribbon and you're going to find all

the power query related options inside

the data ribbon here there is also going

to be a query tab that appears once we

have set up a successful

connection if you're using any version

of excel from 2016 or onwards you will

find these options here for a slightly

older version like 2013 you may have to

enable some features of excel to see the

get and transform data or power query

options there so we're going to use

these buttons If you see here get data

there are many ways in which you can get

the data into Excel through part query

and some of these options will appear or

disappear depending on what type of

excel you have as we want to get the

data from web we can say get data from

other sources from web alternatively

there is also a button called from web

right here so we're going to click on

that and paste the URL here click okay

and you're going to get a navigator

screen with all the tables that are

available in that web page

you might see some different options

depending on which version of excel you

are running but you should essentially

see the text and the HTML tables that

are available on the page and you can

kind of select the 2020 Summer Olympics

medal table 36 that's the name of the

table and this is the data that we want

you can see all the information here

nicely listed we would like to load this

data into Excel but before we do that we

would also like to do some cleanup steps

on this data for example Japan has an

asterisk next to it because it's the

host nation likewise there might be some

other options here like null values for

all of these countries where they have

shared the rank of the medals so for

example Greece and Uganda have same

kinds of medals so they're both tied for

the 36th space so this is where you can

use the transform data button to load

this data into Power query so that we

can experience what power query is like

like so let's hit on

that and this will open the power query

editor this is a really powerful and

amazing software that we use to clean up

transform and create new kinds of data

based on what we already have let me

first quickly explain what we see on the

screen the screen has four main areas

we've got this big ribbon area up top

this is where all the buttons of power

query are listed the ribbon itself is

bro broken into various kinds of ribbons

we have got home transform add column

view ribbons and then the main area of

power query is split into three areas

this is 1 2 3 the first area lists all

the queries that you have right now

there is only one query think of query

as a table for now but essentially you

can also have other kinds of data here

the second area shows whatever query we

have selected so right now we have one

query and that is being shown as a preview

preview

here in this part of the screen and the

third area tells us what is it that we

have done on that data so far so these

are called steps and think of steps as

operations or things that you do on top

of the data right now we haven't done

anything all we did is we connected to a

source the source is the Wikipedia

website and we extracted the table so

those are the two steps that we are

seeing right now but as you make more

operations on this data

you will see Power query records those

steps for each step there is a bit of

associated code written into the data

and you can kind of see the preview of

the code here in the formula bar now if

you're not seeing this formula bar that

means youve disabled it or it was not

enabled at all you can go to the view

ribbon and enable the formula bar there

so that you can start to see that little

piece of code anyhow we don't even need

to know what this code is in in order to

use the power query so let's go ahead

and actually use this data and Define

some Transformations or cleanup steps on

top of this data so this is where we are

connecting to the web page getting the

data and now as I'm seeing the data I'm

thinking okay wait a sec the headers are

all wrong they are not column 1 2 3 4 5

there should be this column this row

here so in order for us to do that we

would like to take this and move it into

the header area likewise if there is any

extra characters like this we want to

delete them and if there is any extra

blank spaces here we want to treat that

as well so that's all we are going to do

to begin with we are going to take this

row number one and make it a

header if for you when you loaded the

data this was already the header you can

ignore this step so from the home ribbon

we can use use first row as headers

button here when you click on that this

row will become the header and it will

discard the the previous headers now

that that step is done we're going to

look at the no column here this is

actually the member country or country

so I'm going to double click on that

column and then call this as

country and then wherever there is an

extra symbol like this as risk we would

like to take it out so we can do that

through a find replace kind of an

operation you can just right click on

this column and then select replace values

values

in here I'm going to say replace the

star with nothing so I'm not going to

type anything here and when you click

okay that star from Japan is going to go

off you can also because there is only

one host nation this time around

directly replace Japan star with

Japan you might be thinking okay this

all looks a bit manual what if I need to

do it again with a different kind of

data well the beautiful thing about par

query is as you do this steps they will

be recorded here and these steps once

they're recorded if you connect to a

different table and you need to do those

exact steps again you can just refresh

the query and it will run all of those

steps against that new file

automatically next up we're going to

look at this rank column wherever there

is a blank value we want to fill the

value from above because it's a shared

rank the reason why these nulls are

happening is if you look at the rank

table here further down wherever there

is a shared rank in this case 36 between

Greece and Uganda it's actually a merged

cell here and that's why it kind of only

looks like the the first country has the

value so we're going to select this

column and then go to the transform

ribbon and use an operation called

fill fill

down this is essentially going to take

the value from sell above and fill it

down and now we no longer have any nulls

36 is repeated and likewise if the

countries share the rank like here 46

that's also going to get repeated let's

say as part of the analysis I would like

to find out what percentage of total

medals are from the gold medals so to do

that first up we are going to take all

of these columns you can select the

first column the gold medal column hold

down shift and select the last column so

you're grabbing all the columns and see

how power query in my case thinks these

are ABC or text values so I'm I'm going

to right click and then say change type

to a whole

number now that they're converted into

number values we can calculate this

number as a percentage of that number to

do this we'll select this column hold on

control and select the second column

this way we are grabbing gold first and

then the total medals we want to add

gold percentage as a new column so we're

going to go to the add column ribbon and

from here we are going to to do a

standard arithmetic operation so select

this standard and from here let's do a

division this is going to take this

value here divide that with that and

calculate the fraction here for example

for United States 39 gold medals out of

113 medals and that comes up to be 34

0.34 as the value we just want to see

this as a percentage so I'm going to

select this column right click and then

change type to

percentage all right we are nearly done

but there is one extra problem with this

data and that kind of thing is hard to

spot unless we start scrolling let's go

all the way down here and you'll see

that there is actually a total row as

well with total 93 entries and then all

the medals Etc we don't probably need

this kind of thing when you are

analyzing the data in either Excel or

power query so we want to take out this

row this kind of an operation is called

filtering one way of thinking about

filtering is it's like adding a we

Clause inside SQL so we want to take out

this total Row for this we can easily go

into either the rank column or the

country column I'm going to go into the

rank column and then scroll all the way

down and uncheck the total

row that's going to go off now let's

take a look at the step that we have

done and what it is showing as the code

here in order to see this code you will

need the formula bar so I recommend

going to the view ribbon and enabling

the formula bar this is a onetime step

once you do it it will stay on for all

of your power queries in other Excel

files so let's take a look at this

filtered row here it is saying if rank

not equal to totals 93 entries so it's

actually very specific let's say you

connect this entire thing to 2016

Olympics table or 2012 Olympics table

and then there the total row is not

totals 93 entries it is totals 112

entries or something like that in that

case this thing is not going to work so

this is where while power query is a

very powerful and extremely valuable

tool it is also a little bit like a dumb

robot all it does is it listens to your

actions and Records the steps so if you

are doing any step that needs to change

when the data changes you will need to

be a little bit smarter with these kind

of filters for now I leave out that part

but I have got other videos on the

channel That Go much more in- depth into

Power query so do check them out if you

want to learn more tricks on how to

handle these kind of issues let's do one

last thing which is we want to load this

data into Excel but before loading we

want to give it a proper name this name

here 2020 Summer Olympics modal table

within square brackets 36 is a very bad

name so I'm going to select this entire

name name column here and then type it as

medals that's it we have now

successfully set up our first Power

query web scraping example in this case

it's going to a Wikipedia page and

getting the data let's go ahead and see

this data in Excel you can go back to

excel by using the home ribbons close

and load button when you click on that

it's going to get the data and load it

into Excel as a nice little table so we

are with all the medals information

neatly available to us notice that the

division is now back to a decimal format

we can go to home and apply the

percentage formatting here again to see

that as a percentage the beautiful thing

about what we have just done is it is a

dynamic living connection against the

Wikipedia page so for example if

something were to happen of course

nothing is going to happen these medals

are kind of frozen but if something were

to happen and some of these numbers were

to change then all you have to do is

come back here right click on this table

and refresh it you can also select the

table cell and go to the query ribbon

and from there from here you can also

hit the refresh button when you refresh

it's going to execute all of these steps

again against the Wikipedia page and get

you the latest values here again as this

data comes to excel as a table

you can also use this data in formulas

pivot tables or anything else that you

normally do inside Excel like maybe you

can make a graph of how many gold medals

are there for each country by selecting

these two columns and

inserting a graph of course with so many

countries it's going to look a little bit

bit

busy for our second example on power

query let's go to something a little bit more

businesslike here I have got a sample

staff data file this is the data set

that we are maintaining at awesome

chocolates about all our staff and let's

say you work as a human resources

analyst in the company so you want to

connect to this file and load up the

data into Excel to do some analysis how

are we going to do that so first up

we'll close this file we'll come to the

same file here where we are getting the

Olympic medal data and this time we're

going to go to the date one and get the

data from file from Excel

workbook make a note that even though

I'm using Excel workbook as an example

we could do the same thing even when the

data is in a text file or on a PDF or in

a SharePoint folder the steps are

exactly same but for the sake of

convenience I'm using Excel as a source

data and we're going to point to this

file if you need a copy of this file

refer to the video description to grab

this again we'll get a familiar

Navigator screen this time we are

navigating an Excel file so it's going

to show you all the spread sheets and

any named ranges and tables if you have

in that file so we'll select the staff

data file this is what I want to load

and I'm going to say transform data

because we would like to fix some

problems with the data if you see

carefully there are some null values in

the gender column some departments have

question marks and probably there is

some other issues with the data as

well so let's hit on the transform

data and this time again we are back in

power query now you can see there are

two queries we have got the medal query

and the staff query so while you are in

power query you can work on the staff

data but if you change your mind and if

you want to do something with the metal

data you can just tap on that and go

back here to do some more

things let's go to the staff data and

again we're going to repeat the steps

which is to make this row as the header

keep in mind that by default power query

may already promote this as a header I

have turned off that option because I

would like to take control over these

things but by default power query would

like to promote this if you want to

adjust some of these settings you can

also go to the file menu here and use

the options and settings and query

options button here for now let's take

this row and make it a header so we're

going to go to the home ribbon and use

first row as

headers the next thing that we are going

to do is we're going to carefully scan

these columns and see if there are any

issues that need

fixing as I iBall this information I can

already sense there is a lot of things

wrong with the data for example we have

got these null values in the gender

column that needs fixing question marks

in the department column that needs

addressing and some of these dates are

left alent some of them are right alent

indicating that the data type is wrong

here we'll need to fix that problem as

well likewise we have got some FTE

information some people work full-time

some people work 0.8 0.9 and we would

like to do some calculation on top of

the FTE for example if somebody is not

working one or full-time then I want to

tag them as part-time employee in our

data likewise on further careful

exploration of this data you might

notice that there are some names that

have these Extra Spaces in the beginning

you you can see that they don't quite

attach to the line in the left hand side

and that means there is some extra

spaces there so we'll need to take out

those spaces as well so let's go one at a

time first up we're going to address the

name column here to fix the problem on

the name column we want to remove those

Extra Spaces this kind of an operation

is called trimming and you can do this

by selecting the column and right

clicking from here and then using the

transform trim option

many times it is hard for us to remember

exactly where these things are so

another handy trick that I use is I

select the column whatever you find in

the right click menus is usually also

listed in the uptp ribbons there so as

we want to change the name in place that

kind of an operation is called

transforming so I will go into the

transform ribbon and then I'll start

looking around so for this name we want

to transform it we'll go to the

transform ribbon and we are looking in

the format area here and selecting the

trim operation this trim is going to

remove any extra spaces at the beginning

as well as end now that Extra Spaces are

gone let's look at the gender column

some employees don't have their gender

information available in this data so

what we want to do is rather than leave

it as null we want to first flag them as

missing but here is a little handy trick

that you can also use if you see when

you load the data into part query it's

also going to

show this green bar up top and when you

hover your mouse on it it's going to

tell you how many values are present and

how many are empty as well and if there

are some errors it's going to show those

as well and it'll give you a handy

option to remove those empty values of

course we don't want to remove those

empty values we want to just flag

them so this kind of a thing is called

column quality indicator normally when I

load the data I always make a point to

look at the column quality indicator to

make sure that we have got good quality

data all the way through in all the

columns so to fix this problem I'm going

to select the gender column right click

and then say replace values and this

time around we're going to replace the

nulls so we're going to write null in

the small letters and then replace this

with missing as the valuee now all those

null gender values will become the

missing and this way when I load into

Excel I can filter on this data and I

can do some further checks or followups

on that information again as you're

doing each of these steps you can see in

the applied steps those will get

recorded let's say you have done the

replacement with missing but you had a

change of mind you don't want to say

missing you want to write some other

word like you know need to check or

something like that how do you change

your mind it's a really simple thing you

can look at the step that you want to

edit and for most steps you you will

find a little gear icon right next to it

so you can just click on that gear icon

and then write the other word here you

can use the same trick if you ever have

a scenario where the file path has

changed so for example in the source

step we are connecting to the Excel file

if I if I have to connect to a new file

I'll click on the gear icon there and

then I can point to a different path

here I can use the browse button or I

can copy paste the information here just

just as youve got a gear button next to

each step for each step there is also a

x button if you don't want to run a

particular step you can delete it right

now I do want to run all these steps so

I'm not deleting anything but this is

how you can remove a step if you want to

ever change things now let's take a look

at the department column wherever there

is a question mark this is happening

because of some data entry error so

those question marks should have

actually been the engineering department

but but our payroll people were a bit

lazy and they were not recording this

correctly so we want to do that we will

again add a replacement rule this time

we want to replace the three question

marks with

engineering let's take a look at the

salary column salary column also has 11

empty values I want to see what's going

on if somebody's not getting paid why

are they even in our company so some

people have zero salary some people have

null salary the zero salary is still

technically a number so it's being

considered but the nulls are a problem

let's say for the sake of Simplicity the

reason why these zeros are happening is

or zeros and nulls are happening is

because they no longer work in the

organization it's just that we were lazy

and we did not remove them from the

staff file so if someone's salary is

either zero or null we don't want that

information anymore because they're no

longer here we can do this with a filter

operation you can just click on this

little button here and uncheck null and

zero and click okay now those people are

gone and you can see the green bar is

all the way green here you can also

check for the condition that is being

used by power query here it is saying

salary is not null and salary is not

zero so both conditions need to be met

if you are wondering what kind of

language this is this uses a special

language called M language and again I

do have other videos on the channel That

go a bit more in depth into this so feel

free to check that for learning a bit

more about this m language now comes the

most fun as well as probably the most

annoying problem when you're working

with the dates in any kind of system

it's that if the dates are not

consistently maintained then formatting

and working with them becomes a pain so

here you can see what is happening some

dates are left aligned some dates are

right aligned it is just a mess power

query makes this problem almost trivial

all you have to do is just right click

on this column change type and then

select the date

option Bingo all the values are

automatically converted into proper date

and you'll see that there is also a

calendar symbol up top indicating that

the data type conversion is now done

this column is flagged as a date this is

not the only way of dealing with the

date problems power query also offers

even more powerful options if you ever

want to deal with more complicated or

messed up date values again I do have

other powerquery videos on the channel

do check them if you want to learn a bit

more the FTE is all right employee type

and work locations are all right so now

that all the data is in a clean State

let's go ahead and add a few more

columns that help us explore the

functionality of power

query we're going to start our journey

with the name column instead of having

one long name like this we want to have

first name and everything else or first

name and last name as two columns to do

this we'll select the name column and we

are going to use the transform ribbons

split column feature so we can select

this split column and we are going to

use delimiter but if you ever want to do

it by number of characters or positions

or anything else you can refer to these

additional options there so I'll use the

delimiter and the delimiter would be

space again you can select different

kind of DMS from here and I just want to

split it once at the leftmost D limiter

so we'll select that and click

okay this is going to create two columns

for us name one and name two name one is

my first name so I'll double click on

that and type first name and name two is last

last

name you might be thinking okay this is

fine but what if somebody has a middle

name as well so for example here we have

got Nazir and Basha Musta as the middle

name and last name so maybe you don't

want to have everything just the last

name alone here one way of dealing with

that problem is we can select this first

of all there is some inconsistent

spacing so I'm going to trim this I'll

go to transform trim so that that Extra

Spaces if there is any in the beginning

are gone and

now what we want to do is again split

this but from the right hand side this

time so we select this and use the

transform split column by

delimer same space but this time we are

going to go from the right hand side and

click okay so this is going to split

that and leave the values here as Basha

and and whatever else is there that

would be the middle name as we no longer

care for this middle name I can remove

this but if you want to keep it you can

also rename this column as middle name

I'm going to just remove this right

click cck and remove and then rename

this column as last

name now let's take a look at the salary

column in the salary we have got all

kinds of numbers but I want to tag our

employees by three buckets under 50K 50

to 100K and more than

100K before we do that let's convert the

salary column into a number right now it

says abc1 2 3 so it could be either

number or text well technically there is

no text values here so I'm going to

write click on this change type to a decimal

decimal

number now let's add a salary bucket

colum for this we're going to go into

the add column and we want to create

three buckets under 50K 50 to 100K and

more than 100K these kind of columns are

called conditional columns you can see

that in the add column there are

different kinds of columns that we can

add but we're going to use just the

conditional column option here and we

have already selected salary and I'm

going to name this as salary bucket and

here we're going to select the salary

and you just create a if condition

ladder here this is kind of

self-explanatory if salary is less than

50,000 then I'm going to say under

50K we're going to add one more Clause if

salary is less than 100k then it's going

to be 50k to

100K and if none of these conditions are

true that means they must be above 100K

so it will be above 100K click okay and

you will instantly have a salary bucket

column added usually when you add a

column it goes all the way to the end of

the table you can move it next to the

salary column by clicking and dragging

and dropping it next here so that way

when you get into to excel these columns

Stay Together the date problem is

already fixed given the start date if

you want you can also calculate what is

the tenure or age of the employee age as

in how long they have been in the

organization to do that we can select

the start date column again using the

add column you need to make sure that

this is actually a date so date

formatting is essential once it is there

in the add column you'll find that this

date ribbon date buttons are activated

from here

you can calculate the

age this is going to tell you how long

that employee has been within our

organization as of today so here it says

2048 but by the time you run it it will

depend on the current date right now I

recording it on 21st of June

2024 so this number might be different

for you this tells you in the days so it says

2048 let's say you want want to see this

in a different format you can select

this and go to

transform and use the duration buttons

here to convert this into a different

format maybe we want to see this in

years so I'm going to use the duration

total years and we can see how many

years each employee has been with our

organization the very first employee in

the data has been here for 5.61 years

again a note of caution this number

would be different depending on when you

are running this operation our final

thing is we're going to look at the FTE

column and add a fulltime SLP parttime

kind of a thing this time also we can

use the add column and the conditional

column option here but we have already

done that so I'm going to show you the

custom column option this is where you

can write a little bit of power query M

language code yourself of course this is

not necessary as this kind of a thing

can be done sufficiently with the

conditional column but let's just take a

look at

this so we'll select this custom column

and here I'm going to call this as employee

employee

type and we're going to write a

condition this condition is

if and select the FTE insert that if FTE

is equal to 1

then the output that I want is full time

else part time so this is how you can

write a condition you simply say if

column condition then whatever is the

output that you want else whatever is

the output let's click okay and you will

have the full-time part-time tagging of

the employees here I forgot that we

already have an employee type column so

this is why when you named it it kind of

change it to type do1 I'm going to go

into edit the

step and you can see that when I'm EDI

in it actually took me back to the

conditional column because it saw that

it was a simple if condition so it kind

of converted that back to this thing

here I'm going to rename this as

employee work type so that's the column

name and all of the data is now in a

clean format the way that I want I can

now go ahead and load this we're going

to go and hit on the home ribbon here

and click on close and load this is

going to create a spreadsheet with your

employee data and clean it up nicely for

us notice that all of this is dynamic so

if my source file changes I'm going to

open that file for you right

now make a note of these three people

they're all on 120,000 I'm going to

change this person salary to

98,000 let's save this crl s and close

this file now we now we come back to

this file and you can refresh this query

by right clicking and hitting the

refresh button so we are looking for

this value to change to 98 and this

condition to also change right click

refresh and boom we have the new

information here the same works even

when you add new data at the bottom of

that file if you add five more employees

they will all happily appear at the end

of the table here now that we have used

power query to clean the data and bring

it in a neat structure into Excel let's

go ahead and understand some of the

essential Excel formulas and functions

we are going to attempt these 10

business questions and along way

discover many of the powerful popular

and really useful Excel functions so the

10 problems that we going to look are

these as you can see they go from easy

to difficult with these three being more

complex ones so let's go ahead and solve

each of them and along way we are going

to learn Lear all of these essential

Excel functions and formulas as well as

Concepts like using operators like Star hash

hash

Etc let's go one at a time a quick note

about the data the data is human

resources data of our employees and

their gender Department salaries and

other information we have been using

this data on and off in the previous

lessons but if you look at this data

this data is neatly structured in an

Excel table with the table name

staff you can grab a copy of this data

file along with this Concepts in the

video description link below so our

first one is total salary and head count by

Department as we have got lots of data

let's say you have got an upcoming HR

meeting and you just want to explain to

the meeting that we have got all of

these six or seven Department ments and

how many people work in each department

and what is the total amount of salary

we pay in the

Departments to do this kind of a thing

we're going to add a new sheet I'm going

to call this as one and in here we want

to list all the Departments their head

counts and their total salaries let's

set up the report format like that and

here I want to list all the Departments

for now what we are going to do is we

are going to go to the data table here

and copy the ENT ire Department values

contrl C come back here right click and

paste as values this will give you all

the Departments but you can see that

departments like Business Development

here are repeated many many times so

once the data is there I can select this

entire column and use the data rebun

remove duplicates option to just remove

all the duplicate Department names for

now I'm just going to select without

expanding and just say remove duplicates

and click okay and that's going to take

out all the duplicate values and leave

one per Department like that so here we

no longer have those duplicates and we

have the department information they

will appear in the order we pasted them

but if you want you can also sort this

data next up let's go ahead and figure

out how many people work in each

department for this kind of a thing we

want to count in the data all the

employees that are in the training

department so essentially we want to

look at how many times this word

training has appeared in the table here

to do this kind of a thing we can use

the count ifs function so that's the

first concept that we are going to learn

we can say count ifs so this function

it's going to count by applying various

conditions so first option for this is

the criteria range and this is my staff table

table

so staff square bracket and we can point

to the department column directly like

that comma and what is the name of the

department now the name of the

department we can write in double quotes like

training but as we already have the

value here it's better to just point to

the value there directly in B3 and when

you close bracket you're going to get

the value as 24 to see the department

head counts for all of these others you

just have to drag this formula down and

you will get the head counts for

everybody else now here is a pro trick

if you are using Excel

365 you can also instead of saying B3 as

we do need the head counts for all of

these departments take the B3 out and

point to this entire range here

directly what this does is it's going to

count individual departments and

automatically show the entire range for

you here directly so see what happens

when I hit enter you're going to get

this entire values here this is called

spilling and it only works in Excel 365

or Excel on the web so if you're using

an older version of excel like Excel

2016 or

2013 then you will not be able to do

that you will have to write one formula

and then drag it down let's do the same

for total salary this time we are not

interested in counting rather we want to

add up all these values the salary

values where the department is training

to do this kind of a thing we will use a

function called sum ifs so here is how

you can write it Su ifs and this time

you'll need to specify what is the range

that you want to sum up so the range is Staff

Staff

table salary column and the criteria

range is Staff table Department column

is here and again if you're using Excel

365 you can just drag this whole thing

directly there and that's going to give

you total salaries for each department

here directly you can select this entire

column and apply a currency formatting like

that so that we can see the values in

millions so for example training

department with 24 people the total

salary is

23,000 something whereas engineering

department with 26 people is 2.1 million

given these two sets of information you

may also want to calculate something

like average salary so let's go ahead

and do that quickly and here you can use

the average IF function to directly do

it against the data you can also

alternatively take this number and

divide that with that number to come up

with the average here let's go ahead and

use the average ifs instead so here here

I want to get the average salary so

we're going to again say staff table

salary column is what we want to

average staff table department is here

and even this formula you can select

multiple values like that and it's going

to tell you what the average is and this

tells you a better story so for example

here I can see the training department

has a higher average of $83,400

as against something like engineering

which has 81 or marketing which has

64,000 before we move on to the second

concept let me give you a small

challenge so just as we have calculated

total salary and head count can you

calculate the permanent head count this

is where in the data table you can see

that some employees are permanent staff

like all of these people here but we

also have other kinds of people in our

data so further down you will find that

we have got fixed term staff and even

further down there are some temporary

staff so I don't want to count all of

these fixed term and temporary people

just the permanent people in each

department how would you do that give it

a try and add that as a column here our

second business question is let's say we

want to look at all the employees with

more than 100,000 salary you can do this

in two way is one is you can use manual

filters and the other one is you can

also use the filter function first let's

try with manual filters and then I'll

introduce you to the filter function

which we then go in elaborate more in

the next

example so here in the data let's say

you're in a hurry and you just want to

see every employee with more than 100K

salary you can apply the filters by

pressing control shift L on your data

and once the filters are added you can

use this thing here number filters

greater than and then type the salary

limit when you hit enter you're going to

see all the people with more than 100K

salary that is a lot of people in this

chocolate company and here in the status

bar you can also see how many records

are found so for example 47 out of 260

people have more than 100,000

salary now let let's say you don't want

to manually apply the filters you would

want to see this extract in a sheet like

this here we can use the filter function

for that purpose this function is a new

formula added to excel 365 and it is a

really powerful function that can be

used to extract subsets of data that

meet criteria so we're going to say

filter and we would like to see the

entire staff data so I'm going to say

staff and then include is where you

specify the criteria so the criteria is

a really simple one staff table salary

column needs to be more than 100,000 so

we'll just say greater than and then

write the

100,000 and when you close bracket and

hit enter it's going to give you that

filtered list here as a spilled range

you can see that you know it

automatically expands to all these

columns and rows and shows you all the

data you can double check this by

selecting all of this data you can see

that here when I

selected I have the same count of 47

values one problem with the filter

function is it only filters the data it

doesn't apply the formatting so for

example all of these here they were

nicely formatted as a date in my data

whereas here I see them as number

representation because Excel thinks

dates are numbers

you can pre-select the entire range

where your filter formula may go and apply date formatting to that so that

apply date formatting to that so that automatically when the filter formula

automatically when the filter formula does its thing it will show you in the

does its thing it will show you in the date formatting same for these numbers

date formatting same for these numbers you can apply decimal formatting and

you can apply decimal formatting and here you can apply the currency

here you can apply the currency formatting many times when we use filter

formatting many times when we use filter you may want to not just see the data

you may want to not just see the data but also the Header information up top

but also the Header information up top so for this what I'm going to do is I'm

so for this what I'm going to do is I'm going to select this entire range make

going to select this entire range make it bold and fill a color and now in this

it bold and fill a color and now in this cell here I'll just ask Excel to get me

cell here I'll just ask Excel to get me the same headers as my staff table so

the same headers as my staff table so for this we are going to say staff table

for this we are going to say staff table Open Bracket and if you scroll all the

Open Bracket and if you scroll all the way down you can see that we can also

way down you can see that we can also access the headers of the table for this

access the headers of the table for this you need to use hash headers as an

you need to use hash headers as an option you can just scroll down and

option you can just scroll down and select this double click so Excel writes

select this double click so Excel writes it for you close the square brackets and

it for you close the square brackets and hit enter and you're going to get the

hit enter and you're going to get the headers as they were written in the data

headers as they were written in the data table when you are looking at it you

table when you are looking at it you might think okay chandu this is all good

might think okay chandu this is all good but for my extract I don't need to see

but for my extract I don't need to see all of these columns all I care about is

all of these columns all I care about is employee ID their names first name last

employee ID their names first name last name and everything department and

name and everything department and salary values so you don't want

salary values so you don't want everything you just want a subset of the

everything you just want a subset of the data

data this is where we can combine the filter

this is where we can combine the filter function with a different function like

function with a different function like choose columns to tell Excel that once

choose columns to tell Excel that once the filter has done its job we would

the filter has done its job we would like to see for example column number

like to see for example column number one 2 3 we don't want to see four we

one 2 3 we don't want to see four we want to see five and sixth column so

want to see five and sixth column so let's give it a go so here I have set up

let's give it a go so here I have set up a area for us I want to see all

a area for us I want to see all employees with greater than 100K salary

employees with greater than 100K salary but just the columns 1 2 3 5 and six so

but just the columns 1 2 3 5 and six so here we're going to say

here we're going to say filter staff

filter staff table staff table

table staff table salary greater than

salary greater than 100,000 while we are at it instead of

100,000 while we are at it instead of hardcoding this number like that you can

hardcoding this number like that you can also put it as input cell value so I'm

also put it as input cell value so I'm going to point to this cell here which

going to point to this cell here which is

f67 right now f67 is blank but we are going to fix that in a second and when

going to fix that in a second and when you hit enter as initially f67 is blank

you hit enter as initially f67 is blank it's going to give me everything because

it's going to give me everything because it's thinking greater than zero now in

it's thinking greater than zero now in this cell here I'm going to type

this cell here I'm going to type 100,000 and you'll see that as soon as a

100,000 and you'll see that as soon as a type I'll get a subset of people but I

type I'll get a subset of people but I can go even higher so for example I can

can go even higher so for example I can go

115 and we'll see even fewer people now we don't want to see all these columns

we don't want to see all these columns as I mentioned we just want to see a

as I mentioned we just want to see a subset of the columns so we will take

subset of the columns so we will take the filter function and send this to the

the filter function and send this to the choose columns function and here we are

choose columns function and here we are going to now learn a powerful concept

going to now learn a powerful concept called nesting this is where we take the

called nesting this is where we take the output of a formula and then pass it to

output of a formula and then pass it to another formula so that you could do

another formula so that you could do more powerful things so this new

more powerful things so this new function is called choose

function is called choose columns choose calls and we are going to

columns choose calls and we are going to say take this entire filter output and

say take this entire filter output and from this give me column number 1 2 3 5

from this give me column number 1 2 3 5 and

and six you can comma separate and mention

six you can comma separate and mention any number of columns and when you hit

any number of columns and when you hit enter you're going to get just those

enter you're going to get just those columns

columns directly again a quick note of caution

directly again a quick note of caution both these functions choose calls and

both these functions choose calls and filter are only available in Excel 365

filter are only available in Excel 365 or Excel on the web if you are using a

or Excel on the web if you are using a old version of excel

old version of excel unfortunately these kind of formulas are

unfortunately these kind of formulas are not possible now that these are done you

not possible now that these are done you may also want to add a cherry on the top

may also want to add a cherry on the top by setting the headers correctly for

by setting the headers correctly for this again we are going to do the same

this again we are going to do the same thing and this time we are going to say

thing and this time we are going to say staff hash headers and this is going to

staff hash headers and this is going to give you all the headers like you see

give you all the headers like you see earlier but we also don't need all the

earlier but we also don't need all the headers we only need the headers 1 2 3 5

headers we only need the headers 1 2 3 5 and six so we can pass the choose

and six so we can pass the choose columns to this as well we can say

columns to this as well we can say choose column columns from the

choose column columns from the headers 1 2 3 5

headers 1 2 3 5 6 and that's going to give you only

6 and that's going to give you only those headers from the table and that

those headers from the table and that data point and all of this is dynamic so

data point and all of this is dynamic so if I change my requirement and say I

if I change my requirement and say I want to look at for example

want to look at for example 108,000 I'm going to see all of those

108,000 I'm going to see all of those employees

employees here later on in this video I'll tell

here later on in this video I'll tell you how to access individual Columns of

you how to access individual Columns of this data for example you may want to

this data for example you may want to count how many such people are there or

count how many such people are there or you may want to average all of these

you may want to average all of these salaries how to do that we're going to

salaries how to do that we're going to cover in the later on aspects of this

cover in the later on aspects of this video our third business question is all

video our third business question is all female employees with more than 100K

female employees with more than 100K salary and here we are going to kind of

salary and here we are going to kind of piggyback on what we already did so

piggyback on what we already did so instead of writing new formulas I have

instead of writing new formulas I have set up this worksheet as all such female

set up this worksheet as all such female employees and we want to see columns 1 2

employees and we want to see columns 1 2 3 5 and six to do that we can use

3 5 and six to do that we can use multiple conditions in filter function

multiple conditions in filter function so we are going to go back to the same

so we are going to go back to the same filter staff

filter staff table and then staff table

table and then staff table salary is greater than the f67 cell but

salary is greater than the f67 cell but that's not enough we also need to check

that's not enough we also need to check for the gender of the employee and then

for the gender of the employee and then make sure that we are only looking at

make sure that we are only looking at female employees so when you want to

female employees so when you want to write multiple conditions you need to

write multiple conditions you need to start by putting brackets around each

start by putting brackets around each condition and then the second condition

condition and then the second condition needs to be multiplied with the first

needs to be multiplied with the first one so we'll write star Open Bracket and

one so we'll write star Open Bracket and then inside here we write the second

then inside here we write the second condition so staff table gender is

condition so staff table gender is female again you could hardcode these

female again you could hardcode these values here you could also for example

values here you could also for example link it to a cell like this one here and

link it to a cell like this one here and then use that as an input cell I'm just

then use that as an input cell I'm just going to hard code because our question

going to hard code because our question itself is female employees once this

itself is female employees once this part is done you can close the filter

part is done you can close the filter formula all together and hit enter and

formula all together and hit enter and you're going to see just a subset of

you're going to see just a subset of employees and here you can see clearly

employees and here you can see clearly that we are only looking at the female

that we are only looking at the female employees this time around we would like

employees this time around we would like to see the gender column as well which

to see the gender column as well which is the fourth column so I'm going to

is the fourth column so I'm going to adjust this as columns 1 2 3 4 5

adjust this as columns 1 2 3 4 5 6 now as these columns are all

6 now as these columns are all continuous and they start from one and

continuous and they start from one and go up to six you can also use a

go up to six you can also use a different version of the filter rather

different version of the filter rather than the choose columns option so here

than the choose columns option so here instead of saying staff I can say in the

instead of saying staff I can say in the staff table so we can open a square

staff table so we can open a square bracket to say in the staff table and

bracket to say in the staff table and then you can open one more square

then you can open one more square bracket and then select the first column

bracket and then select the first column that you want to extract this is

that you want to extract this is employee ID close this square

employee ID close this square bracket colum and then open one more

bracket colum and then open one more square bracket and and then select the

square bracket and and then select the salary column as the last

salary column as the last one close all these square brackets and

one close all these square brackets and essentially what you're saying here is

essentially what you're saying here is if you look closely you're saying in the

if you look closely you're saying in the staff table I want to start with

staff table I want to start with employee ID column and then I want to go

employee ID column and then I want to go up to the salary column so you want

up to the salary column so you want every column in between from employee ID

every column in between from employee ID to salary and once you say it like that

to salary and once you say it like that this filter function is just going to

this filter function is just going to give you those six columns instead of

give you those six columns instead of every everything so this is a much

every everything so this is a much better syntax than the choose columns

better syntax than the choose columns one because our columns are continuous

one because our columns are continuous and they go from 1 to 6 you could use

and they go from 1 to 6 you could use the same approach when you have 3 to 7

the same approach when you have 3 to 7 or 6 to9 or something like that as well

or 6 to9 or something like that as well time for another

time for another challenge while looking at the female

challenge while looking at the female employees with more than 100K salary or

employees with more than 100K salary or whatever was the f67 value if you also

whatever was the f67 value if you also want to add one more condition that says

want to add one more condition that says those employees must have joined our

those employees must have joined our organization in in the year 2020 or

organization in in the year 2020 or after so if you look at the data table

after so if you look at the data table here we have got a start date as well

here we have got a start date as well and essentially we are looking at

and essentially we are looking at employees who are with us in these two

employees who are with us in these two years alone so we don't want to pay

years alone so we don't want to pay attention to this two years how would

attention to this two years how would you do that give it a try and let me

you do that give it a try and let me know in the comments now let's go to the

know in the comments now let's go to the next one which is I want to look at the

next one which is I want to look at the lowest highest and top five salary

lowest highest and top five salary values and here we are going to learn

values and here we are going to learn about min max Max large and sort plus

about min max Max large and sort plus take

take functions all these three functions min

functions all these three functions min max and large are available in most

max and large are available in most versions of excel but the sort and take

versions of excel but the sort and take functions are only available in Excel

functions are only available in Excel 365 let's take a look at them so here we

365 let's take a look at them so here we would like to first try this for all

would like to first try this for all employees and then eventually do this

employees and then eventually do this for male and female as well so if I just

for male and female as well so if I just want to get the lowest value across all

want to get the lowest value across all the employees we can straight away use

the employees we can straight away use the min function for that minimum of

the min function for that minimum of Staff table salary

Staff table salary column and that just tells you what that

column and that just tells you what that value is it's $

28,6 let's apply currency formatting on all these cells you can select all the

all these cells you can select all the cells and you can use this key or you

cells and you can use this key or you can also press control Shift 4 for the

can also press control Shift 4 for the highest salary the logic is similar we

highest salary the logic is similar we just use the max function Max of Staff

just use the max function Max of Staff table salary column and that's going to

table salary column and that's going to come up with the value as the value is

come up with the value as the value is too big for the cell Excel is showing me

too big for the cell Excel is showing me these hashes let's make this column wide

these hashes let's make this column wide enough and there is the volume how do we

enough and there is the volume how do we get the top five salaries these are the

get the top five salaries these are the highest five salaries and to get these

highest five salaries and to get these we can use the large function we can say

we can use the large function we can say large staff

large staff table salary

table salary colum and then you can specify the K

colum and then you can specify the K value K is going to be one for the first

value K is going to be one for the first person and that will be

person and that will be 120,000 and in the next cell we write

120,000 and in the next cell we write the exact same formula large staff

the exact same formula large staff table salary but this time we say two

table salary but this time we say two coincidentally the second highest salary

coincidentally the second highest salary is also

is also 120,000 and then when you add the rest

120,000 and then when you add the rest of the numbers you're going to get these

of the numbers you're going to get these normally when you are using the large

normally when you are using the large formula you may want to set up the

formula you may want to set up the numbers 1 2 3 4 5 in a Range like this

numbers 1 2 3 4 5 in a Range like this and then instead of writing these values

and then instead of writing these values you can point to the cell like that that

you can point to the cell like that that way you can drag this formula down and

way you can drag this formula down and you get to see all the values by

you get to see all the values by referencing these many times when we use

referencing these many times when we use these kind of things you don't want

these kind of things you don't want these to be visible when you create this

these to be visible when you create this report for your colleagues or managers

report for your colleagues or managers you can also hide this column so that

you can also hide this column so that the numbers stay there and help you

the numbers stay there and help you calculate the values but they're not

calculate the values but they're not abstracting the view or don't make it

abstracting the view or don't make it look clumsy so that is how now we can do

look clumsy so that is how now we can do this part but if I'm interested in all

this part but if I'm interested in all employees let me show you an alternative

employees let me show you an alternative for these values the top five using a

for these values the top five using a different version that we could do with

different version that we could do with the sort and take function Excel 365

the sort and take function Excel 365 adds new powerful functions to work with

adds new powerful functions to work with all of your data and one of them is the

all of your data and one of them is the sort function so for example if I simply

sort function so for example if I simply say sort my staff

say sort my staff table salary

table salary column and hit enter

column and hit enter I'm going to get all the salaries not

I'm going to get all the salaries not just the top five from lowest salary

just the top five from lowest salary here up to the highest salary the

here up to the highest salary the 120,000 all the way at the bottom here

120,000 all the way at the bottom here we are we can take this sort formula and

we are we can take this sort formula and tell it that when you are

tell it that when you are sorting sort it in the descending order

sorting sort it in the descending order so that's the third parameter the index

so that's the third parameter the index is used if you have bigger data and you

is used if you have bigger data and you just want to sort it on a specific

just want to sort it on a specific column and here if I say minus one I'm

column and here if I say minus one I'm going to see 120,000 up top with all of

going to see 120,000 up top with all of these values now once sort has finished

these values now once sort has finished its thing I don't want to see everything

its thing I don't want to see everything I just want to see these five rows so

I just want to see these five rows so this is where there is also a take

this is where there is also a take function what it does is it takes a

function what it does is it takes a certain number of rows or columns from

certain number of rows or columns from your data so after sorting has done its

your data so after sorting has done its job just take the first five rows so

job just take the first five rows so Take Five is going to give you only

Take Five is going to give you only those five values so this is another way

those five values so this is another way of getting the top five salaries in this

of getting the top five salaries in this case we are sorting this entire data you

case we are sorting this entire data you can also use other variations for these

can also use other variations for these formulas to get the top five values and

formulas to get the top five values and each of them have its own advantages for

each of them have its own advantages for example with the take approach if I

example with the take approach if I change my mind and I want to see 10

change my mind and I want to see 10 values I can easily update this to 10 in

values I can easily update this to 10 in fact you can also take this out the

fact you can also take this out the number five no pun intended and Link it

number five no pun intended and Link it to a cell so for example let's link it

to a cell so for example let's link it to this cell here so I'm going to say

to this cell here so I'm going to say i12 as i12 is blank we're going to get a

i12 as i12 is blank we're going to get a cal error but now if I go here and then

cal error but now if I go here and then put 10 there I'm going to get all the 10

put 10 there I'm going to get all the 10 values remember with these kind of

values remember with these kind of spilling formulas Excel only gets you

spilling formulas Excel only gets you the value the formatting doesn't happen

the value the formatting doesn't happen so it's a good idea to preformat the

so it's a good idea to preformat the values so that if and when the spill

values so that if and when the spill happens you will see the formatting and

happens you will see the formatting and now this is a dynamic thing so I can

now this is a dynamic thing so I can change this 10 to for example five or

change this 10 to for example five or eight and I'll get that many

eight and I'll get that many employees how awesome is this now let's

employees how awesome is this now let's take it up a notch and instead of

take it up a notch and instead of looking at just all employees let's

looking at just all employees let's figure out how to do it only for male

figure out how to do it only for male employees or only for female

employees or only for female employees as a challenge try to take it

employees as a challenge try to take it up yourself but I'm going to show you my

up yourself but I'm going to show you my Solutions now to get the lowest salary

Solutions now to get the lowest salary only for male employees we can use the

only for male employees we can use the Min ifs function this is available since

Min ifs function this is available since Excel 2019 or 2016 I believe so it's

Excel 2019 or 2016 I believe so it's been around for a while and what it does

been around for a while and what it does is it'll tell you the minimum value by

is it'll tell you the minimum value by applying a criteria just like we have

applying a criteria just like we have got count ifs sum ifs and average ifs

got count ifs sum ifs and average ifs this also works the same way so here

this also works the same way so here staff table salary column

staff table salary column staff

staff table

table gender is same as whatever I have in D3

gender is same as whatever I have in D3 and you can lock this as d dollar3 by

and you can lock this as d dollar3 by changing the reference style you can

changing the reference style you can either manually type this dollar symbol

either manually type this dollar symbol or you can while typing this press the

or you can while typing this press the F4 key a few times so that the dollar

F4 key a few times so that the dollar gets added

gets added automatically so we now get the male one

automatically so we now get the male one as 2860 I think the lowest employee is

as 2860 I think the lowest employee is the m employee and that's why these two

the m employee and that's why these two values match let's copy and paste this

values match let's copy and paste this for female and we'll see the female

for female and we'll see the female value is slightly different let's do the

value is slightly different let's do the maximum just as there is min ifs there

maximum just as there is min ifs there is also a Max ifs function and we can

is also a Max ifs function and we can say staff table salary column staff

say staff table salary column staff table gender

table gender is here and again I'm going to change

is here and again I'm going to change the reference style to

D3 while there is a difference in minimum values the maximum value is

minimum values the maximum value is exactly same whether you are male or

exactly same whether you are male or female I guess we have got employees in

female I guess we have got employees in both but how do we do it for the top

both but how do we do it for the top five this is going to be a bit tricky

five this is going to be a bit tricky but not impossible to get the top five

but not impossible to get the top five salaries for male and female employees

salaries for male and female employees we can use the filter function first so

we can use the filter function first so for example we can say filter staff

table salary column staff table gender is equal to

column staff table gender is equal to male what that's going to do is it's

male what that's going to do is it's going to give you all the male salaries

going to give you all the male salaries and spill them down here so everybody's

and spill them down here so everybody's salary is going to be here if their

salary is going to be here if their gender is male we can then send this

gender is male we can then send this filter output to either large or sort

filter output to either large or sort and take I'm going to send this to the

and take I'm going to send this to the large function large of this and we are

large function large of this and we are going to refer to that hidden column so

going to refer to that hidden column so it's between F and

it's between F and H

G6 and I'll get the 120 and when I drag this down I'll get all the five salaries

this down I'll get all the five salaries of top five male

of top five male employees and I can copy this and paste

employees and I can copy this and paste it

it here and we get an error this is

here and we get an error this is because the G has become H so I'm going

because the G has become H so I'm going to change this reference again and fill

to change this reference again and fill this down and we will get the top five

this down and we will get the top five female salaries you may want to double

female salaries you may want to double check these numbers against the data and

check these numbers against the data and that's really simple if you go to the

that's really simple if you go to the data you can for example clear the

data you can for example clear the filters on the salary and apply the

filters on the salary and apply the filter on gender let's look at male

filter on gender let's look at male while looking at male employees if you

while looking at male employees if you expand the salary filter you should see

expand the salary filter you should see the top five values at the bottom here

the top five values at the bottom here these are the top five 120 to4 691

these are the top five 120 to4 691 and when you look at the calculation

and when you look at the calculation here you should see the same numbers 120

here you should see the same numbers 120 214 691 just as you're using the large

214 691 just as you're using the large function you could also use the sort and

function you could also use the sort and take approach on the filter data I leave

take approach on the filter data I leave this bit to you as homework let's go to

this bit to you as homework let's go to the next one our next question is list

the next one our next question is list all the Departments and this kind of

all the Departments and this kind of piggybacks on what we did in question

piggybacks on what we did in question one in order to get the total salary and

one in order to get the total salary and head count by Department we were using

head count by Department we were using the remove duplicates feature of excel

the remove duplicates feature of excel but now we are going to use the unique

but now we are going to use the unique function this is also a new function

function this is also a new function available in Excel 365 and it is quite

available in Excel 365 and it is quite helpful if you know how to use this so

helpful if you know how to use this so here I have set up a page for our

here I have set up a page for our department names and I just want to see

department names and I just want to see all the Departments here we can use the

all the Departments here we can use the unique function like this unique of

unique function like this unique of Staff table Department

Staff table Department column and when you hit enter it's going

column and when you hit enter it's going to remove all the duplicates and you'll

to remove all the duplicates and you'll just get all the 12 Department names

just get all the 12 Department names this is going to list the items in the

this is going to list the items in the order they appear in the data so if you

order they appear in the data so if you look at the data you can see that the

look at the data you can see that the very first department is training the

very first department is training the next one is business development and

next one is business development and then engineering and that is how they

then engineering and that is how they appear here many times when you are

appear here many times when you are doing this kind of a thing you may also

doing this kind of a thing you may also want to alphabetically sort them so you

want to alphabetically sort them so you you could use the sort function on top

you could use the sort function on top of the

of the department to sort them in the

department to sort them in the alphabetical order so now you have

alphabetical order so now you have accounting up top and training all the

accounting up top and training all the way at the

way at the bottom let's say you want to count how

bottom let's say you want to count how many departments are there you could of

many departments are there you could of course select them and look at the

course select them and look at the status bar here to see what that count

status bar here to see what that count is but that is kind of lame so let's go

is but that is kind of lame so let's go ahead and count them here I'm going to

ahead and count them here I'm going to say count and we can use the function

say count and we can use the function count a as these are text values you

count a as these are text values you need to use the count a function which

need to use the count a function which is helpful for counting

is helpful for counting everything Open Bracket and select this

everything Open Bracket and select this range now we know that the Departments

range now we know that the Departments begin from

begin from B4 but we don't know how far they go in

B4 but we don't know how far they go in this case there are 12 departments but

this case there are 12 departments but in future we might do a reorg and there

in future we might do a reorg and there might be 20 department so this can go

might be 20 department so this can go forever so how far down should you count

forever so how far down should you count this is where when you have a formula

this is where when you have a formula that is spilling the values down Excel

that is spilling the values down Excel gives you a new operator called

gives you a new operator called hash what this does is it basically

hash what this does is it basically tells my formula begins on B4 and it is

tells my formula begins on B4 and it is spilling just go ahead and get me

spilling just go ahead and get me everything so let's use that B4 hash

everything so let's use that B4 hash close bracket and you can see that B4

close bracket and you can see that B4 hash actually refers to all of these

hash actually refers to all of these values when you hit enter you're going

values when you hit enter you're going to get 12 now let's go ahead and do some

to get 12 now let's go ahead and do some reorganization I'm going to go to the

reorganization I'm going to go to the department data here and let's take some

department data here and let's take some of these business development and make

of these business development and make them business

them business growth so now I have got a new

growth so now I have got a new Department called business growth and

Department called business growth and when you see here Bingo we get 13 and

when you see here Bingo we get 13 and that growth also appears here so that is

that growth also appears here so that is what this does it automatically sees

what this does it automatically sees that your spill range has grown and it

that your spill range has grown and it gets the new values directly let me give

gets the new values directly let me give you a small piece of challenge at this

you a small piece of challenge at this point

what if you want to see all the departments in one cell comma separated

departments in one cell comma separated how would you do that give it a try our

how would you do that give it a try our next two examples involve working with

next two examples involve working with data and looking up specific information

data and looking up specific information and we are going to learn all of these

and we are going to learn all of these formulas a long way we look up index

formulas a long way we look up index match X lookup and if error let's say

match X lookup and if error let's say you are looking at your data and you

you are looking at your data and you want to find information about a

want to find information about a specific employee maybe you have their

specific employee maybe you have their last name or you have their employee ID

last name or you have their employee ID so maybe you want to look at this person

so maybe you want to look at this person p o z

p o z 1306 one easy way of doing this is you

1306 one easy way of doing this is you can apply the filters and you can search

can apply the filters and you can search for what you're looking for as you type

for what you're looking for as you type Excel automatically finds them and when

Excel automatically finds them and when you click okay you're going to get their

you click okay you're going to get their row here isolated and you can get all

row here isolated and you can get all the information but this is manual and

the information but this is manual and it requires you to do this filtering

it requires you to do this filtering repetitively every time you have to look

repetitively every time you have to look up someone else

up someone else so this is where the lookup formulas in

so this is where the lookup formulas in Excel come in handy there are quite a

Excel come in handy there are quite a few of them and we are going to cover

few of them and we are going to cover majority of them in this next

majority of them in this next segment so here I have set up the lookup

segment so here I have set up the lookup worksheet and let's say we want to look

worksheet and let's say we want to look up p 3886 as the person I want to get

up p 3886 as the person I want to get all of this information for that but

all of this information for that but notice that this C4 is an input cell so

notice that this C4 is an input cell so I can type in a different number and I

I can type in a different number and I would like to see those values updated

would like to see those values updated later on we are going to find out how to

later on we are going to find out how to build a search with the last name and

build a search with the last name and learn the index match formula but first

learn the index match formula but first let's take a look at V lookup what V

let's take a look at V lookup what V lookup does is it vertically looks up

lookup does is it vertically looks up data so if you look at the data it

data so if you look at the data it vertically looks up for an employee ID

vertically looks up for an employee ID and once it finds the ID it's going to

and once it finds the ID it's going to tell you the corresponding

tell you the corresponding information so here we're going to do V

information so here we're going to do V lookup it starts with the lookup value

lookup it starts with the lookup value and that's the value that we looking up

and that's the value that we looking up for C4 and where do you want to look up

for C4 and where do you want to look up in the staff table the look up will

in the staff table the look up will always happen in the first colum of

always happen in the first colum of Staff table so whatever you say it will

Staff table so whatever you say it will happen against the very first column and

happen against the very first column and then what is the column that you want to

then what is the column that you want to get now here in my staff table you can

get now here in my staff table you can see that we are looking up on the

see that we are looking up on the employee ID and then we want to get the

employee ID and then we want to get the first name which is the second column if

first name which is the second column if you start counting from here this is

you start counting from here this is First Column second thir four five like

First Column second thir four five like that so in this case the first name

that so in this case the first name happens to be number two so we can go

happens to be number two so we can go back

and type two here and this one is important if you don't say anything

important if you don't say anything Excel is going to do an approximate

Excel is going to do an approximate match which is going to mess things up

match which is going to mess things up for us so we always want to say false

for us so we always want to say false here and when you hit enter you're going

here and when you hit enter you're going to see for that person this is their

to see for that person this is their first name let's double check this

first name let's double check this 3886 so here they are Ed is their first

3886 so here they are Ed is their first name Mac knocker is their last name and

name Mac knocker is their last name and accounting and that's the value so these

accounting and that's the value so these are what we are looking for Ed we have

are what we are looking for Ed we have already received that so the last name

already received that so the last name is again a same V lookup formula you can

is again a same V lookup formula you can directly copy this formula and paste it

directly copy this formula and paste it here and this time change the number two

here and this time change the number two to number

to number three

three and for

and for Department we can change this to

Department we can change this to five and for salary we can change this

five and for salary we can change this to

to six one handy trick that I learned early

six one handy trick that I learned early on when I was working with these

on when I was working with these functions is rather than manually typing

functions is rather than manually typing these column numbers you may want to set

these column numbers you may want to set them up somewhere here or in a hidden

them up somewhere here or in a hidden column there so for example I would

column there so for example I would write 2 3 5 6 here and I might just gray

write 2 3 5 6 here and I might just gray them out so that they're not really

them out so that they're not really there and instead of writing it like

there and instead of writing it like that I can point to that and I'll make

that I can point to that and I'll make sure that C4 is absolute reference by

sure that C4 is absolute reference by changing the style to Dollar C do4 like

changing the style to Dollar C do4 like that and this way I can just drag this

that and this way I can just drag this formula down and all the values come so

formula down and all the values come so at a later point if I change my mind and

at a later point if I change my mind and maybe I want to see gender which is the

maybe I want to see gender which is the fourth column I can just type four here

fourth column I can just type four here and I can see that gender Ender

and I can see that gender Ender information there of course I also need

information there of course I also need to change the wording there but that's a

to change the wording there but that's a different

different problem let's go back to salary so this

problem let's go back to salary so this is how the vlookup works one problem

is how the vlookup works one problem with v lookup is if there is no matching

with v lookup is if there is no matching value it's going to just come up with an

value it's going to just come up with an error so for example let's search for

error so for example let's search for 3887 we don't have any such employee so

3887 we don't have any such employee so all of these formulas are going to come

all of these formulas are going to come up with hash n a one easy way to fix

up with hash n a one easy way to fix this is we can send this vlookup output

this is we can send this vlookup output to if

to if error and then say if the V lookup is

error and then say if the V lookup is giving an error print this message not

giving an error print this message not found that way if I don't have that

found that way if I don't have that person I'll get not

person I'll get not found again note that this if error is a

found again note that this if error is a simple check it will look at the vlookup

simple check it will look at the vlookup output if there is an error it's going

output if there is an error it's going to just print whatever you give there

to just print whatever you give there but what if you're not looking for an ID

but what if you're not looking for an ID but something else like last name in

but something else like last name in this case if you see the vook up

this case if you see the vook up function can only look up in the very

function can only look up in the very first column so my ID alone but as we

first column so my ID alone but as we are interested in looking up in the last

are interested in looking up in the last name column and then find the last name

name column and then find the last name of scad or whatever else we will need to

of scad or whatever else we will need to use a different type of a lookup and

use a different type of a lookup and this is where there are couple of

this is where there are couple of options in Excel my preferred option

options in Excel my preferred option these days is to use x

these days is to use x lookup man it takes a long while to

lookup man it takes a long while to write with my mouse but if you using an

write with my mouse but if you using an older version of excel you can also use

older version of excel you can also use the index match formulas so first up

the index match formulas so first up let's take a look at the index match and

let's take a look at the index match and then I will introduce X lookup to you so

then I will introduce X lookup to you so here I have got my last name as card and

here I have got my last name as card and I want to look up their employee ID

I want to look up their employee ID department and

department and salary so first up we need to find where

salary so first up we need to find where this last name is in the bunch of data

this last name is in the bunch of data that we have so if you look at the last

that we have so if you look at the last name column somewhere down here we will

name column somewhere down here we will have that person scad here this is the

have that person scad here this is the person and if I know what is their row

person and if I know what is their row number in all of the data then we can

number in all of the data then we can kind of go ahead and get other items

kind of go ahead and get other items from that row so this is where the match

from that row so this is where the match function is helpful we can

function is helpful we can say right here for example match scad in

say right here for example match scad in the staff table last

name and the match type is exact match so we'll say

so we'll say zero and that's going to tell you scad

zero and that's going to tell you scad is the 43rd employee in the data that

is the 43rd employee in the data that means it's going from top to bottom this

means it's going from top to bottom this person is one and scad would be

person is one and scad would be 43 now that I know 43 is the row in

43 now that I know 43 is the row in which my person that I'm interested in

which my person that I'm interested in is I can go ahead and ask questions like

is I can go ahead and ask questions like what is the employee ID of the 40 third

what is the employee ID of the 40 third person what is the Department of 43rd

person what is the Department of 43rd person and for this Excel offers a index

person and for this Excel offers a index function what index does is it looks at

function what index does is it looks at a data so for example staff table ID

a data so for example staff table ID column employee ID column it can tell

column employee ID column it can tell you what is the ID number 43 so you can

you what is the ID number 43 so you can type 43 directly here and then it'll

type 43 directly here and then it'll tell you pr0

tell you pr0 3532 we don't want to type 43 so we can

3532 we don't want to type 43 so we can point to the match cell calculation here

point to the match cell calculation here and then that will also tell you when

and then that will also tell you when you combine this whole thing into one

you combine this whole thing into one formula that is called index match so

formula that is called index match so normally this is how we write it index

normally this is how we write it index of that and then we say

of that and then we say match scad in the staff table last

match scad in the staff table last name with zero as the exact match and

name with zero as the exact match and close bracket and we will get pr0

close bracket and we will get pr0 3532 to get the Department it's the

3532 to get the Department it's the exact same formula so we can can copy

exact same formula so we can can copy this and this time just change the first

this and this time just change the first part from employee ID to

part from employee ID to department and finally to get the salary

department and finally to get the salary again we can change this to salary and

again we can change this to salary and we will get the salary of that person

we will get the salary of that person just as V lookup wouldn't work if there

just as V lookup wouldn't work if there is no value to find if I type in a last

is no value to find if I type in a last name that doesn't exist in my data so

name that doesn't exist in my data so for example KH I'm going to get all

for example KH I'm going to get all these values as na again and you can

these values as na again and you can also use if error around these functions

also use if error around these functions to print a message like not

to print a message like not found but a more pressing problem would

found but a more pressing problem would be what if two people have the same last

be what if two people have the same last name so in our data here we do have some

name so in our data here we do have some data like that in fact the very first

data like that in fact the very first rows itself have some

rows itself have some duplication of course the problem here

duplication of course the problem here is all of these data points are also

is all of these data points are also similar so to tell them apart I'm going

similar so to tell them apart I'm going to change the salary of this second tax

to change the salary of this second tax well from 8696 to

85,000 so now different values are there and let's see what happens if I search

and let's see what happens if I search for tawell so if I come here and then

for tawell so if I come here and then say

say tawell we are going to get the very

tawell we are going to get the very first employee and their salary values

first employee and their salary values so

so 8,695 this is how all lookup formulas

8,695 this is how all lookup formulas work whether you're using index match

work whether you're using index match bookup or even the new X lookup once

bookup or even the new X lookup once they find a match like tux in this

they find a match like tux in this example they just tell you what the

example they just tell you what the value is they're not going to bother

value is they're not going to bother looking for the next such employee and

looking for the next such employee and this is a limitation of all the lookup

this is a limitation of all the lookup formulas so if you want to see all the

formulas so if you want to see all the employees with the same last name you

employees with the same last name you want to use the filter function instead

want to use the filter function instead which I have introduced earlier in this

which I have introduced earlier in this video now let's take a look at solving

video now let's take a look at solving these exact problems

these exact problems using the xlup function xlup is a

using the xlup function xlup is a improved version of the previous lookup

improved version of the previous lookup formulas that is V lookup hookup and

formulas that is V lookup hookup and index match and it can do all of those

index match and it can do all of those things in one formula or one function so

things in one formula or one function so that is why if you are using a modern

that is why if you are using a modern version of excel like Excel 365 I

version of excel like Excel 365 I recommend just learning xook up and

recommend just learning xook up and calling it a day rather than knowing all

calling it a day rather than knowing all of these individual things so let's try

of these individual things so let's try that here I have got a separate area for

that here I have got a separate area for all the X lookup formulas and we are

all the X lookup formulas and we are going to again look up for this person

going to again look up for this person PR 0388 7 and then get the values

PR 0388 7 and then get the values remember this doesn't exist so I'm going

remember this doesn't exist so I'm going to change this back to 3886 and let's

to change this back to 3886 and let's take a look at the results with X lookup

take a look at the results with X lookup so here we write X lookup what is it

so here we write X lookup what is it that you're looking up for so that value

that you're looking up for so that value in C4 and then you'll say lookup array

in C4 and then you'll say lookup array and return array

and return array separately lookup array refers to the

separately lookup array refers to the range of data where you want to look up

range of data where you want to look up and this would be the staff table

and this would be the staff table employee ID C

employee ID C column return array is what you want to

column return array is what you want to get back once the lookup has done its

get back once the lookup has done its job so in this case it would be the

job so in this case it would be the first name column so staff table first

first name column so staff table first name and that's it you don't need to

name and that's it you don't need to specify anything like exact match or

specify anything like exact match or anything by default xlup is going to do

anything by default xlup is going to do the exact match and then give you the

the exact match and then give you the result so here we'll get the same result

result so here we'll get the same result as the we lookup but this is how that

as the we lookup but this is how that syntax looks you might be thinking okay

syntax looks you might be thinking okay this looks exactly same where is the

this looks exactly same where is the advantage of xlookup xlookup offers many

advantage of xlookup xlookup offers many other advantages and we'll go to unpack

other advantages and we'll go to unpack those as we go along but for now let's

those as we go along but for now let's calculate all of these values as

calculate all of these values as well I'm going to copy this formula as

well I'm going to copy this formula as it is paste it here and just change the

it is paste it here and just change the first name to last

first name to last name this time

name this time department and

department and finally

finally salary as you can see the values do

salary as you can see the values do match now let's see what happens if I

match now let's see what happens if I search for a value that doesn't exist so

search for a value that doesn't exist so 3887 we're going to get not found here

3887 we're going to get not found here because here I have put if error whereas

because here I have put if error whereas the X lookup just comes back with n a so

the X lookup just comes back with n a so the first advantage of X lookup is while

the first advantage of X lookup is while writing the formula itself you can

writing the formula itself you can specify what to do if the value is not

specify what to do if the value is not found so this is an optional parameter

found so this is an optional parameter for X lookup if not found this way you

for X lookup if not found this way you don't have to write a if error around it

don't have to write a if error around it it comes part of the formula itself so

it comes part of the formula itself so here I can say not found and that's how

here I can say not found and that's how that will look and we can apply the same

that will look and we can apply the same logic for all of these as well let's go

logic for all of these as well let's go back to a value that exists so here we

back to a value that exists so here we have got that and if you notice

have got that and if you notice carefully the X lookup instead of saying

carefully the X lookup instead of saying the entire table we are going to split

the entire table we are going to split the table into two parts what is it that

the table into two parts what is it that you're looking for and what is it that

you're looking for and what is it that you want to get this way even when you

you want to get this way even when you are not looking against the ID let's say

are not looking against the ID let's say you're looking up against the last name

you're looking up against the last name like here in tux swells case we can just

like here in tux swells case we can just swap around the columns and we don't

swap around the columns and we don't need a index match kind of a thing so

need a index match kind of a thing so the second advantage of xlup is it can

the second advantage of xlup is it can do what index match kind of a thing does

do what index match kind of a thing does because it's not limited by looking up

because it's not limited by looking up only in the First

only in the First Column so here for example in this case

Column so here for example in this case we can also use x lookup like this X

we can also use x lookup like this X look up look up this

look up look up this value in the staff data last name column

value in the staff data last name column and then get the staff data employee ID

and then get the staff data employee ID column and optionally we can also say

column and optionally we can also say not found as the error value and we will

not found as the error value and we will get the same thing and I can do the same

get the same thing and I can do the same for department and salary as

well another advantage of X look up is instead of returning a single column you

instead of returning a single column you can return multiple values so for

can return multiple values so for example I want to look up tawell and

example I want to look up tawell and then get their entire record here all of

then get their entire record here all of their information I could do that I can

their information I could do that I can say entire data and then say x lookup

say entire data and then say x lookup look up this in the staff table last

look up this in the staff table last name column and then get the entire

name column and then get the entire staff table itself once tux Val is found

staff table itself once tux Val is found I want their entire data and when you

I want their entire data and when you hit enter it's going to give you that

hit enter it's going to give you that full row corresponding to

full row corresponding to txell as our data is horizontal here in

txell as our data is horizontal here in this case the data goes horizontally

this case the data goes horizontally across the screen that is how X lookup

across the screen that is how X lookup is going to lay it out but you can also

is going to lay it out but you can also use other formulas in Excel to transpose

use other formulas in Excel to transpose this that is change from rows to columns

this that is change from rows to columns and to do that we are going to say x

and to do that we are going to say x lookup this value in the staff table

lookup this value in the staff table last

last name and get the staff table and rather

name and get the staff table and rather than pressing enter send this entire

than pressing enter send this entire thing to a function called

thing to a function called transpose this is going to take rows and

transpose this is going to take rows and turn them into columns and when you hit

turn them into columns and when you hit enter the values will come down on the

enter the values will come down on the screen like that this is super helpful

screen like that this is super helpful if you want to for example present the

if you want to for example present the data by looking up and retrieving entire

data by looking up and retrieving entire records and and laying them out on the

records and and laying them out on the screen let's conclude this concept with

screen let's conclude this concept with one extra challenge for you can you find

one extra challenge for you can you find all the employees with

all the employees with $120,000 of salary so that means we are

$120,000 of salary so that means we are looking up but we are not stopping at

looking up but we are not stopping at the first employee we want to see

the first employee we want to see everybody how would you do that and if

everybody how would you do that and if you need a hint we already covered this

you need a hint we already covered this concept earlier in this video now let's

concept earlier in this video now let's switch up the gears by looking at some

switch up the gears by looking at some complex business questions

complex business questions our eighth business problem is finding

our eighth business problem is finding out the highest salaried person mind you

out the highest salaried person mind you we are not interested in what is the

we are not interested in what is the highest salary which we were able to get

highest salary which we were able to get it with the max function instead we want

it with the max function instead we want to know what is the name of such person

to know what is the name of such person so here I'm putting two formulas one is

so here I'm putting two formulas one is the first such person with the highest

the first such person with the highest salary and if there are ties then list

salary and if there are ties then list of all the people here in this

of all the people here in this cell to find the highest salary we could

cell to find the highest salary we could use the max formul

use the max formul you can say Max of Staff table salary

you can say Max of Staff table salary column and we'll see that that is

column and we'll see that that is 120,000 like I said we don't want to

120,000 like I said we don't want to know the value here we want to know who

know the value here we want to know who is that person so we want to do a lookup

is that person so we want to do a lookup against the max value in the salary

against the max value in the salary column so we will say X look up this

column so we will say X look up this value the maximum value in the staff

value the maximum value in the staff table table salary column and then get

table table salary column and then get the staff

the staff table first name of the person as we

table first name of the person as we want maybe the full name not just the

want maybe the full name not just the first name here is a little trick that

first name here is a little trick that you can use in xlookup when you are

you can use in xlookup when you are returning a value you can specify a

returning a value you can specify a column or multiple columns or operations

column or multiple columns or operations on columns so I'm going to say

on columns so I'm going to say end within double quotes space ENT this

end within double quotes space ENT this is how you can combine first name and

is how you can combine first name and last name with the space in the middle

last name with the space in the middle and then say Staff last

and then say Staff last name what this is going to do is it's

name what this is going to do is it's going to look up the maximum salary in

going to look up the maximum salary in the salary column and then for that

the salary column and then for that person give you the first name and last

person give you the first name and last name together as one value so here we'll

name together as one value so here we'll see that as minurva record dot let's

see that as minurva record dot let's double check this in the data if I go to

double check this in the data if I go to the data and look at the

the data and look at the salary and scroll all the way down and

salary and scroll all the way down and select 120,000 which is the highest

select 120,000 which is the highest value and click okay we'll see Mina's

value and click okay we'll see Mina's name

name here and that is the value that our X

here and that is the value that our X lookup has come up with but as you can

lookup has come up with but as you can see there is also mck value here this

see there is also mck value here this person also makes the same salary both

person also makes the same salary both of them are sitting on

of them are sitting on 120,000 in this case what we want is we

120,000 in this case what we want is we don't want to stop at minurva we want to

don't want to stop at minurva we want to get both minurva and mix

get both minurva and mix names so that's the next Formula that we

names so that's the next Formula that we are going to do this time we are going

are going to do this time we are going to use the filter function instead so

to use the filter function instead so we're going to say filter staff

we're going to say filter staff table and first name

table and first name column staff table salary is equal to

column staff table salary is equal to maximum of Staff table

maximum of Staff table salary this is going to give both

salary this is going to give both minurva and mix values again you're only

minurva and mix values again you're only going to get first name but you can

going to get first name but you can apply the same trick with Filter as well

apply the same trick with Filter as well you can select this and then say instead

you can select this and then say instead of first name Amer send within double

of first name Amer send within double quotes space and then one more Amper

quotes space and then one more Amper send

send Staff last

Staff last name that's going to give Min varica Dot

name that's going to give Min varica Dot and mix prary both of their names in two

and mix prary both of their names in two cells but what if I don't want to see in

cells but what if I don't want to see in two cells what if I want to see the

two cells what if I want to see the whole names here comma

whole names here comma separated we can take this entire filter

separated we can take this entire filter output and send it to a function that

output and send it to a function that can combine text values Excel has the

can combine text values Excel has the perfect function for that it is the text

perfect function for that it is the text join function

join function text join and you need to specify what

text join and you need to specify what delimer to use we want to use comma

delimer to use we want to use comma space as the delimer and for the next

space as the delimer and for the next parameter we can just leave it out by

parameter we can just leave it out by typing another comma and then give the

typing another comma and then give the filter

filter output this is going to take the filter

output this is going to take the filter output and comma separate that and give

output and comma separate that and give you both names in one cell like this so

you both names in one cell like this so you can see Minar Dot and mix braberry

you can see Minar Dot and mix braberry here as the values our next example is

here as the values our next example is to get all the employees that have

to get all the employees that have joined in the month of March mind you in

joined in the month of March mind you in the data

here we have the start date and we are looking for march across any year so

looking for march across any year so here I have got an employee that joined

here I have got an employee that joined on 30th March of

on 30th March of [Music]

[Music] 2021 and further down here I have got

2021 and further down here I have got another employee that joined on 24th of

another employee that joined on 24th of March

March 2020 so irresp of which year it is if

2020 so irresp of which year it is if they have joined our organization in the

they have joined our organization in the month of March then I would like to see

month of March then I would like to see them this is where again we can use the

them this is where again we can use the filter function to solve this problem in

filter function to solve this problem in an elegant

an elegant fashion first of all let me show you

fashion first of all let me show you some tricks with the filter for example

some tricks with the filter for example if I say

if I say filter staff

filter staff data staff

data staff data first

data first name and if I simply say James I'm not

name and if I simply say James I'm not even sure if we have a James we don't

even sure if we have a James we don't have a James let's go get a name that

have a James let's go get a name that exists

Hogan so let's search for

for Hogan we'll get their record but if I

Hogan we'll get their record but if I want to see all the people whose name

want to see all the people whose name begins with the letter H what we could

begins with the letter H what we could do is instead of simply saying Staff

do is instead of simply saying Staff first name is like this we can send this

first name is like this we can send this to a function like left left of Staff

to a function like left left of Staff first name comma 1 and that's going to

first name comma 1 and that's going to get the leftmost character and I can

get the leftmost character and I can simply check that is that equal to H and

simply check that is that equal to H and we are going to get all these people

we are going to get all these people whose names begin with the letter H So

whose names begin with the letter H So within the filter function and not just

within the filter function and not just filter function by the way even X lookup

filter function by the way even X lookup and other kind of functions you can use

and other kind of functions you can use these kind of operations to extract

these kind of operations to extract portions of the data so we are going to

portions of the data so we are going to use the same technique but but this time

use the same technique but but this time look at the date so instead of this what

look at the date so instead of this what we want to do is we want to say look at

we want to do is we want to say look at the staff data and filter by looking at

the staff data and filter by looking at staff date

staff date joined and we want to look at the start

joined and we want to look at the start date of March but let's just say you are

date of March but let's just say you are interested in all the staff that have

interested in all the staff that have started since year

started since year 2020 you could also use greater than or

2020 you could also use greater than or equal to and then specify the date for

equal to and then specify the date for this you could use the date function and

this you could use the date function and then generate the date of

then generate the date of 20211 which corresponds to 1st of

20211 which corresponds to 1st of January 2020 and when you hit enter

January 2020 and when you hit enter you're going to see all the staff that

you're going to see all the staff that have joined on or after 1st of January

have joined on or after 1st of January 2020 we could use similar logic but

2020 we could use similar logic but instead of greater than we are going to

instead of greater than we are going to take the start date and calculate the

take the start date and calculate the month of that so for this we'll say

month of that so for this we'll say month

month of start staff table start

of start staff table start date is equal to three three corresponds

date is equal to three three corresponds to March and you're going to see all the

to March and you're going to see all the people that have joined in the months of

people that have joined in the months of March I'm going to select this date

March I'm going to select this date column here and apply the date

column here and apply the date formatting you could do that by control

formatting you could do that by control shift 3 and here you can see all of

shift 3 and here you can see all of these are in the month of

March now remember we are not done we are not interested in all the columns we

are not interested in all the columns we just want to see employee ID first name

just want to see employee ID first name and last name so the first part of this

and last name so the first part of this filter instead of saying staff I'm going

filter instead of saying staff I'm going to open square bracket and one more

to open square bracket and one more square bracket and select employee ID

square bracket and select employee ID close square bracket

close square bracket column last

column last name that way we are saying when you

name that way we are saying when you done filtering just get me the three

done filtering just get me the three columns and nothing else so here we got

columns and nothing else so here we got all these names we can see

all these names we can see vasvi Carlin Etc that have joined in the

vasvi Carlin Etc that have joined in the month of

month of March before we move to the next one I

March before we move to the next one I do have a challenge for you and that is

do have a challenge for you and that is to get all the female employees that

to get all the female employees that have started on a Monday how would you

have started on a Monday how would you do that let me know in the

do that let me know in the comments now let's go to the last

comments now let's go to the last example this complex formula involves

example this complex formula involves actually creating a report so we want to

actually creating a report so we want to make a department report that includes

make a department report that includes head counts salaries and and percentage

head counts salaries and and percentage of difference from the overall average

of difference from the overall average all visualized and presented in a neat

all visualized and presented in a neat format so here I have set up a page for

format so here I have set up a page for this report up top we want to see what

this report up top we want to see what is happening for all the Departments and

is happening for all the Departments and further down what is happening at an

further down what is happening at an individual Department we want to see

individual Department we want to see what is the head count average salary

what is the head count average salary percentage difference from the overall

percentage difference from the overall average and what is the highest salary I

average and what is the highest salary I also have a surprise for you here I'll

also have a surprise for you here I'll will reveal that towards the end to get

will reveal that towards the end to get the head count at an overall level we

the head count at an overall level we can simply use count a function and

can simply use count a function and Select Staff and something like employee

Select Staff and something like employee ID we should get 260 for this value and

ID we should get 260 for this value and to get an overall average we can use the

to get an overall average we can use the average function staff table salary

average function staff table salary column we're going to get the value with

column we're going to get the value with a lot of precision but you can apply

a lot of precision but you can apply currency formatting by pressing control

currency formatting by pressing control Shift 4 and you'll get the value I'm

Shift 4 and you'll get the value I'm going to take it down to just no decimal

going to take it down to just no decimal values and we are looking at

values and we are looking at 73870 as the

73870 as the average remember if you are doing this

average remember if you are doing this with raw data directly this number might

with raw data directly this number might be different because through the course

be different because through the course of this video we did update the data a

of this video we did update the data a little

little bit and this one we don't have to

bit and this one we don't have to calculate because there is no difference

calculate because there is no difference from from an overall level the highest

from from an overall level the highest salary is maximum of Staff table salary

salary is maximum of Staff table salary column again we're going to get 120,000

column again we're going to get 120,000 we want similar formatting as this cell

we want similar formatting as this cell so I'll select this cell click on format

so I'll select this cell click on format painter and apply that formatting there

painter and apply that formatting there now let's see all the Departments here

now let's see all the Departments here in alphabetical order for this we could

in alphabetical order for this we could use the unique function unique staff

use the unique function unique staff table Department

table Department we're going to get all the columns I

we're going to get all the columns I thought we are going to get 12 but we

thought we are going to get 12 but we are getting 13 because we did add a new

are getting 13 because we did add a new Department called business growth

Department called business growth halfway through the

halfway through the video so I'm just going to extend this

video so I'm just going to extend this uh formatting here as well so we have

uh formatting here as well so we have that let's calculate the head counts

that let's calculate the head counts this is where we could use the countifs

this is where we could use the countifs function count ifs staff table

function count ifs staff table department and the department m is in

department and the department m is in this range B6 to B18 but as this is a

this range B6 to B18 but as this is a spill range I can simply say B6 hash and

spill range I can simply say B6 hash and that's going to point to the entire

that's going to point to the entire range

range here we will get the values for

here we will get the values for everybody in those

everybody in those departments for average we're going to

departments for average we're going to use the average ifs function average of

use the average ifs function average of Staff table salary column staff table

Staff table salary column staff table [Music]

[Music] department is B6

department is B6 hash and we're going to get these values

hash and we're going to get these values let's round them up to node decimal

let's round them up to node decimal values and apply the currency

values and apply the currency formatting now comes the fun part I want

formatting now comes the fun part I want to calculate how much these values

to calculate how much these values differ from the overall

differ from the overall average so here we want to select this

average so here we want to select this minus that but we want to do these

minus that but we want to do these subtractions for every value here as D6

subtractions for every value here as D6 itself is a spill range we can simply

itself is a spill range we can simply say D6 hash minus D3 you don't have to

say D6 hash minus D3 you don't have to say this as dollar D dollar3 when you

say this as dollar D dollar3 when you are using spillable formulas Excel will

are using spillable formulas Excel will automatically stick this reference to D3

automatically stick this reference to D3 as needed so if you hit enter you're

as needed so if you hit enter you're going to get all of these values 79,000

going to get all of these values 79,000 here is

here is 5,827 more than the average there let's

5,827 more than the average there let's apply currency formatting on this one as

apply currency formatting on this one as well and we'll get that and you can

well and we'll get that and you can quickly see which departments have more

quickly see which departments have more average and which departments are

average and which departments are lagging behind while this red coloring

lagging behind while this red coloring helps there is also another trick that

helps there is also another trick that you could use you can select this column

you could use you can select this column and apply conditional formatting and use

and apply conditional formatting and use a data bar to quickly see where the

a data bar to quickly see where the values are so far ahead or where they're

values are so far ahead or where they're behind so I'm going to add a solid data

behind so I'm going to add a solid data bar and let's go ahead and customize

bar and let's go ahead and customize this by looking at the manage rules and

this by looking at the manage rules and select this rule edit it and I'm going

select this rule edit it and I'm going to adjust this rule so instead of

to adjust this rule so instead of automatic to automatic what I want to do

automatic to automatic what I want to do is I want to make sure we can read the

is I want to make sure we can read the bar as well as the

bar as well as the number so first upep what I will do is I

number so first upep what I will do is I will just say show the bar only here and

will just say show the bar only here and click okay that's going to give me just

click okay that's going to give me just the bar and then I'm going to insert

the bar and then I'm going to insert another column here and in this column

another column here and in this column I'll say equal to and then E6 hash so

I'll say equal to and then E6 hash so we're going to get the same values again

we're going to get the same values again and in this range it also copied the

and in this range it also copied the conditional formatting so I'm going to

conditional formatting so I'm going to take out that conditional formatting so

take out that conditional formatting so we are only looking at the values like

we are only looking at the values like that I'll take down this values by

that I'll take down this values by reducing the for font size a little bit

reducing the for font size a little bit and just change the color to that I also

and just change the color to that I also don't want to see the red coloring here

don't want to see the red coloring here so I'm going to go to format cells

so I'm going to go to format cells and from currency I'll just change this

and from currency I'll just change this to that style of formatting so that it

to that style of formatting so that it comes up like that so these numbers are

comes up like that so these numbers are showing up in an adjacent column and

showing up in an adjacent column and I'll just size this a little bit like

I'll just size this a little bit like that so that we can now see the bars as

that so that we can now see the bars as well as the numbers finally I'll select

well as the numbers finally I'll select these two cells and merge and center

these two cells and merge and center them so that that comes up nicely up top

them so that that comes up nicely up top in the

in the middle finally let's calculate the

middle finally let's calculate the highest salary for each department this

highest salary for each department this is nothing but Max

is nothing but Max ifs staff table salary

ifs staff table salary column staff

column staff table

department is my B6 hash we're going to get the maximums for

hash we're going to get the maximums for every Department again let's apply

every Department again let's apply currency formatting I'll select this

currency formatting I'll select this column here click on format painter and

column here click on format painter and apply that formatting here as well so

apply that formatting here as well so there you go a concise Department report

there you go a concise Department report that tells you what is happening and

that tells you what is happening and shows everything I did forget a couple

shows everything I did forget a couple of steps number one being sorting these

of steps number one being sorting these departments in alphabetical order so I'm

departments in alphabetical order so I'm going to select this unique function and

going to select this unique function and add the sort around it and like magic

add the sort around it and like magic all of these values recalculate now that

all of these values recalculate now that these departments have

these departments have changed now time for

changed now time for surprise how would you calculate these

surprise how would you calculate these values I want to know what is the median

values I want to know what is the median as well as what is the female ratio in

as well as what is the female ratio in each of these departments what kind of

each of these departments what kind of formulas would you use to come up with

formulas would you use to come up with these numbers here as well as up top

these numbers here as well as up top there go ahead and give it a try that's

there go ahead and give it a try that's going to help you unlock and learn some

going to help you unlock and learn some new techniques with formulas just like

new techniques with formulas just like formulas Excel also offers another

formulas Excel also offers another powerful way to analyze the data and

powerful way to analyze the data and that is called pivot tables so in this

that is called pivot tables so in this segment of the video Let's understand

segment of the video Let's understand how to use pivot Tables by exploring

how to use pivot Tables by exploring these 10 analysis themes we are going to

these 10 analysis themes we are going to switch track here and use a different

switch track here and use a different type of data for this purpose we are

type of data for this purpose we are going to use a madeup call center data

going to use a madeup call center data set for analyzing and using pivot tables

set for analyzing and using pivot tables of excel along way we are also going to

of excel along way we are also going to uncover some of the amazing and Powerful

uncover some of the amazing and Powerful features of excel like interactive

features of excel like interactive graphs and slices and dynamic pivot

graphs and slices and dynamic pivot reports let's continue our journey here

reports let's continue our journey here are the 10 analysis themes that we are

are the 10 analysis themes that we are going to attempt using pivot TBL

going to attempt using pivot TBL let's first take a look at the data here

let's first take a look at the data here I have got a call center data set that

I have got a call center data set that tells me the call numbers customer IDs

tells me the call numbers customer IDs the customer who is calling us how long

the customer who is calling us how long they spoke with us who is the

they spoke with us who is the representative that took the call the

representative that took the call the date on which the call came in how much

date on which the call came in how much is the purchase amount and what is the

is the purchase amount and what is the satisfaction rating I have the data for

satisfaction rating I have the data for year 2023 and we have 1,000 call

year 2023 and we have 1,000 call information

information here we can use pivot tables to quickly

here we can use pivot tables to quickly and efficiently analyze this data in

and efficiently analyze this data in Microsoft Excel I do have a surprise for

Microsoft Excel I do have a surprise for you here which I'm going to reveal later

you here which I'm going to reveal later in the video but for now let's take a

in the video but for now let's take a look at the analysis themes closely our

look at the analysis themes closely our first one is how many calls we are

first one is how many calls we are getting by customer in order to answer

getting by customer in order to answer something like this we can quickly set

something like this we can quickly set up a pivot table on top of this data and

up a pivot table on top of this data and then look look at the customer ID and

then look look at the customer ID and count how many times each ID has

count how many times each ID has appeared before you can set up the pivot

appeared before you can set up the pivot table it is a good idea to turn this

table it is a good idea to turn this data into tabular format in Excel to do

data into tabular format in Excel to do this just select any cell and press

this just select any cell and press contr t t for table and click okay to

contr t t for table and click okay to add the

add the table this is going to apply consistent

table this is going to apply consistent formatting on the top of data and you

formatting on the top of data and you can also see the table design ribbon

can also see the table design ribbon here to set up more Styles

here to set up more Styles Etc before we can make a a table I'm

Etc before we can make a a table I'm going to give this table a name right

going to give this table a name right now it is called table one I'm going to

now it is called table one I'm going to name this as calls and hit enter to

name this as calls and hit enter to commit that name now you can use this

commit that name now you can use this summarize with pivot table button right

summarize with pivot table button right here on the table design Ribbon or you

here on the table design Ribbon or you can also go to the insert ribbon here to

can also go to the insert ribbon here to find the pivot table

find the pivot table options let's click on summarize with

options let's click on summarize with pivot table when you do this it'll

pivot table when you do this it'll identify the existing table the calls

identify the existing table the calls table and it'll ask you where do you

table and it'll ask you where do you want this pivot I'm going to make this

want this pivot I'm going to make this in a new worksheet and later on in this

in a new worksheet and later on in this video we are also going to understand

video we are also going to understand what this will do and how we can use

what this will do and how we can use that to build even more powerful pivot

that to build even more powerful pivot tables for now let's select new sheets

tables for now let's select new sheets and click on

and click on okay this is going to add a new

okay this is going to add a new worksheet and set up the pivot table

worksheet and set up the pivot table grid area here a pivot table is a

grid area here a pivot table is a concise business report that you can

concise business report that you can create in Microsoft Excel it might seem

create in Microsoft Excel it might seem a bit weird at the first attempt but

a bit weird at the first attempt but once you make it it all makes sense so

once you make it it all makes sense so remember the thing that we are trying to

remember the thing that we are trying to do right now is how many calls we are

do right now is how many calls we are getting by customer so I'm going to go

getting by customer so I'm going to go to my sheet one and in this area here we

to my sheet one and in this area here we want to see customer IDs go along the

want to see customer IDs go along the screen like this and how many calls

screen like this and how many calls we're getting

we're getting here so this is where the pivot table

here so this is where the pivot table Fields area comes into picture this

Fields area comes into picture this little tab that appears on the right

little tab that appears on the right hand side is what we can use to set up

hand side is what we can use to set up the pivot table there are two important

the pivot table there are two important aspects of this screen one is the field

aspects of this screen one is the field list this is where all the fields of

list this is where all the fields of your table of data will show up each

your table of data will show up each field is one column of your data and

field is one column of your data and then the second is the fields area here

then the second is the fields area here where you can put whatever you want into

where you can put whatever you want into the relevant aspect of the pivot report

the relevant aspect of the pivot report again like I said this all seems a bit

again like I said this all seems a bit weird so let's first build it and then

weird so let's first build it and then it all makes sense so I'm going to take

it all makes sense so I'm going to take the customer ID and as I want to see

the customer ID and as I want to see customer IDs go down screen I'll put

customer IDs go down screen I'll put this into the rose area here you can

this into the rose area here you can just drag and drop it into the rose area

just drag and drop it into the rose area there and for each customer that we have

there and for each customer that we have in our data we have got 15 customers

in our data we have got 15 customers their IDs will be displayed here

their IDs will be displayed here immediately we want to see how many

immediately we want to see how many calls they're making

calls they're making we can take the call number and put it

we can take the call number and put it into the values

into the values area and Excel is going to count how

area and Excel is going to count how many calls are coming up you can see the

many calls are coming up you can see the total is 1,000 and this is how the

total is 1,000 and this is how the customers are calling for example

customers are calling for example customer 4 has called us 82 times

customer 4 has called us 82 times whereas customer 3 has only called us 50

whereas customer 3 has only called us 50 times as you can see creating a pivot

times as you can see creating a pivot table is really like Child's Play you

table is really like Child's Play you just drag and drop things into various

just drag and drop things into various parts of the pivot table construction

parts of the pivot table construction thing here and then it will create the

thing here and then it will create the report for you now that you have seen a

report for you now that you have seen a report let's go ahead and examine the

report let's go ahead and examine the areas of this pivot table Fields thing

areas of this pivot table Fields thing one

one closely like I said earlier these are

closely like I said earlier these are the fields of your data and whatever

the fields of your data and whatever field you want you can put it into the

field you want you can put it into the rows area whatever you put in rows they

rows area whatever you put in rows they will appear along the screen here you

will appear along the screen here you can also put items into the columns area

can also put items into the columns area we're going to do this in the next part

we're going to do this in the next part of this video and whatever you put in

of this video and whatever you put in the values that is going to be either

the values that is going to be either counted added or

counted added or averaged let's take a look at the next

averaged let's take a look at the next example our next example is how

example our next example is how satisfied are our customers and we're

satisfied are our customers and we're going to do that in the same pivot table

going to do that in the same pivot table rather than making a new one altoe so as

rather than making a new one altoe so as I'm seeing the count of calls I also

I'm seeing the count of calls I also want to see the total satisfaction

want to see the total satisfaction rating information here so I'll select

rating information here so I'll select the pivot table cell again and then this

the pivot table cell again and then this pivot table Fields thing

pivot table Fields thing activates a quick note of caution here

activates a quick note of caution here if you click outside the pivot you will

if you click outside the pivot you will not be able to access the pivot table

not be able to access the pivot table Fields thing so this is why you need to

Fields thing so this is why you need to click inside and then use this and once

click inside and then use this and once you're looking at the fields list here

you're looking at the fields list here you can take another field like

you can take another field like satisfaction rating and put it into the

satisfaction rating and put it into the values this is going to initially sum up

values this is going to initially sum up the total satisfaction rating values so

the total satisfaction rating values so for example customer 1 it says the sum

for example customer 1 it says the sum of satisfaction rating is

of satisfaction rating is 249 now that is a ridiculous number if

249 now that is a ridiculous number if you look at the data closely we have got

you look at the data closely we have got satisfaction rating here that usually

satisfaction rating here that usually goes from zero all the way up to five so

goes from zero all the way up to five so ideally what I want is here I want to

ideally what I want is here I want to see what is the average rating of that

see what is the average rating of that customer's

customer's satisfaction so as satisfaction rating

satisfaction so as satisfaction rating is a number by default pivot will Summit

is a number by default pivot will Summit up but you can right click on this

up but you can right click on this number and you can say summarize values

number and you can say summarize values by and then change the nature of

by and then change the nature of calculation from sum to average you just

calculation from sum to average you just have to click on the average and it will

have to click on the average and it will change that so there you go we'll get

change that so there you go we'll get the average rating while I'm happy with

the average rating while I'm happy with this average rating it also looks a bit

this average rating it also looks a bit ridiculous with six decimal points so

ridiculous with six decimal points so let's fix that you can right click on

let's fix that you can right click on the number again and go to number form

the number again and go to number form format option here and from here you can

format option here and from here you can tell Excel how you want all of these

tell Excel how you want all of these numbers to be formatted so I'm going to

numbers to be formatted so I'm going to select number format and reduce this

select number format and reduce this down to just one decimal point and click

down to just one decimal point and click okay so now everybody's satisfaction

okay so now everybody's satisfaction rating is rounded to one decimal point

rating is rounded to one decimal point and you can see some fours some threes

and you can see some fours some threes and at an overall level we have got a

and at an overall level we have got a average of 3.9 satisfaction

average of 3.9 satisfaction rating let's take a look at the next

rating let's take a look at the next example

example so here I want to look at who are our

so here I want to look at who are our top 10 customers and for this we are

top 10 customers and for this we are also going to understand how to apply

also going to understand how to apply filters on top of the pivot table so

filters on top of the pivot table so this time we're going to make a brand

this time we're going to make a brand new pivot table I'll go to the data

new pivot table I'll go to the data select any one cell and then go to

select any one cell and then go to insert and click on pivot table option

insert and click on pivot table option here all of these will do the same thing

here all of these will do the same thing you can use the pivot table you can use

you can use the pivot table you can use the pivot from the table design you can

the pivot from the table design you can also if you're not really sure what kind

also if you're not really sure what kind of pivots to make try out this

of pivots to make try out this recommended pivots option which is a new

recommended pivots option which is a new feature available in Excel 2016 and

feature available in Excel 2016 and 365 let's try out the pivot table option

365 let's try out the pivot table option here so if you click on that it's going

here so if you click on that it's going to again identify the table range as

to again identify the table range as calls and it'll ask you where you want

calls and it'll ask you where you want I'm going to make this into a new

I'm going to make this into a new worksheet click

worksheet click okay let's rename this as 1 and

okay let's rename this as 1 and two and this sheet I'm going to call it

two and this sheet I'm going to call it as three because here we're going to do

as three because here we're going to do the third analysis problem so here what

the third analysis problem so here what I want to do is I want to see our top 10

I want to do is I want to see our top 10 customers by the amount of business they

customers by the amount of business they are doing with us if you look at the

are doing with us if you look at the data apart from the call duration and

data apart from the call duration and the date we also know what is the

the date we also know what is the purchase amount so for our top 10

purchase amount so for our top 10 analysis I would like to look at the

analysis I would like to look at the total amount purchased by each customer

total amount purchased by each customer and then bring this down to just the top

and then bring this down to just the top 10 customers

10 customers this is really simple we're going to

this is really simple we're going to take the customer ID and put it into the

take the customer ID and put it into the rose area here and take the purchase

rose area here and take the purchase amount and put it into the

amount and put it into the values initially you're going to see the

values initially you're going to see the total of all the numbers added up here

total of all the numbers added up here and they will all look like this as

and they will all look like this as these are dollar values it's a good idea

these are dollar values it's a good idea to First apply the dollar formatting so

to First apply the dollar formatting so you can right click on this number go to

you can right click on this number go to number

number format and set this to currency with

format and set this to currency with zero decimal

zero decimal so now everything looks nice and a total

so now everything looks nice and a total of $96,000 total value here and let's

of $96,000 total value here and let's say we don't want to see all of these we

say we don't want to see all of these we are only interested in our top 10

are only interested in our top 10 customers so you can click on this

customers so you can click on this little filter button that shows up top

little filter button that shows up top here and from there you can go to Value

here and from there you can go to Value filters and select the top 10 filter

filters and select the top 10 filter option while you're here you can also

option while you're here you can also try out some of these other things if

try out some of these other things if you're feeling fancy so before doing top

you're feeling fancy so before doing top 10 let's try out the greater than option

10 let's try out the greater than option let's say I want to look at all the

let's say I want to look at all the customers who are bringing in more than

customers who are bringing in more than $7,000

$7,000 Revenue I can go to Value filters

Revenue I can go to Value filters greater than and then type the value as$

greater than and then type the value as$ 7,000 and click okay and I'll be able to

7,000 and click okay and I'll be able to look at the four customers that are

look at the four customers that are bringing in that amount you'll also see

bringing in that amount you'll also see the grand total here reflects that

the grand total here reflects that value now let's go back to the problem

value now let's go back to the problem that we were trying to do which is just

that we were trying to do which is just the top 10 customers not the ones that

the top 10 customers not the ones that have more than 7,000 so to do that I'm

have more than 7,000 so to do that I'm going to click on this again first clear

going to click on this again first clear up the filter from this and then go back

up the filter from this and then go back again value filters top 10 and as we are

again value filters top 10 and as we are only interested in top 10 you can

only interested in top 10 you can straight away click okay and that's

straight away click okay and that's going to show you the top 10

going to show you the top 10 customers whenever you looking at top 10

customers whenever you looking at top 10 numbers like this it's a good idea to

numbers like this it's a good idea to arrange this report in the descending

arrange this report in the descending order of the amount to do that you can

order of the amount to do that you can again right click on the these numbers

again right click on the these numbers and go to sort and select largest to

and go to sort and select largest to smallest option so that you can see the

smallest option so that you can see the customer with the highest amount up top

customer with the highest amount up top and the 10th most value at the

and the 10th most value at the bottom let's go to the next

bottom let's go to the next one we also now want to see the top 10

one we also now want to see the top 10 customers for a specific

customers for a specific representative if you look at the data

representative if you look at the data each call is taken by a specific

each call is taken by a specific representative so if I want to ask the

representative so if I want to ask the question who are the top 10 C customers

question who are the top 10 C customers for

for r02 to answer such a thing we can use

r02 to answer such a thing we can use the pivot table fields and there is an

the pivot table fields and there is an option called filters here if I put a

option called filters here if I put a filter there that's going to filter down

filter there that's going to filter down the report just to that value for

the report just to that value for example I can take the representative

example I can take the representative and put it into the

and put it into the filters and that will be added up top

filters and that will be added up top here and from now from here you can

here and from now from here you can select instead of all r02 and when you

select instead of all r02 and when you click okay you will see who are the top

click okay you will see who are the top 10 people for

10 people for r02 coincidentally it is the same

r02 coincidentally it is the same customer

customer c005

$2,280 let's take a look at r04 for r04 it's a different person C13

r04 for r04 it's a different person C13 with

with $1,863 you'll also note that as you

$1,863 you'll also note that as you change these values the pivot will

change these values the pivot will automatically rearrange the values and

automatically rearrange the values and sort them in the descending order

sort them in the descending order consistently so this is why if you apply

consistently so this is why if you apply the sort order it kind of works all the

the sort order it kind of works all the time even when your data

time even when your data changes while I like these kind of

changes while I like these kind of filters these are called report filters

filters these are called report filters by the way they are a bit clunky to use

by the way they are a bit clunky to use so I'm going to show you a different

so I'm going to show you a different trick to do the same thing and these are

trick to do the same thing and these are called slicers and these are one of my

called slicers and these are one of my most favorite features in Excel so let's

most favorite features in Excel so let's take a look at that

take a look at that first up I'm going to delete this report

first up I'm going to delete this report filter so I'll set this to all and then

filter so I'll set this to all and then I'll take out the representative to

I'll take out the representative to delete a field from pivot table you can

delete a field from pivot table you can just select it and drag and drop it

just select it and drag and drop it outside you'll see that the cursor will

outside you'll see that the cursor will have this little x mark next to it and

have this little x mark next to it and when you do that it will go off you can

when you do that it will go off you can also while looking at the field list

also while looking at the field list here uncheck the pivot so here there is

here uncheck the pivot so here there is a check box and you can uncheck that and

a check box and you can uncheck that and that will also take out the field from

that will also take out the field from the pivot table

the pivot table so now let's take a look at another way

so now let's take a look at another way of setting up this filter just right

of setting up this filter just right click on the representative and you'll

click on the representative and you'll have ADD as slicer option this will give

have ADD as slicer option this will give you a button kind of a thing with all

you a button kind of a thing with all the five Representatives you can hover

the five Representatives you can hover your mouse and you'll see that you can

your mouse and you'll see that you can click on any one of them so if you now

click on any one of them so if you now tap on r02 you'll see all the values for

tap on r02 you'll see all the values for that person r05 r04 this is really cool

that person r05 r04 this is really cool and it adds that layer of interactivity

and it adds that layer of interactivity to your pivot reports not only the

to your pivot reports not only the slicer is interactive but it also helps

slicer is interactive but it also helps you create interactive visuals for your

you create interactive visuals for your reports let's take a quick look at that

reports let's take a quick look at that as well I'm going to move the slicer

as well I'm going to move the slicer here and position it like

here and position it like that and here I want to see the top 10

that and here I want to see the top 10 customers as a graph so I'll select any

customers as a graph so I'll select any one cell in the pivot and then I'm going

one cell in the pivot and then I'm going to go into insert and click on a column

to go into insert and click on a column chart we'll get the column chart for

chart we'll get the column chart for these 10 customers and now see the magic

these 10 customers and now see the magic if I click on a different representative

if I click on a different representative you'll see the graph updates

you'll see the graph updates immediately so this is how the slicers

immediately so this is how the slicers can help you create interactive

can help you create interactive visualizations right in Excel we are

visualizations right in Excel we are going to go more into this topic later

going to go more into this topic later on in this video as well as in the

on in this video as well as in the followup videos of my free data analyst

followup videos of my free data analyst course for now let's go to the next one

course for now let's go to the next one the fifth one is call duration analysis

the fifth one is call duration analysis let's go to the data here and you can

let's go to the data here and you can see that we have got the duration of the

see that we have got the duration of the call here let's just assume these are in

call here let's just assume these are in seconds let's say you want to understand

seconds let's say you want to understand how the call distribution looks like one

how the call distribution looks like one quick way to do that is you can insert a

quick way to do that is you can insert a pivot you can go here and insert a pivot

pivot you can go here and insert a pivot but as we have been adding quite a few

but as we have been adding quite a few pivots you can also for example take any

pivots you can also for example take any one of these pivots copy it contrl C and

one of these pivots copy it contrl C and paste it so I'm going to do that so copy

paste it so I'm going to do that so copy pasted that pivot you'll get the same

pasted that pivot you'll get the same thing but all the fields and everything

thing but all the fields and everything will be there this is a quick way to get

will be there this is a quick way to get a clone of an existing pivot with all

a clone of an existing pivot with all the settings and quickly adjust things

the settings and quickly adjust things so here what I want to look at is call

so here what I want to look at is call duration so I'm going to take out

duration so I'm going to take out customer ID and some of these other

customer ID and some of these other things let's leave the count of call

things let's leave the count of call number here if you notice this pivot

number here if you notice this pivot table right now the one that we are

table right now the one that we are seeing this one it has a little weird

seeing this one it has a little weird structure there is nothing in this pivot

structure there is nothing in this pivot except the count of call number so when

except the count of call number so when there is nothing it just tells you what

there is nothing it just tells you what is the total number so right now it's

is the total number so right now it's just telling you there's 1,000 calls

just telling you there's 1,000 calls what we will do is we'll take the

what we will do is we'll take the duration and put it into rows and you'll

duration and put it into rows and you'll see how many calls have that specific

see how many calls have that specific duration for example three calls had 6

duration for example three calls had 6 seconds one call has 2 seconds that is a

seconds one call has 2 seconds that is a really short call but there are lots of

really short call but there are lots of longer calls here so for example 176 one

longer calls here so for example 176 one call I'm just going to assume these are

call I'm just going to assume these are actually in minutes and not in seconds

actually in minutes and not in seconds so given this information while it does

so given this information while it does tell you the call duration it's kind of

tell you the call duration it's kind of not really analytical so when you are

not really analytical so when you are looking at this you may want to for

looking at this you may want to for example group this and then consider all

example group this and then consider all calls under 10 minutes as one bucket 10

calls under 10 minutes as one bucket 10 to 30 minutes as one bucket 30 to 60

to 30 minutes as one bucket 30 to 60 Minutes 60 to 120 like that that kind of

Minutes 60 to 120 like that that kind of a thing is really easy to do with Excel

a thing is really easy to do with Excel you can just right click on any one of

you can just right click on any one of these numbers and then you'll have the

these numbers and then you'll have the group option here using which you can

group option here using which you can create groups so I'm going to first

create groups so I'm going to first pre-select some values 2 to 10 right

pre-select some values 2 to 10 right click and group that's going to create a

click and group that's going to create a group from 2 to 10 and it also going to

group from 2 to 10 and it also going to create other kinds of groups where there

create other kinds of groups where there is no values so normally when you want

is no values so normally when you want to do it like that you'll need to repeat

to do it like that you'll need to repeat these steps so for example from 10 to 30

these steps so for example from 10 to 30 we want one group so I'm going to select

we want one group so I'm going to select up to here and then group that so you'll

up to here and then group that so you'll have a group two which has these values

have a group two which has these values and then from 30 up to 60 we will create

and then from 30 up to 60 we will create one more

group and from 60 to 120 we'll create one more

one more group and finally 120 to all other

group and finally 120 to all other values we create one group so this is

values we create one group so this is one way of doing where you are manually

one way of doing where you are manually grouping items and setting things up and

grouping items and setting things up and once you have the group you can see that

once you have the group you can see that pivot actually adds a second duration

pivot actually adds a second duration field which is kind of the group field

field which is kind of the group field and then set this up in the row area

and then set this up in the row area here so now that we have got the second

here so now that we have got the second duration I'm going to take out the

duration I'm going to take out the original duration from the pivot table

original duration from the pivot table and then you can see how many calls are

and then you can see how many calls are coming so for example group one the

coming so for example group one the calls under 10 minutes very few calls

calls under 10 minutes very few calls happen there group two 51 calls group

happen there group two 51 calls group for 524 calls so most of our calls are

for 524 calls so most of our calls are between 1 hour to 2 hours which is quite

between 1 hour to 2 hours which is quite interesting and really long if you ask

interesting and really long if you ask me this is one way of doing it but there

me this is one way of doing it but there is also another way of doing it and

is also another way of doing it and there is a problem with this kind of a

there is a problem with this kind of a grouping approach one problem with this

grouping approach one problem with this grouping approach is if you go to the

grouping approach is if you go to the data

data and let's go back here for a second and

and let's go back here for a second and I'm going to add the duration as well

I'm going to add the duration as well and you can see that group one has only

and you can see that group one has only these values 2 4 6 7 8 10 now what if we

these values 2 4 6 7 8 10 now what if we get a call that lasted 9 minutes you can

get a call that lasted 9 minutes you can see that that is not part of group one's

see that that is not part of group one's definition right now so if I go to the

definition right now so if I go to the data here and change one of these values

data here and change one of these values I'm going to select this 13 here and

I'm going to select this 13 here and make it N9 just make a note that this is

make it N9 just make a note that this is called this one and once that change is

called this one and once that change is applied we'll go here right now nothing

applied we'll go here right now nothing happens but you can right click on the

happens but you can right click on the pivot and then select refresh this is

pivot and then select refresh this is going to update the calculations for you

going to update the calculations for you and when it is updated you'll see that

and when it is updated you'll see that the nine doesn't appear in the group one

the nine doesn't appear in the group one where did it go it actually went into

where did it go it actually went into its own little group because these group

its own little group because these group definitions are attached to the

definitions are attached to the ingredients that you had when you set

ingredients that you had when you set them up so this is a problem with this

them up so this is a problem with this kind of a grouping and this is why I

kind of a grouping and this is why I don't really recommend this sort of a

don't really recommend this sort of a grouping but for a quick ad hoc analysis

grouping but for a quick ad hoc analysis this is fine let's go back and change

this is fine let's go back and change this to 13 and come here and refresh the

this to 13 and come here and refresh the pivot once

again so now our nine group disappears all together and we we are back to

all together and we we are back to original problem like I said this is a

original problem like I said this is a problem and we'll need to fix it I'm

problem and we'll need to fix it I'm going to introduce a different technique

going to introduce a different technique later on in this video and one more

later on in this video and one more technique in the second part of this

technique in the second part of this video our next next analysis theme is

video our next next analysis theme is how busy is our call center in 2023 in

how busy is our call center in 2023 in order to answer this we'll need to

order to answer this we'll need to understand how to work with date fields

understand how to work with date fields when creating a pivot report let's go so

when creating a pivot report let's go so we'll make a new pivot by inserting

we'll make a new pivot by inserting pivot table

pivot table here and in this pivot table we are now

here and in this pivot table we are now going to use the date of the call

going to use the date of the call feature so we'll take it and put it into

feature so we'll take it and put it into the row label area and you'll see that

the row label area and you'll see that as soon as I put a date Excel autom

as soon as I put a date Excel autom automatically groups the date at a

automatically groups the date at a monthly level you'll also see that it

monthly level you'll also see that it adds a few more Fields the kind of

adds a few more Fields the kind of fields that are added are slightly

fields that are added are slightly different from version to version of

different from version to version of excel to version of Excel and you might

excel to version of Excel and you might even see a quarter and year added here

even see a quarter and year added here but in my case it has added day and

but in my case it has added day and month apart from the existing date

month apart from the existing date itself I find that the day itself is

itself I find that the day itself is kind of useless but the month value is

kind of useless but the month value is super helpful so what we want to do is

super helpful so what we want to do is for each month here we would like to see

for each month here we would like to see how many calls came in that way once

how many calls came in that way once that number is there I can see it in a

that number is there I can see it in a graphical format and understand how busy

graphical format and understand how busy our call center is throughout the year

our call center is throughout the year so we are going to take the call ID call

so we are going to take the call ID call number and then put that into the values

number and then put that into the values and we'll see how many calls are coming

and we'll see how many calls are coming in every month and you can see that the

in every month and you can see that the number is not consistent it kind of

number is not consistent it kind of varies a little bit but the total is

varies a little bit but the total is 1,000 now let's make this into a

1,000 now let's make this into a graphical format for that we can go to

graphical format for that we can go to the insert ribbon and click on a line

the insert ribbon and click on a line chart and add a line graph here to see

chart and add a line graph here to see the call

the call Trends you can see that we have got a

Trends you can see that we have got a slow start of the year with Jan and Feb

slow start of the year with Jan and Feb having about 80 calls but then it

having about 80 calls but then it quickly picks up in March April May and

quickly picks up in March April May and then it goes down again and then picks

then it goes down again and then picks up in October November so this is the

up in October November so this is the typical pattern for our call center and

typical pattern for our call center and you can look at this and you can

you can look at this and you can understand when the busy per are for our

understand when the busy per are for our customers when we add a date field to

customers when we add a date field to the pivot table Excel also automatically

the pivot table Excel also automatically adds this kind of a grouping and you'll

adds this kind of a grouping and you'll see that there is a little plus button

see that there is a little plus button right next to each date so for example

right next to each date so for example if I click on this plus button and

if I click on this plus button and expand the January you'll see all the

expand the January you'll see all the individual data for every day in January

individual data for every day in January not only that even the graph gets

not only that even the graph gets updated so now the entire Jan itself is

updated so now the entire Jan itself is expanded out with individual details

expanded out with individual details so one way of understanding all of this

so one way of understanding all of this is whatever the pivot table is saying

is whatever the pivot table is saying whatever level of detail pivot table has

whatever level of detail pivot table has that is how the pivot chart will behave

that is how the pivot chart will behave I jokingly call this as the pivot table

I jokingly call this as the pivot table and the pivot chart are married couple

and the pivot chart are married couple whatever one says the other will do so

whatever one says the other will do so if I for example have a daily

if I for example have a daily information here that is how my Pivot

information here that is how my Pivot chart is going to look see what happens

chart is going to look see what happens the moment I Collapse January

the moment I Collapse January automatically this graph updates

automatically this graph updates likewise if I expand March I'll have

likewise if I expand March I'll have that kind of an expansion for March data

that kind of an expansion for March data here we can take this behavior and use

here we can take this behavior and use it to our advantage by for example

it to our advantage by for example adding a slicer or something else that

adding a slicer or something else that controls the pivot table and hence

controls the pivot table and hence controls the chart as well we have

controls the chart as well we have actually done a little bit of that

actually done a little bit of that earlier here by looking at

earlier here by looking at representative yse totals so when I

representative yse totals so when I select a different rep I'll see their to

select a different rep I'll see their to totals we could do the same for this one

totals we could do the same for this one as well we could for example right click

as well we could for example right click on representative add as slicer and then

on representative add as slicer and then I can click on r02 to see their Trend

I can click on r02 to see their Trend r04 to see their Trend here is a pro

r04 to see their Trend here is a pro trick as well if you want to multi-

trick as well if you want to multi- select different Representatives maybe

select different Representatives maybe you want to understand how R1 and R4

you want to understand how R1 and R4 together are doing you can hold on

together are doing you can hold on control and select both of them and then

control and select both of them and then see this if you want to select all the

see this if you want to select all the values together you can click on r01 and

values together you can click on r01 and drag to select three items in a

drag to select three items in a go our next analysis is year toate sales

go our next analysis is year toate sales analysis and for this purpose we would

analysis and for this purpose we would like to look at our data and look at the

like to look at our data and look at the purchase amount and then see how much it

purchase amount and then see how much it adds up over the course of year again we

adds up over the course of year again we would like to make a new pivot but we

would like to make a new pivot but we already have many pivot so I'm just

already have many pivot so I'm just going to copy this pivot contrl C and

going to copy this pivot contrl C and paste it right here

paste it right here in this pivot I would like to instead of

in this pivot I would like to instead of seeing count of call number the amount

seeing count of call number the amount so I'm going to take this one out and

so I'm going to take this one out and then put the purchase amount there so

then put the purchase amount there so we'll see individual monthly values and

we'll see individual monthly values and the total is only

the total is only 59,000 that doesn't look all right if

59,000 that doesn't look all right if you remember correctly from

you remember correctly from earlier when you clear we have got a

earlier when you clear we have got a total of

total of 69,000 so what's going on with this

69,000 so what's going on with this pivot how come it is only showing 59

pivot how come it is only showing 59 this is because when you copy paste one

this is because when you copy paste one pivot to another you're not just getting

pivot to another you're not just getting a pivot you're also getting any slicer

a pivot you're also getting any slicer connections as well so if you see here

connections as well so if you see here this

this pivot is connected to that slicer but

pivot is connected to that slicer but because we copy pasted that

pivot even this guy is connected to that slicer that's why these totals are just

slicer that's why these totals are just for R1 R2 and R3 see what what happens

for R1 R2 and R3 see what what happens when I clear the slicer if I clear it

when I clear the slicer if I clear it right now I'm looking at all the

right now I'm looking at all the representatives and my total again comes

representatives and my total again comes back to

back to 96,000 here it is 69 because we are only

96,000 here it is 69 because we are only looking at the top 10 customers but once

looking at the top 10 customers but once you include all the customers it will go

you include all the customers it will go up to 96 or whatever that is all right

up to 96 or whatever that is all right so now that you understand the trick

so now that you understand the trick behind connecting the same slicer to

behind connecting the same slicer to multiple pivots let's go ahead and look

multiple pivots let's go ahead and look at how to get the year to- date totals

at how to get the year to- date totals by default each month will only show its

by default each month will only show its value but you can right click on the

value but you can right click on the number any number would do and you can

number any number would do and you can go to the show values as and from here

go to the show values as and from here you can change the type of calculation

you can change the type of calculation right now we are not doing any

right now we are not doing any calculation but instead what we want to

calculation but instead what we want to do is we want to do a running total so

do is we want to do a running total so this is what I'm going to

this is what I'm going to pick right

pick right click show value as running total in

and when you click okay you're going to see the running total in the month field

see the running total in the month field and then the values will add up and

and then the values will add up and December will have

December will have 96,000 now that the running total is

96,000 now that the running total is there you can see it either in the pivot

there you can see it either in the pivot or you could make a cool little graph

or you could make a cool little graph like for example an area graph to see

like for example an area graph to see how over the course of the Year our

how over the course of the Year our total amount has changed it started slow

total amount has changed it started slow but it quickly picked up and we reached

but it quickly picked up and we reached up to 96,000 by the end of the year and

up to 96,000 by the end of the year and the beautiful thing with this graph is

the beautiful thing with this graph is it is still linked to that slicer so if

it is still linked to that slicer so if I click on r02 I'll see how the trend is

I click on r02 I'll see how the trend is for them likewise r03 r05 you can see

for them likewise r03 r05 you can see that as I click on this these graphs

that as I click on this these graphs change you might be thinking okay chandu

change you might be thinking okay chandu this is all good but my year doesn't

this is all good but my year doesn't start on January and end on December

start on January and end on December what if I need to end the year at June

what if I need to end the year at June and then restart it from July in this

and then restart it from July in this case we will need to First calculate

case we will need to First calculate what is called a financial year and then

what is called a financial year and then bring that into the

bring that into the data to do such things you can go to the

data to do such things you can go to the data itself and add a column that tells

data itself and add a column that tells you what Financial year it is once you

you what Financial year it is once you add such a column you can come back and

add such a column you can come back and use that column in the pivot table so

use that column in the pivot table so let's attempt that I'm going to go to

let's attempt that I'm going to go to the data here and here I'm going to say

FY and in the FI we want to look at the date and then tell Excel what the

date and then tell Excel what the financial year is so the financial year

financial year is so the financial year would be

would be 2023 if the date is prior to 30th of

2023 if the date is prior to 30th of June anything after that that is 1st

June anything after that that is 1st July onwards it will be

July onwards it will be 2024 so here we can say

2024 so here we can say if month of the

date is less than or equal to 6 that means you're in the first 6 months of

means you're in the first 6 months of the

the year then the FY is same as the year of

year then the FY is same as the year of the data so in this case year will tell

the data so in this case year will tell me

me 2023 else it will be year

2023 else it will be year of this +

of this + one so here we will have 2023 and the

one so here we will have 2023 and the moment we switch over to

moment we switch over to July here you can see that it kind of

July here you can see that it kind of switch overs from 2023 3 to

switch overs from 2023 3 to 2024 this logic will work even when you

2024 this logic will work even when you have more than one year's worth of data

have more than one year's worth of data because the consistent approach of

because the consistent approach of selecting the date looking at the month

selecting the date looking at the month and then just picking the year of the

and then just picking the year of the date or year of the date plus one you

date or year of the date plus one you could do these kind of things here in

could do these kind of things here in Excel or you can also set up an add

Excel or you can also set up an add column logic in power query if your data

column logic in power query if your data is coming in like that so now that we

is coming in like that so now that we have got the FY value here let's go

have got the FY value here let's go ahead and use it in the pivot let's go

ahead and use it in the pivot let's go to the pivot here and I want to copy the

to the pivot here and I want to copy the same thing again but this time set up a

same thing again but this time set up a financial year running total so we'll

financial year running total so we'll set it up and I want to use the FY field

set it up and I want to use the FY field but it is nowhere to be found in the

but it is nowhere to be found in the field

list to get the FI field appearing here we'll need to First refresh the pivot so

we'll need to First refresh the pivot so if you go to the data ribbon and click

if you go to the data ribbon and click on refresh all that's going to refresh

on refresh all that's going to refresh refesh the pivot and you will now have

refesh the pivot and you will now have the FY column here so I'm going to take

the FY column here so I'm going to take the FY and then put it into the row

the FY and then put it into the row label area before doing that I'm going

label area before doing that I'm going to take out the days and date of call

to take out the days and date of call values so it's only just months that way

values so it's only just months that way it looks a lot cleaner in my opinion and

it looks a lot cleaner in my opinion and then bring the FY and put it up

then bring the FY and put it up top so now we have got 2023 FY 2024 FY

top so now we have got 2023 FY 2024 FY and then here we will have the values

and then here we will have the values you can see that the running totals

you can see that the running totals automatically adjust

automatically adjust we'll have the values going from 1,000

we'll have the values going from 1,000 to 9,000 and 1,800 to 11,000 and it kind

to 9,000 and 1,800 to 11,000 and it kind of resets here in the middle because we

of resets here in the middle because we rolled over to a new Financial

rolled over to a new Financial year and this thing is still linked to

year and this thing is still linked to the slicer here so if we were to put

the slicer here so if we were to put another graph this time let's go with

another graph this time let's go with the column chart you'll see the running

the column chart you'll see the running totals neatly have this kind of like two

totals neatly have this kind of like two columns going up kind of a structure

columns going up kind of a structure and if you change this for example clear

and if you change this for example clear the filters you'll have different kind

the filters you'll have different kind of graphs

of graphs again let's go to the next

again let's go to the next one which days of the week are the

one which days of the week are the busiest in order to do this kind of

busiest in order to do this kind of analysis just as we have done FY we'll

analysis just as we have done FY we'll need to add day of week column into the

need to add day of week column into the pivot

pivot table I'm going to quickly do that day

table I'm going to quickly do that day of

of week and we want to see this as spelled

week and we want to see this as spelled out rather than number so here I'm going

out rather than number so here I'm going to say text of this date and then DD d d

to say text of this date and then DD d d that's going to take the date and turn

that's going to take the date and turn that into the day of the week name and

that into the day of the week name and you'll see Sundays Mondays Tuesdays like

you'll see Sundays Mondays Tuesdays like that all the days spelled out let's go

that all the days spelled out let's go ahead and make a pivot I'll insert pivot

ahead and make a pivot I'll insert pivot table click okay and again the day of

table click okay and again the day of the week field won't show up here in the

the week field won't show up here in the field list because the pivot has not

field list because the pivot has not been refreshed so I'm going to refresh

been refreshed so I'm going to refresh this and now we will have the day of the

this and now we will have the day of the week field here and I'm going to take

week field here and I'm going to take that and then put that here into the

that and then put that here into the columns we haven't used the columns

columns we haven't used the columns option but it works exactly like rows

option but it works exactly like rows whatever you put in columns will have

whatever you put in columns will have one

one column per value here on the screen so

column per value here on the screen so you'll basically have column A for

you'll basically have column A for Sunday B for Monday like

Sunday B for Monday like that now for each day I want to see how

that now for each day I want to see how many calls we are getting to do that

many calls we are getting to do that we're going to take take the call number

we're going to take take the call number and add that here and then again you'll

and add that here and then again you'll get that kind of values there let's also

get that kind of values there let's also add a representative there so that we

add a representative there so that we can see how many calls each rep is

can see how many calls each rep is getting on each day of the week now when

getting on each day of the week now when you have got lots of numbers like this

you have got lots of numbers like this it's very hard to get the answer which

it's very hard to get the answer which is which days of the week are busiest

is which days of the week are busiest for us so there are a couple of

for us so there are a couple of techniques that I normally use to get

techniques that I normally use to get this one is we could do a percentage

this one is we could do a percentage analysis that is where we will look at a

analysis that is where we will look at a number that is here and then Express

number that is here and then Express that as a percentage of the total calls

that as a percentage of the total calls that that representative is getting so

that that representative is getting so that we could kind of do apples to

that we could kind of do apples to apples comparison so for example you

apples comparison so for example you might think 44 is a lot of calls but all

might think 44 is a lot of calls but all in all they were getting quite a few

in all they were getting quite a few calls as well so maybe they're working

calls as well so maybe they're working as hard as somebody who's getting 28

as hard as somebody who's getting 28 calls but getting less number of calls

calls but getting less number of calls themselves so to fix that a percentage

themselves so to fix that a percentage analysis really helps

analysis really helps and to do this you can just select the

and to do this you can just select the cell right click go to show value as and

cell right click go to show value as and then select percentage of row

then select percentage of row total so that it'll be all percentages

total so that it'll be all percentages and you'll see each row adds up to 100%

and you'll see each row adds up to 100% now that these are percentages we will

now that these are percentages we will be able to see some abnormalities if

be able to see some abnormalities if there are any again when you have got

there are any again when you have got lots of numbers like this it is very

lots of numbers like this it is very hard to spot if there is some extreme

hard to spot if there is some extreme values going on so one more trick that I

values going on so one more trick that I picked up along my way of learning P

picked up along my way of learning P pivot tables is using conditional

pivot tables is using conditional formatting and doing this is really

formatting and doing this is really simple just select any one number and

simple just select any one number and then from home ribbon click on

then from home ribbon click on conditional formatting and I like to use

conditional formatting and I like to use color scale for this kind of a thing so

color scale for this kind of a thing so I'm going to pick color scale and you

I'm going to pick color scale and you can pick a Divergent scale green to red

can pick a Divergent scale green to red or one of these other options let's go

or one of these other options let's go with this one and when you click okay

with this one and when you click okay you'll only see the color in the first

you'll only see the color in the first one but here is the trick you'll also

one but here is the trick you'll also see this little thing there click on it

see this little thing there click on it and select the third option from

here this is going to instantly apply that kind of a formatting for all of

that kind of a formatting for all of these numbers and whichever number has

these numbers and whichever number has most percentage that will be the

most percentage that will be the greenest likewise whichever number has

greenest likewise whichever number has the lowest percentage will be the

the lowest percentage will be the reddest so for example here you can see

reddest so for example here you can see r01 is relatively free on Tuesdays with

r01 is relatively free on Tuesdays with only 7% of their calls coming in but

only 7% of their calls coming in but they're very busy on Saturdays whereas

they're very busy on Saturdays whereas uh for example r02 on the other hand

uh for example r02 on the other hand they are busy on Tuesdays and this guy

they are busy on Tuesdays and this guy here is busy on Thursdays but in general

here is busy on Thursdays but in general we could see for example Thursday is

we could see for example Thursday is kind of lighter workload whereas a more

kind of lighter workload whereas a more green

green appears consistently on Saturday so if I

appears consistently on Saturday so if I were to tell my boss which days are the

were to tell my boss which days are the busiest in our call center I would tell

busiest in our call center I would tell them looks like Saturday Saturday is the

them looks like Saturday Saturday is the busiest day probably that is when most

busiest day probably that is when most of our custom custers are free so they

of our custom custers are free so they pick up the phone and give us a call or

pick up the phone and give us a call or something like

something like that how would you do this analysis let

that how would you do this analysis let me know in the

me know in the comments let's go to the next one which

comments let's go to the next one which is is there a link between call duration

is is there a link between call duration and satisfaction rating so far the first

and satisfaction rating so far the first eight ones are more specific questions

eight ones are more specific questions but now you could see that we are going

but now you could see that we are going into a little bit of vague or

into a little bit of vague or qualitative kind of an area of analysis

qualitative kind of an area of analysis where there is no definite black and

where there is no definite black and white answer for these kind of questions

white answer for these kind of questions but we want to explore and get a feel of

but we want to explore and get a feel of the data so one thing that I like to do

the data so one thing that I like to do is use pivot tables to again get most of

is use pivot tables to again get most of the work done when I'm doing these kind

the work done when I'm doing these kind of questions and then where the pivot

of questions and then where the pivot tables cannot do it then bridge the gap

tables cannot do it then bridge the gap with some formulas so for example here

with some formulas so for example here is there a link between call duration

is there a link between call duration and satisfaction rating if you look at

and satisfaction rating if you look at the data the duration has this kind of a

the data the duration has this kind of a very granular level in information with

very granular level in information with every minute being recorded likewise

every minute being recorded likewise even the satisfaction rating 4.9 2.9

even the satisfaction rating 4.9 2.9 going to the decimal points so in order

going to the decimal points so in order for us to do this analysis effectively

for us to do this analysis effectively what I'm going to do is I'm going to add

what I'm going to do is I'm going to add two more columns one is a duration

two more columns one is a duration bucket and the other one is a

bucket and the other one is a satisfaction rating rounded up so it

satisfaction rating rounded up so it doesn't have these decimal points it's

doesn't have these decimal points it's just one to five

just one to five rating let's do that quickly I'm going

rating let's do that quickly I'm going to insert a few columns

to insert a few columns and in the First Column here duration

and in the First Column here duration bucket and this one is basically looking

bucket and this one is basically looking at the duration here we want to just

at the duration here we want to just categorize this into five buckets it's

categorize this into five buckets it's the same way as how we did earlier with

the same way as how we did earlier with this we have got under 10 minutes 10 to

this we have got under 10 minutes 10 to 30 30 to 60 60 to 2 hours and then 2

30 30 to 60 60 to 2 hours and then 2 hours or more but rather than just doing

hours or more but rather than just doing manual grouping here we are going to use

manual grouping here we are going to use formulas to do that grouping so duration

formulas to do that grouping so duration bucket here we are going to use the ifs

bucket here we are going to use the ifs function in Excel to do this ifs

function in Excel to do this ifs duration is less than or equal to 10

duration is less than or equal to 10 then it'll be under 10

minutes next if duration is less than or equal to

equal to 30 10 to 30 minutes

if it is under 60 30 to 60

means if the duration is less than or equal to 120 then it

equal to 120 then it is 2

is 2 hours else so for else part we just say

hours else so for else part we just say true and then we'll

true and then we'll say more than 2

say more than 2 hours this is going to basically give us

hours this is going to basically give us a bucketing here the beautiful thing

a bucketing here the beautiful thing with this is if and when our data

with this is if and when our data changes or we have new data it

changes or we have new data it automatically again tags the call into

automatically again tags the call into the right bucket whereas as I shown you

the right bucket whereas as I shown you earlier this kind of a grouping doesn't

earlier this kind of a grouping doesn't really hold up when you have a new call

really hold up when you have a new call that is not part of any of the existing

that is not part of any of the existing values so this is a duration bucket uh

values so this is a duration bucket uh if you have data in Excel you can use

if you have data in Excel you can use formulas if you have got data coming in

formulas if you have got data coming in from SQL server or a web page or a

from SQL server or a web page or a SharePoint folder then you can do this

SharePoint folder then you can do this in the power query as well the next one

in the power query as well the next one that we want to do is satisfaction

that we want to do is satisfaction rating again we want to round it up so

rating again we want to round it up so rating rounded and this one is going to

rating rounded and this one is going to be basically

be basically round take the rating value and just

round take the rating value and just round it to zero decimal places so we'll

round it to zero decimal places so we'll have the rating of 0 to 5 uh it's

have the rating of 0 to 5 uh it's surprising that some people have zero

surprising that some people have zero rating but let's just go with that from

rating but let's just go with that from now

now so now that these two columns are there

so now that these two columns are there we want to use that and then kind of

we want to use that and then kind of explore if there is any kind of

explore if there is any kind of interesting patterns emerging when I

interesting patterns emerging when I look at duration and the satisfaction

look at duration and the satisfaction rating to do that we'll just insert a

rating to do that we'll just insert a pivot

pivot table and in this pivot table

table and in this pivot table here we're going to put the duration

here we're going to put the duration bucket and the satisfaction rating into

bucket and the satisfaction rating into the report first we need to refresh it

the report first we need to refresh it so we can see those fields and now I can

so we can see those fields and now I can see the duration bucket and rating

see the duration bucket and rating rounded Fields I'm going to take

rounded Fields I'm going to take duration bucket and put it into rows and

duration bucket and put it into rows and rating rounded into

rating rounded into columns so now that that is there let's

columns so now that that is there let's just see how many calls we are getting

just see how many calls we are getting in each of these combinations and you'll

in each of these combinations and you'll start to see some interesting patterns

start to see some interesting patterns emerging I'm going to expand these

emerging I'm going to expand these things out so that we could kind of

things out so that we could kind of observe this

observe this closely

closely and before we could even read this it

and before we could even read this it kind of looks lopsided that's because if

kind of looks lopsided that's because if you look carefully at the ordering here

you look carefully at the ordering here it kind of shows 1 to 2 hours up top and

it kind of shows 1 to 2 hours up top and then under 10 minutes at the bottom 10

then under 10 minutes at the bottom 10 to 30 30 to 40 and then more than 2

to 30 30 to 40 and then more than 2 hours so it's kind of like jumbled and

hours so it's kind of like jumbled and if you're thinking why is it like that

if you're thinking why is it like that it's because it is sorting this list in

it's because it is sorting this list in alphabetical order while that might work

alphabetical order while that might work for names or cities or something for

for names or cities or something for some ad hoc items like this that's not a

some ad hoc items like this that's not a good idea so here is a trick that you

good idea so here is a trick that you could use to rearrange items in the

could use to rearrange items in the pivot table I want under 10 minutes to

pivot table I want under 10 minutes to show up top so I'll click on under 10

show up top so I'll click on under 10 minutes and while looking at the cell if

minutes and while looking at the cell if you place your cursor on the border of

you place your cursor on the border of the cell any border you'll see that it

the cell any border you'll see that it turns into this black color arrows

turns into this black color arrows cursor click and drag it

cursor click and drag it now likewise this one should be

now likewise this one should be next and then this one and then we have

next and then this one and then we have one to two hours like that so with this

one to two hours like that so with this you'll now start to see this kind kind

you'll now start to see this kind kind of like a very weird triangular pattern

of like a very weird triangular pattern appearing uh and that should tell us a

appearing uh and that should tell us a little bit more of what is happening in

little bit more of what is happening in this data and at this point you can

this data and at this point you can apply conditional formatting to kind of

apply conditional formatting to kind of really bring out the numbers

really bring out the numbers quickly so we're going to select the

quickly so we're going to select the first conditional formatting dark green

first conditional formatting dark green to light green option and then apply it

to light green option and then apply it all over and then you can see what is

all over and then you can see what is happening so The Sweet Spot seem to be

happening so The Sweet Spot seem to be here this area there where if the calls

here this area there where if the calls are between one to two hours then the

are between one to two hours then the customers seem to be kind of rating as

customers seem to be kind of rating as highly there uh a lot of calls are

highly there uh a lot of calls are happening and then that is where the

happening and then that is where the satisfaction seems to Peak but again uh

satisfaction seems to Peak but again uh these are we have very few calls like

these are we have very few calls like under 10 minutes only nine calls so it's

under 10 minutes only nine calls so it's not fair to compare things like that so

not fair to compare things like that so you may want to do a percentage based

you may want to do a percentage based analysis and again uh you could do this

analysis and again uh you could do this by right clicking at a row level and

by right clicking at a row level and then let's take a closer look at

then let's take a closer look at that so when I do this you can see that

that so when I do this you can see that there's no definite pattern as such in

there's no definite pattern as such in general most of the customers are very

general most of the customers are very happy with that that's why we have kind

happy with that that's why we have kind of like more fours and fives very few

of like more fours and fives very few customers here uh and we could kind of

customers here uh and we could kind of see there's no pattern if if anything

see there's no pattern if if anything you know the shorter the calls are uh

you know the shorter the calls are uh the more number of people are satisfied

the more number of people are satisfied and just leaving whereas the longer

and just leaving whereas the longer calls have 40 and 30 there whereas

calls have 40 and 30 there whereas shorter calls have 50 and 55 there so

shorter calls have 50 and 55 there so maybe that could be one conclusion maybe

maybe that could be one conclusion maybe we can tell our agents uh keep it quick

we can tell our agents uh keep it quick keep it to the point so that the

keep it to the point so that the customer can finish the call and maybe

customer can finish the call and maybe go on and do what they have to do um but

go on and do what they have to do um but that is one way of looking at this data

that is one way of looking at this data uh do keep in mind that the numbers and

uh do keep in mind that the numbers and everything here are randomly made up so

everything here are randomly made up so there's actually no real pattern in the

there's actually no real pattern in the data it's just all random noise but you

data it's just all random noise but you could use the pivot tables and

could use the pivot tables and conditional formatting and the value

conditional formatting and the value settings to really dig deep and look at

settings to really dig deep and look at your data like

your data like this and here is a little challenge for

this and here is a little challenge for you if you want you can also create a

you if you want you can also create a scatter graph to explore this data as

scatter graph to explore this data as our data is randomly made up I'm not

our data is randomly made up I'm not bothering with that but you can try it

bothering with that but you can try it out with your numbers to see if there is

out with your numbers to see if there is any kind of exhibited relationships and

any kind of exhibited relationships and if there is then you could do some

if there is then you could do some hypothesis testing to actually find out

hypothesis testing to actually find out if this is true uh and establish that as

if this is true uh and establish that as a case for your

a case for your scenarios let's go to the last one which

scenarios let's go to the last one which is should we hire extra people in any

is should we hire extra people in any specific months this kind of goes back

specific months this kind of goes back to what we did earlier with our monthly

to what we did earlier with our monthly call trend information here you can see

call trend information here you can see that uh this is how it looks and we do

that uh this is how it looks and we do have a couple of Peaks so looking back

have a couple of Peaks so looking back at this I could kind of tell that as we

at this I could kind of tell that as we are getting lots of calls in February to

are getting lots of calls in February to April maybe we should have in the months

April maybe we should have in the months of March April and May someone coming in

of March April and May someone coming in like a temporary staff or someone else

like a temporary staff or someone else from another department could also be

from another department could also be part of the call center team for that

part of the call center team for that months so that they can better handle

months so that they can better handle the workloads but then comes the

the workloads but then comes the question of who should we hire to

question of who should we hire to support so for example do we hire in

support so for example do we hire in general or should we bring in somebody

general or should we bring in somebody to support r03 or r04 so let's do that

to support r03 or r04 so let's do that kind of analysis again I'm going to

kind of analysis again I'm going to insert a pivot

insert a pivot table and here I want to see

table and here I want to see representatives and duration two which

representatives and duration two which is sorry month uh there and then let's

is sorry month uh there and then let's take a look at the

take a look at the calls this is how the the calls are and

calls this is how the the calls are and I want to see which reps have most

I want to see which reps have most workload throughout the year so that

workload throughout the year so that then we could bring in that extra person

then we could bring in that extra person and support them while all the numbers

and support them while all the numbers are there they're kind of hard to spot

are there they're kind of hard to spot and this is again where I would normally

and this is again where I would normally use conditional formatting and set up a

use conditional formatting and set up a data bar and let's extend that and you

data bar and let's extend that and you can quickly see for example there's a

can quickly see for example there's a couple of 40s here for

couple of 40s here for r02 uh so this kind of tells me that

r02 uh so this kind of tells me that maybe r0 two could use a little bit of

maybe r0 two could use a little bit of helps in the month of March and April

helps in the month of March and April when they have almost twice the number

when they have almost twice the number of calls than they normally would so 19

of calls than they normally would so 19 in January and double the number of

in January and double the number of calls in March and April likewise maybe

calls in March and April likewise maybe r03 could use some help here in the

r03 could use some help here in the month of October so if I am going to

month of October so if I am going to decide and get some somebody to help us

decide and get some somebody to help us I would say bring an extra help here for

I would say bring an extra help here for supporting r02 2 and then bring in here

supporting r02 2 and then bring in here to support r03 so that could be one way

to support r03 so that could be one way of looking at it um and again as this

of looking at it um and again as this data is completely randomly made up I

data is completely randomly made up I wouldn't really go and read too much

wouldn't really go and read too much into it but this sort of an analysis is

into it but this sort of an analysis is easy to do once you start using pivot

easy to do once you start using pivot tables and setting up with your

tables and setting up with your data let's conclude all of this with

data let's conclude all of this with couple of homework exercises for you

couple of homework exercises for you here I have listed them the first one is

here I have listed them the first one is time with wasters which customers call

time with wasters which customers call most but buy least do we even have any

most but buy least do we even have any time wasters in our data if so who are

time wasters in our data if so who are they and who are the ones that just call

they and who are the ones that just call us but never really do a lot of

us but never really do a lot of transaction with us and then the second

transaction with us and then the second one is rep training which reps could use

one is rep training which reps could use satisfaction training program so let's

satisfaction training program so let's say you want to look at the satisfaction

say you want to look at the satisfaction ratings at a rep level and then see if

ratings at a rep level and then see if there is anybody who could use a little

there is anybody who could use a little bit of help and this could be done at an

bit of help and this could be done at an overall level or maybe you can see the

overall level or maybe you can see the trend of their satisfaction and if

trend of their satisfaction and if somebody's satisfaction is consistently

somebody's satisfaction is consistently dropping over the period of time maybe

dropping over the period of time maybe that could be a signal for you to say

that could be a signal for you to say that they could use a little bit of

that they could use a little bit of training so that they can take their

training so that they can take their calls better so how would you do these

calls better so how would you do these kind of analysis with pivot tables give

kind of analysis with pivot tables give it a go build that out and tell me in

it a go build that out and tell me in the comments how you will attempt those

the comments how you will attempt those things now that we have covered many of

things now that we have covered many of the powerful and useful Excel Concepts

the powerful and useful Excel Concepts like power query Excel formulas tables

like power query Excel formulas tables Excel pivot tables and Excel charts

Excel pivot tables and Excel charts let's put everything together to come up

let's put everything together to come up with a beautiful interactive portfolio

with a beautiful interactive portfolio project like

project like this this is what we are going to build

this this is what we are going to build it is a call center performance

it is a call center performance dashboard this dashboard combines many

dashboard this dashboard combines many valuable and practical Excel Concepts

valuable and practical Excel Concepts like power pivot slicers interactive

like power pivot slicers interactive charts and conditional

charts and conditional formatting let me do a quick demo of

formatting let me do a quick demo of this this is a dynamic report that shows

this this is a dynamic report that shows me what is happening across our call

me what is happening across our call center right now I have highlighted r01

center right now I have highlighted r01 but I can select a different

but I can select a different representative and I can see how things

representative and I can see how things are for that particular person whoever I

are for that particular person whoever I select I'll also see their picture along

select I'll also see their picture along with a quick summary underneath

with a quick summary underneath you can also multi- select items and

you can also multi- select items and understand how things are happening for

understand how things are happening for multiple

multiple Representatives whatever you select you

Representatives whatever you select you will see up top here the overall calls

will see up top here the overall calls and the selected calls underneath so for

and the selected calls underneath so for example out of th000 calls 64 came from

example out of th000 calls 64 came from r01 2 and 3 whichever representative I

r01 2 and 3 whichever representative I select they are clearly highlighted in

select they are clearly highlighted in the calls and amounts graph as well as

the calls and amounts graph as well as their column is highlighted in the

their column is highlighted in the customer table here by region this is a

customer table here by region this is a super helpful and really simple

super helpful and really simple portfolio project that showcases what

portfolio project that showcases what you capable of building using

you capable of building using Excel so let's go and build this from

Excel so let's go and build this from scratch using the blank

workbook I have provided you a copy of this blank workbook as well as the

this blank workbook as well as the completed file and use this blank

completed file and use this blank workbook to build this whole whole thing

workbook to build this whole whole thing on your own let's start by doing a quick

on your own let's start by doing a quick review of this data it is a call center

review of this data it is a call center data we have got call number in the

data we have got call number in the First Column who is the customer how

First Column who is the customer how long they spoke with us who is the

long they spoke with us who is the representative that took the call the

representative that took the call the date purchase amount of that call what

date purchase amount of that call what is the satisfaction rating and some of

is the satisfaction rating and some of these other columns that we calculated

these other columns that we calculated in the previous lessons these are simple

in the previous lessons these are simple formulas that are used to calculate

formulas that are used to calculate Financial year day of the week duration

Financial year day of the week duration bucketing and rounded value of the

bucketing and rounded value of the rating apart from this data I have also

rating apart from this data I have also added another data set here which is my

added another data set here which is my customer data here for each customer I

customer data here for each customer I know what is their gender how old they

know what is their gender how old they are and what city they are from as you

are and what city they are from as you can see here we operate out of three

can see here we operate out of three different cities all our customers are

different cities all our customers are from Columbus Cincinnati and Cleveland

from Columbus Cincinnati and Cleveland the first thing that I want to explain

the first thing that I want to explain is the assets tab this is where I have

is the assets tab this is where I have put some icons and images that we are

put some icons and images that we are going to use later on to prettify our

going to use later on to prettify our dashboard these images are downloaded

dashboard these images are downloaded from the stock image option of excel so

from the stock image option of excel so these are not real people images uh

these are not real people images uh they're just stock

they're just stock pictures the first thing that we are

pictures the first thing that we are going to do is set up a color theme and

going to do is set up a color theme and font choice for our dashboard this is

font choice for our dashboard this is really important and an early step that

really important and an early step that you should do so that everything else

you should do so that everything else becomes easier later on

becomes easier later on for this I'm going to go into the page

for this I'm going to go into the page layout ribbon and choose the colors and

layout ribbon and choose the colors and from the default office theme here I'm

from the default office theme here I'm going to select the slip stream option

going to select the slip stream option underneath and you'll see that as soon

underneath and you'll see that as soon as I select all the colors change in my

as I select all the colors change in my tables as well this is fine and for the

tables as well this is fine and for the fonts I'm going to select the choice of

fonts I'm going to select the choice of Aptos extra bold and Aptos these are new

Aptos extra bold and Aptos these are new fonts that Microsoft added to excel 365

fonts that Microsoft added to excel 365 recently if you don't have them just use

recently if you don't have them just use any fonts that is still

any fonts that is still fine you can customize the fonts by

fine you can customize the fonts by using this button here customize the

using this button here customize the fonts and select the heading font as

fonts and select the heading font as well as the body font this way you don't

well as the body font this way you don't have to make individual choices when

have to make individual choices when you're are setting up titles or body

you're are setting up titles or body text in the dashboard you just use this

text in the dashboard you just use this and that automatically trickles down

and that automatically trickles down into the report so now that the color

into the report so now that the color scheme and font choices are made let's

scheme and font choices are made let's add a tab and this tab is going to be

add a tab and this tab is going to be our Customer Center report in here I'll

our Customer Center report in here I'll make the first two columns narrow and

make the first two columns narrow and I'm going to select a big G of range

I'm going to select a big G of range let's just select up to this and we are

let's just select up to this and we are going to fill up a dull gray color like

going to fill up a dull gray color like this this is where our report is going

this this is where our report is going to set and now in order to calculate all

to set and now in order to calculate all of the items in the report we need to

of the items in the report we need to set up a bunch of pivot tables so we'll

set up a bunch of pivot tables so we'll go here to the data Tab and select the

go here to the data Tab and select the calls table you you can select just any

calls table you you can select just any one cell and go to insert and click on

one cell and go to insert and click on the pivot table that's right all the

the pivot table that's right all the calculations and Analysis for our

calculations and Analysis for our dashboard is going to be generated

dashboard is going to be generated through the pivot tables and we're going

through the pivot tables and we're going to use a very minimal amount of formulas

to use a very minimal amount of formulas to drive the process so we'll click on

to drive the process so we'll click on the pivot table and we are going to make

the pivot table and we are going to make one important change at this point we

one important change at this point we are going to say add this data to the

are going to say add this data to the data model previously we have not used

data model previously we have not used this feature but this feature helps us

this feature but this feature helps us take this call data and combine it with

take this call data and combine it with the customer data that is further on the

the customer data that is further on the screen so let's select this and we're

screen so let's select this and we're going to add it to a new

going to add it to a new worksheet

worksheet okay this is going to introduce a new

okay this is going to introduce a new sheet I'm going to rename this as pivots

sheet I'm going to rename this as pivots and here we are going to set up all the

and here we are going to set up all the calculations before we do any

calculations before we do any calculations we do need to combine this

calculations we do need to combine this data with the customer data that is here

data with the customer data that is here so if you notice this table is called

so if you notice this table is called customers and this table is called calls

customers and this table is called calls you can see the name here in the corner

you can see the name here in the corner and what we will do is we'll combine

and what we will do is we'll combine these two tables on the customer ID

these two tables on the customer ID column so you can do this from the pivot

column so you can do this from the pivot table itself you can go to the analyze

table itself you can go to the analyze ribbon and click on the

ribbon and click on the relationships and make a new

relationships and make a new relationship between the call stable

relationship between the call stable customer ID and customer stable customer

customer ID and customer stable customer ID and when you close this screen you

ID and when you close this screen you have now connected both both of these

have now connected both both of these tables and you can take the data from

tables and you can take the data from either table and see that in the pivot

either table and see that in the pivot report the process of using pivot tables

report the process of using pivot tables is same whether you are working off one

is same whether you are working off one table or multiple tables but once you

table or multiple tables but once you have two tables you can see that the

have two tables you can see that the field list here changes and now you have

field list here changes and now you have active and all tables option if you go

active and all tables option if you go to the all

to the all tables you'll see that both of your

tables you'll see that both of your tables are listed for you here so you

tables are listed for you here so you can take data some data from calls and

can take data some data from calls and some data from customers and combine

some data from customers and combine that to generate a complex Business

that to generate a complex Business Report so that's what we are going to do

Report so that's what we are going to do for our dashboard we need a overall

for our dashboard we need a overall summary of the business what is

summary of the business what is happening across the board so that's the

happening across the board so that's the first pivot that we are going to make

first pivot that we are going to make you can expand the calls table and

you can expand the calls table and you'll see that most of the information

you'll see that most of the information is here rather than dragging the items

is here rather than dragging the items into the values directly so for example

into the values directly so for example if I want to see the total amount I can

if I want to see the total amount I can select the purchase amount and put it

select the purchase amount and put it into values and then I can see what is

into values and then I can see what is that amount here directly this is how we

that amount here directly this is how we have done pivot tables in the previous

have done pivot tables in the previous video this time I'm going to show you a

video this time I'm going to show you a different technique which involves using

different technique which involves using the power pivot and Dax features of

the power pivot and Dax features of excel so I'll take this out and right

excel so I'll take this out and right click on the calls table here and you'll

click on the calls table here and you'll see that there is now an add measure

see that there is now an add measure option this is one of those additional

option this is one of those additional features that get activated when you are

features that get activated when you are using relationship ships and data model

using relationship ships and data model feature of excel pivot tables that is

feature of excel pivot tables that is what when we were inserting the pivot

what when we were inserting the pivot table by clicking this checkbox we are

table by clicking this checkbox we are enabling so let's add this measure and

enabling so let's add this measure and this measure I'm going to call this

this measure I'm going to call this as call count and here we can use a

as call count and here we can use a formula count rows of the calls table to

formula count rows of the calls table to count how many rows are there as each

count how many rows are there as each row is one call it just tells us how

row is one call it just tells us how many total calls are there this language

many total calls are there this language here is the Dax language data analysis

here is the Dax language data analysis Expressions language and using this you

Expressions language and using this you can build calculations on top of your

can build calculations on top of your data and then see those calculations

data and then see those calculations inside pivot tables another advantage of

inside pivot tables another advantage of using this kind of an notation is that

using this kind of an notation is that while creating this itself you can tell

while creating this itself you can tell how the number should be

how the number should be formatted so you can set this to number

formatted so you can set this to number say that it should be a whole number

say that it should be a whole number with thousand separator and click

with thousand separator and click okay you'll see that this will add a

okay you'll see that this will add a call count measure to the table this is

call count measure to the table this is not like a physical column in the table

not like a physical column in the table this is a calculation on top of the

this is a calculation on top of the table so now if I select this I'll see

table so now if I select this I'll see the call count as 1,000 but you'll also

the call count as 1,000 but you'll also see that it is having that number

see that it is having that number formatting applied as well let's

formatting applied as well let's calculate the total amount as well add

calculate the total amount as well add measure and this time we're going to use

measure and this time we're going to use the sum function sum of

the sum function sum of calls table purchase amount

calls table purchase amount column and this we're going to set it as

column and this we're going to set it as currency we don't need any decimal

currency we don't need any decimal points on that and click okay and again

points on that and click okay and again you'll have the total amount you can add

you'll have the total amount you can add that to see

that to see [Music]

[Music] 96623 next up we want to calculate the

96623 next up we want to calculate the total

total duration and this is some of the call

duration and this is some of the call stable duration column

we'll do two more measures one is the average

average rating and use the average of

rating and use the average of calls satisfaction rating

column and we can see that here I'm going to add one more measure and this

going to add one more measure and this is the number of happy callers or how

is the number of happy callers or how many calls have five star

many calls have five star rating so this is nothing but if you

rating so this is nothing but if you look at the data every call has a

look at the data every call has a satisfaction rating and we have rounded

satisfaction rating and we have rounded that to a six point scale from 0 to 5

that to a six point scale from 0 to 5 and I just want to count how many fives

and I just want to count how many fives are there in this data so in a way this

are there in this data so in a way this is nothing but counting the calls where

is nothing but counting the calls where the rating rounded is five so let's add

the rating rounded is five so let's add this measure and we'll call this as five

this measure and we'll call this as five star

star calls and here we can use a special

calls and here we can use a special function called

function called calculate call count

calculate call count comma rating

comma rating rounded is equal to

rounded is equal to 5 what this does is it calculates the

5 what this does is it calculates the call count by adding an extra filter

call count by adding an extra filter context by saying that get me the count

context by saying that get me the count of calls only if the rating grounded is

of calls only if the rating grounded is five so that's what calculate does it

five so that's what calculate does it kind of Alters the calculation logic by

kind of Alters the calculation logic by introducing extra filters through these

introducing extra filters through these options

options at the end of

at the end of it let's do this as a number as

it let's do this as a number as well and add that and let's add that and

well and add that and let's add that and you can see that out of 1,000 calls 307

you can see that out of 1,000 calls 307 are five star calls or happy callers so

are five star calls or happy callers so this is my overall business summary and

this is my overall business summary and it tells me what is happening across the

it tells me what is happening across the board so I'll go to the pivot table

board so I'll go to the pivot table analyze ribbon and you'll see that this

analyze ribbon and you'll see that this pivot table is named pivot table one

pivot table is named pivot table one here I'm going to rename this as summary

here I'm going to rename this as summary pivot you can just select here and do

pivot you can just select here and do that and I'll copy this pivot crl C and

that and I'll copy this pivot crl C and paste it once again and this time I'll

paste it once again and this time I'll name this as rep pivot the purpose of

name this as rep pivot the purpose of this second pivot is I want to see what

this second pivot is I want to see what those numbers are if I select a specific

those numbers are if I select a specific representative for that purpose I'm

representative for that purpose I'm going to expand the calls table and

going to expand the calls table and while activating the second pivot right

while activating the second pivot right click on the representative and add it

click on the representative and add it as a

as a slicer so now I have got a slicer and

slicer so now I have got a slicer and this slicer is only linked to this pivot

this slicer is only linked to this pivot table so it's not linked to the main one

table so it's not linked to the main one just the rep one you can test this by

just the rep one you can test this by selecting r02 and you'll see that only

selecting r02 and you'll see that only these numbers change so now I have got

these numbers change so now I have got an overall summary and whatever rep I

an overall summary and whatever rep I select I know what the numbers are for

select I know what the numbers are for that person as well we can take all of

that person as well we can take all of these numbers and plug them into the

these numbers and plug them into the dashboard now so let's go here and start

dashboard now so let's go here and start putting together our report for our

putting together our report for our report we are going to use some shapes

report we are going to use some shapes to lay down the important numbers we

to lay down the important numbers we leave first few rows blank so that we

leave first few rows blank so that we can fill that up with useful headers and

can fill that up with useful headers and other stuff later on so I'll go to for

other stuff later on so I'll go to for example row number four here and insert

example row number four here and insert a rectangle

a rectangle shape and you can move this around if

shape and you can move this around if you hold down the ALT key on your

you hold down the ALT key on your keyboard it snaps to the cell borders

keyboard it snaps to the cell borders around there

around there so let's make it uh that big and I'm

so let's make it uh that big and I'm going to fill up with this nice brick

going to fill up with this nice brick color here let's take out the outline

color here let's take out the outline from this and let's go to the shape

from this and let's go to the shape effects Shadow and add a drop shadow

effects Shadow and add a drop shadow effect you can press control one to open

effect you can press control one to open the format shape and from here enhance

the format shape and from here enhance the shadow a bit I'm going to go with

the shadow a bit I'm going to go with 50% and blur it by 20

50% and blur it by 20 points and make it 10 points distance so

points and make it 10 points distance so you'll get this kind of a look here and

you'll get this kind of a look here and that's going to be our tile that shows

that's going to be our tile that shows how many calls are there and once we set

how many calls are there and once we set it up we can kind of copy paste this for

it up we can kind of copy paste this for rest of the things so in this I'll

rest of the things so in this I'll select this and then select the formula

select this and then select the formula bar here and then say equal to this is

bar here and then say equal to this is how I can tell Excel that I want to have

how I can tell Excel that I want to have in this a value from my Pivot so once

in this a value from my Pivot so once you click on the formula bar and then

you click on the formula bar and then say equal to you can navigate to the

say equal to you can navigate to the pivots and then point to the 1,000 value

pivots and then point to the 1,000 value make sure that when you do this it says

make sure that when you do this it says pivot stable and A4 or something if it

pivot stable and A4 or something if it is saying get pivot data I'll tell you

is saying get pivot data I'll tell you how to fix that in a second when you hit

how to fix that in a second when you hit enter you'll see that that 1,000 comes

enter you'll see that that 1,000 comes through here now like I was saying there

through here now like I was saying there is a problem with many pivot Tables by

is a problem with many pivot Tables by default when you set up a pivot table

default when you set up a pivot table Excel has an option here in the pivot

Excel has an option here in the pivot table options if you expand the options

table options if you expand the options you'll see that this generate get pivot

you'll see that this generate get pivot data is checked by default what it means

data is checked by default what it means is I'll show you what that

is I'll show you what that does I'll enable this and let's go back

does I'll enable this and let's go back here and this time let's say equal to

here and this time let's say equal to pivots and point to it so now you can

pivots and point to it so now you can see that when I point it's not pointing

see that when I point it's not pointing to the A4 cell it's actually giving me a

to the A4 cell it's actually giving me a get pivot data function that generates

get pivot data function that generates the value this is fine it's just that

the value this is fine it's just that the boxes don't like get P data so

the boxes don't like get P data so you'll get a error ER like this so this

you'll get a error ER like this so this is why you need to disable that to

is why you need to disable that to disable this you can just go to the

disable this you can just go to the options and uncheck generate get pivot

options and uncheck generate get pivot data this is a onetime step once you do

data this is a onetime step once you do it for this pivot it's kind of turned

it for this pivot it's kind of turned off across the board so you don't have

off across the board so you don't have to worry about this going

to worry about this going onone so now that that is done you can

onone so now that that is done you can see that this is actually showing me A4

see that this is actually showing me A4 value I'm going to Center align

value I'm going to Center align this and let's select our heading font

this and let's select our heading font which is Aptos extra bold

which is Aptos extra bold and make it like that and change it to

and make it like that and change it to white color so it kind of easy to read

white color so it kind of easy to read this so that is 1,000 calls and we also

this so that is 1,000 calls and we also want to say that information calls up

want to say that information calls up top so for this I can insert a text box

top so for this I can insert a text box I'm going to draw a small text box up

I'm going to draw a small text box up top and move it and in this text box I'm

top and move it and in this text box I'm going to type the word calls again we

going to type the word calls again we using the shape format to fill it up

using the shape format to fill it up with no outline no fill

with no outline no fill and we'll make it a dull shade of

and we'll make it a dull shade of white and I think we can go with the

white and I think we can go with the same extra bold option for that as well

same extra bold option for that as well so that is the calls that is the number

so that is the calls that is the number let's also bring in the icon we have got

let's also bring in the icon we have got some icons in the assets tab here this

some icons in the assets tab here this teleone icon is perfect icon for calls

teleone icon is perfect icon for calls so I'm going to copy this go here and

so I'm going to copy this go here and paste it and move it I'm just going to

paste it and move it I'm just going to rotate it a little bit and size it small

rotate it a little bit and size it small enough so that it can kind of goes into

enough so that it can kind of goes into the corner and finally using the

the corner and finally using the graphics format I'm going to format it

graphics format I'm going to format it in a dull color of the same background

in a dull color of the same background color as that box so that we can see the

color as that box so that we can see the calls here that icon and then the number

calls here that icon and then the number now underneath this number I want to

now underneath this number I want to show the number of calls that

show the number of calls that corresponds to the representative that

corresponds to the representative that we have selected remember here I've have

we have selected remember here I've have got a slicer and we can see this 28 so

got a slicer and we can see this 28 so let's add one more text box and this

let's add one more text box and this text box goes under here here and this

text box goes under here here and this text box is equal

text box is equal to this

to this value again we're going to Center align

value again we're going to Center align these

these values no outline no

values no outline no fill and just change it to a color like

fill and just change it to a color like that so that looks good you may want to

that so that looks good you may want to adjust the alignment of these things to

adjust the alignment of these things to suit your needs but once you have one of

suit your needs but once you have one of them ready you can copy all of these and

them ready you can copy all of these and paste it five times for the remaining

paste it five times for the remaining bits so let's do that quickly to select

bits so let's do that quickly to select all of these items in one go you can

all of these items in one go you can kind of hold shift and select them but

kind of hold shift and select them but this is a bit annoying so here is a

this is a bit annoying so here is a trick that I normally use when building

trick that I normally use when building dashboards I go to the home ribbon and

dashboards I go to the home ribbon and look at find and select and here is a

look at find and select and here is a tool called select objects when you

tool called select objects when you click on that your mouse cursor changes

click on that your mouse cursor changes to a regular pointer and you can kind of

to a regular pointer and you can kind of drag around the whole thing to select

drag around the whole thing to select all of the items so once you select all

all of the items so once you select all of them you can control C C to copy and

of them you can control C C to copy and press escape and control V to

paste to get all the five boxes the next job is to change all of

boxes the next job is to change all of these words this is really simple you

these words this is really simple you can just start typing where the words

can just start typing where the words are typed and where the values are from

are typed and where the values are from a this becomes

a this becomes B4 don't worry about the formatting we

B4 don't worry about the formatting we can fix that later

so now that all the values are there let's also change these icons this is a

let's also change these icons this is a really simple process you can go to the

really simple process you can go to the assets tab select the second icon so for

assets tab select the second icon so for example for amount this is the icon I'm

example for amount this is the icon I'm going to contr C to copy that come here

going to contr C to copy that come here right click on this icon change graphic

right click on this icon change graphic from clipboard so that's going to

from clipboard so that's going to replace that we'll need adjust the color

replace that we'll need adjust the color we'll do that in a second let's do this

we'll do that in a second let's do this for duration as well and let's adjust

for duration as well and let's adjust these colors

these colors quickly I'm going to fix the rotation as

quickly I'm going to fix the rotation as well for some of them we don't need the

well for some of them we don't need the rotation for

rotation for everything and to fix the font

everything and to fix the font issues you can select the first box

issues you can select the first box which has the correct formatting double

which has the correct formatting double click on the format painter and just

click on the format painter and just select all of these

select all of these items now when I do that I can see that

items now when I do that I can see that some of the numbers are getting cropped

some of the numbers are getting cropped popped out so maybe these needs to be a

popped out so maybe these needs to be a little bit wider or the font needs to be

little bit wider or the font needs to be smaller I'm going to go with the second

smaller I'm going to go with the second option which is reducing the font

option which is reducing the font size and let's make this 24 points

size and let's make this 24 points that'll do and for the bottom ones again

that'll do and for the bottom ones again we can just select them and either use

we can just select them and either use format painter or change the color from

format painter or change the color from here so that makes the first part of the

here so that makes the first part of the dashboard ready Let's test this out I'm

dashboard ready Let's test this out I'm going to go into the and cut this slicer

going to go into the and cut this slicer contr

contr X place it here you might get a warning

X place it here you might get a warning this is

this is fine and now let's test this out if I

fine and now let's test this out if I select a a different representative I

select a a different representative I can see all of these numbers

can see all of these numbers change I can also multi select by

change I can also multi select by clicking and dragging or I can use

clicking and dragging or I can use control and select different

control and select different Representatives whoever I select I'll

Representatives whoever I select I'll see their number of calls amounts

see their number of calls amounts durations ratings and how many happy

durations ratings and how many happy callers they had

callers they had let's go ahead and build some of these

let's go ahead and build some of these graphs now just to give you a reminder

graphs now just to give you a reminder this is what we are trying to build so

this is what we are trying to build so as you can see here we have got some

as you can see here we have got some Trend graphs that tell me how many calls

Trend graphs that tell me how many calls have come through over the course of

have come through over the course of 2023 and a breakdown of the pattern on

2023 and a breakdown of the pattern on weekdays so let's first create this but

weekdays so let's first create this but keep in mind that all of these graphs

keep in mind that all of these graphs are interacting with the slicer that is

are interacting with the slicer that is here so we'll need to set up the slicer

here so we'll need to set up the slicer con connectivity and set up all the

con connectivity and set up all the customizations as needed I'm going to go

customizations as needed I'm going to go to the pivots here and let's create

to the pivots here and let's create another pivot this pivot needs to

another pivot this pivot needs to interact with the slicer and tell us how

interact with the slicer and tell us how calls have come through over the course

calls have come through over the course of year so instead of creating a brand

of year so instead of creating a brand new pivot from the data and then linking

new pivot from the data and then linking it to the slicer we can copy an existing

it to the slicer we can copy an existing pivot this pivot for example is

pivot this pivot for example is interacting with the slicer and I'm

interacting with the slicer and I'm going to contrl C to copy and paste it

going to contrl C to copy and paste it here control V and now we can just

here control V and now we can just change the items in this pivot so that

change the items in this pivot so that whatever new items you put they will all

whatever new items you put they will all be still connected to the slicer so

be still connected to the slicer so let's go to the pivot Fields here and

let's go to the pivot Fields here and I'm going to take out everything and in

I'm going to take out everything and in this pivot we would like to see what is

this pivot we would like to see what is happening by the call

happening by the call date so we'll take the date of call and

date so we'll take the date of call and put it into the rows area the first time

put it into the rows area the first time you put a date into the pivot table

you put a date into the pivot table Fields it's automatically going to group

Fields it's automatically going to group the date by month or quarter or year as

the date by month or quarter or year as well depending on how much data you have

well depending on how much data you have in this case the data is only for one

in this case the data is only for one year so it automatically grouped that by

year so it automatically grouped that by month and within the month you have got

month and within the month you have got individual days this is perfect I can

individual days this is perfect I can just see this information as it is and

just see this information as it is and for each date I want to see how many

for each date I want to see how many calls have come through so we're going

calls have come through so we're going to take our call count measure that we

to take our call count measure that we created earlier and put that into the

created earlier and put that into the values

values and we will get the call count notice

and we will get the call count notice that this adds up to 575 which is what

that this adds up to 575 which is what this number is because in our report we

this number is because in our report we are currently highlighting R1 R4 and R5

are currently highlighting R1 R4 and R5 for example if I were to select now just

for example if I were to select now just R2 alone you'll see that 218 is the

R2 alone you'll see that 218 is the calls and in my Pivot the date breakdown

calls and in my Pivot the date breakdown will also be for that

will also be for that 218 so let's create a graph from this uh

218 so let's create a graph from this uh to make a pivot graph you can just

to make a pivot graph you can just select any one set and then go to insert

select any one set and then go to insert and I'm going to insert a line chart

and I'm going to insert a line chart with

with markers so we'll get this now for the

markers so we'll get this now for the purpose of our dashboard what I want is

purpose of our dashboard what I want is I don't want just a line but I also want

I don't want just a line but I also want to have a shaded area

to have a shaded area underneath to get this we're going to

underneath to get this we're going to use a simple trick on the line chart

use a simple trick on the line chart we'll move this line chart here and in

we'll move this line chart here and in this pivot I'm going to add call count

this pivot I'm going to add call count again so it's going to have two call

again so it's going to have two call count values and when you update the

count values and when you update the pivot this chart will also update now it

pivot this chart will also update now it has two lines it's just that the lines

has two lines it's just that the lines overlap so we can't really see them you

overlap so we can't really see them you can see that you know when I move around

can see that you know when I move around it will show me call count and call

it will show me call count and call count two both of these lines perfectly

count two both of these lines perfectly overlapping now I'll right click on the

overlapping now I'll right click on the line and go to change series chart

line and go to change series chart type and for the second call count I'm

type and for the second call count I'm going to change that to an area so we

going to change that to an area so we now have a area area along with the line

now have a area area along with the line as the chart this is what I wanted I'm

as the chart this is what I wanted I'm going to cut this chartr x go to the

going to cut this chartr x go to the dashboard and paste it here let's make

dashboard and paste it here let's make this column narrow enough and that is my

this column narrow enough and that is my chart uh my slicer is behind I'm going

chart uh my slicer is behind I'm going to move the slicer

to move the slicer there and again you can hold on the ALT

there and again you can hold on the ALT key to get perfect

key to get perfect alignment and once this is done let's

alignment and once this is done let's just adjust the height and select this

just adjust the height and select this chart let's get rid of some of the chart

chart let's get rid of some of the chart junk that is there for example we don't

junk that is there for example we don't need any of these things all of these

need any of these things all of these are useless likewise this Legend is also

are useless likewise this Legend is also useless we had to have two of them to

useless we had to have two of them to get both line and area but we don't need

get both line and area but we don't need that and we also don't need this plus

that and we also don't need this plus minus

minus buttons so to take out all of these

buttons so to take out all of these things select the pivot chart and go to

things select the pivot chart and go to Pivot analyze and turn off the field

Pivot analyze and turn off the field buttons from the graph this is going to

buttons from the graph this is going to take out all of these buttons

take out all of these buttons out of the graph so now that looks clean

out of the graph so now that looks clean and using this plus button I'm going to

and using this plus button I'm going to uncheck Legend So that's gone as well

uncheck Legend So that's gone as well and let's add a chart title

and let's add a chart title and click on that let's move the chart

and click on that let's move the chart title here we're going to add the chart

title here we're going to add the chart title in a minute but first let's abize

title in a minute but first let's abize some

some formatting so I'm going to select this

formatting so I'm going to select this area and press control1 to open the

area and press control1 to open the format data series options here I'll

format data series options here I'll move this here so we can actually see

move this here so we can actually see everything

everything together and let's select this area go

together and let's select this area go to the fill options and select gradient

to the fill options and select gradient fill for my calls I'm going to use the

fill for my calls I'm going to use the same brick color that we are using for

same brick color that we are using for these boxes so here in this gradient we

these boxes so here in this gradient we clean it up and select only two color

clean it up and select only two color gradient and the first color would be

gradient and the first color would be something like that and the second color

something like that and the second color is the darker version and I'm going to

is the darker version and I'm going to flip the this so that the darker color

flip the this so that the darker color is up top and for the bottom one we're

is up top and for the bottom one we're going to make it 50% transparent so you

going to make it 50% transparent so you can kind of get like that sort of a

can kind of get like that sort of a faded look and once that is done let's

faded look and once that is done let's select these lines and for the line I'm

select these lines and for the line I'm going to change the line color to the

going to change the line color to the same brick color

same brick color and switch to the marker options the

and switch to the marker options the marker is circle I'll make it uh 7even

marker is circle I'll make it uh 7even points and and fill the

points and and fill the marker with the white color and for the

marker with the white color and for the marker

marker border I'm going to set it to solid line

border I'm going to set it to solid line and set this dark and let's make it two

and set this dark and let's make it two points what we are going for is this

points what we are going for is this sort of a look the markers should stand

sort of a look the markers should stand out with white color inside them and

out with white color inside them and then the black color as the marker and

then the black color as the marker and now we can see that and let's test this

now we can see that and let's test this out if I select a different presentative

out if I select a different presentative I should see their call patterns and

I should see their call patterns and when I clear the slicer I can see the

when I clear the slicer I can see the overall picture as well that works

overall picture as well that works beautifully now let's add another chart

beautifully now let's add another chart here that tells me what is the weekly

here that tells me what is the weekly Trend we'll need to add the title as

Trend we'll need to add the title as well we'll do that at the end so let's

well we'll do that at the end so let's go back here and let's copy this and

go back here and let's copy this and paste once

paste once again here and this time we're going to

again here and this time we're going to take out these

take out these things in the call counts as well so

things in the call counts as well so that we we have an empty pivot table and

that we we have an empty pivot table and in this pivot expand the calls table and

in this pivot expand the calls table and select the day of week field this has

select the day of week field this has all the days of week and let's add the

all the days of week and let's add the call count now let's add

call count now let's add insert a bar chart and we'll get this

insert a bar chart and we'll get this bar

bar chart make it small so we can actually

chart make it small so we can actually see everything together and you can see

see everything together and you can see the problem here there's a problems one

the problem here there's a problems one is this week days are kind of out of

is this week days are kind of out of order if you're wondering what is going

order if you're wondering what is going on here they're actually in the

on here they're actually in the alphabetical order of reverse so w is up

alphabetical order of reverse so w is up top and F the first letter is at the

top and F the first letter is at the bottom so we don't want the alphabetical

bottom so we don't want the alphabetical order so we can go here and if you set

order so we can go here and if you set this to A to Z you'll see that it now

this to A to Z you'll see that it now sets it from Sunday to Saturday this is

sets it from Sunday to Saturday this is perfect for us but in the chart it still

perfect for us but in the chart it still comes wrong it has Saturday up top

comes wrong it has Saturday up top Sunday at the bottom so to fix the

Sunday at the bottom so to fix the problem with the chart you can select

problem with the chart you can select this axis and control one to open the

this axis and control one to open the formatting and from here use the

formatting and from here use the categories in reverse order what this

categories in reverse order what this does is it takes these items here and it

does is it takes these items here and it kind of flips them around so whatever is

kind of flips them around so whatever is at the bottom that will go to the top

at the bottom that will go to the top and whatever is at the top that will

and whatever is at the top that will come to the bottom so let's just do that

come to the bottom so let's just do that and we'll now have the correct option at

and we'll now have the correct option at this point the chart is ready I'm going

this point the chart is ready I'm going to cut ITR X and paste it here in the

to cut ITR X and paste it here in the report now for this again we're going to

report now for this again we're going to follow the same thing we're going to

follow the same thing we're going to disable the field buttons and the legend

disable the field buttons and the legend as well as access and titles and grid

as well as access and titles and grid lines as well and select these lines

lines as well and select these lines control on to open the formatting

control on to open the formatting options and from the format here I'm

options and from the format here I'm going to adjust the Gap width to

going to adjust the Gap width to 25% so that the lines are nice and thick

25% so that the lines are nice and thick and we'll select these uh colums or bars

and we'll select these uh colums or bars and go to the format and fill it with a

and go to the format and fill it with a solid color that is kind of like

solid color that is kind of like that and let's add data label now the

that and let's add data label now the label comes outside let's uh change the

label comes outside let's uh change the label color okay that color is fine and

label color okay that color is fine and let's move this right next to this

and kind of adjust the size I forgot to put the horizontal or

size I forgot to put the horizontal or vertical axis I'm just going to turn

vertical axis I'm just going to turn that

on yeah that's perfect and let's move this slightly inside like

and let's move this slightly inside like that and while keeping the chart

that and while keeping the chart selected go to the design or format and

selected go to the design or format and send it to the back so it kind of Peaks

send it to the back so it kind of Peaks behind this chart so there is our Trend

behind this chart so there is our Trend and the weekly Trend as well for any

and the weekly Trend as well for any representative right now we have not

representative right now we have not selected anyone so it's showing me all

selected anyone so it's showing me all the 1,000 calls but if I select r02 or

the 1,000 calls but if I select r02 or r03 I can see what is happening at an

r03 I can see what is happening at an individual weekday level as well as the

individual weekday level as well as the overall time period let's add the title

now let's compare with the final look in the final workbook I think I went with a

the final workbook I think I went with a orange color rather than the brick color

orange color rather than the brick color but other than that everything looks

but other than that everything looks good next we want to create this

good next we want to create this particular one this is showing two

particular one this is showing two graphs one is the calls one and another

graphs one is the calls one and another one is amount one along with whatever

one is amount one along with whatever rep that you have currently selected is

rep that you have currently selected is highlighted in a different color

highlighted in a different color underneath this we also want to show

underneath this we also want to show what is happening for the currently

what is happening for the currently selected rip the their picture and some

selected rip the their picture and some information about this so we're going to

information about this so we're going to do this in two chunks first one is we

do this in two chunks first one is we will finish this graph and then towards

will finish this graph and then towards the end of the process we are going to

the end of the process we are going to add these picture and other things let's

add these picture and other things let's go for this we need more pivots and

go for this we need more pivots and let's come here and add those pivots

let's come here and add those pivots before we add them let's give these

before we add them let's give these pivots some names so I'll select one of

pivots some names so I'll select one of these cells go to analyze ribbon and

these cells go to analyze ribbon and pivot table three this we're going to

pivot table three this we're going to name this as monthly trend

name this as monthly trend and this one is weekday Trend let's

and this one is weekday Trend let's create more pivots this time I'm going

create more pivots this time I'm going to create a pivot from scratch just so

to create a pivot from scratch just so that you can understand the process of

that you can understand the process of how to link the slicer after you have

how to link the slicer after you have set up the pivot table so we'll add a

set up the pivot table so we'll add a pivot table here uh you can select a

pivot table here uh you can select a blank cell and go to insert pivot table

blank cell and go to insert pivot table and use the third option from data model

and use the third option from data model option but if you don't see that because

option but if you don't see that because you're running an older version of excel

you're running an older version of excel or whatever you could also go back to

or whatever you could also go back to the data select pivot table and make

the data select pivot table and make sure that you check this and give the

sure that you check this and give the location as the cell in the pivots page

location as the cell in the pivots page so let's do it like that existing and

so let's do it like that existing and I'm going to point

I'm going to point to this cell here this is going to add

to this cell here this is going to add one more pivot but it will still be

one more pivot but it will still be connected to the same date model uh but

connected to the same date model uh but not to the slicers that we will have to

not to the slicers that we will have to do it later make a note of this pivot

do it later make a note of this pivot it's saying pivot table 5 I want to call

it's saying pivot table 5 I want to call this as reps pivot and here we want to

this as reps pivot and here we want to see the representative and for each

see the representative and for each representative I want to know what is

representative I want to know what is the call count and what is the total

the call count and what is the total amount so we'll get this information

amount so we'll get this information unfortunately we can't make a graph from

unfortunately we can't make a graph from this because if I want to create a graph

this because if I want to create a graph like this this is actually two graphs

like this this is actually two graphs this is one and that is another and we

this is one and that is another and we are not able to kind of create such a

are not able to kind of create such a thing with charts if I go here and I

thing with charts if I go here and I want to just get information out of this

want to just get information out of this alone and insert a 2d bar you'll see

alone and insert a 2d bar you'll see that it doesn't really select the calls

that it doesn't really select the calls it has actually

it has actually selected both the amount and the calls

selected both the amount and the calls and it has put them in the same scale so

and it has put them in the same scale so this is the problem with pivot charts so

this is the problem with pivot charts so what we are going to do is we're going

what we are going to do is we're going to play a trick here we are going to use

to play a trick here we are going to use the pivot to do the calculation

the pivot to do the calculation but we are going to use a outside range

but we are going to use a outside range here to create the chart and the chart

here to create the chart and the chart has additional trickery as well because

has additional trickery as well because we have to highlight the currently

we have to highlight the currently selected person so this

selected person so this pivot again remember it's called reps

pivot again remember it's called reps pivot is going to show me everybody's

pivot is going to show me everybody's information it's not going to have

information it's not going to have filtering on it through the slicer and

filtering on it through the slicer and once this pivot is set up outside here

once this pivot is set up outside here I'm going to say for

I'm going to say for graph and simply say equal to and grab

graph and simply say equal to and grab all of these five cells so everything

all of these five cells so everything comes here a quick note here if you're

comes here a quick note here if you're using an older version of excel you

using an older version of excel you won't be able to do it like this you'll

won't be able to do it like this you'll have to do just this one and then drag

have to do just this one and then drag like that so here we have got the data

like that so here we have got the data and now the data is outside I can just

and now the data is outside I can just select this alone and insert a 2d bar

select this alone and insert a 2d bar you'll see that this has no problem when

you'll see that this has no problem when you select a range and make a chart it

you select a range and make a chart it works but when you select a range inside

works but when you select a range inside a PIV table it's want to it wants to

a PIV table it's want to it wants to create the graph for the whole thing not

create the graph for the whole thing not just the selected items so this is the

just the selected items so this is the first one and we want to have one more

first one and we want to have one more but first let's fix the axis here select

but first let's fix the axis here select the axis control1 and then use the

the axis control1 and then use the categories in reverse order so they

categories in reverse order so they appear in the same order as per my data

appear in the same order as per my data this is good now let's uh move this

this is good now let's uh move this here and again we'll select these two

here and again we'll select these two you can hold on control and select them

you can hold on control and select them and insert one more graph you could do

and insert one more graph you could do it like this alternatively what you can

it like this alternatively what you can also do is because we have already set

also do is because we have already set up one I'm going to copy this contrl C

up one I'm going to copy this contrl C and contrl V to paste it so we have got

and contrl V to paste it so we have got the second

the second graph and in this one this one we are

graph and in this one this one we are going to leave it to the

going to leave it to the calls this one here I'll select the r

calls this one here I'll select the r range and you'll see that it highlights

range and you'll see that it highlights the values here when you click on the

the values here when you click on the bars uh this range gets highlighted and

bars uh this range gets highlighted and what we will do is place the cursor on

what we will do is place the cursor on the edge and drag and move it to the

the edge and drag and move it to the second one so that we have got both of

second one so that we have got both of these now I noticed one additional

these now I noticed one additional problem with this it actually starts the

problem with this it actually starts the axis at 160 that's why it looks like

axis at 160 that's why it looks like there's too much variation going on what

there's too much variation going on what I'll do is I'll select this axis press

I'll do is I'll select this axis press control one to format and make sure that

control one to format and make sure that the minimum is set to zero

the minimum is set to zero let's do this for this one as well all

let's do this for this one as well all right so this is done uh I'm going to

right so this is done uh I'm going to take out these titles and access also

take out these titles and access also I'll take out in fact uh we're going to

I'll take out in fact uh we're going to have to do a lot of

have to do a lot of formatting and this one we'll set the

formatting and this one we'll set the for Gap width to

for Gap width to 25% and change the color to this

25% and change the color to this color again we'll do the same for the

color again we'll do the same for the second graph we should have actually

second graph we should have actually done all of this before copy in but uh

done all of this before copy in but uh that's done and now let's uh add data

that's done and now let's uh add data labels here data labels so the data

labels here data labels so the data labels come up I'm going to apply some

labels come up I'm going to apply some formatting I'll select these cells press

formatting I'll select these cells press control one to format and we can apply

control one to format and we can apply currency formatting but as you can see

currency formatting but as you can see the values are in 18,000 20,000 like

the values are in 18,000 20,000 like that so I want to show it in thousands

that so I want to show it in thousands of dollars for this we can use a custom

of dollars for this we can use a custom format code and just set dollar hash

format code and just set dollar hash comma hash hash and then one more

comma hash hash and then one more comma dot 0 and then within double Cotes

comma dot 0 and then within double Cotes K this is going to basically round up

K this is going to basically round up that number to thousands with one

that number to thousands with one decimal point and then show that as a

decimal point and then show that as a label this makes it look uh a bit easier

label this makes it look uh a bit easier on the eyes when you show it in the

on the eyes when you show it in the dashboard all right so this is done but

dashboard all right so this is done but we are still not done there is some more

we are still not done there is some more work to be done which is whatever

work to be done which is whatever representative I select I want their bar

representative I select I want their bar to be in a different color so to achieve

to be in a different color so to achieve that I'm going to move all of these

that I'm going to move all of these graphs

graphs further and let's add some titles to our

further and let's add some titles to our data here this is my rep calls amount

data here this is my rep calls amount and then we'll add two more values

and then we'll add two more values called cell calls cell amount the

called cell calls cell amount the purpose of this is to show me the call

purpose of this is to show me the call value and the amount value for the

value and the amount value for the representative that I currently selected

representative that I currently selected so for example right now we are looking

so for example right now we are looking at r03 here if I come here what I want

at r03 here if I come here what I want is I want these values to go here as

is I want these values to go here as well so

well so 207 and

207 and 20.9 everything else can be blanked out

20.9 everything else can be blanked out so once that is there then we can get

so once that is there then we can get the highlighting mechanism going on in

the highlighting mechanism going on in the chart to make it happen we need to

the chart to make it happen we need to know what is selected by the slicer so

know what is selected by the slicer so underneath here I'm going to insert one

underneath here I'm going to insert one more pivot this time I'm going to use

more pivot this time I'm going to use the from data model option

the from data model option and in this pivot I will put the

and in this pivot I will put the representative it's going to show all

representative it's going to show all the five Representatives this is because

the five Representatives this is because right now this pivot is not connected to

right now this pivot is not connected to the slicer so let's uh select any cell

the slicer so let's uh select any cell here and give this pivot a name from the

here and give this pivot a name from the pivot table name area here it's

pivot table name area here it's currently pivot table 6 I'm going to

currently pivot table 6 I'm going to name this as selected rep pivot and I'll

name this as selected rep pivot and I'll come here right click on this slicer and

come here right click on this slicer and use the report connections to see what

use the report connections to see what reports it is currently connected to So

reports it is currently connected to So currently it is connected to monthly

currently it is connected to monthly Trend rep pivot and weekly Trend I'm

Trend rep pivot and weekly Trend I'm going to also connect it to selected rep

going to also connect it to selected rep one at this point if I go here you'll

one at this point if I go here you'll see that this is going to show me r03

see that this is going to show me r03 I'm going to turn off Grand totals for

I'm going to turn off Grand totals for this so whatever rep I select that's

this so whatever rep I select that's going to be showing up in the cell a66

going to be showing up in the cell a66 this is the address so if I select

this is the address so if I select r03 which is what we have done here

r03 which is what we have done here that's what this will say if I select

that's what this will say if I select R1 this cell will say R1 if I select

R1 this cell will say R1 if I select multiple people then it's going to show

multiple people then it's going to show me all the multiple people but we'll

me all the multiple people but we'll only look at the first such person so if

only look at the first such person so if I multi select it's going to highlight

I multi select it's going to highlight just the very first one this keeps it

just the very first one this keeps it easy for us

easy for us and here we'll say selected rep and this

and here we'll say selected rep and this is equal to and we'll point to this cell

is equal to and we'll point to this cell so whatever I select that's going to

so whatever I select that's going to come up here if I select multiple then

come up here if I select multiple then the very first person will come up for

the very first person will come up for the sake of Simplicity we'll just keep

the sake of Simplicity we'll just keep selection to one item at a time so r02

selection to one item at a time so r02 is currently selected I'll see that here

is currently selected I'll see that here and then here I can simply say if this

and then here I can simply say if this representative is equal to that then I

representative is equal to that then I want the calls otherwise I want Na and

want the calls otherwise I want Na and you can drag this down you'll see that

you can drag this down you'll see that if it is r02 only that value comes up 28

if it is r02 only that value comes up 28 everybody else gets an a note that here

everybody else gets an a note that here we are writing formulas in cells and

we are writing formulas in cells and dragging but if you notice this is

dragging but if you notice this is actually an entire spill range so you

actually an entire spill range so you may want to write a spillable formula

may want to write a spillable formula here directly as in our case we only

here directly as in our case we only have five Representatives it's not a big

have five Representatives it's not a big issue so I'm not bothering with that

issue so I'm not bothering with that we'll do the same for this as well if

we'll do the same for this as well if this is equal to that and we'll make

this is equal to that and we'll make sure that is locked then this value else

sure that is locked then this value else na a again fill this down and you'll get

na a again fill this down and you'll get the thing so now that these values are

the thing so now that these values are there we'll select the first chart right

there we'll select the first chart right click select data and add another series

click select data and add another series the series name is selected calls and

the series name is selected calls and the values are here okay so now you'll

the values are here okay so now you'll see that for the currently selected rep

see that for the currently selected rep it has an extra item here everybody else

it has an extra item here everybody else has na so we don't even see that now

has na so we don't even see that now what we want is we don't want two bars

what we want is we don't want two bars we want the bar color itself to change

we want the bar color itself to change so this is achieved by a technique

so this is achieved by a technique called overlapping this is where we take

called overlapping this is where we take this thing and overlap it perfectly on

this thing and overlap it perfectly on top of that and because of overlapping

top of that and because of overlapping it looks like the color is changing but

it looks like the color is changing but reality is there's two of them one

reality is there's two of them one sitting on top of another to do this

sitting on top of another to do this we'll just uh select the blue colored

we'll just uh select the blue colored one or the new one press control one to

one or the new one press control one to format and from the formatting options

format and from the formatting options here here you can use the series

here here you can use the series overlapping option to adjust the overlap

overlapping option to adjust the overlap so if I do a 100% overlap you'll see

so if I do a 100% overlap you'll see that this one goes and sits on top of

that this one goes and sits on top of that and it gets that kind of a color

that and it gets that kind of a color changing effect now let's change this

changing effect now let's change this blue color uh we can go here and uh

blue color uh we can go here and uh change this to that color and while we

change this to that color and while we are there you may want to try gradient I

are there you may want to try gradient I like the

like the gradient that it adds a little bit of

gradient that it adds a little bit of depth so I'm going to use the gradient

depth so I'm going to use the gradient option and

option and select this style of gradient and let's

select this style of gradient and let's do the same for here right click select

do the same for here right click select data add selected amount and that is the

data add selected amount and that is the data let's apply 100% overlap and let's

data let's apply 100% overlap and let's select the color again we're going to

select the color again we're going to use gradient this time we're going to

use gradient this time we're going to use the brick color for the amounts

use the brick color for the amounts we're going to take out the axis value

we're going to take out the axis value because it's the same axis so we don't

because it's the same axis so we don't need that repeat twice

need that repeat twice and now our graphs are done I'm going to

and now our graphs are done I'm going to control X them and go back here and

control X them and go back here and paste them here and let's select the

paste them here and let's select the graphs and I'm going to go to the format

graphs and I'm going to go to the format and make sure that there is no outline

and make sure that there is no outline so once you take out the outlines it

so once you take out the outlines it looks like they're all one happy family

looks like they're all one happy family and you can insert a

and you can insert a rectangle place it here and fill it up

rectangle place it here and fill it up with white color and set the outline to

with white color and set the outline to be that and send it back so now you have

be that and send it back so now you have got an outline as well and I'm going to

got an outline as well and I'm going to just make sure that there's no fill

just make sure that there's no fill color in the charts so that we can

color in the charts so that we can actually use the background box to get

actually use the background box to get that fill color and for this slicer I'm

that fill color and for this slicer I'm going to move it slightly

going to move it slightly inside

inside and send it back I forgot we need to

and send it back I forgot we need to have titles so let's add Title Here

have titles so let's add Title Here chart title and this one is call

chart title and this one is call and let's add title for this guy as

and let's add title for this guy as well that looks great I can select a

well that looks great I can select a different rep and I'll be able to see

different rep and I'll be able to see that

that highlighted in the graph and you can see

highlighted in the graph and you can see all of these things are also changing

all of these things are also changing nicely how awesome is this we do need to

nicely how awesome is this we do need to adjust some of the settings for the

adjust some of the settings for the slicer if you see the slicer color is in

slicer if you see the slicer color is in the blue color whereas all of these

the blue color whereas all of these highlighting is in the orange or red

highlighting is in the orange or red family so let's fix this formatting

family so let's fix this formatting issue for the slicer you can select the

issue for the slicer you can select the slicer go to the slicer formatting here

slicer go to the slicer formatting here and you'll see that there are different

and you'll see that there are different themes that you can select so for

themes that you can select so for example you can select this and that

example you can select this and that kind of Suits what we want but I want to

kind of Suits what we want but I want to tone down the unhighlighted colors so

tone down the unhighlighted colors so we'll select this and use this as a base

we'll select this and use this as a base style and then right click on it and

style and then right click on it and then duplicate it to create a duplicate

then duplicate it to create a duplicate style and we are going to modify this

style and we are going to modify this first up we're going to start with whole

first up we're going to start with whole slicer go to format

slicer go to format select the border and instead of this

select the border and instead of this orange border I'm going to set this to

orange border I'm going to set this to that border so that it has the same kind

that border so that it has the same kind of Border color as rest of my elements

of Border color as rest of my elements on the dashboard next up we're going to

on the dashboard next up we're going to use unselected item with data selected

use unselected item with data selected item with data is fine it kind of is

item with data is fine it kind of is looking exactly how I want UNS selected

looking exactly how I want UNS selected item with data will format it and we're

item with data will format it and we're going to fill color to

going to fill color to that and I'm going to go to the selected

that and I'm going to go to the selected item with data here quickly make sure

item with data here quickly make sure that the font is bold and click okay so

that the font is bold and click okay so now we have created a new style let's

now we have created a new style let's apply that you can see that currently

apply that you can see that currently this is the style uh that we are using

this is the style uh that we are using so while keeping the slicer selected

so while keeping the slicer selected click on this style and there is our new

click on this style and there is our new style if you want you can tone it down

style if you want you can tone it down even more uh this is the style that I

even more uh this is the style that I have used in this report you can see the

have used in this report you can see the colors are a little bit more mellowed

colors are a little bit more mellowed down here except for the one that we are

down here except for the one that we are s

s selecting but that looks good now let's

selecting but that looks good now let's add the remaining bits and when we are

add the remaining bits and when we are done we're going to bring in the picture

done we're going to bring in the picture and other stuff here next we are going

and other stuff here next we are going to create these graphs that tell us

to create these graphs that tell us what's happening at a customer level so

what's happening at a customer level so this graph here tells me how many female

this graph here tells me how many female versus male colors are there between

versus male colors are there between various cities so we have got Cincinnati

various cities so we have got Cincinnati Cleveland and Columbus and this is how

Cleveland and Columbus and this is how that picture looks like and this is the

that picture looks like and this is the overall rating distribution of the

overall rating distribution of the currently selected rep I think of all

currently selected rep I think of all the graphs in this page only this one is

the graphs in this page only this one is not connected to the slicer so there's

not connected to the slicer so there's no connection here everything else is

no connection here everything else is connected so if you see when I select

connected so if you see when I select something this one doesn't really change

something this one doesn't really change we're going to keep it like that and for

we're going to keep it like that and for this we are going to keep the connection

this we are going to keep the connection to the slicer active so let's go ahead

to the slicer active so let's go ahead and build these two we'll come here add

and build these two we'll come here add one more pivot

one more pivot and this pivot here this one doesn't

and this pivot here this one doesn't need to be connected to the rep slicer

need to be connected to the rep slicer so we're going to directly insert a

so we're going to directly insert a pivot from the date model and in this

pivot from the date model and in this pivot we'll just add

pivot we'll just add customers

customers City and

City and gender so we'll see both the three

gender so we'll see both the three cities and female and male and let's add

cities and female and male and let's add the call count as the measure so here

the call count as the measure so here you can see the power of that data model

you can see the power of that data model values from both tables coming the value

values from both tables coming the value of this city and the gender are coming

of this city and the gender are coming from the customer table whereas this

from the customer table whereas this call count is coming from the calls

call count is coming from the calls table once this is there we can go to

table once this is there we can go to insert and add a 100% stacked graph that

insert and add a 100% stacked graph that shows me male versus female distribution

shows me male versus female distribution female is under and male is above this

female is under and male is above this here I'm going to cut this graph paste

here I'm going to cut this graph paste it

it here and let's uh adjust the size of

here and let's uh adjust the size of this and let's quickly format this again

this and let's quickly format this again turn off the field buttons turn off the

turn off the field buttons turn off the legend and select this go to format and

legend and select this go to format and make sure the Gap width is

25% and let's add data labels and I'm going to take out the

labels and I'm going to take out the vertical

vertical axis as well as the grid

axis as well as the grid lines that looks like that uh let's

lines that looks like that uh let's adjust the formatting a little bit I'm

adjust the formatting a little bit I'm going to select the female series and go

going to select the female series and go to format and use the brick color for

to format and use the brick color for that and for the male series I'm going

that and for the male series I'm going to use this uh green color and I'll

to use this uh green color and I'll select these labels and change the shape

select these labels and change the shape to a rounded

to a rounded rectangle and go to the fill color here

rectangle and go to the fill color here and set it to solid color but make it

and set it to solid color but make it 50% transparent let's do the same for

50% transparent let's do the same for the female as well and there is our

the female as well and there is our female versus male color so let's add a

female versus male color so let's add a title to that I'm going to add a title

title to that I'm going to add a title up top and change the words to the right

up top and change the words to the right colors for that

colors for that gender so that we can kind of quickly

gender so that we can kind of quickly tell which area of the chart corresponds

tell which area of the chart corresponds to what gender this way we don't need

to what gender this way we don't need that Legend anymore and you know it

that Legend anymore and you know it tells us the story

tells us the story correctly all right uh so that is

correctly all right uh so that is working and as you can see when I click

working and as you can see when I click on the slicer this doesn't change so

on the slicer this doesn't change so this is presenting the overall picture

this is presenting the overall picture now here we want to see the rating

now here we want to see the rating information for the currently selected

information for the currently selected rep so again we'll need one more pivot

rep so again we'll need one more pivot I'm going to copy this pivot paste it

I'm going to copy this pivot paste it here and in this pivot I'm going to take

here and in this pivot I'm going to take out the city and the gender and we have

out the city and the gender and we have got the

got the calls and we'll go here and select the

calls and we'll go here and select the rating rounded and put it into rows so

rating rounded and put it into rows so that we can see the call count let's

that we can see the call count let's select any cell here go to pivot table

select any cell here go to pivot table analyze and give it a name this is pivot

analyze and give it a name this is pivot table 8 I'm going to name this as rep

table 8 I'm going to name this as rep satisfaction and come here right click

satisfaction and come here right click on the slicer go to report connections

on the slicer go to report connections and Link it to the rep satisfaction as

and Link it to the rep satisfaction as well so now this is going to show me the

well so now this is going to show me the number of calls that rep has received

number of calls that rep has received and how how satisfied are the customers

and how how satisfied are the customers and here let's just add this as a column

and here let's just add this as a column chart and cut this and put it right here

chart and cut this and put it right here let's adjust the size of this we'll

let's adjust the size of this we'll leave the we'll need the title uh and

leave the we'll need the title uh and this one

this one here I'm going to set the Gap width to

here I'm going to set the Gap width to 10% and let's fill it with this color

10% and let's fill it with this color and let's just set the title as rating

and let's just set the title as rating and there is our rating information

and there is our rating information again you can select a different uh

again you can select a different uh representative and you'll see this graph

representative and you'll see this graph updates nicely based on who you select

updates nicely based on who you select one problem with this is if a

one problem with this is if a representative for example

representative for example r05

r05 here you can see that they don't have

here you can see that they don't have any calls that have one rating or zero

any calls that have one rating or zero rating so they'll have only four columns

rating so they'll have only four columns here whereas a different representative

here whereas a different representative like r02 they'll have one 1 2 3 4 5 so

like r02 they'll have one 1 2 3 4 5 so the number of columns here keeps

the number of columns here keeps changing depending on who you pick and

changing depending on who you pick and this might be a little bit annoying if

this might be a little bit annoying if you present it it to an audience so how

you present it it to an audience so how are you going to fix it I'm going to

are you going to fix it I'm going to leave that to you as a homework figure

leave that to you as a homework figure it out and leave a comment on how you

it out and leave a comment on how you would solve this problem and now that

would solve this problem and now that brings us to the last part of the report

brings us to the last part of the report here we want to see what is happening at

here we want to see what is happening at each customer level for each rep so we

each customer level for each rep so we have got 15 customers they're scattered

have got 15 customers they're scattered across three cities uh you can see this

across three cities uh you can see this in the data here these are the 15

in the data here these are the 15 customers and they are scattered in

customers and they are scattered in these three cities I want to see for

these three cities I want to see for each City each customer for all of our

each City each customer for all of our five reps how the revenue breakdown

five reps how the revenue breakdown looks like and for whatever rep that I'm

looks like and for whatever rep that I'm currently interested in so right now r05

currently interested in so right now r05 I want to highlight that combination as

I want to highlight that combination as well so that we can see how that is for

well so that we can see how that is for that particular person so let's go ahead

that particular person so let's go ahead this is going to be another pivot table

this is going to be another pivot table and we can kind of copy this pivot and

and we can kind of copy this pivot and paste it here and in this pivot let's

paste it here and in this pivot let's just take out the rating and go to

just take out the rating and go to customer and first add City and then the

customer and first add City and then the customer ID underneath that I'm going to

customer ID underneath that I'm going to take out the call count and introduce

take out the call count and introduce the

the representa as well as total amount now

representa as well as total amount now the problem with this pivot because we

the problem with this pivot because we copy pasted it is only showing me r05

copy pasted it is only showing me r05 whereas what I want is I don't want to

whereas what I want is I don't want to just see r05 I want to see all the

just see r05 I want to see all the representatives R1 R2 R3 R4 R5 but

representatives R1 R2 R3 R4 R5 but highlight r05 so first we need to dink

highlight r05 so first we need to dink the slicer from this pivot how are we

the slicer from this pivot how are we going to do that you can do it in two

going to do that you can do it in two ways you can go to the report right

ways you can go to the report right click report connections and uncheck the

click report connections and uncheck the new pivot that we just added another

new pivot that we just added another option is you can also select any cell

option is you can also select any cell in the pivot table go to the analyze

in the pivot table go to the analyze ribbon and here you'll see that there is

ribbon and here you'll see that there is a filter connections button so if you

a filter connections button so if you tap on that it will tell you what

tap on that it will tell you what slicers are connected to this pivot and

slicers are connected to this pivot and I can uncheck the representative slicer

I can uncheck the representative slicer so it's not paying attention to that at

so it's not paying attention to that at this point you'll need to clear the

this point you'll need to clear the filter from representative column so

filter from representative column so that it shows everything so whichever

that it shows everything so whichever way you do it make sure that you dink it

way you do it make sure that you dink it so now that this is D I'm going to

so now that this is D I'm going to rename this pivot it's pivot table 9 I'm

rename this pivot it's pivot table 9 I'm going to call this as rep amounts and

going to call this as rep amounts and let's take out this particular grand

let's take out this particular grand total row we don't mind grand total

total row we don't mind grand total column so I'm going to go to the design

column so I'm going to go to the design ribbon Grand totals and just say on for

ribbon Grand totals and just say on for rows only so that there is only this

rows only so that there is only this column and that row is gone next up

column and that row is gone next up we'll just take all of this information

we'll just take all of this information put it into the dashboard and from there

put it into the dashboard and from there we are going to adjust all the things so

we are going to adjust all the things so I'll select all of

I'll select all of this this kind of makes an assumption

this this kind of makes an assumption that there's only ever going to be 15

that there's only ever going to be 15 customers scattered across these three

customers scattered across these three cities if you were to end up with more

cities if you were to end up with more customers than this then this approach

customers than this then this approach doesn't work but for now this is all

doesn't work but for now this is all right so I'll copy this contrl C come

right so I'll copy this contrl C come here select this cell we're going to

here select this cell we're going to need few more cells up top for the

need few more cells up top for the titles uh let's go here and right click

titles uh let's go here and right click and Link the values paste as link so

and Link the values paste as link so whatever that is there it kind kind of

whatever that is there it kind kind of comes up here as a linked value we have

comes up here as a linked value we have got lots of zeros here which is what

got lots of zeros here which is what happens because in the pivot up top we

happens because in the pivot up top we have got blank value so that's why it's

have got blank value so that's why it's coming up as zero um this is a bit

coming up as zero um this is a bit annoying so what I'm going to do is I'm

annoying so what I'm going to do is I'm going to take this out and instead what

going to take this out and instead what I'll says if Open Bracket and then go to

I'll says if Open Bracket and then go to the pivot grab this entire thing so then

the pivot grab this entire thing so then we here we will say if this is equal to

we here we will say if this is equal to empty space then I want empty spaces not

empty space then I want empty spaces not zeros uh else I want the same

zeros uh else I want the same range and this way those blank ones will

range and this way those blank ones will come in here as blanks and this looks

come in here as blanks and this looks all right to me uh what I'll also do is

all right to me uh what I'll also do is I'll make sure this First Column is a

I'll make sure this First Column is a little bit wide enough to print the

little bit wide enough to print the cities correctly and I'll just adjust

cities correctly and I'll just adjust some of these things okay so that looks

some of these things okay so that looks good and

good and here we'll just say equal to and go to

here we'll just say equal to and go to the pivot and get these five and the

the pivot and get these five and the last one I'm just going to manually say

last one I'm just going to manually say total and then for these individual

total and then for these individual customers I'm going to select them using

customers I'm going to select them using control and indent them a little bit

control and indent them a little bit inside and select all of

inside and select all of these reduce the font size by one point

these reduce the font size by one point and select these things control1 and

and select these things control1 and apply currency formatting with the zero

apply currency formatting with the zero decimals okay so that looks good the

decimals okay so that looks good the next thing that I want to do is I want

next thing that I want to do is I want to add conditional formatting data bars

to add conditional formatting data bars here so we can see how big these numbers

here so we can see how big these numbers are so I'll select all of these values

are so I'll select all of these values you can just select the whole thing and

you can just select the whole thing and go to conditional formatting data bar

go to conditional formatting data bar and apply a solid bar the problem with

and apply a solid bar the problem with the data bars is while they do tell the

the data bars is while they do tell the volume of the numbers they also kind of

volume of the numbers they also kind of overwrite the value so sometimes it

overwrite the value so sometimes it makes it hard for us to read so we're

makes it hard for us to read so we're going to follow another trick here we'll

going to follow another trick here we'll set select this again and go to

set select this again and go to conditional formatting manage rules

conditional formatting manage rules select the data bar double click on it

select the data bar double click on it and instead of going from automatic to

and instead of going from automatic to automatic I'm going to just set the

automatic I'm going to just set the minimum to number zero and maximum to a

minimum to number zero and maximum to a formula so the formula is and here I'm

formula so the formula is and here I'm going to say Max of Open

going to say Max of Open Bracket select all these

Bracket select all these numbers times two so whatever is the

numbers times two so whatever is the maximum I want the

maximum I want the maximum value of this bars to be twice

maximum value of this bars to be twice as much what this does is it's kind of

as much what this does is it's kind of tricky to explain but once you see the

tricky to explain but once you see the result it makes sense so I'll wait for

result it makes sense so I'll wait for that and then I'm going to change the

that and then I'm going to change the color to a dark color like that so now

color to a dark color like that so now you can see that whatever is the maximum

you can see that whatever is the maximum value I think the maximum is

value I think the maximum is 2,280 and whoever has that value so here

2,280 and whoever has that value so here is the guy that bar will only be half as

is the guy that bar will only be half as wide as the entire cell width so this

wide as the entire cell width so this means everything else kind of gets

means everything else kind of gets scaled down and we can get a sense of

scaled down and we can get a sense of how big the numbers are but we can also

how big the numbers are but we can also read them if we need it this looks

read them if we need it this looks perfect the only additional thing that

perfect the only additional thing that we need is whoever is the rep that we

we need is whoever is the rep that we are currently interested in so for

are currently interested in so for example r05 I want to highlight that

example r05 I want to highlight that entire column this is really simple I'm

entire column this is really simple I'm going

going to select this entire range including

to select this entire range including the

the headers and go to conditional format in

headers and go to conditional format in and add another Rule and this time we're

and add another Rule and this time we're going to use a formula based Rule and

going to use a formula based Rule and the formula is we simply want to check

the formula is we simply want to check if the value in this row so q19 and it

if the value in this row so q19 and it says dollar

says dollar q19 if you press f41 it will become q19

q19 if you press f41 it will become q19 so that's what we want to use so

so that's what we want to use so whatever is in the row number 19 but

whatever is in the row number 19 but relevant column so q r s like that is

relevant column so q r s like that is equal to and go to the pivots and and

equal to and go to the pivots and and point

point to this cell here this is the one that

to this cell here this is the one that tells me which representative we have

tells me which representative we have selected so if that is equal to that

selected so if that is equal to that then I want to apply a formatting which

then I want to apply a formatting which is of this color and I also want to add

is of this color and I also want to add border around it um The Border will be a

border around it um The Border will be a little bit darker color on both

sides so for r05 that's going to be highlighted if I I select r03 that will

highlighted if I I select r03 that will be highlighted r01 2 if I multi- select

be highlighted r01 2 if I multi- select it's always going to just highlight the

it's always going to just highlight the very first one so if I go from two to

very first one so if I go from two to four it's just going to highlight the

four it's just going to highlight the two that's not a problem and this is

two that's not a problem and this is done now I can select this entire range

done now I can select this entire range and I

and I can press

can press control1 to open the formats and go to

control1 to open the formats and go to border and I'm going to add

border and I'm going to add a border around it

a border around it and I'm going to fill this up with white

and I'm going to fill this up with white color what this does is it kind of

color what this does is it kind of creates this boxy look here and it sets

creates this boxy look here and it sets that apart from our background there I

that apart from our background there I notice that some alignment is happening

notice that some alignment is happening uh this alignment so you can see this is

uh this alignment so you can see this is aligned there so I'm going to move this

aligned there so I'm going to move this up there I think that'll do and this bit

up there I think that'll do and this bit here I'm going to reset the color to

here I'm going to reset the color to that there we go that looks beautiful

that there we go that looks beautiful and everything works nicely I'm going to

and everything works nicely I'm going to select this row make it bold I'm going

select this row make it bold I'm going to select these cities and make them

to select these cities and make them bold as

bold as well let's test this out if I select a

well let's test this out if I select a different representative I can see that

different representative I can see that Representatives information highlighted

Representatives information highlighted filtered all of these graphs update and

filtered all of these graphs update and this conditional formatting also working

this conditional formatting also working beautifully only bit reminding is

beautifully only bit reminding is bringing in the picture of the

bringing in the picture of the representative and showing some quick

representative and showing some quick summary about them

summary about them we'll need to go to assets tab for

we'll need to go to assets tab for this in the assets tab I have got for

this in the assets tab I have got for each representative their picture like I

each representative their picture like I mentioned earlier I made these pictures

mentioned earlier I made these pictures from the stock images so I'll quickly

from the stock images so I'll quickly show you how one picture is made um so

show you how one picture is made um so that if you want to kind of zazz it up a

that if you want to kind of zazz it up a bit and add customer images or something

bit and add customer images or something you know how to do it this is really

you know how to do it this is really simple you want to go to insert click on

simple you want to go to insert click on pictures and place over the cells and

pictures and place over the cells and select stock

select stock image from here go to cut out people and

image from here go to cut out people and you'll get uh lots of people images that

you'll get uh lots of people images that are doing weird poses with their faces

are doing weird poses with their faces and hands kind of like YouTube

and hands kind of like YouTube thumbnails really and you can select

thumbnails really and you can select whatever you want for example you think

whatever you want for example you think okay this person would be perfect fit

okay this person would be perfect fit for a customer I can select that and

for a customer I can select that and insert that and you'll get a high

insert that and you'll get a high quality picture of that person now this

quality picture of that person now this is a full picture of them standing we

is a full picture of them standing we don't need that level I just want their

don't need that level I just want their face and kind of see that much alone so

face and kind of see that much alone so I'm going to go to picture format crop

I'm going to go to picture format crop aspect ratio 1 is to one so this will

aspect ratio 1 is to one so this will give you a square crop and right now it

give you a square crop and right now it is cropping just her body what I'll do

is cropping just her body what I'll do is I'll move this around until we see

is I'll move this around until we see the face and you can kind of make the

the face and you can kind of make the picture big so that you just get the

picture big so that you just get the shoulder and head kind of like a

shoulder and head kind of like a passport picture once this is done you

passport picture once this is done you can s select this picture again go to

can s select this picture again go to picture format crop crop to shape and

picture format crop crop to shape and this time select an oval shape and that

this time select an oval shape and that will give you a circle crop around their

will give you a circle crop around their whole thing as it is 1 is to one we'll

whole thing as it is 1 is to one we'll get a perfect circle crop and this is

get a perfect circle crop and this is the picture that I'm using once the

the picture that I'm using once the picture is ready you want to select is

picture is ready you want to select is place the picture in a cell again if you

place the picture in a cell again if you hold down the ALT key it kind of snaps

hold down the ALT key it kind of snaps and once it is there if you see in Excel

and once it is there if you see in Excel 360 you'll have this option to place the

360 you'll have this option to place the picture in a Cell so if you tap on that

picture in a Cell so if you tap on that that picture is now belonging to the

that picture is now belonging to the cell so I3 cell has the picture so

cell so I3 cell has the picture so wherever else if I want to use the

wherever else if I want to use the picture if I go here and equal to and

picture if I go here and equal to and then say assets

then say assets I3 I'm going to get that picture so this

I3 I'm going to get that picture so this is the basic approach that we are using

is the basic approach that we are using Let's uh set up rest of it here so here

Let's uh set up rest of it here so here we know what representative it is we're

we know what representative it is we're going to create an area in the pivots

going to create an area in the pivots rep summary and what we want to see is

rep summary and what we want to see is again I'll Flash the completed report

again I'll Flash the completed report here we want to see the picture

here we want to see the picture percentage of calls they have taken what

percentage of calls they have taken what is their rank in the number of calls as

is their rank in the number of calls as well as total amount one being the

well as total amount one being the highest rank or highest amount or

highest rank or highest amount or highest calls so for example r05 has the

highest calls so for example r05 has the third rank so they're halfway there

third rank so they're halfway there percentage of

percentage of calls this is easy we just say What is

calls this is easy we just say What is the

the you can see that there's two pivots here

you can see that there's two pivots here what is this number as a percentage of

what is this number as a percentage of that number so I can select this divide

that number so I can select this divide that with that and we'll get that as

that with that and we'll get that as 0.186 and you can apply percentage

0.186 and you can apply percentage formatting so you'll see that as

formatting so you'll see that as 19% call

19% call Rank and amount rank these are a little

Rank and amount rank these are a little bit tricky but essentially what we want

bit tricky but essentially what we want to know is what is the total number of

to know is what is the total number of calls r04 took and what was their rank

calls r04 took and what was their rank as against all of the people so for this

as against all of the people so for this first we need to know what is the total

first we need to know what is the total number of calls they took and then we

number of calls they took and then we can calculate the rank so here I'm just

can calculate the rank so here I'm just going to move these a little bit

going to move these a little bit down and first let's calculate the calls

down and first let's calculate the calls and

and amount calls for this representative r04

amount calls for this representative r04 would be this is really simple we can

would be this is really simple we can just say x

just say x lookup

lookup r04 in the representative column here

r04 in the representative column here and get the number number of calls

and get the number number of calls likewise we will do one more X lookup X

likewise we will do one more X lookup X lookup r04 in this and get the amount

lookup r04 in this and get the amount then we can just ask what is the rank we

then we can just ask what is the rank we can use rank.avg of that

can use rank.avg of that number in all the calls what is the rank

number in all the calls what is the rank of that

of that amount in these amounts so for that

amount in these amounts so for that representative r04 you can see that they

representative r04 you can see that they are fifth ranked in calls and fifth

are fifth ranked in calls and fifth ranked in the amounts because they they

ranked in the amounts because they they are the lowest calls and lowest amount

are the lowest calls and lowest amount you can see that here in the data as

you can see that here in the data as well this all works fine until you

well this all works fine until you select multiple items so I'm going to

select multiple items so I'm going to select three and you can see that even

select three and you can see that even though when I selected three I'm getting

though when I selected three I'm getting some sort of a rank here so this

some sort of a rank here so this shouldn't really be the case so we'll

shouldn't really be the case so we'll need to have a fail safe mechanism kind

need to have a fail safe mechanism kind of a thing we'll deal with that later

of a thing we'll deal with that later further down but the numbers are there

further down but the numbers are there and now we can generate the labels um

and now we can generate the labels um the first one is percentage of calls and

the first one is percentage of calls and this is basically percentage of

this is basically percentage of calls and prend that number I was

calls and prend that number I was thinking it's going to say percentage of

thinking it's going to say percentage of calls 61% but it was actually showing me

calls 61% but it was actually showing me the decimal value of that so we'll need

the decimal value of that so we'll need to use the text function around this

to use the text function around this value with 0% as the format code what

value with 0% as the format code what that does is it takes that number and

that does is it takes that number and applies the format code and converts the

applies the format code and converts the whole thing into a text representation

whole thing into a text representation so you can kind of stitch them together

so you can kind of stitch them together to make a sentence like

to make a sentence like this likewise call

this likewise call rank end this

rank end this one amount Rank and percent so these

one amount Rank and percent so these will always show even when you have

will always show even when you have multi selected what we want is we don't

multi selected what we want is we don't want to see these two things if I multi-

want to see these two things if I multi- select so here I can kind of uh write an

select so here I can kind of uh write an if condition

if condition if and then I can check count a of

if and then I can check count a of these five

these five cells is greater than one that means you

cells is greater than one that means you have picked more than one value then I

have picked more than one value then I don't want anything so that way this

don't want anything so that way this thing is kind of prevents the call rank

thing is kind of prevents the call rank from showing we'll use the same logic

from showing we'll use the same logic for amount rank as well so the

for amount rank as well so the information is coming what about the

information is coming what about the image image is easy we'll say image and

image image is easy we'll say image and we're going to fetch the image of the

we're going to fetch the image of the person that is currently active so again

person that is currently active so again for the image we'll say

for the image we'll say X

X lookup look up this person and go to the

lookup look up this person and go to the assets and select the representative

assets and select the representative names and once the lookup has done its

names and once the lookup has done its job and found the person we want their

job and found the person we want their image so we're going to select these

image so we're going to select these values so whoever is the first value in

values so whoever is the first value in r01 their image will come up if we multi

r01 their image will come up if we multi select then it will show the first one

select then it will show the first one but if you select just one person

but if you select just one person r04 it will be r04 and we'll get their

r04 it will be r04 and we'll get their image here again we don't want the image

image here again we don't want the image if you multi selected so we can apply

if you multi selected so we can apply the same if logic if the count is more

the same if logic if the count is more than one then we don't want anything

than one then we don't want anything else do the X lookup so this way the

else do the X lookup so this way the image will come but if you multi

image will come but if you multi select there won't be any image it's

select there won't be any image it's just blank all right so everything is

just blank all right so everything is now ready let's get the image here for

now ready let's get the image here for the image I'm going to select all of

the image I'm going to select all of these cells and merge them so we have

these cells and merge them so we have got enough space to show a big image and

got enough space to show a big image and then here I'll say equal to and point to

then here I'll say equal to and point to the image cell and hit enter so that

the image cell and hit enter so that image will come up and here underneath

image will come up and here underneath we'll say equal to percentage of calls

we'll say equal to percentage of calls equal to call rank equal

equal to call rank equal to amount Rank and I'm going to select

to amount Rank and I'm going to select these three and I'll say merge across so

these three and I'll say merge across so each row will be merged and we can

each row will be merged and we can Center align that tone on the color and

Center align that tone on the color and reduce the size a little bit and you can

reduce the size a little bit and you can test this if I select somebody I'll see

test this if I select somebody I'll see their picture as well with additional

their picture as well with additional details and insights that might help me

details and insights that might help me understand how we doing at a call center

understand how we doing at a call center that kind of concludes the construction

that kind of concludes the construction part of our portfolio project how do you

part of our portfolio project how do you like this I'm really impressed by how

like this I'm really impressed by how far we have come in this free data

far we have come in this free data analyst course and how we are able to

analyst course and how we are able to create such a gorgeous beautiful and

create such a gorgeous beautiful and insight full dashboard out of the call

insight full dashboard out of the call center

center data let's add a cherry on the top by

data let's add a cherry on the top by bringing in a title here this is really

bringing in a title here this is really easy you can just make the row number

easy you can just make the row number one big enough for that and type the

one big enough for that and type the call center

call center report 2023 as the title adjust the

report 2023 as the title adjust the alignment and make it big that kind of

alignment and make it big that kind of adds the detail on the top if you want

adds the detail on the top if you want you can add more insights and titles to

you can add more insights and titles to kind of illustrate or explain the

kind of illustrate or explain the information that is being shown here uh

information that is being shown here uh but for now I am very happy with what we

but for now I am very happy with what we have achieved and everything is dynamic

have achieved and everything is dynamic if you have got new data let's say you

if you have got new data let's say you have got data for 2024 calls you can

have got data for 2024 calls you can just add this at the bottom and set this

just add this at the bottom and set this up for everything once the data is added

up for everything once the data is added you can just come here and go to the

you can just come here and go to the data rbon and refresh all all the pivots

data rbon and refresh all all the pivots and calculations will be updated and

and calculations will be updated and everything will nicely fall in place

everything will nicely fall in place as a challenge I recommend you try out

as a challenge I recommend you try out replacing some of these visuals with

replacing some of these visuals with your own ways of looking and

your own ways of looking and understanding the data that way you get

understanding the data that way you get to think like a data analyst and see

to think like a data analyst and see what else can be presented or what else

what else can be presented or what else can be explained from the data give it a

can be explained from the data give it a try and let me know how that goes in the

try and let me know how that goes in the comments below I want to sincerely thank

comments below I want to sincerely thank you for taking time to learn Excel with

you for taking time to learn Excel with me through this free data analyst course

me through this free data analyst course I hope you have found all the

I hope you have found all the information that you need to master

information that you need to master Excel and be a confident data analyst

Excel and be a confident data analyst but if you think you need more help with

but if you think you need more help with Excel either handholding and explanation

Excel either handholding and explanation of more detailed features or repetition

of more detailed features or repetition of some of the core fundamentals in

of some of the core fundamentals in different examples I recommend checking

different examples I recommend checking out my Excel school program I have been

out my Excel school program I have been running a variation of this program

running a variation of this program since

since 2010 and in the last 14 years I have

2010 and in the last 14 years I have helped more than 12,000 people online

helped more than 12,000 people online with this course it is a tremendous

with this course it is a tremendous program designed with all my experience

program designed with all my experience of working and helping others as a data

of working and helping others as a data analyst so naturally in this course you

analyst so naturally in this course you won't find boring and useless

won't find boring and useless information like what each button will

information like what each button will do or what are the numerous shortcuts of

do or what are the numerous shortcuts of excel do instead I focus on how to take

excel do instead I focus on how to take a data analysis situ eqution and how to

a data analysis situ eqution and how to solve it using various features of excel

solve it using various features of excel I put a link to this course in the video

I put a link to this course in the video description below please check it out

description below please check it out and sign up the course goes much more

and sign up the course goes much more deeper than the free data analyst

deeper than the free data analyst programs Excel module and introduces you

programs Excel module and introduces you to a lot of advanced concepts and how to

to a lot of advanced concepts and how to work with them how to combine them to

work with them how to combine them to get the outputs that you

get the outputs that you need there is also an optional module on

need there is also an optional module on Excel dashboards that helps you create

Excel dashboards that helps you create amazing looking dashboards like this

amazing looking dashboards like this from your data check out the course

from your data check out the course using the video description link and

using the video description link and sign up today to take your Excel skills

to the next level thank you so much once again for

level thank you so much once again for taking part of this Excel module of the

taking part of this Excel module of the free data analyst course bye

Click on any text or timestamp to jump to that moment in the video

Most transcripts ready in under 5 seconds

One-Click Copy125+ LanguagesSearch ContentJump to Timestamps

Paste YouTube URL

Enter any YouTube video link to get the full transcript

Most transcripts ready in under 5 seconds

Get Our Chrome Extension

Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.

Add to Chrome — Free

Works with YouTube, Coursera, Udemy and more educational platforms

Get Instant Transcripts: Just Edit the Domain in Your Address Bar!

YouTube

←

→

↻

https://www.youtube.com/watch?v=UF8uR6Z6KLc

YoutubeToText

←

→

↻

https://youtubetotext.net/watch?v=UF8uR6Z6KLc

YouTube Transcript:Complete Excel Tutorial for Data Analysis in 4 Hours (with FREE Files)

Video Transcript

Paste YouTube URL

Transcript Extraction Form

Get Our Chrome Extension

Get Instant Transcripts: Just Edit the Domain in Your Address Bar!

YouTube Transcript:
Complete Excel Tutorial for Data Analysis in 4 Hours (with FREE Files)