Master Excel for Data Analysis: From Basics to Interactive Dashboards

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

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

press control shift 8 to select all the 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 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

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

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 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 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 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 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 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 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 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 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 automatically when the filter formula

does its thing it will show you in the date formatting same for these numbers you can apply decimal formatting and

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

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

it bold and fill a color and now in this cell here I'll just ask Excel to get me the same headers as my staff table so

for this we are going to say staff table Open Bracket and if you scroll all the way down you can see that we can also

access the headers of the table for this you need to use hash headers as an option you can just scroll down and

select this double click so Excel writes it for you close the square brackets and hit enter and you're going to get the

headers as they were written in the data table when you are looking at it you might think okay chandu this is all good

but for my extract I don't need to see all of these columns all I care about is employee ID their names first name last

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

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

choose columns to tell Excel that once the filter has done its job we would like to see for example column number

one 2 3 we don't want to see four we want to see five and sixth column so let's give it a go so here I have set up

a area for us I want to see all employees with greater than 100K salary but just the columns 1 2 3 5 and six so

here we're going to say filter staff table staff table

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

also put it as input cell value so I'm 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 you hit enter as initially f67 is blank

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

100,000 and you'll see that as soon as a type I'll get a subset of people but I 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

as I mentioned we just want to see a subset of the columns so we will take the filter function and send this to the

choose columns function and here we are going to now learn a powerful concept called nesting this is where we take the

output of a formula and then pass it to another formula so that you could do more powerful things so this new

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

from this give me column number 1 2 3 5 and six you can comma separate and mention

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

directly again a quick note of caution both these functions choose calls and filter are only available in Excel 365

or Excel on the web if you are using a old version of excel unfortunately these kind of formulas are

not possible now that these are done you may also want to add a cherry on the top by setting the headers correctly for

this again we are going to do the same thing and this time we are going to say staff hash headers and this is going to

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

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

headers 1 2 3 5 6 and that's going to give you only those headers from the table and that

data point and all of this is dynamic so if I change my requirement and say I want to look at for example

108,000 I'm going to see all of those employees here later on in this video I'll tell

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

you may want to average all of these salaries how to do that we're going to cover in the later on aspects of this

video our third business question is all female employees with more than 100K salary and here we are going to kind of

piggyback on what we already did so instead of writing new formulas I have set up this worksheet as all such female

employees and we want to see columns 1 2 3 5 and six to do that we can use multiple conditions in filter function

so we are going to go back to the same filter staff table and then staff table

salary is greater than the f67 cell but that's not enough we also need to check for the gender of the employee and then

make sure that we are only looking at female employees so when you want to write multiple conditions you need to

start by putting brackets around each condition and then the second condition needs to be multiplied with the first

one so we'll write star Open Bracket and then inside here we write the second condition so staff table gender is

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

then use that as an input cell I'm just going to hard code because our question itself is female employees once this

part is done you can close the filter formula all together and hit enter and you're going to see just a subset of

employees and here you can see clearly that we are only looking at the female employees this time around we would like

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

6 now as these columns are all continuous and they start from one and go up to six you can also use a

different version of the filter rather than the choose columns option so here instead of saying staff I can say in the

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

bracket and then select the first column that you want to extract this is employee ID close this square

bracket colum and then open one more square bracket and and then select the salary column as the last

one close all these square brackets and essentially what you're saying here is if you look closely you're saying in the

staff table I want to start with employee ID column and then I want to go up to the salary column so you want

every column in between from employee ID to salary and once you say it like that this filter function is just going to

give you those six columns instead of every everything so this is a much better syntax than the choose columns

one because our columns are continuous and they go from 1 to 6 you could use the same approach when you have 3 to 7

or 6 to9 or something like that as well time for another challenge while looking at the female

employees with more than 100K salary or whatever was the f67 value if you also want to add one more condition that says

those employees must have joined our organization in in the year 2020 or after so if you look at the data table

here we have got a start date as well and essentially we are looking at employees who are with us in these two

years alone so we don't want to pay attention to this two years how would you do that give it a try and let me

know in the comments now let's go to the next one which is I want to look at the lowest highest and top five salary

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

functions all these three functions min max and large are available in most versions of excel but the sort and take

functions are only available in Excel 365 let's take a look at them so here we would like to first try this for all

employees and then eventually do this for male and female as well so if I just want to get the lowest value across all

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

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 cells and you can use this key or you can also press control Shift 4 for the

highest salary the logic is similar we just use the max function Max of Staff table salary column and that's going to

come up with the value as the value is too big for the cell Excel is showing me these hashes let's make this column wide

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

we can use the large function we can say large staff table salary

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

120,000 and in the next cell we write the exact same formula large staff table salary but this time we say two