🔥Microsoft Excel Tutorial For Beginners 2024 Microsoft Excel Full Course Excel Simplilearn

hey everyone welcome to simply's YouTube
channel today we will learn the Excel
full course this full course will cover
fundamentals of Excel data visualization
pivot tables to all the advance
functions in Excel like the lookup
functions AI with Excel and many more
that's it if these are the type of
videos you'd like to watch then hit that
like And subscribe buttons and the bell
icon to get notified craving a career
upgrade subscribe like and comment
below dive into the link in the
description to FasTrack your Ambitions
whether you're making a switch or aiming
higher simply learn has your
back just for a quick info if you want
to upskill yourself master data
analytics and land your dream job or
grow in your career then you must
exposing clars cohort of various data
analytics programs simply learn offers a
data analytics postgraduate program from
P University in collaboration with IBM
through this program you will gain
knowledge and work ready expertise in
skills like prescriptive and Predictive
Analytics regression classification and
over a dozen others that's not all you
also get the opportunity to work on
multiple projects and learn from
industry experts in top to product
companies and academic from top
universities after completing these
courses thousands of Learners have
transition into a data analytics role as
a fresher or moved on to a higher paying
job profile if you are passionate about
making your career in this field then
make sure to check out the link in the
pin comment and description box below to
find a data analytics program that fits
your experience and areas of Interest
now without further delay let's get
started Microsoft Excel so Microsoft
Excel is a software product designed and
developed by Microsoft is storing data
in an organized way that is rows and
columns and Microsoft Excel is also
capable to manipulate data through some
mathematical
operations followed by that Microsoft
Excel is also used to extract the
insights from the data and represent it
in the form of visually appealing graphs
and charts now we have a basic
understanding or an overview of what a
Microsoft Excel software product is now
moving ahead we will understand the
fundamentals of Microsoft Excel so
following are some fundamentals that you
need to know before getting started with
Microsoft Excel so basically when you
install the Microsoft Office in your
computer you will have various Microsoft
products out of which Microsoft Excel is
one of the product so we will be dealing
exactly with that particular product
that is Microsoft Excel so when you get
started with Microsoft Excel this is
what you will see in the first page so
this particular page is called as
Microsoft Excel homepage where you will
be having various varieties of sheets
Microsoft Excel will give you some
suggestions based on the type of sheet
you want to work with we will see this
in a better way through the Practical
session so once you get started with the
sheet you will have some more options so
this particular option is called as the
toolbar menu you will have the file home
insert draw page layout formulas data
review View and help so these are the
tools that you will be using to work on
your data using Microsoft Excel
furthermore we have a toolbar ribbon so
when you select some or the other option
from the file home insert draw page
layout formulas data riew View and help
buttons you will have a ribbon so for
example you can see that I have selected
the Home Tool here so when I press on
the Home Tool this is the ribbon which
Microsoft Excel gives me so this ribbon
has some options in it which can perform
various operations now in a further more
detailed way we will have toolbar groups
so when you see in the previous slide we
have a complete toolbar ribbon so this
particular ribbon is segmented into
groups so you can see the first group as
paste Cut Copy format painter etc etc
and the second group is the font the
size of the font and to increase the
size of the font to decrease the size of
the font bold italic underline etc etc
and here you can see the text alignment
so each and every group has separate
functions so each set is called as a
group and I think you can see a small
Arrow option over here so this Arrow
option is used into the toolbar groups
when the group is not able to fit all
the operations or all the
functionalities in one one single
provided section so when you click on
this particular Arrow Mark you will have
another dialogue box so this is called
toolbar more options so you can see that
when I clicked on this icon you can see
a new dialogue box which opens me a new
set of operations which are not able to
be fit in this particular group so we
will also see more about this in a
better way in the Practical session now
moving forward we have cell and address
so when you open a Microsoft Excel sheet
you can find boxes so each every box is
named as a cell and each cell has its
own address for example the highlighted
cell over here has an address B3 so B is
the column name and three is the rule
name and apart from that you can have
the sheet tracker in the bottom left
corner of the Excel sheet where you can
navigate through different sheets and in
the bottom right corner you have an
option of increasing or decreasing the
sheet size so these are the basic
fundamentals of Microsoft Excel that you
need to keep in mind before getting
started started so we will have more on
this in the Practical session if getting
your learning started is half the battle
what if you could do that for free visit
skillup by simply learn click on the
link in the description to know more so
now we enter the demo inventory in
Microsoft Excel so we will be using
Microsoft Excel to create a sheet of the
employees in a company so basically an
employee in a company has employee ID
name and designation salary etc etc so
we will be trying to create the same
table using Microsoft Excel but before
that let us understand the fundamentals
of Microsoft Excel through the Practical
demo first so I have started my
Microsoft Excel and this is how the
homepage of Microsoft Excel looks like
so you have a blank workbook over here
if you want to create a new workbook you
can select new Option so Excel will
provide you with various variety of
sheets you can see money in Excel
adjustable meeting agenda streaming
showers small business cash flow and
many more
if you're not able to find what you're
looking for then you always have an
option of selecting the particular type
of sheet what you're looking for so you
have various options if it's business if
it's personal if it's planners and
trackers list budgets charts etc etc so
let's imagine that you wanted something
from business so just by clicking at
business option the Excel will load a
variety of sheets related to business
options so this might take a while so
you can see that the Excel is loading
few types of
sheets so you can see that Excel has
provided us with some online varieties
of sheets for example any calendar
business expenses Channel marketing
budget budget summary report blue
product list etc etc you can see
construction proposal enro you name it
Excel has got it so Excel will provide
you with some variety of options based
on your requirement now for this session
let's get started with your plank
workbook which looks something like this
since this tutorial is based on the
fundamentals we'll go with the blank
workbook now over here you can see the
toolbar that we discussed earlier that
has the file home insert draw page
layout formulas data review View and
help so this is the toolbar and under
the toolbar I have selected home and you
can see this is the particular ribbon
what we discussed about this particular
ribbon belongs to homepage and when you
select file option you'll get back to
home and if you select insert option
you'll have another different ribbon
with different groups etc etc so every
particular tool has different ribbons in
them and remember the extra options that
we discussed when you press over this
Arrow key this is it so when you press
the arrow key you'll have few more
settings which cannot be fit in this
particular section of groups so you can
have some variety of options over here
of changing the font changing some
effects to the text font size and font
style etc etc apart from this we have
also discussed about the cells in every
sheet so this particular cell has an
address so you can see the address over
here which is B3 so B happens to be the
column name and three happens to be the
room name now we also had a discussion
about the sheet tracker right now we
just have one sheet if you want multiple
sheets you can just feel free to select
on the plus option which will always
create you some extra sheets and you can
navigate through sheets just by pressing
on the sheet
name and when you get back to the bottom
right corner you have an option of
increasing and decreasing the cell size
or the sheet size now let's keep it
default with 100% now these are the few
fundamentals that you need need to keep
in mind before getting started with
Microsoft Excel now that we know the
fundamentals of Microsoft Excel let's
get started with a practical session
which is about the employe details in a
company now let's select this particular
cell and let's type in employee
details yeah we have the cell
now an employee details table will have
the information related to employees so
the information will be about name it
will be about employee
number it will be the
designation and maybe
salary and maybe blood group as well
and uh let's take another one which is
phone
number
yeah so so far so good and uh you can
see that we have some problem with this
particular column the designation the
name uh the the name of the designation
is practically good but it is not
visible so when you uh when you're not
on that particular cell you can see that
the name is incomplete over over here so
you can always fix that you can just you
know manually change the size of the row
or cell or you can also feel free to you
know double click on that cell which
will automatically you know set the size
of that particular cell and same goes to
the blood group just let's try to double
click on that and same goes to phone
number
great so you can see that the employ
details are just confined to the first
two cells it's supposed to be somewhere
in the middle right so no problem we can
do that as well we can select all the
cells and we have an option of merging
them you can just select this one which
will help you with merging and centering
that particular data to the center part
so that's how we do it now let's get
started by adding the names of the
employees uh let's add the names
Joe
John uh Mary mark
Susan and
then
Jennifer let's type in
Mike let's type in
Tim Jeff
Jeffrey yeah we have a couple of
employees now let's type in the empo
numbers for
yeah we have the employee numbers now
let's type in the designation okay let's
choose uh Joe to be the CEO of the
company and John as the software
developer and maras
tester Mark and finance
Susan also in
finance and Jennifer in
testing and Mike in uh
marketing same goes for Tim
and again Jeffrey into software
development and
uh morgin into testing
again now let's click on C so that it
gets you know resized according to the
length of the text now it's done so
let's get into
salary uh
$10,000 and
$115,000
and
$119,000 let's increase the salary of a
CEO so let's keep it $1
lakh and finance again 20,000
so yeah the salaries are alocated again
the blood group
yeah we have the blood groups now let's
type in the mobile numbers
[Music]
yeah now we have typed in some random
mobile numbers as well so uh yeah this
is how you can add in some data into
your table and now let us imagine that
you forgot to add or remove a column so
let's imagine that we wanted to add a
serial number as as well but somehow we
forgot to add it now you can always add
a new row or column for example here we
wanted to add a new column so we just
have to right click on a select the
insert option here we have a new row now
now let's type in serial
number and let's type
one now can you see the small box option
over here if you just drag it you can
you know copy paste all those over here
and now let's right
click and fill series now we have the
employee number starting from 1 to 10
that's how you do it and apart from this
you you can also uh you know change the
font of the entire row you can change
the font to say uh aroni and you can
always also change the font and same
goes to the employee table you can
select it bold it and you can also
increase the size size and again select
a color for the text maybe a different
color green would be
better and uh you can also select the
entire cells and align them to the
center looks more good and you can
select or double click the row names so
you'll have the proper spacing between
all the rows and columns so we have
double click on the column right yeah so
basically that's how you make things
happen now let's save this so so I'd
like to go to the save option and uh EMP
data let me save this in my local
location and just save it's done so
that's how you work on your Excel file
with some basic data and to learn more
don't forget to get subscribed to Simply
learns YouTube channel and don't forget
to hit that Bell icon to stay updated so
you are the first person to get an
update on any techn techology not just
Excel now let's get back to the
Practical mode and now we are on our
Excel spreadsheet so here we have some
text boxes on the column A and on column
B we have lower and upper okay so um we
have a mix of uh uppercase and lower
case in our text so first we'll try to
convert them to lower case and then
we'll try to convert the entire text
into uppercase so for that you need to
press on equal symbol and select the
lower formula tap and you selected the
function and now click on the cell A3
that's your cell address and press on
okay now all the letters are converted
to low case so we had J in the uppercase
which is converted to lower case now all
you can do is drag the formula to all
the cells and it will be applied to all
the cells right now let's similarly try
to convert into uppercase and tab space
and there you go you have selected the
formula now the cell address
press
enter and drag the same address I mean
drag the same formula to all the cell
addresses and the echo all the cells are
converted to uppercase now that's how
you convert the cells or data in Excel
to uppercase or lowercase in Excel now
we are on the Excel
spreadsheet now here we have some sales
data based on a store now what we going
to do is we going to add some multiple
rows or a single Row in this particular
data so here you can see the region wise
category Wise statewise subcategory Wise
and the sales and quantities right now
let us imagine that you must have to add
a sale data here right so let us imagine
that on the row 7th you were supposed to
add some detail for example some
technology related sale happened on that
day and you missed out to add it right
so how do you create a new rule now so
for example if multiple sales happened
on the same day like 3 to four and you
missed out them so how do I add three to
four rows in between the existing data
right so don't worry about it it's
completely simple all you have to do is
select the row and right click and press
on the option insert now this will
insert one single row if you wanted to
add one single uh row or you know toule
and if you wanted to add multiple rows
for example you wanted to add three rows
all together then it's also simple all
you have to do is just select three
rows and right click and press on insert
and then you have three rows so there
you go so that's how you add multiple
rows in a Excel spreadsheet or that's
how you add one single Row in an Excel
spreadsheet now we have started our
Excel worksheet and here you can see we
have employ details on our worksheet now
all of a sudden uh you wanted to add
employee second name as well or a L me
right so you are a beginner and you
don't know how to add a column or you
know you might be a little ambiguous
right so instead of using this simple
step you might end up creating a whole
new sheet or maybe other approaches as
well which are timec consuming so let me
give you this simple step all you have
to do is select the column so here you
wanted to add a last name right so B is
your column with the first name and here
you wanted to add a second name or the
last name which is in between theum
colums B and C so remember that Excel
adds a column always towards the left
side of a column so here if if you
wanted to add a column next to V then
you want to select the column C and
right click and select the insert option
so that it can be added in between B and
C which is adjacent to B so let's look
at it right so you have added the next
uh column or a new column right next to
the column V and it's in between the
designation and employee name now you
can add your you know second name so
that's how you create a new column in
Excel now in case if you wanted to you
know create multiple columns all
together so uh here we have or here we
needed a second name also here you have
a designation and you wanted to add
another column which you know assigns
manages to the employees right so let's
delete it uh Delete the second column
and let me explain you how what I want
to do so here you wanted to add the
second name of the employee and also the
manager of these employees right so here
you wanted to add two columns Al
together right uh and now you might be
wondering could I add two columns
together uh or will it work only for the
one column thing right no it works for
two columns and also more than two
columns altogether so you wanted to add
two columns here right so select the two
columns you you can select or you can
click on on the column C and hold that
left key and drag it along the right
side okay so here I have selected two
columns and here it's three right so you
can see here uh it's 1 0 48 5 676 R
cross 3C that means you have selected
three columns if I navigate on the four
you can see we have selected four
columns right now right click and select
on insert and you'll have four four
columns in between employee name and
designation now you might have to add
your second name manager's name manages
employee ID you know and the department
everything or you can add anything or
any number of columns in between the
existing columns now we are on our Excel
spreadsheet now you might want to you
know add uh some extension to your data
since we have sales here you might want
to add the dollar logo right so if you
have like five to 10 r rows then it's
easy but if you have multiple rows then
it might be a little time consuming in
such scenarios you might want to select
the entire column right so how do you do
that in a shortest method so there are
multiple methods to do it so the easiest
one the first and the easiest one is
clicking on the column number or column
name so here we have the column name
that is e and if you click on it the
entire column is selected and here we
have numbers so let us imagine that you
wanted to select the row number two
click on number two and you have all the
rows I mean the entire row selected all
the cells are
selected right so this is one way and
again there is another way where you can
just you know uh click on the first
table header and press control spacebar
so the combination key is control space
bar for columns and if you want to
select the entire row it is shift space
bar so this is the method two and
another method so you might have a doubt
here right so when you click on the
column name or when you click on the row
number the entire row or entire column
gets selected right and you wanted to
select only the cells which have data so
we have another way for that so you can
select the cell now hold the control and
shift key and press the lower Arrow key
to select the entire column with cells
having data so there you go you have
selected all the cells in one single
column with only data and all the cells
which do not have data are not selected
same applies to rows as well control
shift right arrow key or left Arrow key
based on your rows and all the rows with
data will be selected I mean all the
cells in a row are selected not the
entire row and U coming back to the
first question that is adding the dollar
symbol so here we have General so go to
the data type and add currency so we
have rupees here so currently we are in
India so we have rupees now what is the
advantage of selecting the entire row
when you don't have any data in the 57th
row or the next cell right I'll show you
now let us add the number 100 and press
enter there you go let us add another
number like th000 and press enter so the
data formatting is automatically applied
to all the cells so most of the time
your cells or the data sheet will be
varying you might have to add or remove
elements or numers right so in such
scenarios this will be helpful now
comparing two different columns or
multiple columns happens to be an
important job when you're working in
data analytics as you have to come up
with some decisive decisions based on
the data now if you had to do it
manually then you might end up taking
hours or even days based on the data set
you're working with but if I say that
you can do it within minutes then it
would be interesting to work with right
now we'll be doing the same now we'll
work on a sample data set right on my
screen so here we have column 1 and
column two now our job is to compare the
column 1 and column two and come up with
the result now the first and the
simplest way to do it is use the
built-in condition formatting which
comes with Excel by default now all you
have to do is select all the data and
navigate to home and in the home go to
Styles group and select conditional
formatting and in conditional formatting
you can see highlight cells so in that
you can use duplicate values when you
click on duplicate values a small pop-up
window will come on your screen and here
you have an option of choosing whether
duplicate or unique so duplicate means
you comparing the cells and you can see
there are some duplicate cells which are
present in column one are also present
in column two now you can also check how
to uh you know find out the unique
values which are only present in column
1 but not in column two so you can just
press unique and there you go you can
find it now you can also try to you know
change the color by filling with green
for The Unique columns and duplicate
cells with color red now this was the
first method and you can also work out
some different ones like uh you can just
directly uh press equal to and select
the cell and equals to and the next cell
press enter and if there is a match it
will will give you true if there is no
match then it will give you false now
you can drag it down and see which all
are matching and which all are not now
you can also make some minute
modifications to this so uh if in case
you didn't find the data then you can
say not found so for that uh you can try
if and inside brackets and then you can
give the value as not found if true it's
found first you have to give the True
Values so for true you can write it as
found and in case if it's not found then
you can write it down as not
F there you go close the bracket press
enter and there you go if there is a
perfect match you will have found and if
there is no match then you'll get not
found now so far we have discussed uh
comparing two cells using conditional
formatting and also by using equals to
operator and trying to uh add some
tweaks to the equals to Operator by
involving with if operator and apart
from those we also have another way to
compare two columns in Excel so another
way is using the lookup functions so
we'll use a simple V lookup function to
compare both the cells so for that let's
type in equals to V
lookup space and um select the cell you
want to check for and then the range of
elements you want to uh compare and then
I'll press F4 to log
them now uh you want the data from
column
one and now you want the exact match so
zero press enter so there you go so the
elements which are present in column 1
and column 2 are been displayed here so
what case if the elements are not found
you'll get an error so let's look at
that simply drag the formula to all the
cells and there you go so the elements
which are matching will give you the
proper result but the ones which are not
matching will give you not applicable or
error message this can be fixed we can
make some minor tweaks to the same
formula so we can add if error
comma simply write an as not
found and close the bracket so that
should do so we have closed the bracket
and press enter there you go now simply
drag this and soon you can see the data
which is having no match is shown as not
found now uh there is another
possibility okay let's get back to
another different sheet so this should
work so uh we we already have the
formula so let's erase that first I'll
erase the whole
data and even this
one yeah now we have the clear column
for so here you can see we have Ford
India and here you can see we have just
for and similarly here you have Mahendra
and Mahendra and here you have Mahendra
right so in some situations you might
have to compare two different columns
but the names might be a little
different right so for example uh if
you're working with Oracle and if you're
working with Oracle America and you have
in the first case is Oracle and in the
second column is Oracle America then
those are one and same but you have some
minor text changes right just like here
you have for India and here you have
Ford so what if you had such kind of
issues so you can also make some minor
tweaks to it so using the same lookup
formula you can add equals to we
lookup and also the comparision cell
which is this one and you're not you're
not stopping them you're trying to add
the wild card here so the wild card is
HRI which means if there is anything
like uh if you get the comparison
between the first cell and the second
cell which matches and if there is
anything extra rather than the actual
cell please try to consider it so that's
what we going to do so after entering
entering the wild card symbol you'll
select the range of columns which you
have to uh you know fetch the data from
and then try to fix them using function
key F4
you're looking from the column number
one and then you're finding the exact
match now you can close it and
enter now let's try to drag
it there you go so you have the data
Mahindra and Mahindra hindai India Honda
India so that's how you try to use uh
comparing cells in Excel now we can see
I'm on my Excel worksheet and I have one
table on my worksheet right now
so you can you know transposing or you
know converting rows to columns is a
very simple task I'll explain you in two
different ways so the first way select
all the sales that you have on your
sheet and press on contrl C so it will
give you the copy option and then you
can also paste in the same sheet or if
you want you can go to a whole new sheet
and select the paste option over here
but before selecting the actual paste
option you can see a small drop- down
icon click on that and navigate to paste
special and in this you can see
transpose option click on that or you
know make sure that it's checked and
press okay now you can see all your
columns are been converted into rows and
all your rows are converted into columns
so this was the first way and uh what is
the second way you ask me so it's really
simple select one cell somewhere
and you know you can or let's create a
new sheet as well so here you can you
know write down the formula as
transpose and select the array which you
want to transpose so this is the
array so transpose is basically an array
function in Excel so once you are done
with you know selecting your data press
enter there you go so all your rows are
now columns and all your columns are now
the r
now on my spreadsheet you can see some
data which is the sales data of
quarterly basis and the four different
zones that is northwest southeast so you
can you know make things look better by
hiding these columns and if you hide
these columns it's critical for the end
user to identify which columns are
exactly hidden right to make things a
little easier for your end user you can
select the columns and then go to the
data option and in the data option click
on the group option so this will group
these columns and you will have the
symbol of minimization which will help
you hide these columns automatically and
unhide these columns automatically now
let's also try to group GH J
columns and there you go now similarly
let's also try to group the rows of
North and row of
West then you have have the rows of
South and rows of
East right now all these rows and
columns are been grouped now all you can
do is just click on the minimize button
and those will be minimized similarly
the rows right and to unhide them all
you can do is click on the plus icon and
they will be
unhidden you can also try to ungroup the
columns and rows all you have to to do
is select the columns and click on the
ungroup option similarly the rows as
well and that's how you can group and
ungroup rows and columns in Excel now we
are on our spreadsheet and you can see I
have some sample data on my spreadsheet
and it does have some blank rows now how
do I eliminate these blank rows so one
step is you can you know select the
entire row and try to right click and
delete
it again this sort of method might take
hours to get rid of all the blank rows
and most of the time you might end up
even selecting the rows with data in it
right so if I want to select multiple
rows then I'll be selecting some rows
with data as well which might be a
little Troublesome in the future now
let's try to use the easier way now for
that go to the find and select option
select the
go to special option and in that select
the blanks option and press okay now
hold control key and press minus key and
you will have an option of deleting the
cells now select the entire row now we
are selecting the entire row with blank
data and press okay and the cells will
be shifted now this was simple but let
me show you another example so even here
we have some blank rowes but in between
those blank rows for example take cell
17 or cell d17 or row 17 we have some
data testing and here in the employee ID
we have some data in
a26 and again in b39 we have some data
that is first
name right in such scenarios how do I
eliminate data so we have a simple Logic
for it so all we need to do is add
another row which is to count the number
of cells in the row now that can be
count now here equals to the function is
Count
a let's
Tab and the cells will be from A2 to
G2 press enter now you have seven so now
we are basically calculating the number
number of cells so if you apply the same
to the
entire um data set let's drag
it and there you go so there are certain
places where you find zero which are
basically the empty columns right now
let's do some formatting if you
need the let's try to add
filters crl a control D and now table
will have filters and
headers now here you can see the drop-
down icon click on that now instead of
select all what you can do is Select
zero now you will be highlighted with
all the rows which do not have any data
select all those and press on delete or
what you can do is
the same way control
minus there you go
now clear the
filter now all you have is the data
without any kind of blank rows so all
the rows that had some amount of data
retrained and all the data or all the
cells or all the rows which do not have
any data which are completely blank they
have been eliminated so here you can see
columns and rows so especially the
fourth row and the column C right so uh
currently we have data on our uh
spreadsheet and it is exceeding the
number of rows currently visible on our
screen right so when you scroll down you
can see that the header or the headers
of all the columns are getting vanished
or scrolled up along with the data right
and you might want to keep that so that
you'll have reference to every single
aspect of or every single cell data
you're referring to right so for that
reason you might want to freeze this and
also you might have a doubt what if uh
even even I had this data in the column
section right and what if I had to
freeze that and you know when when you
scroll this and keep that data uh set on
the column as it is right so we can also
do that let's look at both of them so
there is a simple process to do that all
you have to do is select the cell or for
which you want to uh you know set the or
freeze the column adjacent to it or the
row adjacent to it right so currently
you want to let me expand it so
currently I want to freeze this
particular row right and this particular
column so the cell I would be selecting
is the first cell here that is
D5 right so when I select this
particular C I can be able to freeze
this particular row and this particular
column so now you need to navigate to
the toolbar and then select the view
menu and inside view option you can see
there is an option of freeze pains right
so click on the drop down so you can see
three different options as we discussed
earlier we can either choose to freeze
an entire row that is the second option
or you can freeze the entire column that
is the third option or you can freeze
both that is the first option right
freezing rows and columns so let's
select this one and see if our rows and
columns are frozen or not right so now
let's scroll down and you can see our
row is successfully Frozen there right
and similarly scroll towards right and
you can see our column has been
successfully Frozen right so this is how
you freeze rows and columns or you can
freeze panes in Excel now here on my
sheet you can see we have some numbers
that is $10 or
$10.20 $22 $ 53.1 to $12 and $110 now
the task is to convert these numbers
into word format that is representing
the numbers alphabetically right this
might be a little curious and we also
might end up searching for a predefined
method which is not readily available in
Excel but there is a way where you can
create a function using Excel VBA or
macros now again creating a macro is
completely complex now identifying the
trouble Microsoft has already come up
with a readily available macro code on
their official website which will
support the various versions of
Microsoft Excel starting from Microsoft
Excel 365 to all the way up to the older
Excel version that is 2010 now you can
scroll down and use this particular
macro function which mcro Microsoft has
readily made us available and use this
to create our own macro in Excel VBA now
let's get back to Excel and you can
create a macro using the developer
option now developer options will not be
readily available in Microsoft Excel to
activate developer option go to home and
go to the options menu in the
last and here go to customize ribbon and
here in the second drw down menu you can
see developer option by default it will
not be checked click on the check option
to activate developer options and press
okay now you have your developer options
ready click on developer options click
on Visual Basic and here to insert a new
macro click on the insert button click
on module and a new macro window is
readily available for you now control V
to paste your code and your function is
with the name number to text right now
close the
matro and here your function will be
readily
available equals to and the function
name was number to text press tab to
select the function and select the cell
where you have the data close the
function press enter now drag the same
formula across all the cells and here
you have the text converted from numbers
so here we have $10.20 and you have $102
$22 and no C
$532 $12 and no cents and $110 with no
sense now that's how you can convert
numbers to words using the macro which
is predefined for excel in the Microsoft
website so for example uh let us imagine
that you wanted to create one single
cell for all the employees in your
company which does the first name second
name or last name including the email
IDs at one place right if you want to do
so like let us let us let me show you
how it looks like so let me copy this
here then that's the first name now it's
your second
name along with the email ID now let us
imagine that you wanted to store all the
employees in the same way right so you
can do this by using combine operation
in Excel now all it takes is just a
simple formula that is equals to and the
first cell
address that is
A2 and you can use an ENT
sign now you can use the next cell
reference that as
B2 and then colon and the last C address
that is E2 now press enter
so when you press enter or tab you will
get this particular answer but this data
is completely uh in a state where you
can't understand it right the first name
last name and the email ID has got
combined all together so you want to use
some spaces right so all you need to do
is just make a little modification to
this particular existing data so you can
press another ENT and include
spaces and then you can separate the
email ID and the name with some
colon so now we have separated the first
two cells that is the first name and the
last name with the space using the ense
and then again we have used an ENT to
include the colon and see that we have
also included a space between the colon
marks that it gets separated cleanly and
now let's try to press Tab and see the
result so there you go the first name
and the second name is separated by
space and the email ID and the complete
name is separated by a colon now you can
use this to all the
celles and there you go all the first
name last name and the email IDs have
been printed here so that's how you use
the combin operation in Excel and you
can see on my screen we have the sales
data of one single employee in the month
of January okay so this is the sales
data of John in January sales now let us
imagine that we have the same person's
or same John's data of sales of all the
three months okay so here we have the
sales data of John for January February
and March and you wanted to Club all
these 3 months and write it down as
quarter 1 right so let us try doing
it now you can see that uh only Janu is
replaced as quter 1 and February and
March are still uh remaining the same so
let us try to delete them so we have
deleted them and I think now it looks
like uh this is a sales data of one
single quarter that is quar one but
again qu one is under column B and
anyone who looks at it feels like this
data is of January February and March
and all the quarter one details are only
present in V column but C and D are
completely you know different from this
one right so you don't want that kind of
an impression on your uh you know the
one who presenting to you so in those
situations you want to merge all these
three columns and make sure that the
quarter one is present somewhere in
between the cells so that all the data
looks relevant and uh it it defines that
these data elements belong to quoto one
right so there is an option in Excel to
do that so select all the cells that you
want to merge and going to home button
and in home button get into the
alignment group and select merch and
Center so you can do it directly also
you have a couple of more options that
is merge and center merge across merge
sales and unmerge so we'll go through it
one by one so first we'll go through
merge and center so there you go the CES
C merged and it has been aligned into
center now again if your manager or
anyone Superior to you wants this to be
you know independent like they want it
to be in terms of January February and
March so that they can have access to it
in month-wise so you want to unmar it
back right how to do that now so the
same menu and select unmerge and there
you go you have your data back in the
original form again all you have to do
is you know rename a few
cells there you go and you have it back
right so in a few situations okay you
can also add the BS so that it looks
more relevant now um you know now let's
try to mares again and margin Center
yeah now again uh let us imagine that
you are going through an appraisal cycle
and you also wanted to add some comments
about the sales happening in your
company uh regarding uh all the
employees right so also we have that
here now let me expand a little
bit yeah there so again let's Marg and
center now uh you want it to you know uh
you wanted to take a review of your
level one level two and level three
managers so you have all the three um
rows here but you can see the cells are
not merged and uh you wanted to merge
every single row so merging and
centering everything rowwise would be a
little time consuming I mean three
columns is okay but what if you had like
uh you had to take a review from a
couple of more manages like 5 to 10
right or imagine you wanted to write
some random comments apart from review
you wanted to write some random comments
based on month-wise uh sales or
something like that so it would be time
consuming right so apart from that you
have another option where you you know
select all the sales and come over here
and select the option of merge across
and all the sales will be merged all
together at once and you will be having
those individual rows where you can
write down your reviews like review one
and so on
review two right so this is how the
option of merch across works and we
already have been through the merge and
center and merge is completely similar
to merge and center so this is how merge
and center merge celles and uh merge
across and unmerge cells and Excel now
there are multiple ways to add day to
Excel spreadsheet for example equals to
today this is a shortcut method and you
can enter done you will have today state
but again there is some problem with
this kind of approach so today's date is
4th of September 2022 now if you open
the same spreadsheet after today that is
tomorrow or day after tomorrow then you
will have a concurrent date that is 5th
of September or 6th of September
right the date will always vary or get
updated according to time now you don't
want to change that or you don't want
that to be happening with employee state
of joining right it should be stagnant
it should not vary right so there is
another shortcut way apart from using
the datetime functions there is a
keyboard shortcut where you can add date
to excel that is control semicolon so
this is the shortcut way to add date to
your Excel spreadsheet and this
particular date will not change this
will remain the same constant date
throughout the time now there is a
chance where you might also have to add
time to your spreadsheet that is date of
joining and time of joining right so
there is another simple shortcut where
you can add time by just pressing three
keys that is holding control holding
shift and pressing semicolon then you'll
have the time so this is how you can add
date to your Excel spreadsheet and add
time to your Excel spreadsheet and this
date and time will remain constant it
will not vary like the today function
that we have used here so you can see on
my screen we are on the Excel
spreadsheet and on my spreadsheet you
can see dates in column A and different
formats in column C D E and F
respectively Now by default the data
type or the data present in the a column
will be considered as general and there
are a few situations where even if you
try to format the date in your format
like you know the customized format you
may not be able to do it because it's
still under the general data type so for
that for being on the safer site what we
will do is select the data and navigate
to the data toolbar and in that you have
the text to columns option press that
and remember to have the delimited icon
you know you might be having the dash as
your icon or slash as your icon for the
delimited thing and select that and next
next and here you can see right it's in
general mode so you can press on date
and DMI will be your date format that is
date month and year now click on finish
so it's been formatted now now let's
copy the same data and paste it in all
the four formats so that we'll see the
different formats that we can change to
or customize to right so there you go
let's expand the columns a little bit
now we have day month and year so now
you might want to change a couple of
things right for example you wanted to
change okay let's see the menu so the
way is to select the CES and right click
and select the format sales option and
in here you can see the date function
and then here you can see different
types of modifications that you can do
to your data right
so you might want to also change your
dates based on the location so right now
we are in India and imagine if you
wanted to you know change something
based on us or if you if you're having
your client in us and he wants the dates
in US format you can also change that so
here you can see English United States
and press on that press okay and you can
see it's been changed to us format where
you have ears in the first place months
in the second place and dates in the
last place right and now let us imagine
that uh your client does not want the
dates all he needs is just months right
you can also do that get back to the
format sales option go to custom and
here instead of uh you know uh month
date and year all you can do is just
write down month and year and select
okay so you'll have only month and year
not the dates now your client is being a
little more you know he wants a little
more detail and he wants you to add a
lot of granularity to your data which
includes day time Etc everything right
so you can also do that so the same way
go to format sales custom and here I
think it's in date itself so let's
navigate a little yeah here so you have
the day date month and year press okay
so there you go right so that's how you
can do it and now let's try a little bit
more if we have a few more options
pending so go to format
cells and C
system and you also have
timings so here you have date month year
and timings press okay and there you
have all the timings as well so since
these dates are you know I created them
just a few moments ago to keep it simple
so I did not have to add the diamonds
here so if you are Keen about adding the
timings you can also do that for example
if you wanted to add the timings of the
employees logged in and logged you can
also do that using the same format and
you can see now we are on our Excel
spreadsheet and you can see we have a
column of dates and on the column be we
have age now how do you calculate the
age using the date of birth so this is
completely simple all you have to use is
the dated IF function or if you also
call it as date diff function based on
your choice we also have a specific
entire tutorial based on Dat diff and
Excel for further information that video
will be linked in the description box
below you can go through it or you can
use the end screens and I cards link to
this video to get back to the tutorial
based on date diff in Excel now let's
calculate the age of these dates or
these people with these date of births
so all you have to do is type equals to
and type dated
F and open a bracket we need three
parameters here that is the date of
birth that is the first parameter now
the second one is the today function
because you're calculating the age of
these people as per today right so we
have a today function in Excel press on
Tab to select it and give a close
bracket now comma and if you wanted to
calculate the age in terms of days you
have to um you know give d as the
input yeah here it should be D as the
input and don't forget to use the double
codes here and if you use the single
codes there might be an error so use
double codes if you want it in the Days
part you can give days and if you want
it in months you can give months for now
we want in ear so we will be giving y as
the third parameter and press enter
there you go you have the age here in 26
years you can drag the same function and
you have the ages here as you wanted
right so this is how you calculate age
in Excel and on my Excel spreadsheet let
me expand
it we have have two different timings
that is in and out so here we are
considering a scenario where we are
calculating the total number of hours
worked by an employee in an organization
so the end time will be the time where
the employee logged in and the out time
will be the time when the employ logged
out so here we have made some minor cell
formatting so when you right click on
any of the cell you can get back to the
formatting sales option and in that
option we have selected the AM PM so
there is also another option of choosing
the 24hour timing that is
1320 uh 13:30 so this represents the
24hour
format but since we are you know working
on Office timings let's keep it as Am
Pm okay so there you go so we have our
timings of in and out now to calculate
the time difference between the in time
and out time so this particular
calculation is completely simple all you
have to do is press equals to select out
time minus select in time so there you
go press enter and you have the total
number of asks the employee has been
working in your organization so here
also we have made some minor cell
formatting so usually it comes out as Am
Pm right but instead of that we have
made a minor cell formatting we have
navigated into time and we have selected
the 24-hour formatting so that we have
just RS in our output so that's how we
have calculated the time difference
between two in times and out times of an
employee using Excel now Dax and Excel
is a sophisticated formulas type
language that comes in handy while
working with relational data and
extracting information via Dynamic
aggregation functions Dax in Excel
stands for data analysis Expressions Dax
functions are completely familiar to the
general and default functions that are
available in
Excel now Dax allows users to perform
slightly escalated and advanced and
custom calculations upon various data
types like character data date time time
intelligence functions and manyo and in
Dax there are a variety of functions
such as Dax table valued functions Dax
filter functions Dax aggregation
functions time intelligence functions
date and time information time logical
parent and many more now in today's
session we will look into one such type
of Dax function in Excel that is Dax
date and time function in Exel Excel now
without further Ado let's get into
practical
mode and we have started our Microsoft
Excel inside Excel we have opened a new
blank worksheet Now navigate to data
option in the toolbar and in that go to
the power pivot
window now we have entered the power
pivot window and inside that you can see
the option of get external data click on
that and you can see a right you of
options that is you can get the external
data from a database you can get from
web servers that is from data service
and you have also other forms of data
sources and existing connections for now
we will take other forms of data that is
Microsoft Excel data which is in our
local system select next now provide the
location of your folder just browse and
we will select Max employee data
open and there you go we have our data
connected to our powerpint window click
on next and there you go you have the
selected table and Views click finish
and you can see the data getting
imported into your power
window there you go by default if you
want to add any tax related functions
into your powerp window you'll have an
external added column over here which
looks something like this if there is
not an external column you can also so
add the column it's not a big trouble
now we have some data here that is the
employee ID employee name designation of
the employee Department of the employee
salary and joining date Etc right let's
expand this View and see what's what's
more in
it so this column is the date of birth
and this column is the employee joining
date right now we have so much of data
now let us imagine that we want to
calculate the retirement age of all the
employ employes so now let's rename the
column to
retirement
date so we have our new column that is
retirement date now we will make use of
Dax date time functions to calculate the
age and find out the exact retirement
date of that particular employee so let
us imagine that every employee will
retire after the age of 65 so we have
our date of birth of every employee and
the joining date of every employee now
we'll come up with a Dax date and time
function and calculate the age and then
we will find the retirement date of that
employee now every Dax function starts
with the same method or the same way as
the simple Excel default method starts
that is by starting with an equals to
and then we will find
eate eate and
and now our first parameter that is the
start date that is the employee uh the
date where the employee joined the
organization so our sheet name is sheet
one now our joining date is in the F7
column select F7 press tab to select it
and comma Now how many months so each
and every year will have 12 months so 12
months in to the age that is 65 Star 65
and now you can close the uh function
and press enter and then you'll find the
retirement date of that particular
employee so there you go so we have the
employe dates that is Employee
Retirement dates for all the employees
so the first one that is Jack has the
retirement date of 19 February
2085 and similarly Jennifer has her
retirement date at 15th of August 2084
so that's how we use the Dax functions
in Excel so this is one of the several
Dax functions in Excel that is Dax date
and time functions in Excel so you can
see that I've got some data which is
first name of the employee last name of
the employe and phone number and let us
assume that uh your manager has given
you a task to identify all those uh
employees which did not give an phone
number so you wanted to identify them
using a check box right like you will be
providing a check for all the employees
that we have received the phone numbers
and you will not be checking the
employees which did not give the phone
number right so let's try to do that so
to include a checkbox you might want to
get into the developer option but for
few reasons Microsoft has disabled
developer option by default to enable
developer option all you have to do is
just navigate anywhere on the
ribbon here and right click and then you
can see customize the ribbon option when
you click into it you will be redirected
into the ribbon option and here you can
see the developer option on the right
side right this one so by default you
can see that your developer option is
not enabled to enable it you have to
check in it and there you go you have it
now so press okay and you'll have the
developer option now go to the developer
option and select the insert option and
there you can see the checkbox now draw
the checkbox
anywhere I'll draw it over here and
there you go you have the first check
box right now if you want you can even
customize
this so carefully click it
you can edit the
text and write it
as
done
okay now you can even copy this to all
the
cells just select and drag and and there
you go now you can go ahead and check
all the employees which you have
received the phone numbers and you can
eliminate the ones which did not provide
you with the phone
numbers that's how you can do it and
there's also another option where you
can use this to include it in a formula
since this data is only of 25 rows you
can manually check in the data and you
can leave the data which is you have not
received but what if you had 2500 right
then you might need formula so in such
scenarios you can use a formula so how
do you use that so for example uh if
this particular box is checked then you
will get a True Value here and if it's
not checked you will have a false value
then you can use that Boolean value to
include in your formula right sounds
interesting now let's try to implement
that you can right click the cell and
you can find format
control then you can see here you have
an option called cell link here select
the cell
link and press okay so here you can see
if you have checked this the value is
true and if you uncheck
this you can see the value turned false
now you can use this particular cell
reference for including this in a
formula now let us also check how to
include checkboxes on Google Sheets in
Google sheet it's really simple all you
have to do is get into the insert option
and you can see a readily available
checkbox you don't have to use any
developer options over here and there
you go you can just check now let us
assume that you're giving an important
presentation and you're also requested
by your manager to add the Excel
document that you have used to create
that particular report which would be
something like this when you click on it
it will open a new Excel document or
Excel Window with all the data in it
right so how do we do that let's look at
the demo now let's have a blank slide
and let's get back to the source where
our Excel file is located that is in my
documents so we are currently in my
documents and we have the Excel file
which we used for creating that
particular report now if we copy and try
to paste it on our Excel document it
would look something like
this and clearly it's not visible and
it's not accessible right so instead of
this there is another way where you can
directly add the Excel document in the
form of logo which is a rather simple
idea all you have to do is just click on
the insert option in the toolbar then
navigate to the text group and here you
will find the option called object click
on the object and it will give you a new
window where you can identify which type
of document or object you want to add
either create or create from file so we
have the file in our local system so
we'll choose create from file browse and
from browse we will go into the
documents section where we have our
Excel file click on the Excel file and
click on okay now if you want the data
or Excel file to be displayed in the
form of an icon then click on the
display as IC icon section and here you
can change the icon so you can choose
any one of the given options let's
choose this particular one and click on
okay another okay and there you go the
icon will be now displayed on your
Microsoft PowerPoint PPD which is
completely clickable as the previous one
whenever you click on this the Excel
document will be opened so that's how
you add Excel document into Microsoft
PowerPoint now we are on our Excel
spreadsheet and here I'm trying to
insert some images onto my spreadsheet
so here we have company logos and
Company brand names so since we already
have the brand names what we need to add
here is the brand logos okay so how do I
add such kind of an image into an Excel
spreadsheet that you might be thinking
that Excel spreadsheets are just for
data not multimedia image or files right
so no it's not it's completely wrong you
can also add your images onto your Excel
spreadsheets it's completely simple so
click anywhere on the Excel spreadsheet
navigate to the insert button select the
illustrations option from the addin
group click on that and you know in the
drop down you can see the pictures icon
or the pictures uh button click on
that and select from this device or if
you have an online stock image you can
take that or you can also check out from
online pictures for now I'll select the
device this
device and here I have some images the
images what we need are simply learn and
simply code logos press okay or insert
so here you have
them now you can also adjust the size of
the image using the small buttons
here as it will
here and
done so you might be wondering is it all
done is there anything more to do uh or
just adding an image is done no most of
the times when you create or you work
with Excel spreadsheet you will not just
you know add some data and add a
relevant image to it most of the time
you add some graphs or you most of the
time you'll add some charts uh to
showcase the data dashboard right so it
will be something like this so this is
how you add the image or graph onto your
uh uh dashboard and sometimes you might
not have have enough place to uh display
your entire dashboard during those
situations what you do is you try to
hide the row or column so now let's try
to hide this particular row let us
imagine that you wanted to show only uh
two uh charts and there is no space for
your third chart in such scenarios you
might want to hide or remove that third
chart okay so let's try to hide it and
see see if gets if it gets hidden or not
when you try to hide it you can see the
column A has no data and the row number
for has been hidden so we have three and
five but what about the image it is
still present right so if you want to
make sure that the row also hides the
image then you might have to do a little
formatting with it so select the image
go to picture
format or you can also right click and
and directly navigate to picture format
this opens up a set of options and here
you might want to go to the um third uh
one which is picture format third and
select the properties and here select
the option of move and size with sales
so when you select this when you try to
hide this it will also I mean the graph
of the image will also hide along with
the R so that's how you do it so how to
insert PDF in Excel now there are
multiple ways to insert a PDF into Excel
so we will go through a couple of ways
first is here let us imagine that we
wanted to insert some PowerPoint
presentation in PDF format into our
Excel sheet so that it belongs to excel
Basics now click on the cell where you
want to add the PDF or just click any
cell in the Excel spreadsheet then go to
the option of insert in the toolbar then
navigate to text option click on it and
select
object and here you can select create
from file and then you can browse and
here you have your Excel Basics PDF in
my documents click on that select insert
so here you can just press okay and the
PDF will be inserted now you can
minimize the size of PDF to fit the
cell there you go the PDF code
successfully inserted into your Excel
spreadsheet now let's discuss about the
second way now the second way is
completely same the only difference is
you can add some logo to your PDF so you
can see it has been added in direct form
of a PDF like what's the first slide of
the PPD but instead of that if you
wanted to highlight it as an icon then
you can select the display as icon
button here and press okay now you don't
have to reformat the size of your PPD or
PDF all you have to do is add the logo
go to your PDF and it will also include
the title so this is another way of
adding you know PDF to your Excel sheet
now another way so a few times you don't
want the first page of the uh PDF to be
visible or any kind of logo of that PDF
reader to be visible on your spreadsheet
sometimes all you want is some dedicated
image or a dedicated logo right so this
time let's try to add a dedicated image
to our PDF so go to insert again here
you can see the illustrations option
click on picture and from this device so
I will select the simply larns logo and
now you have to resize the logo a little
bit to fit the
cell there you
go you can uh you know click on the
right click button and and select the
option of Link now
um select the existing file or web page
option so if you are interested to learn
more on hyperlink in Excel we have
dedicated tutorial on that please go
through hyperlinks in Excel by simply
learn which will explain you everything
about hyperlinks in Excel how to insert
them different types of documents to be
inserted and how it works in real time
for now let's try to insert the existing
web page and go to documents navigate to
documents and inside this
you have the Excel basic PDF click on
okay and it has been added to your image
so when you click on it it will open the
PDF document attached to it so when you
hover over to the cell you can see it
will ask you a permission do you want to
continue or not just click on this
ignore the warning so you can see the
PDF has been successfully open convert
PDF into
Excel now to do this let's get back to
the Practical mode and try to convert
some PDF data into Excel now you can see
the PDF document which I'm looking to
convert from PDF to excel so this
particular PDF document has a table so
this is the statewise GST collections
that happened during March of 2020 now
we need to convert this tabula data from
PDF format to excel format now we are on
the Microsoft Excel now to initiate the
process we might want to choose a blank
workbook for this so now we are at the
blank workbook and the complete sheet is
empty now let's go into the data option
in the
toolbar and in the data option of the
toolbar we
have the ribbon of get and transform
data in this ribbon you can see get data
option select that and you can see
various options here from file from
database from Azure from other sources
Etc now we need the first option that is
from file and in that we have a drop-
down and in that drop-down you might
want to select the PDF if you have a
Json file you can also choose from a
Json and if you have the data from XML
you can choose that and even you can
extract the data from text or CSV as
well now we need the PDF option so
select the PDF Now navigate where your
file is existing so my file is on the
desktop and now let me select the PDF
document select import now Excel will
automatically analyze the tab data in
the PDF format and give you the results
now according to excel there are
multiple Tabo formats table one table
two so basically this table is one and
the same so all the 29 states are fixed
in one table itself but since the PDF is
divided in sheets this particular
document is considering the first table
as a separate table and the table which
is present in the third page as a
separate table so we have table one
table two and there are few more tables
which Excel is assuming that it might be
table but it's not and another table
which is page number 002 it is a table
and another one is right here now there
is an option of selecting all the tables
all together at once or you can select
only one table which you want to select
so if you select multiple items Excel
will automatically give you an option
option of choosing your tables you can
just tick and select the tables you want
or you can directly select the table you
want on your Excel
sheet so right now let's try to select
multiple tables so I'll be selecting 1 2
4 and five and this particular one is
not a table so I'm eliminating that now
you might want to choose the load option
so Excel is now loading the data
now all the data has been successfully
loaded
now yeah it is giving us a notification
that all the data has been successfully
loaded and now just right click on it
and here choose the option load to and
here select table option and if you want
the data to be uh you know loaded to a
new worksheet you can choose that and
select
okay now you can see all the data is
been successfully loaded in the form of
tabular format in the new Excel
sheet similarly let's try to load
another
page in the table
format new
worksheet select
okay and the third sheet
now get ready to impress your boss by
converting all the PDF data into Excel
sheet just in a matter of few seconds
with few steps and there you go all the
four tables have been loaded
successfully in the form of excel in
just a few steps now this is how you
convert PDF to excel that is how to add
tick mark in Excel now on my spreadsheet
you can see some employee data so
they've been assigned a task and they
want to update the task status as done
and not done using symbolic statements
that is tick mark and into marks now how
to do that it is really simple we'll
discuss two different ways the first way
is to do it by conditional formatting so
click a sell on the task status go to
conditional formatting click on the new
rule yes of course we can use the icon
settings but we want to automate it so
click on the new rule right so here
instead of two color scale select icon
sets now go to the settings here we
don't want the third symbol so select no
cell icon and here instead of percentage
number formula let's add just a simple
number and the second one as well just a
simple number now in the icon settings
select Tech Mark you have various types
of them you know the circular Tech Mark
let's select the generic tick mark
without any outer circles and into here
generate x mark and and now let's set
the values when the value is greater
than zero it will be green and when the
value is less than or equal to Z it will
be X and the reference value is zero and
here also the reference value is zero
everything is okay click on okay and
done and here you cannot see the logos
yet we need to add some formula here
which is equals to if click on Tab to
select the symbol and if Cell address C2
is equals to
done then the value should be one else
the value should be zero close the
bracket
enter right now you can see this
particular logo here now drag the same
formula it's done now you can see we do
have numbers you can eliminate those
numbers as well just edit the
rules and here you can see show icon
only click okay and the numbers will be
removed also addit the second one and
there you go the numbers are gone and
only
the symbols remain now what is the
second technique the second technique is
available in the developer options you
can activate it by you know uh going
into the options here go to file and
here go to options and here go to
Advanced customized ribbon and in here
on the right hand side you will get
developer options click on the tick box
and it will be enabled now go into
developer options and click into insert
menu click on the form controls or
active X controls select the checkbox
and now just place it over here you know
you can edit this particular checkbox
delete everything drag it as checkbox as
well now you can drag the same cell
across all the functions and you will
have the checkbox as well now you can
click on it and say if it's done or not
that is how to add Watermark to excel
now on my sheet you can see some data
so here we wanted to add some Watermark
logo to your uh Excel spreadsheet so
that it looks genuine and work by you
right so that's the overall idea of
adding a watermark now go to the insert
window and in the insert window go to
the text option click on that select
header and footer once you select header
and footer you can see something on your
screen like this so in this box which is
highlighted by a cursor is where you're
going to add add the picture on my top
in the menu bar you can see header and
footer elements in this select the
picture element and it will navigate to
you to a different option window since
we are adding the image from our local
system select from file or if you are
adding from your Bing or any search
window you can also add that or one
drive as well but now let's go with from
file it will directly navigate you to
the pictures folder in your local system
so here is my SARS logo click on it and
and select insert now you can see the
images added in this format which is ENT
and picture and you might wonder where
is the picture it is actually present to
view it click on any cell in the
spreadsheet and there you go you can see
it now you might be wondering the image
is a little towards STP and abnormally
placed right so no worries you can
always you know format your picture just
press enter and use enter to move your
picture towards the center of your data
spreadsheet and and also there is
another option of format picture alog
together here you can adjust the height
and width of your image by you know
increasing and decreasing width or
increasing and decreasing height using
the arrow marks also works and apart
from this there is another option of
picture formatting where you can choose
your image to be washing out black and
white gray scale or anything so
currently I'm going with automatic which
will automatically adjust the brightness
and you know everything for your image
it looks perfectly fine so click on okay
and there you go right so now this
should be
fine right now everything is done and
everything is automatically saved if you
are having that auto save option on your
spreadsheet and let's go back to the
normal vi from the header and foter view
click on The View and go to
normal you might be confused right where
is the logo it vanish did I had to save
it no it's already saved everything is
fine even the image is there but you
cannot see it in normal view to to see
it click on file click on print then you
can see the logo it will be present only
on the printed Pages not on the normal
view okay so that's how you work with
watermarks in Excel and that's how you
can add and customize your watermarks
using Excel how to modify or increase
cell size in Excel now most of you might
probably want to increase just the cell
as you know just to increase the
visibility of the text so one of the
easiest way is just to press on the plus
icon on the bottom right corner of your
Excel spreadsheet so this will
automatically increase the text size and
you can see the text more clearly now
apart from that if it's just not that
what you want to do you can also modify
by hovering over to the line between two
columns and right click which will give
you the option of format cells or column
width so either of these options will
help you to Max maximize or minimize
your column width similarly you can
apply the same idea for your rows as
well so you can either format the cells
or modify the row height of your cell
and this is another way and another
simple way is to just hold that line and
drag it so it improves your width of the
column and another way is to just over
onto that line and hold it and drag it
to improvise or increase your width of
the cell and same applies to your row so
this is another way so apart from that
there is another way that is format
sales from this particular option in the
home button you can select this and here
you have the row height autofit row
height column width autoit column width
for example let us imagine that you
wanted to you know just by mistake you U
you increase the cell size and you
wanted to restore it back to the
original thing right so you can just
click on the autoit height and click on
the autoit col width there you go it
happens in that way and now coming back
to
another simplest way let us imagine that
you are given a lot of data and you are
told to autoit all the rows and columns
now we have already seen the format cell
option where will autof fit all the
columns and width of the column and also
the height of the rows Etc right but
there is also a keyboard shortcut to do
this so all you have to do is select a
cell and press press CR a to select all
the cells in the spreadsheet and once
all the cells are selected all you have
to do is press and hold ALT key and then
release it then the combination of keys
that is
hoi so your column width has been now
adjusted now the same formula or the
same shortcut key with a small
modification press and hold and release
ALT key now h o and a to adjust the row
height so this is the way where you can
increase or decrease or modify or autoit
all the cells in Excel now you can see
that we have the First Column that is a
column and the first set of that is text
and all the other cells are numbers but
as a whole Excel considers the whole
column as a general data format so why
are we discussing about this so to
create a barcode in Excel you have to
make sure that all the elements in the
column are of Text data type so the
first step is to convert this General
data type to Text data type so you can
do that by going into the data type and
selecting text and now all the elements
in your first column A are type
text now the next step is to convert
these elements into bar Cod now for that
let's create a new column called
barcode now our new column that is
barcode has been created now the next
step would be is to check for the
barcode font so the barcode font looks
something like this but let me remind
you the barcode font in Excel is not
available by
default don't worry if it's not
available we can download it from open
source so let's go back to
Google so on the Google you want to
search uh for barod font for Excel so
the one that I would recommend is 3 of9
barcode
font now you can download this font by
just clicking on the download button
over here now I've already downloaded
this font on my local system let's now
try to install that now the three of9
barcode folder will come in the form of
zip folder you have to unzip that
and after unzipping you'll get the setup
file which looks something like this
which is three of9 new select that and
you'll be having the installer file over
here you can install by selecting the
install button over here so I have
already installed it in my local system
so it is really available for me on my
Microsoft Excel now we might want to
create the barcode for this particular
cell in A2 so let's create that so to
create a barcode we need to write in a
formula that is equals to double
codes star double codes and ENT symbol
address of the cell that is A2 another
erson symbol again double codes star
symbol and double codes so you can
either use this or instead of star you
can use brackets as
well so let's try brackets now select
enter now this will generate another
type of code in your resultant cell
now we will change the font for this
particular resultant cell and we will
get our barcode now you can just drag
all the cells so that you can apply the
formula to all the codes now I got all
the codes over here now select all the
cells get into the fonts and here select
the barcode font and there you go you
have the barcode for all the numbers you
have in your column A so that's how you
create barcode in Excel Excel flashfill
is a feature of Microsoft Excel where
Excel can sense a pattern in a cell and
apply the same logic to extract the
similar resultant pattern out of the
remaining cells in a table might be a
little confusing right so let us
simplify it so let us imagine that you
have a text in one of the cells in the
Excel sheet and you wanted to trim a
part of that text for example let us
imagine that there is an assembly an
assembly where the car parts are
assembled together to finish a car so
each and every part has a code the
serial number the product code and the
assembly code Etc right and you wanted
to separate all those three if you
wanted to do that you might want to use
the trim function which might be a
little Tous to apply to all the cells
but what if if I say there is a simple
key format using which you can fill all
the columns in the Excel sheet within a
fraction of second sounds interesting
right so that's exactly the flashfill
function in Excel does
now before we get started let me tell
you guys that we have daily updates on
multiple Technologies if you're a tech
geek in a continuous hunt for latest
technological Trends then consider
getting subscribed to our YouTube
channel and don't forget to hit that
Bell icon to never miss an update from
Simply now without further Ado let's get
started with the flashfill in Excel for
that we might want to start the Excel to
get in the Practical mode now we are on
the Microsoft Excel and remember the
example we discussed so this is a
similar example now the first column in
the sheet is the product detail so this
product detail consists of the product
code that is 112 next we have the
product serial number that is 1 025 637
and lastly the assembly code that is as
snx now we want to separate this product
detail into three columns where we have
the First Column to be storing the
product code second one to be storing
the product serial number and the last
one to be storing the assembly code so
now if you want to do that using trim
function you might want to apply the
trim function here and trim out the left
part and again for the product serial
number you might want to use the trim
function again which might go into a
little complicated pattern So to avoid
the complications and and decrease the
time spent we might want to use the
Flash Fill in Excel now let us help
Excel about the pattern that we might
want to generate so the pattern is 112
and the next one is
322 so now you can see that the text was
highlighted that was the intimation from
the Excel that it understood the pattern
now going into the next cell and holding
the control key and pressing the E
button will help us to fill the
remaining cells in the column B so
that's how it is done and similarly
let's try out for the serial
number for that the serial number is
one2
5637 now in the next cell we have one 3
6582 now we can go to the third cell and
press contr e for Windows and if you
using a Mac operating system then you
have to press command e and all these
serial numbers will be filled so this
will reduce the time in separating the
pattern and filling the
columns now let's try the last column
that is as SX which happens to the
assembly code next we have FG D and V
now the shortcut method that is CR e and
there you go now you might be wondering
will it work only for numbers etc etc or
the text which is properly aligned or
separated using a hyphen no it can also
be used for some random text which is
like this so let us imagine that we have
a text which is of length like 20 or 30
and you wanted to print only a part of
it so here I'm going to print the AL
alphabets from position fourth to
position 8th so I've already tried the
first one that is taking out the
alphabets from fourth location to the
eighth location or the pattern of the
text that is i f e r from Jennifer lope
and in the next cell we have the numbers
starting from the location 4 to8 so that
is
7767 and similarly we have the other
cells consisting of the names of few
cars that is alphar Romeo Bugatti Von
and next we have Superman Returns text
so now let's press the shortcut key that
that is CR e and make sure that all the
remaining cells are filled so that's how
the Flash Fill works so the Flash Fill
in Excel will reduce the time consumed
to fill all the columns by separating
the text in your main cell that is the
column A which we have here in this
example and it also reduces the
complexity of using a trim function in
Excel as hyperlinks in
Excel now hyperlink is something which
is clickable and when you click on it it
will redirect you to a different web
page to a different worksheet or a
different location in your local system
so there are a variety of options and we
will explore all of them using Excel now
creating a hyperlink is really simple we
have multiple ways now let's write some
text on the Excel
worksheet I'll write simply
long now creating a hyperlink is really
simple you have multiple ways the first
simplest shortcut is clicking on the set
where you want to place the hyperlink
and holding control key and pressing K
now this will enable the hyperlink menu
box or the dialogue box this is the
first way and let me close it now again
we have selected the cell there is
another option where you have to click
on the insert menu on the toolbar and
navigate to Links group click on the
link option and you will again have the
dialog box of the hyperlink and Excel
and the third way is right click on the
cell and you can see the link option in
the last and this will enable the
hyperlink option in Excel now let's
create a hyperlink the simplest way
again crol K now we have a variety of
options that is existing file or web
page place in this document create a new
document and an email address so we will
address all of these one at each time so
first one existing file or a web page
now now you can select an existing PDF
file or any file in your local system
and hyperlink or create a hyperlink to
the text or you can select a website
link and place it over here and create
it as a hyperlink Let's uh explore both
the options first let's try to explore
the web page option so since I've have
written as simply learn let's use the
website link of Simply learn and create
a
hyperlink let me open Google
new T and let me type simply
learn.com let me copy the hyperlink and
now let's get back to the Excel workbook
now here let's place the hyperlink now
you can press okay and there you go the
hyperlink has been successfully created
now this link is clickable when you
click on it it will redirect you to a
new simply learns home web page right so
this is how you create a hyperlink to a
website now we should explore another
option that is using an existing file
right let's create a new text box here
let's name it as
existing file and okay existing file
should be good again the same you can
either click uh you know right click and
select hyperlink or control K now let's
select existing file or web page option
now here you can see navigate key press
on that and let's navigate our files
let's select all files here remember to
select all files okay by default it
shows as office files now let's explore
our file
option so let's select uh fundamentals
of computer programming option which is
a PDF file press okay and there you go
we have created a hyperlink so when I
click on this you can directly navigate
to an existing PDF file in your local
system and it will be open as a web page
click on it and you can see the PDF file
now close now another thing when you
navigate onto this you'll see the
lengthier HTTP uh address and same
happens here right so if you're a little
uncomfortable watching this lengthier
link you can also edit it you can choose
the option of edit
hyperlink and
and see the screen dip option click on
it and here you can change the text
display
option and write it as click here to
know
more press
okay again press okay and now if you
navigate onto it instead of see lenier
hyperlink what you can see is click here
to know more okay I think I made a
spelling mistake so I think I forgot an
e somewhere over here yes click here to
know more and press okay right so here
you can see click here to nor more let's
try this again over
here so
edit edit hyperlink screen tap option
and instead of the Lend your text box or
hyperlink you can write as click to open
notes or
PDF click okay and click another okay
and you done so instead of seeing that
lengthier hyperlink you can see click
here to open PDF right so so far we have
explored two options that is existing
file and existing web
page right now let's try to explore
another option again uh hyperlink right
click hyperlink and here you have seen
places in document right so which is
like uh okay let me show this to you
instead of telling you so here what it
is doing is you have the cell A5 and you
wanted to refer to A1 or any other cell
in the same worksheet so you can do that
right so now for your reference let me
cancel it and let me create a new sheet
and here navigate me to here right let
me write it as navigate me to here okay
so now what we will do is so here we
will create that navigate link from
using the hyperlink option place this in
this document you can see the sheet to
option and the cell reference is A1
where we have written
navigate me to here so when I click this
A5 cell okay let me also rename
it write it as navigate cell navigating
cell right
now let create
that control
K let me select sheet two and the cell
address is A1 press okay now what did uh
what What's the change right when you
click on this you will be redirected to
sheet to cell A1 navigate me to here
right so this can be like a shortcut
when you have multiple sheets like 100
sheets then you can uh you know have a
different column over here and uh add
the navigation address to all those cell
links you can just click on that cell
and go into the different sheet and to
the specific cell which you have given
the address in our case I have given
sheet to address cell A1 right so this
is another trick now let's get back to
the hyperlink thing again uh control K
and another option is create new
document so you might have been through
you know option of uh having let's say
some cheat sheets or uh clip on
clipboards right so uh let us imagine
that you're working through uh a huge
worksheet and uh you wanted to you know
uh have a a track of everything and keep
notes updating right right so it might
be a little complicated to understand so
let me show it practically so let me
write it down as clipboard or sticky
note
right okay I think this has a hyper let
us okay this can be used no
worries back
to uh hyperlink option
again create new document now here you
can create a variety of uh documents
right so it can be a text document it
can be an Excel document it can be a PDF
document right for now we'll create a
text document okay uh we'll name it as
uh
tracker. txt so this should create as a
text
document and the location can be
anything so you can you know uh take it
as a desktop documents down down so let
me take documents here and select okay
so I'll be having the tracker. txt
located in the documents
folder and uh you can also choose to
edit the document later or edit the
document now let us select edit the
document now press okay and there you go
so you have your text tracker here so
let us imagine that we have worked on
the Excel sheet and we wanted to write
on us a sticky note uh so that we can
remember what we have done on the Excel
sheet so update
one and uh update two now you can close
it so it'll ask for a save option save
it and done and let us imagine that you
did something else and you also updated
something else on the sticky note I mean
Excel sheet and you wanted to update
that on your sticky note so click on it
and you'll see a minor notice over here
from Microsoft Excel based on potential
security concerns you can select yes and
you'll be having access to your text
document again
and write down your update again here
update three what you have written right
the latest update what you
have and you can save it and
close so that's how you keep track of
your sticky note and you'll have all
your updates right now getting back to
the hyperlinks
again uh the last one is email address
so you can also try to add uh okay let
us imagine that you're working with a
colleague and every time you make an
update in your Excel sheet or anything
and you wanted to send him out an email
right so it would be a little timec
consuming that you open your email and
uh compose something and send it over
here but what if you had a one single
click and access to the email compose
option right that can be done so let us
write down email
about me with the spelling so again the
control K and hyperlink select email
option and you can write down your
email which can be anything so you can
write down your friend's email address
so I'm just writing down some random
email here friend
gmail.com
and can write the subject about
update and press okay this will navigate
you to the Gmail option or the mailing
address
options click on it and you'll have a
variety of mailing options Outlook
Office 365 Yahoo iCloud Google Etc so
you can select any one of those since we
have written as gmail.com it'll navigate
to Gmail and all you have to do is login
you know all the formalities and you're
good to go with
emailing so let's skip this for now so
you you know how to do the rest parts so
so far we have uh you know covered all
the hyperlink options in Excel that is
existing file or a web page place this
in the current executing worksheet or
document creating a new document for
your update thing and uh also including
the email right so so far we have
covered everything around hyperlink in
Excel and if you have anything to know
more about or if you feel that we have
missed out anything important regarding
hyperlink in Excel you can feel free to
let us know roundoff formula in
Excel now for this particular tutorial
we will be using the student data set
now let's get back to the Practical mode
and start our Microsoft Excel now we are
in the Microsoft Excel and as you can
see on my sheet okay let me expand this
so as you can see on my screen we have
the data set belonging to students so we
have 10 students and every one of them
has name role number class blood group
and marks Etc and at the end we have the
percentage and as you can see we have
the percentage in terms of float values
and we have a lot of decimal values like
eight and above so what if you wanted to
just have one or two digits after the
actual percentage like 80.6 or 8.66
right it would be a little bit more or
good to read right so that's what a
round off actually means so now let's
work on having rounded off values for
the percentage now let's create a new
column let's name it as
um round
of
percentage great
so to actually perform the roundoff uh
formula we do have a predefined method
for that for that you just have to type
in equals to
round and there you go we have round up
and round down so both of them perform
the same operation let's select a round
up and this is the mend column and and
the row number is three so we'll have to
press M3 and the number of values after
the decimal point for now let's select
um
one and there you go press enter and the
value should be rounded off now 80. 66
is being rounded off to
80.7 now when you drag the cell to the
all the rowes then you can see all the
values will be rounded off here you can
see 75.1 16 has been rounded off to 75.2
and 82.3 3 is been rounded off to 82.4
so we end up with having grounded
of so first we will understand what
exactly is standard deviation so
standard deviation is a calculated
square of variance now what is variance
okay nothing to worry let us also
understand what is
variance now the next part variance so
what is variance variance is a measure
of variability it is calculated by
taking the average of squared deviations
from the mean now what are deviations so
slowly you can understand that we're
getting into the topic of statistics and
graphs so uh before getting deep into it
let's understand what is deviation so
the deviation is a measure that is used
to find the difference between the
observed value and the expected value of
a variable in simple terms deviation is
the distance from the center point so
for example when you are going through a
graph so you will be expecting some
value right and if you get some
difference that is above or below the
expected point that is called the
deviation what is the difference between
the expected point and the obtained
point so that is the deviation now next
is the observed value so the observed
value is the value that you get in real
time unlike the predicted value so now
you might be thinking to calculate
standard deviation we might need a few
more parameters and you're exactly right
so to calculate standard deviation in
Excel you need variance and then you
need deviation and then you need mean
right so all these parameters are
supposed to be calculated first and then
you will be having the final formula to
calculate standard DV now that we
understood the theoretical and formula
based explanation about standard
deviation it's time we calculate the
standard deviation using one of the most
popular business intelligence tool that
is Microsoft Excel so let's get back to
the Practical
mode so now we are on the Practical mode
and we have some sample data on my
screen right now let me expand it a
little
bit yeah there you go so as discussed
before to calculate standard deviation
we might require the mean variance
deviation and deviation squar and then
we can calculate the standard deviation
so let's go step by step so the first
one is calculating sum right so to
calculate average we might need sum of
all the scores so we have the index
values that is 1 to 10 and scores of
each index value that is 1 to 10 right
now let's calculate the sum of all
values here press tab to select the
function select and drag the cells press
enter and then you have the scores now
you want number of index so clearly you
can see we have 10 but still let's count
them C N tab space to select and select
the number of cells press enter and you
have count now what's the mean so mean
is simply the average of all the numbers
right so you can either divide the sum
by count or you can simply use the
average function so let's try to use the
average function so that we also have an
idea of how the average function works
press tab to select it and select all
the cells press enter now you have the
average or mean value now we have the
main value and now after finding the
main value we are supposed to find out
the deviation so remember the deviation
that is the difference between the
obtained value and the predicted value
right so the obtained value is this one
which is here in C2 and the predicted
value is this one that is 82.6 that's
our mean right to formula we supposed to
eliminate the value of obtained minus
the mean right so select uh the cell
equals to
C2 minus the obtained value that is mean
now press F4 to freeze the value and now
press enter so that you can freeze the
value and drag the formula across all
the cells now you have the
deviation
right now the next step is to find the
sum of all the deviation number numbers
so either you can apply the sum formula
to all these numbers or you can just
drag this cell over here and the formula
will be automatically copied now
similarly the count function to count
all the values that us drag it and
you'll have it over here right now the
next step is to find out the square
deviations or the square of the
deviations which is really simple all
you have to do is equals to select the
cell and uh see that small hat kind of
logo on the number six use that now into
two press
enter we have the square similarly drag
the same formula across all the C and
you'll have your squar numbers similarly
drag this summation function over here
so you'll have the sum of this and the
count number as
well now comes the final
formula now we have the mean value that
is over here let's color
it and we have the variation
Square now that is which is this one
let's color it in a different shape and
we have deviation as well let's color it
in a different
shape now we need to find the variance
and after we find the variance the last
step is to count the standard deviation
or calculate the standard deviation so
what is the formula to calculate the
variance so for variance you need the
sum of deviation
Square divided by total number of
values minus
1 the center and that's your variance
now the standard deviation so the
standard deviation is really simple you
need to
calculate the variance to the power of
.5 and that's your standard
deviation so that's how you calculate
standard deviation in Excel I hope all
the formulas and the explanation was
clear so you can see that we have got
started with Microsoft Excel and on the
left hand side we have a simple table
with all the teams in an IT industry
starting from it admin testing
development to client consultant support
we have got everything in this part
particular
table now our main idea is to find out
the index of these particular elements
for example if you wanted to find the
index of the element marketing then how
could you do it so here let's try to use
the match function in Excel for that we
want look up value and position so on
the right hand side you can see that
I've created two separate columns as
lookup value and positions so in the
lookup value column we will be inserting
the value of which you want to find the
index now for example let us consider
that we want to find the index value of
Team
marketing so I'll be writing Marketing
in this particular lookup
value now how to find the index value of
the element marketing for that we will
be using the match function in Excel
now after writing the match function you
can see that it is asking for three
different parameters so the first one is
the lookup value second is the lookup
array and lastly we have match type so
lookup value is the value of which you
are trying to find the index that is
marketing this particular cell so we
have selected the lookup value which is
the first one next we have to select the
lookup array that is from which set of
elements you want to find the index X so
the set of elements are this it admin
testing the column which we have created
the a column now we have selected the
lookup array and lastly the match type
is it you're looking for less than or is
it you're looking for exact match or is
it greater than match so for now I want
exact match so I will be selecting the
exact match that is
zero now there you go you have the exact
index of the element that is five now we
have selected all the 10 teams starting
from it1 admin 2 testing three
development for marketing five so that's
how you find the index of a selected
element in
Excel now we just have like 10 elements
so we have got the uh writing of the
elements or you know uh choosing the
elements in a easy way but what if if
you had like 100 elements right could be
a little
different okay so let me put it simple
now you wanted to find the index value
of Finance so right now we have
marketing and you wanted to type in
finance and you missed out a letter
A okay now you're not finding the right
index to award such problems you can
always take the help of data validation
in Excel that is using the list in data
validation in Excel you can see a small
drop- down icon here right you can
choose that and here you can see all the
themes that we have created in the left
column right so here I can scroll down
and choose the finance and there you go
I have the index value okay again we
need to copy paste the same match
function in Excel over
here equ to
match look up
value look up
array and exact
match so there you go that's how you
make use of data validation in Excel and
using the list you can comprehend all
the list elements in this particular
column into the list of data validation
and you can choose the team whichever
you want to find the index for so this
is how you can make use of match
function in Excel or index match
function in Excel to sort data in Excel
so on my spreadsheet you can see some
employe data and on the column f and g
you can see employee date of birth and
employee date of joining for example you
wanted to sort the employee data in the
form of earliest joining employee and
the latest joining employee right that
is who is the employee who joined first
to this company and who is the employee
who joined last to this company so for
that you just need to select the column
of date so let's select both the columns
and go to home and check or verify if
the data type is date or not so
generally the data type will be set to
General by default by Excel so we need
to make sure that we have the proper
data type which is State now that we
have the proper data type select the
entire column and go to the data option
and here select the sort option and in
the sort option select the expand
selection click on sort and here sort by
employee date of joining and oldest to
newest that's what we wanted the oldest
employee or the you can also set it to
the newest employee to oldest according
to your requirement but we'll go to
oldest to newest according to the case
study right now just click on okay and
that should be done so Emily who a
manager is the oldest employee who
joined the company and Chris who is A
traine in it support is the latest
employee who joined the company now
that's how you sort date in now on my
screen you can see the sales of four
quarters from different regions that is
east west south north and Central now
you might want to calculate the sum of
sales happened from all the regions in
q1 or you might want to calculate the
sum of sales happened in one region of
all the four quarters that is either you
want to calculate the sum of all all the
regions from quarter one or you might
want to calculate the sum of all the
quarters in one region right so to do
this you have some simple functions in
Excel so you might say me you have some
function Etc right for that you might
want to go into the you know equals to
mode and fetch some option and subtotal
or some you have to select the function
from here but what if I tell you that
there is one simple easy shortcut where
you just have to make one single click
and you'll have all the Su right so that
seems interesting so on the top in the
home bar select home and in on the top
right corner you have the editing group
in editing group you have aggregation
function that is summation so you have
different aggregations here some average
count numbers maximum minimum more
functions here you might want to select
the sum and you can get all the
sumission numbers here so I'll select
one cell so the second query where we
wanted to calculate the sum of all the
sales happened in east region with all
the quarters included that is this one
C2 to F2 right so let us select G2 and
select the autosome option and here it
will automatically select the range for
you that is C2 to FS2 if you want to
change it can also you know change it
like minimize the number if you just
wanted to calculate the sum of three
quarters you can do that so now we want
four quarters so select enter and there
you go you have all the sales of all the
four quarters of east region now if you
simply drag it you will have the sum of
all the four quarters of all different
regions now let us calculate the
summation of q1 of all the regions that
is this one Select Auto sum and it will
automatically select all the celles for
you that is from C2 to C6 right press
enter and you'll have the sumission
similarly you can you know drag it and
you have the numbers here of all the Q2
sales of all the regions Q3 sales of all
region and Q4 sales of all region now
let us try to change the color so you
can have a reference that is it's
different it's it's a total sum or right
you just have a reference to identify
it now similarly to this one as well
so that's how you implement Auto sum in
Excel now we are on the Excel
spreadsheet so you can see that I have
five different sheets of the same data
so why do we have that so I'll be
explaining you how to sort data for
multiple parameters so firstly we will
try to sort data in the form of numbers
right so let us imagine that this data
is being shared with you by your manager
and he wants you to sort this data based
on the salary numbers so he wants you to
arrange the data in the form of
ascending order or descending order
maybe which will help him find the
employee with the highest salary right
so how could you do that so it's really
simple you could sort the entire data
just within a few clicks so how would
you do that so since you're focusing on
salary select any sell from the salary
column only right select this particular
column and select any sell in the salary
column get into the short and filter
options and here select the sort of
arrangement you need that is largest to
smallest or the smallest to largest
since the problem statement was to find
the highest salary click on largest to
smallest and there you go so you have
Tony and the designation is senior and
he works in it support and he draws the
highest salary of
80,000 right and there's also another
one which is Peppa and she she works for
the analytics team and now the next
sheet let's get into the sheet two and
this time let us imagine that you wanted
to you know uh arrange or sort the data
based on the employee date of joining or
date of birth right again if you wanted
to arrange the data in which you find
the youngest employee or the oldest
employee of the organization you can do
that click on any cell in the date of
birth column and go back to the sort and
filter and here you will be seeing the
options sort oldest to the newest or the
sort the data from newest to the oldest
so since we want the oldest employee
select oldest to newest and there you go
so the oldest employee is Alfred who is
an associate designation employee and he
works in the admin department and he's
drawing the salary of
25,000 so that's how you can sort the
data when you are trying to sort the
data based on date wise so first we try
to sort the data on the number wise so
you can apply the same for your employee
ID salary and if there is any
possibility you can do that as well and
if you want to apply the same sorting
based on dates you can do that as well
using the Sorting function in the same
way now let's get back onto the third
sheet and here now uh single column wise
is perfectly fine there is no issues
with that now you wanted to you know uh
let us imagine that you have two columns
okay let's eliminate a few columns here
maybe we can eliminate the employee ID
and uh okay let's keep a few columns so
we'll just keep employee name and
employee salary okay so here we are
trying to demonstrate if you are uh
trying to sort the data using one cell
in the column B will it maintain its
relationship with the cell a and okay uh
that's our query right so let's see I'll
go a little detail so let us imagine
that you wanted to sort this data based
on highest salary okay so when you do
that will it also impact the Sals in the
column A so it will basically impact but
just uh it's a query right any of you
viewers might have that query will I uh
if in case if I try to sort the data
based on this will it affect the cells
on the rest of the data set okay so it
and basically it will impact so I'll try
to you know sort the data based on
ascending
order so this is the smallest to largest
and you can see uh the Tony and pepper
so since I selected the cell in the
column B it also impacted the change on
the cells of column A so that's how the
Sorting works now we'll get to a little
Advanced um sorting techniques so here
we try to I mean already we try to sort
the data based on numbers based on
alphabets based on U you know uh date
date of birth right so I think we forgot
the uh ascending or you know sorting
based on alphabets let's do that as well
select any um cell in the column A and
go to S filter try selecting a to z or
okay let's try to Z to A okay so here
you can see the uh names have been
sorted based on the descending order
from Zed to a right so so far we tried
on alphabets so far we tried on numbers
date of birth and a couple of more
things so let's dig a little deeper and
try to sort based on colors right so for
that you don't uh you know find that uh
extra setting here in the regular
sortant filter so you might want to get
into the data option and here you'll
find another sort function which is a
little Advan so that's how you do it so
since we have colored in the cell or
column of salary select that salary and
we're going with cell value so in the
cell values section select the cell
color and here on the cell color options
you'll have green blue and yellow that's
what we have used in our um cells so
let's keep Green in the first position
which is on top and press okay now
you'll see green has been sorted to the
first position now yellow and blue are
left out so why is that so because you
didn't provide it right so you can do
what that as well mean you can always
add uh the setting to
it right select any cell go to sort and
add a level and then again select the
salary and sell values to cell colors
and now the cell color in the second
position would be blue and that will be
on bottom and I can add another level
the same
salary the cell values will be cell
color and and uh cell yellow will be
somewhere in between or bottom okay so
based on hierarchy it will be selecting
press
okay and there you go so you have cell
yellow and blue at the bottom and green
at the top position as for your
suggestions now we are on the last sheet
so here in this last sheet we are going
to you know um dig a little deeper into
the advanc sorting methods so previously
we tried to apply the advanced sorting
methods for colors now we'll try to
apply the advanced sorting methods based
on the data set we have here I mean the
data we have in our data set right so
select the sort option and let's add a
few details right so firstly let's sort
the column employee ID okay so let's
have the smallest to largest or yeah now
that would be good now let's add another
level where we will
sort okay uh I think we will sort based
on employee name that would be a little
better so let's try to uh sort it on
employee name here and here will be
employee salary okay now the sell values
and this would be from a to zed and the
salaries will be from smallest to
largest so basically what we are trying
to do is we are trying to sort the
employee list based on employee names
from A to Z so the employees name
starting from a to zed will be ordered
first and another subset of the Sorting
will be done based on highest salaries
that is smallest to highest so let us
imagine that we have three employees
with Nam starting from a and their
salaries are 10,000 20,000 and 30,000 so
how would it sort so it will sort
basically uh in the alphabetical order
first so first three names will be
sorted and then it will also have
another condition to sort the data we
keeping the lowest salary on top and
highest salary in the last right so uh
this is how the algorithm works for
sorting now let's press okay and see the
output so there you go the names are
Alfred and you have the highest salary
here smallest to highest so this is
25,000 next we have Banner Barns and Ben
so here you might be seeing why we have
Ben in the third position despite he has
a low salary and and name starts with B
right so here it is trying to work based
on the alphabets in the order right so B
A and B E it's not just comparing the
first letter it's also comparing the
following letters as well right so n
comes first R comes later so B is pushed
to the second position a comes first and
E comes later so B A letters are pushed
to the first position and then is pushed
to the third position so that's how it
works right so accordingly the set ba is
created here 3540 next Ben 12 and next
you have Bobby Brian Brock Chris Clark
right so that's how the data has been
organized and sorted based on multiple
levels of parameters you provide for
sorting so that's how you use sorting in
Excel slicers and filters in Excel so
slicers in Excel are software filters
used along with Excel tables or Excel
pivot tables over a large amount of data
not just filtering out data but slices
also help you with an easy understanding
of the information being extracted and
displayed on the screen now Microsoft
Excel slicers are compatible with
Windows and Mac and touch operating
systems now let us understand how to
implement slicers in Excel so for that
we might want to get back to the
Practical mode that is starting our
Microsoft
Excel now we are on our Excel sheet and
as you can see on on my screen there is
some data available on the Excel
spreadsheet and this table is not just
any table this table from Microsoft
Excel is been converted into an actual
table as you know by default Excel
considers all the data which has been
inserted into the spreadsheet as a
database and to implement this sense in
Excel we might want to create or convert
the format of database into a normal
table for that you can select all your
data and just press contr T and and that
will allow you an option called convert
the data into table now since this table
is already converted as a table we can
directly start implementing or inserting
slices into the
spreadsheet now for that select all your
data then going to the insert
option and in the insert ribbon you can
see the filters group and in the fil
filters group select the option called
slicer now you will be provided with
multiple options since we have the
columns employee ID employee name Zone
designation Department salary employees
date of birth and employees date of
joining so we can choose any of the
options available according to our
columns and create the slices now let me
select the first one that is Zone next
designation department and let's also
select employee name
now press okay and there you go we have
our slices now let us try to rearrange
these slices all you can do is just
select and drag them
up and done now let us imagine that we
want to
see or take a look at the employees who
are working in the EAS Zone then we have
all the information displayed on the
screen who are working in the East Zone
they are Jack Tony Banner Fred Etc and
their Department designation etc etc all
on one single screen so this really
helps you while you're presenting your
data in a presentation now for a change
let's try to select the department now
if your client wants to select the data
from only analytics Department then you
might want to select on the analytics
key on the department slicer and theyo
you have an employee called lops who
works in the East Zone and his
department is analytics and his
designation is contract based and the
salary he is getting is
65,000 now
similarly uh you can see that we have
only selected analytics and your client
also wants data from it support
now you can see that analytics is gone
but what if you want both analytics and
it support then you have an option
called select multiple then you can
select analytics and it support and if
you want HR you can also do that and in
the zone if you want West along with
East then you can do the same east and
west and you have all the employee
details and in the designation let's try
to select U senior
traine okay select multiple senior
traine and manager so that's how you do
it so that's how it's done and that's
how you implement slicers in Excel to
simplify the filtering options using
slicers during a presentation it is
really simple to add filters in Excel so
we are on a practical mode right now so
on my screen you can see an Excel
spreadsheet of sales data so here you
have various columns regions categor
state subcategory sales quantity and
much more right so now let us imagine
that we want to add filter on the region
part so we have south west east Etc
right so we want to you know let us
imagine that you want to extract the
data of only west region and you wanted
a filter so doing it manually could take
a lot of time so using filters will be
helpful now select one cell on the
headers part and select the filter
option in the data toolbar you can see
the data option in the toolbar right so
click on the data option and navigate to
sort and filter uh group and in that
select select the option of filter now
you can see the filters are been added
to all the column headers region
category state subcategory sales
quantity right now we wanted the West
category so when you see or when you
click on the drop- down icon you can see
all the options are selected right the
central east west south every everything
right but now we just want West one so
click on West and select okay there you
go so you have everything from the west
region now let's imagine that you wanted
to look into uh office supplies only or
technology only right you can do the
same with the technology and press okay
and now you'll have all the technology
related sales in the west region right
so that's how you use filters in Excel
or that's how you add filters in Excel
as goalseek in Excel so for this
tutorial we'll be considering the
students database now let's get back to
the Excel so now we are on the Excel
sheet and you can see that in this
particular Excel sheet we have some
student details we have the serial
numbers names role numbers class blood
group and some subjects like math
science computer statistics social GK
and the final marks obtained percentage
and total marks right so let us imagine
that uh a company has come for an
interview so all these students are
going to attend an interview and uh the
minimum percentage to attend the
interview will be like
75% so you can see that we have have 75
here and all the students have got above
75 except for the one student which is
Mike right so for Mike to attend the
interview he needs 75 percentage or
above so uh he can make some
modifications in one of these subjects
right so there is always an exam after
the exam called as Improvement exam so
if you can like to score some extra
marks in the subjects which you have
scored less so that you can make up to
the percentage what your expecting you
can do that so here in this particular
sheet uh Mike has six subjects right and
in six subjects you can see he has
scored 76 91 45 71 94 and 62 so out of
these he have scored Less in one subject
compared to all the subjects one subject
is really low that is computers so what
if he can give an improvement exam and
increase his percentage to 75 right so
that can be done so we can do some root
Force methods like we can change the
marks like 55 and then check this
particular one so it's getting 74 right
so using the brute force method will be
a little bit lengthy so you can do this
because it is just a small uh table
which has least number of data and you
just have one single cell to modify so
that can be done in small tables but
what if you had some table with hundreds
of rows or thousands of rows that that
will be time consuming right so for that
we have some inbuilt functionality in
Excel that is called goal seek in Excel
so for now let's eliminate the marks
here so we have eliminated the marks and
the upgraded percentage is 65 now we
need the target as
75 okay so let's uh write it
as Mike's Target right we need Mike
so
mik new Target
as
[Music]
75 okay now to make sure that we need a
Mark here which can get mic to 75
percentage we will use the data
operation so here on the toolbar which
has file home insert Etc we have one
other option called as data so when you
click on data from toolbar you'll have
this ribbon and in this ribbon we have
various options so when you come into
forecast group we have an option called
water if analysis when you click on vtif
analysis you'll have a set of options
out of which we have goal seek now for
goal seek we need to select a table cell
that is this particular table cell that
is m 9 so we need the value 75 here
right so what is the cell that you need
to change here for that you need to
select this one and select this
particular cell here right and now you
can select okay now you can see that
Excel has automatically run all the
permutations and combinations and has
come up with a number so that the
overall percentage of mic will be 75%
right so the expected marks that Mike
should be getting in his computer's
Improvement exam should be equal to 56
or greater than 56 to get the final
Target as 75 so that he can attend the
company's interview so this is how we
use goal set or goal seek in Excel we
are on our Excel spreadsheet and here
you can see I'm trying to create an
calendar for the employees in the
company where you might have to you know
write down the day and month and year or
week of your word date or maybe your
joining date or maybe also your last
working day right this might be useful
for all the employees to you know uh
avoid them from writing it down manually
so how do I create it so it takes a few
simple steps all it takes is some data
validation methods and also there is an
exclusive video specifically made on
data validation in Excel link to which
is in the description box below or you
can search it on our official YouTube
channel now you can you can see that uh
the word calendar is on the first cell
that is A1 but we have four cells below
it that is A3 B3 C3 and D3 so you might
want to add this to all the cells right
like maybe in the middle so for that you
can do the option of mergin Center also
there is a tutorial on mergin Center
about all the options available in this
particular thing so you can go through
that also and uh here we have done the
first step now all we need as day month
year and week so in a calendar or in a
month you'll have days from 28 29 30 or
31 days right so let's create a list or
the drop down menu for that so that
whenever you click it right so whenever
you click on that particular cell you
will have the list of all the days
available right so let's get started
with that let's create a new
sheet so why are we creating a new sheet
you won't want that all the numbers in
your calendar sheet right over here
right so for that so we are creating a
new sheet and all the data will be
available here so here let's write down
the days first and here the second
number also you can use the option of
Flash Fill so that you can you know uh
reduce the effort here so you can see
the flash full option over here all you
need to do is select the sales and drag
them down until you get your your
required dates right so let's keep it
till 31 days there you go you have the
days so these will be your days and here
now the next option was month right yeah
month and year now using the same flash
F option you will you know add the
months into your sheet so this will be
Sunday
so Excel has this smart flash F option
where you can just drag the data and
everything will be filled according to
your requirements so Sunday to Saturday
now the next one right that is your
month so same as the before process
we'll create
January you can use the flashfill option
and all the way up to December now the
last one was the
year so
1990 and
1991 so you can select the cells and
Flash Fill Up to
2022
maybe yeah 2022 should be good enough
now this is The Flash will option you
have here and also we have an exclusive
tutorial on flashfill all together you
can go through it for a detailed
explanation as well now coming back to
the original sheet so this is where you
create your drop down list right so
select the cell where you want to add
the drop down list navigate to data and
then here is your data validation menu
so we have a variety of data validation
options over here there is an exclusive
tutorial on that you can go through it
for now we'll look into the list option
only because we want to create a drop
down list right so here it will ask for
Source click on the source here the
source bar right now navigate to sheet
number two where we have the days
options select the
range so this will be your range and
select okay also you can add some
message here right what you can do is uh
in case if there is an error or
something right you don't want that to
be happen happening so all you need to
do is uh write down and select from drop
down select from drop down
only so this could be be message select
from dropdown only the title is Select
options or just
options in case if there's an error you
can write down your title as invalid
data and you can write down a message as
please
select from drop down only
so um in case of someone tries to write
as 32 or 33 to check the Integrity of
your data validation then they will
encounter an error and an error message
and then they must be understanding what
exactly they try to do and if it's
correct or wrong right so this is to
make sure that whatever the data you
enter is correct and valid so okay and
here you can see select from the Dr list
only options with with option list as
title and let's try to enter 32 right
and when you press enter you will see an
error so the error messages invalid data
please try to select from drop down only
so you can go for a retry and select the
drop down
right so let's eliminate them and now
select something from the drop down so
let's take it
as maybe 10 right so this is how you
cleared the day one now let's try to do
with the month same data validation
option settings will be list and Source
will be this one
think I made a mistake here so let's
cancel this and go back to sheet 2 and
rename this as our week yeah now coming
back to the month
again it will be a
list and the source will be in sheet 2
navigate to the sheet two and select
your range
and input message the same
options select from dropdown
only and error alert title will be
invalid
data this one will be please
select from dropdown
it's okay and that's done now let's
continue with here as well let's quickly
do the same
it will be a list Source will be in
sheet to
this is your list press okay and lastly
the week same process it will be a list
Source will be in your sheet
two the input
message options
battle invalid
data let's drop down
only right so your drop down list is
ready now so the month will be anything
So currently we have me and year can be
anything anywhere so let's take it as
19997 and we can be you know the
adjacent week so what was 10th of May
1997 let's check our
calendars was a Saturday
so it was a Saturday and that's how you
create drop down list in Excel and if
you want you can also add borders to
your calendar right so that's how it's
done so what is data validation in Excel
so it is a feature by Microsoft Excel
where it can restrict data entry into
certain cells by using data validation
and it will prompt the users to enter
valid data in the cells based on the
rules and restrictions provided by the
creator of that particular sheet now
this would be a little confusing to
understand so to understand it in a much
better way let's get back to the
practical way where we will start
entering some data validation rules to
our Excel sheet and try to enter data
into it now we are on the Microsoft
Excel and this particular sheet is based
on employe ID employee name employee
Department employee salary fiscal year
and work time of the
employees now we will be applying some
data validation rules to each and every
column and try to enter data based on
the rules we have entered now for the
first one which is the employee ID we'll
enter rules based on the employee ID
number so to apply the data validation
you need to go into the data tab on the
toolbar and inside the ribbon you can
see data tools group and in the data
tools group you have the option of data
validation now select the entire column
go to the data tools group select data
validation option and you will have this
particular popup menu and here you can
see different types of options so since
we are entering employee ID it will be a
whole number so let us select whole
number option and now here you can see
the option of between not equal to equal
to not between greater than less than
Etc so let us select between
and let us assume that your employee ID
has five numbers and uh it should be
between 10,000 to
11,000 so let us provide
10,000 and
11,000 now select okay and now the new
rule has been applied
so now the minimum value of each and
every employee ID should be equal to or
more than 10,000 and less than 11,000
and it should have five digits now let's
try to enter a wrong employee ID that is
just one there you go you can see that
Microsoft Excel is not allowing you to
include a wrong number it says this
value does not match the data validation
restrictions defined for this cell so
now you can press retry and try to enter
some number which is in between 10,000
to
11,000 now it is
10,901 and this will take the
entry now let us assume that because I'm
the creator of the sheet I know what
should be entered here but what if I
give this sheet to you and you're the
person who's trying to enter the number
and it shows the error and you don't
know what was the error right to avoid
this kind of confusion confusion what
you can do is you can do some uh ways
where you can provide the message to the
user so that can be done by okay let's
select the sheet again and go to the
data validation and here you have the
same settings now here you have another
option saying input message so before
input message let us go to error alert
now
here the error title would be data
entered
is not
valid please enter this is the error
message please
enter
EMP
ID
between
10,000 to
11,000 press okay now when you try to
enter the wrong number it will show that
particular error alert
message see data entered is not valid
please enter employe ID between 10,000
to 11,000 now this is fine but how long
this will be you know what if there is
an option where you just hover over to
this cell and it automatically tells you
to enter the value between 10,000 to
11,000 without having to face the error
yeah even that can be done so you can
sell it
the entire column go to data validation
and here this is the input message this
is where you can do that so here you can
write the message name valid
data please
enter data
between 10,000 to 11,000 now when you
hover over to any cell in this
particular column it will automatically
show you this message where you can
avoid all the errors just select okay
see when you select any cell in this
particular column it will automatically
tell you please enter data between
10,000 to 11,000 so here you don't have
to face any errors right now let us try
to enter the valid
data
10
9
02 and it will take it as the correct
value and again 1
903 now in case if you try to provide
the wrong number it will show the
error
see so that's how it is now we have
finished the whole number part now let's
get into the employee name where we have
to
provide uh the second type that is text
length now let us imagine that uh in
your company you're trying to provide ID
cards to your employees and and you know
that ID card is really small and inside
that ID card you need to include the
employee photograph and your company
name employee ID blood group phone
number address everything and also you
need to include the name of the employee
so what if there is an employ with very
lengthy name like 30 characters 40
characters yeah in India it is really
possible that you might have a lengthy
name so what you can do is you can
provide the text length where the text
is limited to like 15 or uh 20 uh um
characters so you can include that name
in the ID card so you can do that by
allowing the data validation criteria
with text length uh minimum can be
anything so minimum can be one and
maximum here you can provide 15 and
press okay okay minimum will change it
to at least uh
two and yeah let it be one so minimum is
one and the maximum is 15 characters and
press okay and uh let us also provide
the input
message
enter character type character
data please enter valid
name and error
Alert in inv valid
data please enter valid
data okay please enter
characters less than
50 press
okay now let us try to enter a random
name okay uh we'll enter characters more
than 15 here
quy
z uh yeah it's like uh 1 2 3 4 5
11 okay now this is more than
15 okay I think it's not more than 15
let's try to enter a little bit more
characters yeah now we have entered more
than 15 characters and it is showing
please enter characters less than 15 so
now we can retry and try to enter some
valid
name um
now we have characters less than 50 so
it will take as the proper name now let
us try to enter another
name I'm just entering some random
data so another name maybe
great now the next type of data
validation is also done now let us get
into employee Department employ
department is something really superb so
I'll tell you about it it's actually a
list now let us finish the other ones
first that is employee salary physical
year and work timings after that we'll
learn about uh the list one now let's
come into employee salary so salary is
something where you have to include
decimal points so now let us go go to uh
data validation and inside the values we
have already dealt with whole
number uh text
length yeah now we'll deal with employee
salary that is decimal now minimum is
okay what is uh minimum can be
1. 0 and maximum can
be one
lakh or let us put that as 10
lakhs press okay okay not not just them
enter
valid
salary okay now let's change it to
minimum as
10,000 so the m employee minimum salary
will be 10,000 and input enter valid
salary
enter
salary
between
10,000 to 10
lakhs let us include a comma here
too now error
Alert in valid
salary please
enter
between
10,000 to 10
lakhs press okay now the message and the
data validation conditions are applied
to this column now let us try to enter
some invalid data first it will show the
error now we need to enter the valid
data yeah now it is taking the valid
data let us provide
20,000 and this has
35,000 now the next one is fiscally let
us imagine that we wanted to you know
work work on the employees for current
working year that is 2020 to
2021 and or you can consider 2021 to
2022 not more than 2021 and not less
than 2021 for that you can provide the
year option as well so for that you need
to select the entire column go to data
validation settings and inside here you
have an option of date so uh let us
provide the date option as between and
start date is 01 sl01 SL
2021 and end date is
01/01
SL
2022 and input
message let us provide data as
current fiscal
year Current financial
year enter
date between
2021 to
2022 let us copy this and going to error
alert error message will be S
invalid
date select okay now the error message
and uh the data input message and the
data validation rule has been applied
for this column so let's try to enter
some date here you can enter uh 02 of
February
2021 it will take as the correct data
now let's try to enter some wrong data
which is apart from 2022 02
02 20 22 it will not take this it will
throw error so that's how it works now
let us try to enter as
2021 and this is
March it will take it now another data
04 this time and
2021 so that's how your uh date data
validation will work now coming into the
next type of data validation that is
time so we have time here let us imagine
that you are providing some work timings
to your employees that should be from
9:00 a.m. to 5:00 p.m. not more than
that so you want to keep your work life
balanced for all your employees so you
just want them to work between 925 and
that should be fine and anything apart
from that time should be invalid so you
can do that by selecting uh time option
between it should be in between and the
start time will be
09 0 0 and this should
be
uh
17 that is 5:00 p.m. in the evening and
input message
M please
enter time
between 9
a.m.
to
okay 5 P.M
this should be the input
message now error
alert invalid
time error message okay now
okay work timings now this should be
okay we can do this as two columns
actually let us
cancel let us copy this and paste it
here you can do it as
login login timing and this should be
okay this should be log out
out so this should
be okay so this will be considered as
login and log out times now let us
provide the login time this should be
the right time and uh let's try to
provide wrong timings 08 now it will not
take it it will throw as error so it's
working fine let us provide 10
now let us provide the log out time it
should be below 5:00 p.m. so let us
provide 5 it will take
it
okay let's try to provide something just
1 minute less than
5 that is
59 okay there is some problem in this
let's check it
okay it is taking uh three that is
including
seconds okay let's provide seconds as
well 05 0 0 0
0 okay still there is some problem
okay we have entered four we should we
are supposed to enter 24hour timing
right
14 okay uh it should be 17 I think this
will work yeah now we are following
24-hour timing here so let's provide
another timing that is
16 30 0 0 this will be valid and
1530 yes this is also valid now we have
finished almost all types of uh data
validation things that is we have
finished with any value whole number
decimal date timing test length and the
last one is list so this is where things
get interesting now let's cancel this
now select the employee column
now if you're running a company then
definitely you'll not have only one
Department in it you might have multiple
departments right so let us consider
that you have a software development
company so in that the basic departments
will be software development team and
software testing team so what you can do
is you can provide a list so using that
list you can just uh H over to that cell
select the drop- down menu and inside
that you can select the option so we
will try to work something like that if
we have selected the entire
column selection
okay yeah let us select the entire
column again go to data validation the
selection contains some cells without
data validation settings do you want to
extend data validation to the sales
yes now let us provide the list
option and
here Source you can provide
as
developer and
test press okay
okay now input message select
one select
one error
alert
invalid data
select from drop
down
only press
okay now when you select the cell you
can see small drop- down icon right so
when you press it you will have
developer and tester option so you can
select developer for first one and for
second one you can select uh tester
right okay this is good for uh one or
two options maybe three or even maybe
like five but what if you have more
departments okay let's go to this uh
second sheet where we have the
department data so here we have like 14
departments no 15 departments or 15
employee type names so the first one is
CEO second is developer tester quality
analyst system analyst Finance human
resource so so many options are there so
you cannot keep on typing all of them
right so here it is 15 what if you have
like 25 like 50 Department right it will
be tough so for this you have another
option so let me select all this data
and copy this from this sheet to our
employee
sheet let us paste it
somewhere here let's expand
this okay now we have our uh list over
here now let us remove the data
validation from this
column clear all okay now everything is
cleared let's select okay now let us
arase this data as
well now let us apply the list data
validation again from scratch go to
list now here we can select the
source select this particular
cell and drag it until here and it is
has been
loaded now go to input
message data
options select
one from the menu or the drop
down copy error
alert select only from the drop down
menu invalid entry yeah now let's select
okay fine now you have the icon here you
have all the provided
options you can scroll down and select
any one out of these you can select
knowledge transfer for the first one and
second one would be system
analyist and the last one would
be Human
Resources now in the list everything is
fine okay this is showing you the menu
options and everything so but what if
you provide this sheet to your new joiny
or new employee in your company and by
mistake he messes up something like
deleting this okay so we have deleted
systems analyst and when you click on
the options here you don't have systems
analyst in that place you have blank
space right so there is a way where all
your U you know data can be messed up so
to avoid this what you can do is you can
actually um save this data in a
different sheet like I did here like
Department data you can hide this sheet
or you can protect this sheet with
password or something so that nobody can
mess this up so now let us get back to
the employe data and select this column
and uh clear everything press okay and
let us also clear this
and this as well this one too now let us
eliminate the rows or
cells the employee yeah
everything okay now we have basically
removed this data from here now let us
try to apply the data validation once
again now here let us select as list
and
uh here let us provide the option let us
go to next sheet let us select all these
options and press okay okay let's
provide input
message data
options or Department options
yeah now let's select
okay now let's hide this sheet now the
sheet has been hidden and you still have
the
options
developer or let us provide
CEO then we have
developer then we
have
test so that's how you can protect your
how to protect and lock cells in Excel
now now why do we need to log or protect
Sals in Excel let us imagine that there
is an Excel sheet with really
confidential data and you need to pass
that data to your subordinate or your
colleague to make some minor edits now
let us imagine that the edits are
supposed to be done to only one or two
columns and rest of the columns should
be left as they are now there is a huge
possibility that sometimes unknowingly
or unwillingly there might be some edits
done by your colleague or your
subordinate So to avoid such kind of
unexpected mistakes you might want to
protect and lock your Sal that you don't
want your subordinate or your colleague
to edit so before we begin be kind
enough to get subscribed to our YouTube
channel and don't forget to hit that
Bell icon to stay updated on the latest
it Trends and Tech
content now without further Ado let's
get started with our onepoint agenda
that is lock or protect cells in Excel
now let's get back into the Practical
mode and start our Microsoft Excel now
we are on the Microsoft Excel and this
particular spreadsheet is related to
employee
details yeah now the screen is visible a
bit better now let us imagine that this
is our confidential data and you want to
edit only two columns that is the
designation column and phone number
column now let us imagine that the
company has finished one annual year and
now there are some promotions happening
in the company and let us also imagine
that the phone numbers of the employees
provided by the company have undergone
some changes so now the minor edits that
you want to make are related to the
designation column and phone number
column now you can see that all the
columns that is the blood group new
salary salary hike current salary and
name of the employee serial number
employee number everything is editable
so in such kind of scenarios when you
pass on this data there might be a
possibility where your colleague or
subordinate might end up making some
mistakes right so you want to avoid that
so you can do that by locking the cells
now let us rename the
sheet now the sheet has been renamed
successfully so now you can log the
cells by the following
process so in this process you have two
major steps first one is to log the CES
and the next one is to protect the cells
by a password now when you select all
the cells in this particular sheet you
can just click on this edge here and you
have all the cells selected and now
right click somewhere on the sheet and
you can see an option called format
sales so in this particular option you
have some options provided which is
number alignment font and you want to go
into the protection part so you can see
by default Excel keeps all the cells
locked now we want the phone number and
designation to be unlocked okay so so
let's cancel it for now and get back to
the sheet and select the columns D and I
and now let's right click and go into
format Cs and in the protection option
make sure that you uncheck the logged
icon now select okay and
now okay so you cannot include the
merged the cell fine we have a cell here
which is merged okay let's remove that
okay now again select the entire column
right click get into format cells and
uncheck the lock option select
okay now we have finished the first part
that is locking all the cells which you
don't want to get edit and unlocking the
cells where you want to make some edits
now the second stage is protecting the
sheet now right click on the sheet name
and you can see an option called protect
sheet click that and now here you can
see select locked sales and also select
unlocked sales so let us provide a
password here so let us use some simple
password so that we don't forget that so
I'll be selecting 1 2 3 and now select
okay reenter the password to proceed now
let us type 1 2 3 again now select okay
and now the sheet is protected
now we have successfully locked and
protected all the cells in Excel now let
us verify that the cells you wanted to
protect are really protected and locked
or not so we have unlocked phone number
and designation and everything else is
lock now let me try and edit the blood
group so when I double click or try to
edit this particular sheet then I'll be
receiving a warning from Excel it says
the cell or chart you're trying to
change is on a protected sheet
to make a change unprotect the sheet you
might be requested to enter a
password so it says that this particular
cell is locked and you cannot edit it
now let us try to edit the phone numbers
which we kept as unlocked so you can see
I can really edit the unlocked cell here
now let me try to change this phone
number let me enter some random phone
number so you can see the cell is
editable now let us go through the
designation column and try to edit this
particular column now since I said that
there's a promotion happening in the
company so the deputy CEO is now the
current CEO of the company and software
developer happens to become the senior
software developer and the tester as
well let us imagine that he will become
the
senior and so on okay so this proves
that this particular sheet is editable
not the entire sheet but only the cense
that we kept in the unlock
mode
okay now this is how you lock and
protect your sales in X now you can see
that I have sales data on my worksheet
so uh let us imagine that your manager
asked you to find the average of sales
happening in your company right so let's
navigate to the bottommost cell and you
know write it down as average
here okay average
sales so here this is a cell where you
want to keep your average right so here
we'll be writing a small formula that is
average and tab to select it now you
must select the area of data right
so this is my array since average is an
array function and now press enter so
there you go you have the average sales
data that happened in your
company so to understand the page setup
in Ms XEL we will go through a demo
inventory in Excel where we'll be
considering some restaurant data now
without any delay let's get started with
the Practical demo now we are on the
Excel sheet and this is the data I was
talking about the restaurant data where
we have the First Column that is Rank
and next we have the restaurant name
followed by that we have the sales
happening through that restaurant and
then we have the segment category that
is what kind of service does that
particular restaurant offers now this is
the list what we made and uh what if we
wanted to represent this data in the
form of a printed paper right so I guess
you can see all the columns and rows
here so you can see that we have almost
like 250 rows so there is no chance or
there is no way to print all those 250
rows in one page of course it is
practically possible but when you try to
read the content it'll be so tough to
read Because all the rows will be clued
together so closely so that you cannot
read it so uh you need to print them
Page by page so and you need to also
take care of the data is aligned
properly according to the page size
everything and even you need to take
care of the margins of your page right
so that's what page setup basically
means so we had a detail overview of our
data and what actually we're trying to
make now let us see how page setup is
possible in msxl so to go to the page
setup menu or go to the page setup
options we have three methods let's
start with the simplest method so the
simplest method is by just clicking on
file and go to print option
and you'll see all the page setup
options right over here and you can also
go to print menu by pressing a shortcut
method that is contrl p so you'll
directly end up on the P setup options
now let's look at the second method so
the second method is you can see the
toolbar over here right which has file
home insert draw page layout formulas
data review View and help so the second
method we can use page layout option
from the toolbar and you can see when
you click on the page option from the
toolbar you have a new ribbon over here
so this ribbon is all about page setup
options so it will offer you margins it
will offer you orientation size print
area brakes background print tiles etc
etc right so all these options are many
Ed in this ribbon belong to page setup
now let us look at the third method so
the third method is something similar
you have view option on the toolbar so
just select View and you can see we have
few page setup options over here as well
the normal page break page layout custom
views and we have grid lines formula bar
heading zoom in zoom out select to zoom
free certain panes right so all these
are also the page setup options so we
have the three methods to enter the page
setup options now we are familiar with
those now let's get started with the
Practical mode on page setup for that
let me use the simplest method that is
holding the control key and pressing P
which navigates me to the print
option now we have various set of
options here we have no scaling normal
margins letter portrait orientation
cated print active sheets and note one
for Windows you can see this option is
the printers option for example if you
connected to a printer which is the
hardware printer then your msxl will
show the printer's name here so other
than printer you can also make some
other arrangements where you can just
directly print your complete data in the
form of PDF and then you can export your
PDF to your recipent that's also
possible okay now let's get started with
the first option that is print active
pages okay to understand this better we
need to create a new sheet not a problem
so you can see that we just have one
sheet over
here so let us kind of select all the
items and create a new sheet and try to
paste it over here or let's try to paste
it over
here okay now let's try to extend this
yeah this should be fine now let's
rename this sheet sheet uh or let's
rename it as
restaurant sheet
two for our reference now let us get
back to the print sheet option so I'll
use the shortest method that is holding
the control key and press P now you can
see that here we have have six pages in
total from the active sheets so the
current active sheet is the top 250
sheet so this particular sheet is the
current active sheet so if we print the
entire data in this particular sheet
then we will end up printing six pages
now let us select the other option where
you have to print the entire workbook
right so now you can see that there's a
change in the number that is 12 pages so
what is a work workbook so basically
workbook is the collection of entire
sheets in your Excel homepage so in this
particular Excel homepage you have the
first sheet that is top 250 and the
second sheet that is the restaurant
sheet two right so the combination of
these two sheets make up a
workbook so when you select the entire
workbook option you'll have 12 pages so
that's how we use this setting where you
select the active sheets the entire
workbook and there is another option
where you can only print a selected item
so if you want to do this then let's get
back here now what if your customer or
what if your client wanted the top 10
restaurants only so you can select the
top 10
restaurants by doing
this now you have the top 10 restaurants
let's go to print
so you can see Excel has automatically
selected the selection only print the
selected content only so you can see we
have only the top 10 restaurants or top
11 restaurants that we have selected in
our sheet and it is ready to get printed
so this is how we use the first option
now let's get back let's not select that
and let's make the pages normal now get
back to the print option again now you
can see the next option that is cated
right so in collated you have different
different options that is 1 2 3 1 2 3
and 1 2 3 and the next one which is
uncollated 1 1 1 222 3 3 3 so why are
these numbers present here and what does
colleted mean anyway so this is your
question right now the answer for this
question will be a little similar to
your examination Hall okay let us
imagine that you are an examination Hall
and you are the invigilator and you have
10 students in your room so you wanted
to provide question papers to all the 10
students okay so you have your question
paper distributed into three sheets that
is you have marked questions and your
questions are fit into three papers
right in this scenario we have 250 rows
and all the data is split into six
sheets that is sheet one one or page one
page two page three page four right now
if we wanted to print 10 copies then the
arrangement of papers will be page 1
page 2 page three page 4 page 5 page six
in continuous order right that is CED if
you wanted 10 copies of different
different ways where you wanted to print
the first sheet first then you wanted to
print the second sheet second and the
third sheet third that is you'll have 10
copies of first page 10 copies of second
page 10 copies of third page and so on
that particular approach is called
uncollated approach and if you're
printing the pages in continuous order
for 10 times then that particular
approach is called as collated approach
so this is what we had to learn about
collated and uncollated now let's get
back to the third option now let's press
again print now we have our data
here yes something
wrong select all the elements control P
yeah now we have our data back now we
have the third option that is the
portrait
orientation okay so when we click that
we have two options that is portrait and
landscape so you might be having a good
idea like when you try to click a
picture in your phone we have two modes
as well that is portrait mode and the
landscape mode so when you click on the
portrait mode this is how your data will
be looking now to understand the
difference let's click the landscape
mode so when you click the landscape
mode this is how your data
representation will change you'll have a
wider page to print so this is why we
use the page orientation but now let's
get the page orientation as portrait
orientation and we have now the fourth
option so what is the fourth option we
have various sets of options in here
that is letter and A4 size there are
also some more page options over here
where you can select the page types you
want to
print so basically you'll be having few
more options that is A4 A5 A3 Etc but
here we just have two that is A4 and
letter so basically you'll get those
options and followed by that next we
have margins so here you can see we
don't have any margins for our data okay
so now we can add some margins by
setting up this one as an option like
last custom setting or we can also
choose normal wide narrow right so your
page will be changed according to your
margin
size right so this is how you can change
the margins now let's keep it as default
let's set the normal one
now we have the next option that is
scaling
option so here you can see we are trying
to fit the page so when you're trying to
print these sheets using no scaling then
the actual size of the page will be
printed now you can also modify that you
can fit and print these sizes so you can
remember the first option that I said of
printing all the 250 rows in one single
sheet this is how how you can fit it so
fit the sheet on one page where you can
see all the 250 rows are fit into one
page but the data is not readable so but
we don't want this type of an approach
right so let's keep it as the default
one which is no scaling so these are the
variety of options in print now if you
wanted to print the all the six pages
into the PDF format and mail it to your
client you can also do that I think I
have explained it in the first place so
you can see we can do it by just
selecting this option where you can
print all the six pages and into PDF
format and then you can mail them to
your
client and you can also select the
number of copies you want you can
increase the copies to 2 three and any
number of uh copies you want for now
I'll keep it as one copy
[Music]
now let's print
this now let us select
the
location rename it and
publish now let's get back to documents
and see if the page is printed or
not here you go here we have the
restaurant data in the form of
PDF so we have our entire six pages of
data in the PDF
format now we are back on the homepage
now we had some limited functions in the
print function over here we had tried to
export the data so you can also export
the data or try to print it from the
print option over here you can just
select that as
well yes the pages are printed
now there you go we have the resturant 2
PDF
[Music]
data so that's how you can print all
your data you can either
choose the export option or you can
either choose the print option now when
you see into the print option we have
limited number of page setup options
over here now let's check out what we
missed so we are in the page layout here
so we had gone through the margins we
had set all the margins and then we have
also gone through the portrait and
landscape now you can see this size when
we were in the print menu option we had
only A4 and letter but here you can see
there are a wide variety of options that
is other than letter we have tabloid
legal statement executive
A3 right we have A5 and many more other
options and if you want some more paper
options you can go to more and you can
always select a few more options that's
how you do it and yeah we didn't had the
break option so remember when we had
been through the print option we did not
have the break option there let's select
this to normal A4 so you cannot see the
break option over here right so here we
have the break option so what does break
option does
right you can actually break the page uh
remember we spoke about the top 10
things or the top 10 rows you can always
do that so you can just keep the top 10
restaurants in your first page and then
break the remaining and to set page
breaks you can select the cell now I
need the top 10 so I'll select this
particular cell which is in the 11th
position and now I'll try to implement
the page break insert a page break and
now you can see on my sheet there is a
thin line which is separating the two
pages that's how you can see the
indication of a page break implemented
onto your page now how do you see it you
can see it by selecting contrl P where
you can see the first top 10 restaurants
being printed on your sheet so there you
go we have the first top 10 restaurants
printed on our sheets so that's how you
use the page
break now let's try to eliminate all the
page braks so you can select braks and
reset all page breakes so the page break
has been eliminated
now you can always choose a background
for your data as well you just uh if you
want you can add a picture to your
background I think that's not available
for
now okay you can work offline as well
let us select this
one so you can see that we have added a
background to our
data we can select all the text files
and then we can change the color so that
the text becomes a little visible and
yeah that's how you do it now if you
don't want the image to be added on your
file you can also remove that delete
page background and there you go all the
images are gone now the next important
part that is the print
titles now why do we need print titles
for that let's try to print this so now
you can see the first page in the first
page we have the title rank restaurant
sales segment category but if you go to
the second page you don't have that
title right you don't have the title
rank name of the restaurant sales and
their service category so if we want
that then we need to add
titles now for that let's select the
print titles option now let's select the
row to repeat on top now for that we
need the first row select the first row
and this particular row will be
repeating into all the pages now let's
try to print it so yeah now you can see
the titles are being added to all the
sheets you can see the page two page
three page four page five and page six
so you can see the titles added to all
the sheets now we are on the spreadsheet
so here what exactly are page breaks so
page breaks are something which help you
during your trying to print your data or
present your data in the form of a web
page right in those times you cannot fit
your entire Excel data in one
spreadsheet or one paper or one web page
right so in those scenarios Excel will
try to eliminate a few parts of your
spreadsheet or it will try to split the
spreadsheet so how exactly it looks like
so go to the view option and click on
The View and click on page break preview
here you can see the dotted lines right
so these are the things which separate
your data in form of pages so that it
can be printable on one single page so
some of the times the user or the xuser
manually inserts a page break right so
how do you insert it just go to the page
layout and here you can see the page
breaks insert a page break and you have
successfully inserted a page break again
go to the view go to page break View and
here you can see the solid line so these
solid ones are the ones which are
inserted by the users or anyone who had
the access to the data now how do we
eliminate them right so we can eliminate
them and we can try to you know get the
shet back to the normal way so all you
have to do is click on the cell which
has the page break which is right under
the page break go to the insert page
break yeah page layout menu go to the
brakes option click on remove page break
and there you go you have successfully
eliminated the page break and page break
preview to make sure that the page break
has been successfully removed so you
canot see that anymore so that's how you
try to insert or eliminate a page break
in Excel so conditional formatting is a
method to visualize your worksheet we
already have the charts in Excel to
visualize our Excel sheets in graphical
form but what if you had to see or
visualize the data in your worksheet as
it is so that is where the conditional
formatting comes handy now let's get
back to the Microsoft Excel and try to
implement some conditional formatting
now we are on the Excel work sheet this
particular data set is based on a store
now we have various columns in this
particular data set we have R ID order
ID customer name Etc and finally we have
quantity discount and profits now let's
go to profits uh let us imagine that we
have provided this particular store with
a target of minimum 15% profits now let
us find out the stores that have hit the
15% Target and the ones which did not so
for that let us select the entire column
now you can find the conditional
formatting option in the home toolbar
and in that ribbon the ribbon with
Styles has the conditional formatting
option click on that and you have
various options over here highlighting
the cells top bottom rules datab bars
data scales and icons so datab bars are
actually the bar graphs which highlight
themselves on each and every cell if you
implement them and these are the color
scales if you want to implement some
colors you can go through that and if
you want to represent your CES with
icons you can also do that for now let's
highlight the CES so select that and
inside this select the greater than
option and here you can provide the
percentage that is
15% and the sales or the stores which
hit the target should be highlighted
with green color and select okay and
there you go you have your stores that
hit the 15% Target and the ones which
did not hit the 15% Target now if we
want to highlight the ones which did not
hit the 15% Target you can highlight
them with the color
red
so conditional formatting highlight the
Cs less than
15% with color red and text dark and
there you go it's done now let us
consider another example so this
particular example is based on train
data set and here we have the passenger
ID survived passenger class name Etc now
we have a train accident incident over
here now one is indicated that they are
alive and zero is indicated that they
are not alive now let us use this column
and conditional formatting highlight the
cells containing the text as one with
color
green
okay and
and highlight the
cells that contain text zero as r that
indicates they are not
alive and let us select the
icons now we have the icons over here
the X indicates that they're not alive
and the Green Tech Mark indicates that
they're alive now that is how you use
conditional formatting in Excel Excel
now here on my spreadsheet you can see
some data so this particular data has
some color markings so we have used
color codes to Mark the designations of
all the employees with different colors
so for example blue for manager purple
for senior and so on right now our duty
is to count the total number of
employees present in a company based on
the color code this can be done using
filters but let's try with color codes
now we don't have a readily available
function in Excel to count cells based
on their colors but we can make use of
macro now to make of macro you might
want to enable the developer options so
by default these will be disabled in
Excel to enable them right click on the
toolbar and you can see this particular
option click on the customize ribbon
option and you will be provided with
various set of options now on the right
side you can see developer options so
make sure the checkbox is clicked to
enable developer options and press on
okay to completely enable developer
window right now when you click on
developer options you can see various
options here visual Basics macro Etc now
click on the macro to create a macro now
just give a name to the macro function
so I'll give simply
Lo and press on create to create that
particular macro function so we have
already created a macro that is Count
colored cells which we will be using
using in our current spreadsheet for
counting the colored cells now let us
erase the newly created function now
getting back to the original code so how
does this particular count colored cells
works so let us understand the function
first then we will try to make use of it
in the spreadsheet so the function name
is Count colored cells as you can see
and this particular function will have
two parameters current cell and
spreadsheet area so the current cell
will be the cell address where the color
you want to cut count is present and
then the spreadsheet area so what is the
range of cells where you want to locate
that particular color and count the
number of reputations that is the
spreadsheet area and then comes the
variables used colored cell range color
code and color cell count so the colored
cell which particular cell is having
that particular color and what color
code is involved in that colored cell
and colored cell count so what is the
total count what is the total number of
colored cells you have in that
particular range that is colored cell
count now let us consider color code is
equals to current cell interior color
that is current cell do interior do
color so this is the function which will
identify the interior color of a
selected cell let us imagine that
initially color code is equals to the
current cell's color code okay now we
will have the for Loop and that for Loop
will include colored cell which is the
current cell in the spreadsheet area and
if if the colored cell the currently
selected colored cell do interior do
color is equals to the color code that
we have selected then colored cell count
the variable which is used to count the
number of colored cells is equals to +
one initially by default it will be zero
in case if this particular if condition
is true that is the current cell color
and the color code given is equals to
same then the value of colored cell will
increase by one and this particular if
Loop will run as many number of times
the condition is true so let us imagine
that the condition will remain true for
five times then we have obtained five
similarly colored cells in the given
range of cells in a spreadsheet and once
the condition fails then the latest
value present in color cell count
variable will be displayed which will be
a final result so this is how the macro
function works now let's close the macro
window and get back to a sheet now let
us try to make use of the macro function
that we just created so that is equals
to count colored cells press tab to
select the macro function and the first
one is the current cell so this is the
current cell where the current color is
been provided comma this cell range so
our cell range will be from C2 to
c31 right so this is the range where we
want to count the similarly colored
cells that is Sky BL blue colored cells
close the function and press enter now
you can see the function has identified
six repetition of sky blue color in this
particular range of cells now you can
drag the same formula across all the
cells and it will be providing the
results of the same so six senior colors
that is pink or purple color then two
reputations of this particular color two
reputations of trainy color five for
dark blue and six for green right so
this is how the macro function works and
the key point to remember here is this
particular macro will work only for
manually colored cells so there are
situations where we have used
conditional formatting to color a single
cell let us imagine that we have used
conditional formatting to recognize the
employs with salary above 30,000 as
green color and if we try to use that
particular green color to be counted by
the color count cells function no it
will not happen it will not consider
that color as a f feedback or an input
now in future we will also try to design
a macro which can recognize the colors
made by conditional formatting as well
but this particular function will be
exclusively used for manually colored
cells only dated IF function is majorly
used to find the differences between two
individual dates so it is also called as
dated if by a few people and a few
people also reference it to date diff
that is State difference so anything is
good now to find the difference between
any two dates let's select a few dates
so the first date would be uh let us
imagine that you're an employee and you
wanted to find out how many years that
you've been working with an organization
so for that you might want to need today
and the date where you join the company
right so it might be date of joining and
today so what's the difference right so
let us imagine that you join the
organization somewhere around
2010 0
1
05
2010 and today so for today can you can
give any particular
date let us imagine that uh today is 1st
of January 2021 or
2022 now we have a b dates that is the
joining date and the current date which
can be the current date so the Daye can
be anything so you might be watching
this on a different date as well so the
date of that day for today would be a
little different so we'll imagine the
today's date as an imaginary dat now we
might want to find the difference that
is in terms of ear so you wanted to find
the difference in terms of ear so here
you can find the difference in terms of
years days and months as well so current
L let's try to find out the difference
in terms of years so for years you might
want to specify the third parameter in
the DAT function as why if you wanted
months you have to give the M as the
third parameter and if you wanted to
find the days you might want to give d
as the third parameter uh so we have
discussed the third parameter so what
are the first two parameters right so
the first two parameters are the first
date and the last date so the first date
in this situation is your date of
joining and the last date or the final
date
which you want here as your today so
let's find the difference so for that
equals to so it can be dated if or date
diff so date if and the first state
which is the A2 and comma the last state
that is B2 and your parameter third
parameter in terms of a status why close
the bracket and press enter so there you
go you have your date here so it's been
like 11 years that you're working with
your organization so that's how you find
out the differences so we have 11 years
because uh we did not finish the 12th
year completely so if we had given the
month as U maybe six where we have
finished our total years then it will
automatically change to 12 years
so now for this particular task we will
be considering the student database now
let's get into practical mode and start
our Excel so on my screen you can see
some data related to students now let us
try to duplicate some of the rows
here so we have selected the rows 8 9 10
and 11 now we shall copy them
and paste in so now these are the
duplicate elements right now there might
be possibilities about the duplication
of class because all the students all
the 10 students are in same class so
there might be a duplication but we are
not looking for such kind of
duplications right we are looking for
the duplication of the entire row for
example we have the details of mik over
here in the eighth row which has the
role number or serial number as seven
name as mic role number class blood
group and subject scores percentage
round of percentage and total marks so
the entire eighth row is been copied
here and it is in the row number 12 we
have the exact same details so what if
you have the exact same rows duplicated
so that's what we're going to deal with
we're going to eliminate the duplicate
rules all completely together now to
carry over this task we have to select
the data from toolbar so you have
various options file home insert Etc so
you need to select the data and inside
data toolbar we have the ribbon and
inside ribbon you have got the duplicate
values or remove duplicates uh option in
the ribbon so select all the data select
remove duplicates and now you can see
that we have all our uh column names
over here and make sure that you have
selected my data as headers and then
select okay
and now you can see that Excel has
automatically removed four duplicate
values so four duplicate values are
found and removed 10 unique values
remain now another type of uh remove
duplicates can also be done so you can
see that here we have another data set
which has phone numbers or phone names
that is phone makers that is Samsung
Huawei Vio Oppo Etc so there are some
duplicate values in this I've got Cy
pasted then so now you can also do that
so just select all the
data and now go into home and select
conditional formatting and here select
highlight cell rules and inside that you
have duplicate values selecting this
option will help you identify the
duplicate values in your data set but
not to remove them so you can select
that and you can see the duplicate
values are high highlighted over here in
the red color now again you can get back
to data and select the option of remove
duplicates and select okay and the
duplicate values will be automatically
eliminated so that's how you delete or
eliminate duplicate values in uh data
set and you can also see that we have
another duplicate in this particular row
and if you wanted to select a particular
row and eliminate the duplicate signals
to do that now for that you need to go
to the same process remove duplicates
and here Microsoft Excel is asking you
if you had to expand the selection to
the entire data set or you want to
continue with the selected data set
itself or selected column itself so for
that you need to select the continue
with the current selection and remove
duplicates and press okay and you can
see one duplicate value was found and it
has been removed and six unique values
remain so that's how you use the
function of removing duplicates in Excel
so what is suit in Excel now you might
want to calculate uh the total sum of
sales right so let us assume that this
is your entire store's sales data and
you wanted to calculate the entire sum
of sales so there is a sum function in
Excel where you just have to you know
select all the sales and apply some
function onto it and you'll get the sum
of all the sales right so with something
like this this you'll select all the
cells and then you'll apply a sums
function and you'll get the sum here and
average everything right so this is fine
but what if you just wanted to find the
sales that are happening in only western
region as you can see our column f is
based on region so we have four regions
South West East and Central so your
manager asks you to find the sales
happening in suppose say a west region
so how do you calculate it would you
manually go into each cell and check if
it's West and calc that will be time
consuming right so what if you had a
method which could do it for you just in
a matter of few steps or few clicks
right so that's exactly sum so it will
basically add a condition calculate the
sum of sales where the region is equals
to West simple to like or similar to
seal query right so that is exactly what
we're going to do in Excel today that is
using Summers now let me add here Sales
of West Region now in the next cell
let's add uh summus function remember
there are multiple sum functions in
Excel so when you type in suum you can
see so many options based on some right
you just have some followed by that you
have some if it is just some if not some
ifs today we are concentrating on some
ifs so not just some if we'll go to some
ifs so we'll be have having an option of
placing multiple if conditions there so
let's also try that more on that later
now let's press tab to select our sufs
function now okay we forgot another
important step so before we apply sufs
we need to convert our table into a
table format yes it looks table but
right now it's not in tabula format it
is in the format of database we know
that Microsoft Excel considers its data
as a database by default so to convert
the database into to table just click
anywhere on the data and press contr T
so that you'll have a new dialogue box
and it will ask if your table has
headers just make sure that you have Ted
in it our table does have headers so
select okay now our database has been
converted into a perfect table and it's
ready for applying sums function so now
you can see our database has been
successfully converted into a table now
let's begin with our sum ifs now again
remember we are using some ifs here not
some if or sum so select sum if and
press Tab and we have our sums function
now the first step which column are we
looking at what are we finding out right
which is a sum range what sum do you
want for now we need sum of sales so
select the sales column and we have a
simple key function here let's hold
control again press shift and hold shift
together so we're holding control and
shift key together and now we're
pressing lower Arrow key once yes the
entire sales column has been selected
comma Now where do you want to look so
we want to look I mean which part of the
table you want to fetch right so this is
the region part we want to fetch right
now so again hold control and shift
together lower Arrow key the entire
column is selected now the next last
part so we want to look into the both of
the columns that is sales column and
region column now which type of data you
want so for now we want Western type of
data that is the sales data which is
happening only in West right so we'll
select this cell with the data as W or
you can also manually enter it for now
let's select the data so I'm selecting
the cell F7 which has uh data as West
right now let's close the parenthesis
and press enter so there you go you have
the sales happening in the western
region now you can also try to format it
in the form of currency so let's use uh
dollars since it is based on American
states so let's use dollars so there you
go now we are having the sales data of
West now let's try to copy the same to
all the four CES and uh for now here
let's type in
East and this can be South right and
this can be your
central there you
go right now you you can also copy the
same
formula and make minor edits to it so
here instead of f8 you can select uh
East so where is East here and press
enter so you have the sales related to
East now let's try to edit F9 with South
select South Region enter so you have
South sales and what remaining is
Central so let's edit Central as well
and press enter so there you go right
now you have all the separate sales data
from west east south and Central now
again uh you might want a bit more
granularity in data right or in your
reports right like you wanted to find
the sales data happening in West and you
wanted to find out the category based on
only Furnitures so your manager will ask
you okay a fantastic job so now just
give me another minute detail on the
data want to calculate the sales
happening on West based on category
Furniture right so he wants furniture or
he wants office supplies only right you
can also do that it's not an issue at
all now let's try to copy this cell and
paste it here and sales invest let's add
another input here on
furniture right now you wanted to
calculate the sales happening in west
region only on furniture so you can also
do that now now we can use the same
formula as well just copy paste the
formula here right and you can add few
things here when you try to add a comma
it will automatically generate the next
criteria so it is expecting that you are
looking for adding another criteria
right now which column you want you want
category so let's select category you
want to select all the cells same triack
control shift lower key and again press
comma and you want wanted to select only
uh Furniture so select on furniture cell
there you go now press enter so there
you go we successfully have the sales
data from west region based on category
Only Furniture so that's how you do suf
in Xcel you can also add a few more
details to it like in Furniture you can
also look only for tables so here you
can see in west region we have furniture
sales data and we have sales data of
West Region based on furniture which
includes only tables you can also do
that using sums function in the same way
all you have to do is select the
subcategory column and select the cell
which has the data as tables so this is
how you use sum in Excel now creating a
pivot table is just away from a few
clicks trust me is just a couple of
clicks and you'll have your pivot table
ready to operate now click any cell or
select any cell on your spreadsheet
which has the data and navigate to
insert menu on the toolbar and select
pivot table and that's how simple it is
now automatically Excel will choose your
data you can see the lines over here
dotted lines that means the Excel has
already selected the range of data that
you want to put into your pivot table
and you can either select the existing
worksheet or a new worksheet I'll select
a new worksheet over here and just press
on okay and there you go you have your
pivot table ready now here you can see
your pivot table is a little empty all
you need to do is drag and drop the data
elements that you want on your pivot
table so here you can see on our
spreadsheet we have some business data
related to a store which has furniture
and states and subcategories of the
furniture and all the you know in in
category you have furniture office
suppli technology Etc right so those
have some subcategories as well and
different states and the regions as well
and the quantity total sales happen
right so using this business data we can
find out some key insights of this
particular data using our pivot table
right so here we'll add the data now let
me drag the region into rows click and
drag and let me drag uh categories into
columns which are the different
categories which are involved in the
sales data and values what are the sales
happened right and there you go you have
Furnitures off a supplies technology as
the three different categories and those
are the sales happened in central a
South and West regions and this is the
grand total and instead of regions you
know you have all the regions over here
right so Central East Southwest or
instead of uh all the regions or instead
of all the categories you can you know
place a specific category in your pivot
table for example let me push the
category into the filters and here you
can see the total sales happen in those
particular regions now what if someone
asks you to find the sales of Only
Furniture right so your manager comes to
you and he'll ask you getting the sales
of Only Furniture you can select that
and there you go you have the furniture
sales in all the regions right or you
can also do the vice versa you can also
drag uh regions into the filters and if
you wanted to find out the region wise
sales of furniture you can also do that
right so here we have furniture and here
we have all the regions and let us try
to find the sales of furniture in the
central region and there you go right so
this is how um the pr table works and
you can also remove the regions for
example if you wanted to remove a
certain element from filters or anywhere
if it has columns rows anything you can
just drag and drop it right so that's
how it works and also you can increase
the level of detail or improve the
granularity right so here we have
categories on our uh you know columns so
you can also include subcategories into
the
columns right
and and you have the region wise
sales so in the region filter select
all press okay and in the category
select
all press okay and you have the
furniture here office supplies here and
Technology here and also if you want to
include the quantity how much or how
many number of um you know things got
sold in those particular categories and
subcategories you can also do that so
here in the furniture section books
these are the sales and these are the
number of quantity or elements sold in
in those particular you know sectors so
you can you can also improve the level
of granularity you can also improve the
level of detail using pivot tables in
now AutoFill in Excel is a feature where
you can add some series automatically
using Excel for example let us imagine
that you wanted to add index numbers
right so starting from 1 to 10 so
manually you will be typing in the
numbers and you'll be doing it right so
instead of this what if I said you there
was a simple way where Excel will fill
all the data for you right so just start
with one 2
and select these two cells and you can
see the flash logo over here right so
that's the indication that Excel has
understood your pattern and it is ready
to fill the remaining numbers so as long
as you drag this logo or that ending
part where you have a small square box
that will fill all the numbers until
where you have traged so that's how
autofill works and you might be
wondering will it work only for numbers
no it will also work on some logic as
well okay this was the first part let us
also look at some other tricks with
autofill now most of the time you might
be just adding one number and try to
drag that cell and the result is it'll
copy the same number all the time right
so you can change it by clicking on this
menu you can click on fill series and it
will change to series and this was one
more trick with numbers now getting back
to the next type so let us imagine now
the next type of series you want add as
weeks in a day right so you'll be having
Monday Tuesday Wednesday right so you
can also do the series of week days
using autofill and Xcel as well so drag
that cell and it will give you all the
days or week days in a week now let's
try
January and this will give you the
months in a
year in the shortcut way right since you
just just added the first three letters
of the month it will recognize the
pattern and it will do the same feel now
let us try the full names of
months right and now let's try to drag
it it'll fill all the full names of all
the
months now let's try the dates so let us
imagine that this is the first day of
the current
year right it will now give you all the
dates of that current month or till the
end of the year as
well there you go now let us imagine
that your manager tells you to add the
dates of working days in the month of
January so how can you do that it's
simple all you need to do is select the
cell go to the fill option select series
and here select column wise since you
want the dates and columns and here in
the type select date date unit should be
week dat and the stop value should be
the next month right since your manager
asked for the week days of January so
your stock value will be the first of
February press okay and there you go so
since the 1st of January is Friday it's
selected over here and the rest two
Saturday and Sunday are eliminated
similarly we have all the dates here of
working days only so that's how you use
AutoFill in ex so on my screen you can
see a simple spreadsheet with student
names and there marks on my shield so we
will be calculating what are the total
marks obtained by the students in
overall subjects and calculate what is
the percentage now we will carry forward
a few simple inbuilt functions in Excel
to calculate that first we will be
calculating the sum of marks so here
equals to sum
function and select the range of
cells close the bracket press enter so
you have the sum
now so we have to calculate it repeat so
by clicking on the same formula now we
have the total of all the subjects for
individual students and now let's
calculate percentage so usually we use
the you know divide by option that is
divide the total number of marks
obtained by the total number of marks
available that is we will be dividing 47
by 500 and then we will be multiplying
that into 100 so that's how we calculate
the percentage
so cell G2 ided by cell H2 press enter
and you will end up with you know uh the
decimal format that's perfectly all
right now we have also extended the same
formula for all our cells now select
this overall column or select the
highlighted cells right and get into the
data type here you have General so
that's why we have got that uh decimal
format but when you press percentage
you'll have the actual percentage of all
the students according to their marks
achieved now we are on our Excel
spreadsheet and this spreadsheet has
some values of employees that is their
employee ID name designation Department
salary date of birth and date of joining
now our concern is to highlight some
cells which are duplicate in this or
some rowes which are duplicate so for
example here you can see on row number
seven we have the details of Mara and
you can see the same details of Mara in
the row number 14 as well right so you
need to eliminate those cells or you
need to eliminate those rows now you can
say there is a quick way just few clicks
and those will be eliminated that is
conditional formatting yes that is also
one way but I'll show you what's the
problem with that okay so let us select
the entire table and get into
conditional formatting and here you have
duplicate values highlight cell rules
with duplicate values and there you go
the problem so here we have designation
which is duplicated in certain ways
department and salary state of joining
right so there are certain conditions or
scenarios where you cannot directly
identify which is the duplicate row
again you can use the same for this
right so you can select the
entire uh column of employee ID ID and
you can uh identify the duplicate cells
here since the employee IDs are unique
for everyone so this is a way but let's
dig a little deeper and understand the
way of highlighting duplicates with an
advanced technique so let me cancel this
and for that let's create a new um
column and let's name this as
duplicate flag
e
flag so the new column has been created
which is D flag so here we will be using
a simple formula that is
count if and here press tab to select
the formula and here we will be adding
our uh range that is which range are you
considering so the range will be the
employee ID since employee IDs are
unique for every
employee
and and let's press function F4 to fix
it and now uh again the criteria and the
criteria will be the same cell that is
A2 and now close the bracket and press
enter now let's track the same formula
to all the
rows okay now here we have now what you
can do is uh sort it in an advanced way
go to sort and filter and custom sort
and here you'll be sorting by the D flag
and it will be smallest to
largest so now uh you can apply the
condition formatting to highlight it you
can select the column H or the D flag go
to conditional formatting highlight cell
rules and here you can get into greater
than and give the value as one okay so
all the cells which are you know
repeated multiple times will be selected
here and they will be highlighted with a
selected color what you have given so
I've given Red so now you can select
these cells and highlight them up delete
them from your
spreadsheet and now all your data in the
spreadsheet is unique and clean now
let's quickly get onto the Practical
mode where we will have some data sets
over which we will be trying to apply
the charts now there are various types
of charts in T and those are the pie
chart the column chart the bar chart
column versus line pav chart and Spar
line charts so we will discuss one after
the other first let us discuss about the
pie chart so as you can see we have a
small data set on my screen which I have
personally created and uh this deals
with the companies and this deals with
the shares of the automobile industry so
now for this let's create a pie
chart select all the data and then get
into the toolbar and inside the toolbar
you can see various options and inside
that we're going to use the insert
option and when you select the insert
option you will have a ribbon here and
inside ribbon you can see groups tables
illustrations addin and here you go
these are the charts so now we needed a
pie chart so select on that icon and you
have various types of pie charts you
have two dimensional pie charts three
dimensional pie charts donut type of
charts Etc right so I would like to go
with the three-dimensional pie chart as
it looks a little more appealing and
easy to understand and now there you go
this is the title of the chart and these
are the legends of the chart and this is
the chart area and these are the values
now we can uh you know kind of add the
data labels as well so you can just have
to press the plus icon over here and
you'll have the options you can add the
data labels which will give you a lot
more interesting
values and uh you can also turn this
around as you can see we on double
clicking any part of the pie chart
you'll get this option which is called
as format data point and uh let's
imagine that you wanted to show the
information related to this particular
one which is Volvo which has the biggest
share so this is in front right now now
what if you wanted to show the
information related to data which is
over here right for that you can select
this particular Arrow key and you can
just twist it and there you go it is
coming in front this will be uh a great
way of presenting your data to your
clients right
yeah and there is another option where
you can directly explode this like you
can pick it out you can see this right
Point explosion where you can split it
out and you can show that especially
like you can highlight it and show it so
that's the you know a great way of
presenting your data so that's how you
can work with pie charts in Excel now
let's quickly move to the next one that
is the column chart and in the column
chart we have the data related to some
company and uh the profits of that
company based on a so you can see that
the year has started from 2008 and it
has gone until
2021 and these are the profits of that
company year after year so since we are
dealing with column chart I'll tell you
the simplest way to create a column
chart there is a shortcut key for that
which is alt F1 and there you go you
have the chart right on your screen
now you can uh expand the chart just by
dragging it like that and you can see
that there is some problem with this
chart that is we have numbers here
instead of ear right you can see that
the First Column is the es which is 2008
to 2021 but here you can see we have 1 2
3 4 which is not proper right so to
change the axis that is you can see the
axis which is 1 2 3 let me expand my
screen a little bit
yeah so if you look at my screen we have
two different columns one column is
about the ears and the second column is
about the revenue now what we want to do
is we want to create a line graph where
we have year on the x-axis and revenue
on the y- axis and we will be generating
a line graph based on the data we have
now for that we have to select the
entire data what we have then we need to
go into the insert option in the toolbar
and then you can see a lot of chart
options in the insert ribbon now here
you can see that we have a line chart or
area chart option select that and here
you can see 2D line graphs now let's
select the first one and there you go
you have the line chart here now let's
rename the title
and there is one problem with the chart
here you can see that the x-axis is not
actually pointing the ear you can make a
change to that no
worries just right click and then you
can see the select data option and there
you go you have the edit option here
just edited and now select the range
I am selecting the ears now let's select
okay and there you go you have the ears
here you can just try to expand it a
little
more
yeah so now you have the revenue on the
Y AIS and year on the x-axis and this
shows the growth of Revenue in your
company so this is how you create a line
graph in Excel now I'm on my Excel
spreadsheet
and I wanted to download this particular
data from a website
now it would be not ideal if I go on
typing each and every individual element
from this particular website into my
Excel spreadsheet right that would take
all day or if in case the data set is a
lot bigger than what it has been shown
on the website or it might be next to
Impossible right basically it's way to
hectic now there is a simplified way
where you can do the same right you just
have to go to the website where you want
to download the data from copy that URL
as it and now back to excel here in the
data section go to import option where
you can find the option which says from
website just click on that now P start
URL now press okay and it might take a
little while to import your data and add
it into your rows and
columns now it will show you multiple
options or whatever the data is
available here and you can select the
data or table which you want to have so
this is not the table what I want to
have this is the one so let's check the
third one this is not the one which I'm
looking for I'm looking for this
particular data this particular table
just click on it and say
load in a few minutes the entire data
will be loaded onto a spreadsheet there
you
go so that's how you can import data
from web pages into your Excel so
Microsoft 365 co-pilot is one of the
latest AI products launched by Microsoft
so the Microsoft 365 co-pilot will be
your AI assistant for all your office
sus from Microsoft so it can help you
ease up your process of making
PowerPoint presentations it can help you
write down the notes on Microsoft Word
and many more but today we will be using
this particular 365 co-pilot for our
data analytics using Excel so let me
remind you this particular AI tool works
with the versions of Microsoft Office
365 and later right now there should be
a new feature added onto your Excel on
the top right corner of your home button
but again if it's not it's completely
all right you can use your Microsoft
home button where you can find the
Microsoft 365 C pilot so let's navigate
to the home button yeah now I'm on my
home button and here you can see a small
icon which is completely new out of
nowhere this is your co-pilot right and
you can just click on it and you can
fire your co-pilot and uh today's point
of discussion is about co-pilot with
Microsoft Excel so when you are starting
Excel there are some prerequisites when
you're working with copilot So currently
it is in the preview mode so it is
capable of using some data set which is
in the table format I have converted
this into a table format it's really
simple control a to select all the cells
and then control T to convert this table
uh the option my data set has headers
and okay so your data set will be
converted into a table and another
prerequisite is when you pop up the
Microsoft uh copilot it will ask you for
a data set which is under 1,000
kilobytes so my data set here is about
800 kilobytes which should be clear so
the only process is you just have to
click on this particular option which
says attachments go to the data set that
you want to work today so I'll be
selecting my sales EU copy and just
press open it will take a little time
because it's completely new so it might
take a little time for that so the only
uh catch is Microsoft co-pilot is a
little slow compared to the other
competive co-pilots or AI modules it's
not a big deal they will catch up soon
let's wait for some time while the D set
loads and then you can fire some of the
commands let's say I have given my
command here please help me create a
regional wise sales report with this
data set and you can also give a few
more commands right so it's loaded right
now you can see the icon here now you
can write down give me country
wise sales
report and just fire the command you can
also um you know perform all your data
analytics what you regularly do right
when you have a sales data set you might
be wondering what it can do and what it
cannot you just have to build a story
about the report what you want to create
and then just you know write it down to
the co-pilot and it will generate the
report that you are looking for
currently it is working on the
country-wise sales report so it should
be giving me the report
shortly it's okay so there are some
problems with this so maybe it's the
data set data type so here we have uh
country and the data is General so you
might want to change that to text format
or character format so you have the text
data type here and just okay now save it
back now you can ask the co-pilot once
again okay or let's try to do a
different one okay now here we have the
order date and column C right now let's
say I wanted to find out which day did
they wasit right is it a Friday is it a
Saturday or Monday right so just try to
ask the copilot let me write it down can
you help me find out which week of the
day
was an order
placed here we have a
column named
aate
column
is see we need a new
column that tells
[Music]
me the
day of the
[Music]
week when an order
was placed so let's file the command now
it should give us a column D which is an
empty colum right next to a the date or
it should give us the steps to do the
same just by the command it might take a
little
while okay here we have it certainly
I'll create a new column D in you set
great this is what we needed and soon
let's try to ask another query can you
tell me
name of the
customer with highest
sale record in the data
set so the previous query which we fired
at will not be reflected on this
particular D set but it can be reflected
on the basis that V shared with the go
pilot here so currently I don't have a
direct connection with my Excel over
here I think it should be taking some
time for getting me that update over
here but anyways it will be updated in
did say that it is currently dealing
with that's not a problem now let us see
what's the answer I apologize but it
seems that I'm unable to extract any
text content from the uploaded file it's
okay let's try to file there are some
discrepancies but it should be resolved
soon but let's try some other thing like
region wise
sale so let's say you are an Excel
expert so you can do it within a few
fraction of seconds you can just apply
filters and get that data but there are
a lot of people new to excel so for them
the co-pilot from Microsoft could be a
boon right but down the line there are
improvements rolling out so I could
predict that it could be a lot faster
and you can have seamless integration
with your Microsoft Excel and your
co-pilot and things could be a lot
easier right so far we have the result
over here so it is giving us a few
results and it is finding a little
difficulty to extract a few that
shouldn't be a problem in a few couple
of days where the new updates roll out
so um proceeding ahead let's try to give
it another query or so so let's check
the data set so here we have the
categories and sub categories I think we
can give it um okay can you give
me the category y
profits so here we can identify which
category is performing really good
giving us highest number of profits so I
think let's check out if we have a
profits column yes we do have a profits
column over here so let's check that
brilliant right so it's giving us
distilled reports in one prompt so that
should be helpful for any of the data
analysts which are aspiring right and
which don't have have any idea about how
Excel works okay it seems that I'm able
to extract text content from uploaded
files
okay let's try to copy this and write it
down as okay is it
category okay let's copy this as well I
think it's about the case right
category subcategory okay let's let's F
subcategory category sales
report okay it's giving us the pre
answer to the previous query okay I
think this should help it's enable to
extract a lot of answers so far
currently there should be some
discrepancy but down the line it should
be
fixed able to extract any text content
from the uploaded file okay so basically
that's how you can work with your
co-pilot in Microsoft 365 Excel so there
are some descript so far so it should be
fixed down the line so that's how you
can use copilot in Excel for your data
analytics and so guys an Excel data
analyst always looks for ways to improve
their efficiency and gain deeper
insights from the data that's where chat
jbt comes in handy so chat jbt is an AI
powered language model that can assist
you in various task including Excel
based data analysis and for that data
analysis let's dive into some demos to
see how it works so so guys as you can
see this is the chat GPT 4 and you could
see this has the support of D browsing
and Analysis and usage limits May apply
like we have the version that has some
usage limits and for the data I have
downloaded the data from the kle I will
provide you the link in the description
if you want to download the same data
you can and I have that in my downloads
so this is the data that we'll be using
so the first thing we'll be doing is
data clearing and preparation so firstly
let's explore data cleaning and
preparation before diving into analysis
it's essential to ensure our data is
clean and ready for processing so for
that we will write a prompt in chat jpt
and ask him to clean the data chat jpt
has the feature to insert the files
provide the Excel file to the chat jpt
and we'll ask him that can you help me
identify and remove duplicates in this
Excel spreadsheet so let's ask him can
can you help me identify and remove
duplicates duplicates from this Excel
spreadsheet from the
provided
Excel spreadsheet you could see that sh
has started generating the response and
he is telling us that he can definitely
help us with that and he's analyzing
that
so you could see that J has provided us
the response so let's first go to our
spreadsheet so this is the spreadsheet
order number quantity
ordered price of each quantity and Order
lines so you could see that first CH CPT
has encountered an error and that is
with the corrector and coding Mis patch
and then he has loaded the spreadsheet
successfully with the second attempt and
you could see that he has provided a
like the smaller or the briefer version
of the spreadsheet that is the data set
consist of sales data with column such
as order number quantity order price
each order line sales order date status
so he asking us could you specify how
you would like to identify duplicates
for example should we consider a row a
duplicate if all its values match and
row exactly or are this specifically
columns we should focus on to determine
duplicates so we should or here we'll be
focusing on the column section so here
we want to
identify colum
section so is asking which columns they
should identify to duplicates so we want
the duplicates in order
number so the chj has started analyzing
the
spreadsheet so you could see that CH G
has Pur the response that they have
identified a total of
287 rows in the spreadsheet that have
duplicates based on the order number
number column so this indicates that
multiple rows share the same order
number suggesting there might be
different items or transactions within
the same order would you like to proceed
by removing these duplicates while
keeping either the first or last seg of
each order number or do you have another
approach in mind for handling these
duplicates so before identifying the
duplicates what we will do is we will
first get rid of the extra spaces so
we'll ask chat gbt that
first
find the Extra
Spaces in the
spreadsheet
and help us remove
them so you could see that it has
started analyzing it
so he is asking us that would you like
to proceed with any further cleaning or
data manipulation yes we going to
proceed further with data cleaning only
and now we will treat all the blank
cells if there are
any now treat all the blank
cells if there are
any so the spreadsheet contains plank
cells in the following columns with the
respective counts of blank entries that
is address line two has 2521 blank sales
than the state so depending on your
preference or the requirements of data
analysis we can treat these blank cells
in several ways such as filling them
with the placeholder value that is not
provided using a specific value that
makes sense for data that is example a
default state of Tory but potentially
leaving them as if blanks carry
meaningful information in the context of
your data so what we will do we will
provide a placeholder
provide a placeholder
instead that
is not provided or not applicable
okay so you would see in that has
responded that it has replaced the plank
cells in the columns that is address
line two state postal code and territory
with the place holder not provided so
you could see there's a verification
sample showing the updates so this is
how you could clean the data and now
with chat jpt you can quickly identify
and eliminate duplicates in your Excel
spreadsheet using buil-in functions and
formulas saving your time and effort so
that would be done by the chat jpt and
you can download the Excel file from the
chat gbd only after that you can just
ask him that provide me the output or
the resulted spreadsheet so if we talk
about now data analysis and
visualization let's delve into that so
Excel powerful tools for analyzing and
visualizing data and chat jpd can help
you make the most of them so for this
also we'll write a prompt to the chat JY
and we'll write for example we'll write
please create a PIV table and a
corresponding chart to analyze sales
performance by region so let's see like
this prompt what output does chat Jey
show to us so let's ask him please
create a pivot
table hand
corresponding chart to
analyze sales performance by
region so you would see that chat has
started analyzing the data and the
spreadsheet and you could see that the
pivot table below shows the sales
performance by region or the territory
so this table indicates that the EMA
region has the highest s performance
followed by regions where the territory
was not provided then APAC and finally
Japan so now let's create a chart to
visually represent this sales
performance by region you could see that
it has provided a chart that is a b
chart illustrating the sales performance
by
Ty so you could see the guys that it has
provided us the analysis and CH jpd can
guide you through the process of
creating PIV tables charts and other
visualizations in Excel enabling you to
gain valuable insights from your data
with ease so let's move to Advanced
analysis and automation so lastly we
will explore Advanced analysis and
automation so Excel capabilities extend
beyond basic functions and chat GPD can
help you leverage its full potential and
for this also will write a prompt to the
chat GPD that can you assist me in
building a forecasting model to predict
future sales based on historical data
present in the spreadsheet so let's see
what chj respond to us so let's ask
him can
you assist
me in
building a forecasting
model to predict future
sales based on historical data
so let's see what CH J respond to us so
you could see that building a
forecasting model to build future sales
based on historical data involes several
steps so for this you have to prepare
the data select a model train the model
evaluate the model and make predictions
so let's start by preparing the data
we'll aggregate sales by order date to
create a Time series of total daily
sales then we'll choose a model based on
data's characteristics
so we
will grant the permission to chat GT to
proceed with the steps so we started
analyzing the
data so you could see that that the data
has been prepared into a Time series of
total daily sales and before choosing a
forecasting model it's helpful to
visualize the sales time series to
understand its characteristics such as
Trend and seasonality so Chad gyy has
loed a daily sales data and then he has
given the visual Trends and the possible
seasonal patterns and the next steps
would include testing for stationarity
model selection and parameter touring
model training and then forecasting so
you could follow all these steps with
the help of chat GPT and could create a
forecasting model if you want we can
move to the next step so you could like
provide a prom to the chat gipd that
move
to or proceed with Next
Step so step by step J gy will provide
you a forecasting model or it will help
you create a forecasting model as you
can see it has started analyzing it to
the augmented Dy Fuller that is ADF test
gives up a P value of approximately 0.
033 since this P value is less than the
common significance level of 0.05 we can
reject the null hypothesis that the time
Series has a unit root meaning the time
series is stationary so you could see
that he's asking us shall we proceed
with the Sima model parameter selection
yes here is the model parameter
selection so you could select any based
on the trends and the bar graphs ploted
by the chat gbd
you could see guys that the chat chipt
has analyzed the spreadsheet and
provided the response that the grid
search for the optimal CA parameters
based on the
lower information Criterion suggest the
best fit model uses the parameter 011
and for ARA and
01112 that is for the seasonal component
so you could see that it has provided
the model and the parameters now he
asking us shall we move on to
forecasting future sales with with this
model yes proceed with that so you could
see that with Chad gips assistance you
can build complex forecasting models in
Excel allowing you to make informed
decisions and plan for the future
effectively so chat GPT is a valuable
tool for Excel users and data analysis
offering assistance in data cleaning
analysis visualization and Automation
and by integrating chat J in your Excel
workflows you can enhance your
productivity and uncover new insights
from your data so you can try jat gbt
for Excel data analytics today and take
your Excel skills to the new level and
you would see that uh there's an error
in the analyzing and he has encountered
an error while trying to plot the
forecast with its confidence intervals
so this type of error can occur due to
data type issues particularly when
handling data indices in forecast and
despite the hiup the forecasting process
using the ca model was completed and we
were able to generate forecast for the
next 12 months unfortunately due to the
plotting issue he couldn't visually
display it so we'll ask him can you try
once
again so you could see that this time sh
gbt has provided us the forecast for the
next 12 months that is the forecast have
been successfully plotted along with the
historical daily data so here we have
our forecast model and with the chat G's
assistance we can move with other
analysis also okay let's switch to
powerbi first let's deal with the type
of sources and then switch to excel so
now uh we have landed to the powerbi
desktop so here we have a wide variety
of options Excel workbook you can get
the data from hubs you can get data from
SQL servers and you can also enter the
data manually we have another option
called Data verse it's it's another
story for another day and you can also
customize your inputs here you can get
some data from web pages you can do some
data C you know website crawling and get
the data so apart from that coming back
to today's uh subject which is about
importing data from an Excel sheet so
Excel can be a little tricky sometimes
so here we can see an Excel data file so
here we have sales data for four years
2020 21 22 23 and uh let's say we wanted
to import all these four files into one
desktop PBI desktop can you do that yes
we can do that and another way here we
have is Excel data sets and a combined
Excel sheet where we have multiple tabs
the same four years of data which looks
something like this 2020 21 22 and 23
correct so four years of uh data
separated by four different tabs so can
you combine this yes we can also combine
that so we will check out two
possibilities so let's quickly go back
to powerbi window and go to get dat data
and here we'll try out the first one so
go to more and here select uh the folder
option so
file and uh folder Connect Now browse
the folder so we have the browse option
here go to downloads where we have that
folder go to excel select that and press
okay and another okay and we should be
getting the data
shortly now
uh here we have combine load and
transform you can just go with combine
and load but we don't want to have any
errors in our data sets so for saf as
side we will go to transform data and we
will shape our data manually and for
more tutorials on explanation on
transform data we have a exclusive video
on transform data in Pia you can check
out that so here go to the first one you
know the binary one where you have the
binary just right click it and make sure
that you remove any other colums than
binary we don't want them right so uh an
extra column is the custom column and
here you go and let the name we
custom need the name for that so Excel
we need a function where we can import
Excel workbook so this is the one Excel
workbook and open a bracket then just
double click on this content or you can
use insert and close the bracket and
just press okay there you go now you
have the table over here you can just
expand
it this prefix
okay now again just select the name of
the sheet and of the sheet rest
everything you can delete we don't need
any of
those you have it now expand the
data you can use the original columns as
prefix or not uh you will not choose to
do that press okay and your data should
be loaded shortly you can see we don't
have the names of the columns as the
column headers you find them in the
First Column I in the first row you can
okay let's remove this column we don't
need the name call we need the r ID
first there you go now you can click on
this option over here where you can find
a small table and second option use
first ver headers and that's how you get
the data in from all the different
sheets into your data set you can once
check the filters just make just to make
sure that we don't have any of the
column headers from four different dat
sets as one of the element right so we
don't have it so okay now this is one
way of importing data and another one
let's say go back to home and we wanted
to import a few more data sets uh like
the other one where we have the combined
one right so you have the applied steps
right you can just go on eliminating
them just for demo purposes so that we
go back to the homepage where we didn't
have any of the data set right now let's
go back to the source level we can also
run the source level we have so you can
even close the source level and close
the query
settings
data oh or we can completely C close the
power query editor yes now let's import
the Excel so this might take a little
while worry
not go to Exel Exel workbook and here
just select the Excel data sets and here
we have the combined data set remember
where we had four different tabs so
press
okay automatically it will give us the
power uh query option where we can load
or transform the data now you have four
different tabs now powerbi or power
query will give you an option of
choosing which particular years you want
to work with let's say I want to work
with only the latest two years that is
2022 and 2023 then I can directly go to
load or transform let's go with
transform as we did previously and here
you can combine the data sets all
together you can just uh use the uh
data merging option and combine these
two data sets so here we have the merge
queries option and you can just
Marge here we have the first table which
is 2023 here you just have to select
2022 from the drop
down the data here and just press on
okay and you will have the data sets
March for more explanation on how to
March data in power Pi we have a
dedicated tutorial on how to use merge
in PBI hello everyone I am M and welcome
to Simply Lars YouTube channel are you
want to get really good at using Excel
well Google Gemini can help make it
super easy Excel is all about handling
data organizing it doing math with it
and making it look nice with Gemini you
can learn all about Excel step by step
whether you are just starting out or
already know a bit Google Gemini can
teach you everything from the Basics
like how to write formulas to more
advanced stuff like analyzing big setes
of data all you have to do is ask Gemini
questions or tell it what you want to
learn and it will explain things to you
in a way that makes sense plus you can
practice right there in the chat by
following along with the examples so if
you want to get really good at Excel
without any hustle just start chatting
with Gemini and you will be an Excel
wizard in no time so yeah guys this is
our Gemini prom so here first we will
write how to learn Excel using G right
just for the clarification okay so it
will give some prompt see here you can
see there seems to be slight a
misunderstanding J the large language
model you can interact with okay so
learn Excel Basics you can do you can
learn data analysis with power query and
like this so here we will write give me
week wise
Excel
syllabus okay so it will give you week
wise ex labus so okay so this is week by
week Exel cabus basic to intermediate
okay not Advanced you can learn Advan to
so in week one what you can do you can
objective navigate Excel interface
understand basic spreadsheet topics you
can learn introduction to excel
environment working to sell rows and
even you can
just and you can particular modify the
selected text so in week one you can
learn these type of things entering data
text number dates and these and we in
week two you can learn building formulas
with references related and absolute and
you can learn Lo look up functions V
look up index match okay in week three
you can learn conditional formatting and
in week four you can learn a little
build of data analysis like this okay
so here we can write so this is like you
are chatting with someone okay so you
can write here give me brief
about the week one
syllabus briefly for the
same okay so see it elaborate particular
thing for you so see Master the
interface data entry and editing
formatting fundamental formula fun okay
so what if you want to learn particular
only logical function okay or you can
you want to learn building formulas with
references or you want to learn look of
function or you want to learn date and
time function particular so here you can
ask
Gemini give
okay how to work with dat
function in
XEL
explain me with example
okay so till then let me open the Exel
file for
you okay so yeah so see the date
function is Excel is a handed tool for
creating specific dates of working with
existing on so here is a back dra so
year month day example right so enter
today date in a Cell let's say cell one
okay so Excel is now
here okay so what he saying so what it's
saying enter today's date in a Cell
let's say cell A1 you can type directly
that is 2024 this this okay let me copy
it
directly okay and paste it here
okay is okay or use today
function okay fine or else you can write
today function write like this so if I
want to write
here equals to
today enter see you can see the same dat
exact dat
or in another cell that is B1 either
enter the following formula you can use
this formula date year A1 month this
this this okay so what it will do so
this part extra the year sell from A1
which has already did this then month
from sell this from sell this so this is
how you can learn any formula using
Excel
okay so I will WR write
give
me
example
of data in which I can learn
basic basic
formulas of
Exel okay so we will learn we will sort
some try to formulas of excel so this is
our you
know particular data set so as you can
see export two sheets so either you can
go directly to the sheets and you can
say sheets and uh this Excel are both
same okay so after that what you can do
you can okay first let me try to copy
it okay if possible okay I'm not
sure uh no it came in once and
only okay so I can directly
okay so it's creating a
spreadsheet fine open
sheets so yeah so this is the data so
now what're learning with formulas so
this data allows you to learn practice
okay what I just copy
everything copy it and
P
fine so now nothing is that I want to
learn sum function so calculate the
total quantity of each product sold by
using the sum function on the quantity
sold column okay so what you have to do
formula in cell E2 for chocolo chips
cake write equals to sum B2 okay so I
can write here in which column E2 column
E2 column equals to
sum
B2
B2 enter it's 50 as you can see 50 is
here okay so this is how you can learn
you can uh learn average function okay
let me copy directly from here and you
can paste it anywhere
okay
okay average of
C2 and C5 okay
fine why it's coming like this
okay
E5
right see what's the
issue okay what I can do equals
to
average average of
average okay my
B average
of this
comma this comma this comma this
okay okay so because of dollar it's
happening okay so this is how you can
learn you can find across allun the
price per unit column fora in sell
E5 E5
e if I will copy it
so this is how you
can okay because of dollar it's
happening okay see how it will work
now see because of dollar is happening
so now the average of C2 to C5 is two
okay so this is how you can learn you
can multiply you can apply V look up so
these are some additional tips
from the uh Gemini okay
so I want to create a
chart give
me explain me with
example
okay so this will will give you some
data see data is a export
sheet so uh we'll copy later okay
fine so these are some additional tips
are
here so these are the data
okay copy
it and paste it
here okay so now what I what can I do is
see select the data highlight the data
you want to include in this typically
includes category labels product name
okay in our example select cells A2 D5
A2 to
D5 from here
to okay
all right select data then what you can
do you can choose a chart go to insert
tab on the Excel ribbon and locate the
chart section go
to insert
then okay these are some charts so what
you can do I want this chart okay 2D
column
chart okay I selected this the best
chart depends on the data but you here
are some options column chart line chart
okay let's select column
chart
insert where is the chart where are
column
chart okay so here you can choose
recommended section okay so these are
are some recommended by Excel itself
okay so St chart is there cluster column
chart is here and this St bar is here
then clustered bar is it so we will
choose this stack column okay so as you
can see this is your data is ready okay
chocolate chip cooki cupcake bread lover
muin so this is how you can learn Excel
using Jina you can ask anything from
beginner level to advanced level from
Gemini it will give you through it will
give it will explain you repeat fun fun
in Excel the repeat or R is the short
form of this function and this can help
you build some good paragraphs so to
just check on the demo for today's
session I've been exploring a few graphs
online and I came across this particular
one so this particular chart is from
mckenzi and Company and it is the 2023
the year in charts so if we explore a
little more about the years and the
events in this particular company we
found one of the good charts and since
this is the time for appraisal you might
be considering a lot of opportunities to
explore or a lot of reasons to claim
that extra bonus on your salary pay LEL
now here if you see we have some reasons
why the IT team from keny wants to stay
back in the company and some reasons why
it may not right so here we have why why
I plan to stay in my current job career
development advancing potential and uh a
few more reasons and accordingly a
paragraph has been designed right so
I'll leave this link in the description
box below for your future reference if
you need and uh let's also take the same
data and try to build a similar chart in
Excel using the repeat function so I
have added the same data into my Excel
spreadsheet now let's try to create the
graph firstly I'll reference the reason
let's drag it down so that we have
reasons for all of these there you go
now next one is we will use the repeat
function re PT function and the text
will be Windows dot so that you get the
emojis over here you can take the
symbols or anything that you prefer so
I'll be preferring this particular
symbol for how many times for 47 times
which is ailable in the cell
B2 we will add if you go back to the
Chart here you can see a circular icon
right so we will add that circular icon
as well on the
symbols look for a dot let's go with
this and add a space if you see the
chart again we also have numbers right
so we're leaving a space and present the
number there you go just press enter and
there you have it now drag and drop this
particular formula to all the sales
there you go and now you can expand this
and also take
the
titles and now you have the same kind of
report almost so this also have some
borders and Etc so you can also do that
let's add some borders to our cells you
can add I'll go with all borders and and
they've also highlighted the top five
reasons there you go so that's how you
can create a beautiful bar draw using
the repeat function in Excel which is
about integrating AI into your
day-to-day operations in Excel now let's
quickly switch to the Excel workbook now
here I have a simple basic Excel
workbook and to demonstrate the uh Power
and potential of integrating EI into
your Excel spreadsheet we are performing
a small minor operation over here where
we just have few results right the
students their department and their
percentage now we going to calculate the
result of the pass or fail now you might
be telling me or you you might wonder
this is just a very simple if formula
why do you want to integrate an AI into
this now the special is let's say you
are a beginner in business analytics or
data analytics and even though if you're
a beginner you might figure out the
formula but let's say you don't have any
idea on generating a new formula Al
together it's like asking a question to
chat GPD so here the AI editor that we
are going to integrate with Excel is
related to chat jpt and it's you know
built on chat jpd so basically it will
give you a query editor where you can
just write your query in very simple
terms like you're just talking to excel
right in the same way like you talk to
chat GPD ask AI Editor to give you a
formula to identify column D numbers and
give you a result in column e so let's
get started on how it can be done so
it's really simple go to the home bar
and here you can see add NS option just
click on that and here we have the AI Ed
editor in case if you don't find it just
you can you can just you know download
any of the gbd for Excel so just U you
know click on this button over here and
you will get a search window just type
down AI editor or something so you can
just get one of those just press enter
so I'm using this the first
recommendation a formula editor you can
choose any one of these right charg
video Exel we've got multiple ones so
I'm using the first one already added
into my workbook so uh let's quickly go
to the ai8 formula editor you might have
to you know create your Microsoft
account and try to login right so here I
have the AI generator right now you can
just write it down in your own terms you
can use your local language you can use
Excel anything just write it down the
way you want it right uh you can just
type down uh give me an Excel
formula to identify
if the students passed the exam or not
result
percentage percentage greater than or
okay percentage greater than or equal to
35% is pass and
percentage less than 35 is fail is your
parameter are your par
is just submited and it will give you a
query or a formula which you can just
copy and paste into the Excel cell or
workbook here we have one you can just
copy it the way it is just paste it over
here so in place of A1 you're supposed
to use percentage enter now you have the
results pass f here right so it is we
did not specify the column number here
right in case if we have specified the
column number or column header which is
basically e over here sorry D over here
so we named it as percentage so it did
not and another thing to recognize we
did not convert this into table so basic
few things but overall you are getting a
readily available formula which is
completely Flawless you can also
experiment this particular feature of AI
AED formula editor with some complexed
Advanced Excel tricks as well should you
feel that we should prepare one of the
tutorials on Advanced Excel formulas
which are AI edit formula editor based
videos please do let us know in the
comment section below and we will get
that done for you now we have opened the
Microsoft Excel workbook here let's say
we have a fingerprint ID for all the
employees in a company now we have about
500 fingerprint here 501 fingerprints
right over here right all these
employees belong to different different
departments let's say HR department
development testing sales marketing Etc
right now here in the second sheet I
have a specific list of employees from
HR department let's say I have about 42
fingerprint IDs here right now what I
have to do is I have to create a match
function and
identify all the 42 HR department
employees in sheet two and tag them in
sheet one let's say this particular ID
belongs to an HR department then I
should be writing here
employee from HR department correct now
we have 42 entries it is fine but let's
say we had 440 or 450 entries
now would you manually go and you know
copy this and go to this sheet find
Control Paste and find all would you do
this task manually would you do this
match list manually right 440 times this
is truly time consuming right if there
was one single formula which you can use
and identify all those 450 employees in
one go it could be a miracle right now
that's exactly what we going to do today
so we will be using an F condition we
will be using an EAS num function and
along with that we will use the match
function so these are the three
functions that we are going to combine
together and try to identify the match
between the two lists and given and
result if the employee belongs to the H
department or not right now let's do
that quickly now let me write the
formula equals to if condition pressed
after selected Now The Logical test is
at number so you might be having a
question is number since we have uh the
data in a mix and match it's a number
plus text Will this work for only
numbers or will this only work for
alphabets no worry it works with both
numbers as well as character type data
types now I'm going to select the EAS
number function now comes the match
function so use the match function press
tab to select now the lookup value so
the first lookup value will be this
particular value which is in the cell A2
don't worry it will automatically change
when the formula enters to the next cell
right when it enters to A3 it will I
mean when it enters to B3 this A2 will
become A3 so no doubt in that now it
will be A2 comma the look up array which
array should I look to find the match so
the lookup array is right over here I'm
ignoring A1 we starting from A2 to all
the way up till
a43 so we have Prov ided the lookup
range or lookup array now the match Ty
we want the exact match no matter what
right so press St to select the exact
match now close this particular match
function now if this condition matches
then we should be typing it as or we
should be getting an end result as
employee belongs to HR department and if
not you can just so you know kind of
write another text so the formula is not
completely closed okay just ignore it
okay now we have to close the brackets
with the leading values so the match
function is closed and after that we
need to close the ease number function
now comes to the uh if condition so if
it is true then add this particular
value that is employeed from HR
department if not other department okay
other or you can also leave it as blank
okay so this is the formula press enter
so this particular ID belongs to other
department let's eliminate this now
let's drag okay let's name it as EMP
Department
details now let's drag the formula
across there you go now you can also add
a boundary border and now you can add a
filter and and filter this particular
table for employee from HR department
and there is a small error in the
formula but no problem let's remove the
filter and check the error I think it
should be about fixing the ranges so
let's fix the range function F4 so we
want U the column to be fixed and the
row numbers can change and here the
range what we are looking at so let's
give the range once again now let's fix
fix the range F4 to fix there you go now
enter and we can also remove this for
now I don't think we need this enter and
now drag the formula yes there you go we
have all the 42 entries so previously
there was a small fixing error so we
need to fix the range of elements that
we are looking at now just eliminate the
blanks and just select employee IDs and
there you go you have all your 42
employees present in the HR department
so that's exactly how you use the match
function to match a list of elements
from from one array and match it with
another array so here I have already
created a sample pay slip generator so
here we have used some data validation
methods to select the employee details
let's say I want to go with Brian and
here we have his employee ID his full
name and the month March right and apart
from that the number of hours worked you
know and here the total number of hours
he logged in was this and number of
times his client call got cancelled or
he was on a sick leave casual leave in
the month of March and the total rate
for each and every event so if he
logined he'll get about $45 and in case
if he's on a leave due to a client call
cancelled he'll get $20 for sick leave
$30 casually $225 and public holiday is
$30 and end of the the day we can just
you know multiply these two values to
get his final salary right so if we drag
this select cross overall here so we'll
get a salary for March right so this is
real time so you just select the
employee name and here you just select
the month which is about present in this
particular sheet here and you will get
all the details right and here is the
time sheet for the month of March and
all the employees who logged in and what
the events happening right so this seems
interesting but how did we create it and
how come we have all the employees here
without any duplications when you can
see there is a complete duplicate
entries or two three times entries over
here right how did we filter this out
and how did we export this data into a b
lip generator and how is it looking up
at all those values and how is it
summing up the salary right so we have
multiple uh uh formula and functions
implemented in this particular fa lip
generator so without further delay let's
try to recreate this particular pay list
or pay slip generator so firstly we will
create a sorted and filtered list let's
quickly rename this as sorted and
filtered employee
list so what we're going to do is
firstly we will try to import entire
list of all the employees present in
this particular organization by using
the unique function you guessed that
right so we are just fetching the unique
values one time only for once into this
particular list and we will also if
possible it's not mandatory but here I
have sorted them in the alphabetical
order right so that it is a little bit
easier for you to track now let's try to
name this as
employee right and here I will use the
unique function un and i q e just tap to
select the function and the array is
here so we have converted this
particular you know table into an actual
table using the controlt function in
Excel and I'll select this particular
you can see that highlighted downside
Arrow right in the bold black color so
that means it has pointing to select the
entire column just click that line and
there you go your entire column has been
selected now this is one way of or one
part of extracting unique functions now
another one is to try to sort these
employees list in the alphabetical order
right so I'll be using the sort function
over here press tab to select now close
the bracket over here and press enter
now you have the entire list of
employees in the alphabetical order and
there is no chance for any duplication
in this particular list
right so this is part one you can just
quickly save your file so that you don't
miss any track right and uh the next one
is the activities so one they have
logged in second one they've logged in
and by some reason the client call or
the business meeting has been cancelled
so they're sitting idle or they don't
have anything to do maybe they're
logging off early right so that is the
second situation third one is an actual
sick leave fourth one is a planned
casual leave and the fifth one is the
public hity so mostly many organization
Fons now we will copy and paste these uh
activities or events into this
particular sheet over here which has
sorted and filtered list of employees
now we are going to calculate the month
here so you can add month and here you
can deal with the same March data that
we have used here you can just copy and
paste that value over here as well so
you can also manually write it down as
March 2020 4 March April 2024 anything
correct now we are dealing with this
particular March data so we will proceed
with March data as well now the last one
this can be named as
pay slip
generator right so if we check the Basel
generator over here what we have here is
the first one employee name right so you
can first add that particular employee
select option over here right select
employee and here you will name or you
will try to add the employee name so if
you check this particular sheet too I
have the data validation option enabled
here I can just select the employees
from the drop down so let's try to
create a drop down over here equals to
or you can just go to data option here
just click the S and go to the data
option over here and here you have
columns click the drop- down option and
go to data validation now here instead
of any value you go to list and here you
can give the list of employees that you
recently filtered this is one way of
doing it but what's the downside of it
let's say I add a new employee over here
okay let's cancel this operation right
away and let's say I add a new employee
right over here maybe as Jonathan he's a
new joiny he's an intern or you know he
is a new join something like that so
when I add Jonathan over here the data
validation from this particular section
would not be able to identify him as a
new joiny the list will end at this
place which is this one right so we
don't want that to be happening now we
need to operate on a different approach
for creating this particular data
validation option so the approach is
really simple if you just get into this
particular sorted list over here and if
you check this list you can see we have
uh the First Column as employee starting
from snooper to all the way to the last
Jonathan right now what we need to do is
create a new formula so just enter into
the formula option right away here and
use the defined name option so that
you're defining a new formula Al
together and here you can type it down
as employee so you'll list of
employees list of employees you can
write an underscore so that the formula
does doesn't uh get confused so here we
have so where it is referring to right
so it is referring to this particular
column so a to a so just add an hashtag
over here so that uh it will
automatically update if there is any new
addition to the list now here we'll
create a data validation go to data and
data tools data validation and instead
of any value go to list and here you
will type the source as equal to
employee there you go uh maybe we have
to do a like change over here okay now
go to sort so here we generated a
formula so okay let's try to review that
formula over here list okay
and so let's go to the employees list
select the first cell over here
there you go and Define a name so the
name will be
EMP list sorted and filtered employees
so here you will be adding a hash so we
have we have written the hash after this
now we want that to go along with the
entire spell from second cell to the
entire existence of that particular list
right in case if there is a new edition
you can enter that Edition and consider
that as an employee right so youve done
that just press okay there you go now go
to the pp generator and here create a
data validation option let's clear this
now go to data data validation and here
you will be selecting a list and here
you will be writing
EMP list that you just
created press okay now you have the
employees list and in case if you add a
new EMP employ at the end he will also
here or she will also be considered as
an employee now part of the job is done
the next is a few more details like uhu
the employee number and
employee
name and let's check the yeah the month
correct so now how would you generate
the employee number so employee number
is like fetching when it comes to
fetching nothing is better than a lookup
function or an index match let's proceed
with a lookup function I'd go with v
lookup since it's my favorite one so
this B2 will be my lookup value and the
lookup uh table is this particular table
go to a corner and give that this is the
table you are looking at you want the
second column which states to the
payroll ID or employ ID and I want an
exact match close the bracket and there
you go now select the employee number or
employee name here and you will have the
employee number right here and here you
want to add the employee name which is
this particular cell whatever is present
in that particular cell is your employee
name and here you want to add the month
which is this particular month which we
are currently correct so far so good now
we want to create the fael generator so
first if we take a look at this
particular one here we have the activity
and here we have the number of hours and
salary now let's copy the same past it
in our fael generator right here some
formatting if you feel
like there you go we have our formatting
ready and here as well and here we will
add our hours and rate and
salary there you go and I think we
already predefined our rates so copy and
paste the rate R right
here now we need to calculate the number
of hours logged in and for that we will
be using a simple sufs function equals
to sufs so you need to pay a little
attention to this particular sums
function because everything relies on
how you are freezing the cell values so
you might be a little confused so you
can understand what I mean during the
execution of the sumf function so sus
and now the sum range so the sum range
is nothing but the hours table right
over here just click on the hours comma
so after hours we will be selecting the
employee correct so the person who is
working which is this particular column
just navigate your cursor to the
location where the cell B2 and B3 are
intersecting which is a black line over
there so you can see that particular
Mouse icon which is which looks like a
lower pointing downward Arrow key just
click that downward Arrow key and your
column will be selected and that's next
one now comes the critical part so you
need to go back to the pelp generator
over here and select the employee name
so employee name is existing in cell B5
so select B5 try to freeze it function
F4 so that this value doesn't change
because we going to drag this formula
across the next four activities so when
you drag the formula the formula should
be pointing to B5 but not succeed to B6
B7 Etc right that's the major thing
which you need to make sure and after
that you need to find the activity right
so go back to the time sheet data and
here is the activity or status Point
your mouse pointer to that particular
location where G3 and G2 intersect so
that you get the downward Arrow key just
click that there you go comma Now comes
another major part B slip generator
sheet so now we are on the P slip
generator so we need an if condition so
it needs to identify if the activity is
what's present in cell D3 or D4 D5 D6 D7
based on the table what we have then
print it the way it is and if not just
the way it should be right so you will
understand that more clearly when we
start with the F condition just select
the F condition press tab to select and
sell location D3
if D3 is equals to the activity which is
logged in then it should be nothing just
blank if not then it should have lged in
which is again
D3 now close the brackets so close the
functions
there you go now press enter there you
have it number of hours now just drag
this formula across all the rows and you
have it so now you have a realtime pelp
generator just uh change some employees
to check if our formula is working and
it's working fine you can also eliminate
the leading zeros but but let's keep it
right every minute counts now the final
one the salary slip generator so
E3 Cross or star into the rate enter now
you can drag the same formula across all
the celles to find the total salary now
you can also do a final customization
over here just copy and paste the cell
and drag this and uh you can
merge total
salary can even B this here you can have
sum of all this enter the final salary
you can also do some formatting so there
you go that's how you can create a
realtime automated bip generator in
Excel and with that we have reached to
the end of the session on Excel full
course should you need any assistance PP
or any other resources used in this
session then please feel free to let us
know in the comment section below and
our team of experts we'll be happy to
resolve all your queries at the earliest
until next time thank you for watching
and stay tuned for more from Simply
learn staying ahead in your career
requires continuous learning and
upscaling whether you're a student
aiming to learn today's top skills or a
working professional looking to advance
your career we've got you covered
explore our impressive catalog of
certification programs in cuttingedge
domains including data science cloud
computing cyber security AI machine
learning or digital marketing designed
in collaboration with leading
universities and top corporations
and delivered by industry experts choose
any of our programs and set yourself on
the path to Career Success click the
link in the description to know
more hi there if you like this video
subscribe to the simply learn YouTube
channel and click here to watch similar
videos to nerd up and get certified
click here