Hi guys, welcome
to this interesting
session on MS Excel.
So in the session,
you'll get a complete overview
of Advanced MS Excel 2016.
So we'll start this session
by understanding custom
and conditional formatting
and then we'll go through
advanced functions and formulas.
Once you understand
these two topics will go
through advanced tools
and data analysis
and finally end the session
with introduction to macros.
So today we have
a special guest pressure
who's going to take this session
forward so over to you Trisha.
Hi, my name is Trisha jagtiani
and you're going to do
the Advanced module
for 2016 Excel version.
So what's new in 2016 new
charts like waterfall,
Pareto, whiskers features,
like tell me, smart lookup
and enhanced pivot table
where you have
multi selection options
for slices search functions
at a pivot field list.
Many more such functions
have been added to 2016 Excel.
So let's start with
the important Formulae of Ms. Excel.
The first is the sum function
then enter the equal
to sign followed by
the function name
and the open bracket.
So, let's see in our example.
Let's say I needed total salary
paid to all the employees
in the organization.
So I have the employee list
and their salary detail.
So how do I start is,
I need this information
in cell J 2.
So to get started.
I will have to enter
an equal to sign
in cell J 2 followed
by the sum function
and then open the parentheses
or the bracket next.
We'll enter the range which we
need to total in our case.
It is a salary column
which is H to H.
So I select the h2h column
close the bracket
and press enter it gives
me the total salary
paid by the organization
to all its employees,
which is 1553825 rupees
You must have noticed
that I selected the full.
Column H and not just
the range 1 to h101.
Why did I do that?
I did this in order
to make our range dynamic
because if in future
there is any addition
to the employee data,
the sum function
will automatically incorporate
the new data entered say
we have a new employee.
All right.
So enter the name
of the employee here,
and I enter the employee salary
just for our example
as 50,000 Rupees.
Once I enter the employee salary
in the new column.
What happens is you will see
that the total salary
that we had calculated in column
J2 has automatically changed
and it is now adding
that 50,000 Rupees
that we have added
to the new column.
Let's understand an important
Concept in Excel
which you need to remember
before I get to the next
Formula for any formula
or function to work.
We need to ensure
that we provide correct
input truth excel in order
for it to provide you
an output having said
that there are only four things
that an Excel will understand.
Now, what are those four things
that Excel understands
they are it's
inbuilt function or formulas
which are the sum average min
Max with the another one is
the numbers like 1 2 3 21,000
that is showing
on the screen third
One is the true or false
and the fourth one
Is it cell references
when I say cell references,
what does that
mean the references
that we give to the cell
in order to get any output
of an answer like this one
this cell references
or when I was doing
the sum function.
Also, I gave that H to H as
my cell reference other than the
above all the data need to be
entered in the double quotes So
when I say all the data to
be entered in the double quotes
if I want to give
any information to excel other
than these four things
which I have mentioned
your I will have to put it
in the double quotes
like this anything
that I'm putting other
than the will function
numbers true or false
or cell references.
I'll put something like this so
we will explore this in detail
in the next function,
which is the sum F function
the summary function
sums the range based
on the single condition.
Or the criteria.
So let's explore the function
with the example on my sheet
in order to start
with this function first.
We need to understand
the term syntax to understand
any function for that matter.
We will have to understand
what is syntax.
Every function in Excel
has a syntax syntax
plays a pivotal role
in using a function as
soon as you type equal
to sign followed
by a function name
and open the bracket
after the function name.
It will give you
a list of arguments
which are these arguments or it
can also be called as an input
that Excel requires in order
for it to give you an output.
So if you see
when I open the summary function
for the summer function
the inputs required
our range criteria
and the sum range now
taking the same example
as used for the sum function.
Let's say we need to find
the total salaries paid
to the employees
in the South Region.
So if you can see our list,
we have this list of employees.
Just need to find all
the people working
in the South Region.
So the column that carries
the south region is column e
that is required by
the sumif function is the range.
So let's start
with the Fermi function.
The range that I have to enter
your is the criteria range.
What does the criteria range
means the range
where your criteria sits
that is in column e so,
I will select column e
to e now again,
if you see I am selecting the
whole column because in future
if there is a addition
to the data addition
to the employee list,
it will automatically
catch your as well.
First range that I selected
is the employee range.
Next one is the criteria.
Where is the criteria that
we are looking for which is
the South Region, correct?
So I'll enter South
as my criteria.
If you have noticed
I entered the south
in the double quotes looking at
the previous example are looking
at the previous data
that I told you it's
in Bill formulas functions
or numbers true or false.
It's still references
the criteria that we have
which is the South does.
Fulfill any of these four
conditions and hence.
I will have to put it
in the inverted commas
or the coats.
Once I do that.
The next thing
that I have to enter
is a some range now,
what is the sum
range the first thing
that you selected was
the criteria range
because your criteria sits
in that next was the criteria
because the criteria is your
South third one is a sum range
because you're not going
to some the region
but you're trying
to get a total salaries
paid to the employees
in the South Region.
We will have to select
the sum range as salary
which is our H to H column close
the bracket and press enter now,
what do we get?
Here is the total salaries
paid to the employees
in the South Region.
This is only one answer
that I require
for the whole list.
And that's the reason I
have put it in this column.
Let's look at another example
for the same sum F function.
We need a total salary
of the employees whose salary
is more than 15,000 rupees.
So earlier it was
like we were looking
for total salary
of the employees
in the South Region
and that's the reason
the answer was
three like thirty six thousand.
However, this time
we are looking
for the total salaries paid
to the employees whose salary
is more than 15,000 rupees.
How do I do that by again
using the summit function?
Like I said,
the summit function actually
looks at the criteria
and gives you the total
of that data.
Now if you would have noticed
when I start with any formula,
every formula will give a syntax
like I mentioned earlier
if I open the summer function.
The first one is the range
the second one is the criteria
and the some rage in the range
and the criteria
there is no bracket
or it's just simply
written range and the criteria,
but if you see
the last requirement
or the last argument,
it sees some range
which is in the square bracket.
So you have to remember
one thing the square bracket
means it is optional
when I say optional.
What does that mean that input
that you're giving
to excel is not required
to be given in case
of an optional information.
So in this example,
you can give range you
can give criteria and
if you don't give
us some range Excel
will consider the ID rearranged
as your some range so
the sum range becomes
an optional requirement
in our case the first case
we had to give the same rage
because we were giving
region as our criteria range
and it does not have a numeric
value the some can only be done
in a numeric values.
Hence.
We had to give us some range
which was our salary
but in our second example
where we need a total salary
of the employees earning
more than 15,000 there.
I will not give the sum range.
So the first requirement
which is our criteria range,
which is the column H
to H because that's
where the salary is.
Now.
What is our requirement
as a criteria
or requirement is a criteria
is more than 15,000 again.
It's not only a number
it is not its function.
It is not true or false
or it is not a cell reference.
So we will have to put it
in the double quotes
when I put double quotes.
I put a more
than sign 15,000 rupees
which was the criteria
close the double quotes.
This time, like I said,
I'm not going to give
the sum range and Excel
will consider criteria range,
which is the first range
by default as the sum range.
When I press enter.
It will give me
the total salaries
earned by employees earning
salary more than 15,000 rupees.
Hope this clarifies
how to use the sumifs function
just to summarize
some f function will give you
the total of any cell reference
or cell range
where a criteria is met.
Okay?
Let's see.
What is the next Formula
or function that we
have on our list,
which is the sum function.
What is the
sumifs function again?
Just like the sum function
sumifs function is
the function to some cells
that meet multiple criterias.
So like I said
in the summit function,
we only had one criteria
where and I was looking
in the first example,
I was looking at all
the total salaries
on by the employees
in the South Region.
However, in this case,
I am looking
at multiple criteria,
so We are looking
at the second example
here find the total salaries
paid to the employees
in the sales department in
South Region learning salaries
more than 10,000 rupees.
So now if we see your we
have three criterias,
let's go back to our document
where I can write
down my 3 right areas.
What is my three criterias?
So let's look
at the criteria one.
Let's put a small table
so we can just put it
down in that table.
So to start with I
have criteria one criteria
to and then the criteria 3,
so let's look at
what the deuce three criterias.
The first one that we
are looking at now is salaries
paid to the employees
in the sales department.
So our first criteria
becomes Department as sales.
Yeah, the second criteria
that we have here
is the South Region.
So the second criteria
becomes region as South
And the third criteria
that I have now your is
salaries more than 10,000.
So I just put
the greater than sign.
Okay, so that becomes
are three criteria
as I have three criterias submit
function will not work
because somewhere function
is only used to get outputs
for single criteria.
So we will be using
the sumifs function
the input required
for the sumifs function
are the first thing
that it requires
is the sum range
which in our case is the salary
so why is this salary
because we are looking
at the total salary
of the employees.
So the first some range
becomes our salary
so it will total the salary
next is the criteria range
one first criteria is
Department as sales.
And your the question
that we are asking for all
the input that they are asking
for is criteria range one.
So if our criteria is sales as
a first one our range will be
the place or the cells
or the range of cells
where the criteria sets
where does our criteria sets
that's in the department.
Which is the department,
so I will select D2D
as my criteria range one.
We are going to put this
in the double quotes.
Now comes your
criteria range two.
What is the criteria range two?
The criteria range
two is our region.
Where does the Region's it?
It is the E column.
So I will select e2e as
my criteria range two,
then comes the criteria
to when I say region
as my criteria range.
What is my criteria?
Criteria is South
so I will have to enter
that again in the inverted
commas South these information
that you're putting
in the double quotes.
They are not case-sensitive.
Even if you see sales here
with S capital and all small.
You can still put sales
in all small letters.
Okay going back to our formula
once I have entered e2e s out
then comes the criteria range 3.
Do we have
a criteria range three?
Yes, we do.
What is the Criterion age three
the Great Range 3 is salary why
because a criteria 3 is salary
more than 10,000 rupees.
So I will select
our criteria range 3 as salary,
What is my criteria
three more than 10,000 rupees.
So how will I put
that I will have to put
that in the double quotes
more than 10,000 close
the double quotes
closer bracket enter.
You will get the total salary
as fifty five thousand eight
hundred and twenty five rupees.
This is the total salary
paid to the employees
in the sales department working
for the South Region.
And if they are earning salary
more than 10,000 rupees
moving on to the next one,
which is our count function.
The count function is
the name suggests provides
the count of the cells
containing numeric value.
So going back
to my sheet as an example
where I have the list
of employees data.
There is only one rage
that contains numeric value,
which is the
salary column, hence.
I will perform the count
function for the H how will I do
that to start with I
will again have to start
with an equal to sign
followed by the function name
which is in our case count
and I will select the column H
close the parentheses
or the bracket, press enter.
It will give me an answer 1:01
because that's the total
number of cells
that contains the numeric value.
Next comes our count a function
while count function
counts the cells containing
numeric data count
a function counts.
The number of cells
containing numbers as
well as text or characters.
So when I say this using
our example this time,
I will use region column to
count the number of employees.
Why do I do that in
the first example,
we use the salary column
because the count function will
only count numbers this time.
I'm going to use a region column
in order to identify
how many cells or
how many employees are working
in the organization.
So to At with again.
I will do count a function count
a function counts numbers
as well as text
or characters characters
can be any character
like double quotes or a space
can also be considered
as a character count
a function this time.
I'll not take salary.
It will count the numbers
but we want to show you
how it works with
the alphabets as well.
So I go back
to our my region column.
I close the bracket enter.
It shows me hundred and
two as an answer.
Why does it show
me hundred and two
while the first one showed
me a hundred and one
the reason being count
will only count the number
of cells containing
numbers in this case.
Our number of cells
that contain numbers
is hundred and one.
However, if I am looking
at the region column,
it also has the heading
named as region.
So that is the text.
So if I start with region
and go till the end it is
the total number of cells
that contains the characters
is hundred and two.
So in this case,
how do you avoid
getting a wrong answer?
It's not a wrong
answer it just that
in case if Asking you get
a total employees working
in the organization.
You will use
the countif function
because you don't have
any numeric value in that case.
We will still use
the countif function get
the information after that.
You know that the heading
is also counted as
in this function.
So we will always get used
to putting minus 1
at the end of the formula.
So for example count a e to e,
which is this solemn once you
get an answer you just do - 1
this is just a trick
to get your correct answer
it nothing to do with
the formula formula will count
your first heading as well.
There is another thing
that I would want to show
in the county function is
because I told you it will count
the numbers as well as text
or the character in our example.
We also have another data
where we have numbers as well
as alphanumeric characters.
So in this case,
these are alphanumeric data
is also considered as a text
because it starts with an A.
It has alphabets in it.
So it is considered as a text
and these are all numbers.
So just to see
whether a count Function
works with the numbers
and characters we will do
a count a function again and
open the parentheses this time.
We will use the employee code
as our count range,
which is a to
a close the bracket,
press enter again,
it will give me a hundred and
two y again the same reason
because it is taking
their wedding also in the count.
So again, we'll just
have to do a minus 1
to get the correct answer.
So just remember this why we
are going to do the minus 1 is
when the count function also
County function also counts
the heading and we don't want
that heading to be counted.
We will use minus 1
to get your answer moving
to the next one,
which is the countif function
County function counts.
The number of cells
in the cell range
that meet a particular condition
unlike count function count
if can be used to count cells
with dates numbers and text
that matches Pacific criteria.
So like we learned some function
and the summit function
same is the count
and the counter function
so the countif function
Gowns all the cells
which contains numbers
and the characters
or the text the countif
function counts numbers
characters text everything only
if a particular condition is met
so going back to our example
in our example of employee data.
We have to identify number
of employees working
in the admin department.
So here is the admin
Department XL needs two inputs
for the counter function
as per the syntax.
So let's look at that.
So how do I start
as countif function?
These are the two syntaxes that
it gives the first syntax
is the range again,
like the sum function.
The first range is
the criteria range in this case.
There is no such thing as count
range like in some IF function.
We had some range
encounter function.
We don't have a count range.
Why because it can count
numbers text and characters.
So the first one
is our count range.
What is our condition
here our condition,
is that the Should Be Edwin
so my first criteria range
will be department
and what will be my criteria
the criteria Will Be Edwin.
So I will put Edwin again
as I told you earlier,
these are not case-sensitive
so you can put admin
in small letters
in double quotes
close the bracket enter
the total employees working
in the admin Department are 11.
Okay, just to cross verify we go
and do a filter identify.
How many employees are there
in the admin Department?
I filter by Edmund I select
the whole data now only
the admin Department
if I select discussion me as
Levin if I select the whole cell
it will give me that heading
also in agreement.
Well, so rather than that,
I will only select this cells
which has the data.
So it is 11 in number moving on
to the next function,
which is a countifs function.
Like we learned
the sumifs function.
We also have countifs function.
How does the countif function
work as sumifs is used
to some arrange that fulfills
multiple condition countifs.
Used to count number
of cell ranges
that meets multiple conditions.
For example, the question
here is to identify number
of employees working
in marketing department earning
salary more than 15,000 rupees.
Now, there are two condition
that needs to be fulfilled
in order to get the count.
What are those two conditions
insert a new table?
Because we have
a condition here.
Okay.
So our conditions are what
what is our criteria range
and the criteria?
Let's go back
first and understand.
What is it syntax,
so I open it I get
the first index is
a criteria range one
and the criteria one.
So let's put it that way
criteria 1 and criteria
to we have only
two criterias this time.
We don't have more than that.
So what is our first criteria?
Our first criteria is
that we want
the employees working
in the marketing department.
So Department as marketing now,
we are putting
your as marketing.
However, when you are entering
the data in the formula,
you will have to be careful.
You cannot put marketing
as a full spelling
because the spelling in
our cell or in our database is
M. KT G department has marketing
and what is our second Condition
salary more than 15,000 rupees.
So I will put it here
a salary more than 15,000.
So these are two conditions
going back to our formula
is equal to countifs.
Open the bracket.
What is the criteria range one?
First one is
the department marketing.
So I will select Department
which is d 2 D cell,
What is my criteria one?
It is marketing.
Like I said,
I cannot enter marketing
as mark820 ing because
if I entered
that Excel will not be able
to find this condition
in the department know
where is the swelling as
mark2 ing So to avoid these kind
of issues you will have to check
what is given on the database.
So in our database it is M KT G.
I put that MTG in double quotes.
To the next one which is
criteria range two in our case
criteria range two is salary,
which is the H to H column.
So I select H to H comma
then I give criteria
to which is 15,000 rupees.
So I'll have to put
that in the double quotes
as more than 15,000 close
the double quotes close
the parenthesis Enter key,
it will give me the total number
of employees working
in the marketing department
with celery more
than 15,000 rupees is
the result is 9 moving on
to the next function,
which is the average
function average function
Returns the average of arguments
which can be numbers
or names arrays or references
that contains numbers
which means average like count
only gives the average of cells
that contains numbers again
with the same example used
for the some we
will identify the average
of the total salary
paid to the employees
in the organization.
So the sum was too
Actively 3825 after adding
that new salary information.
We will also now look at what
is the average salary paid
to the employees.
So to start with we will start
with an equal to sign enter
the average function name
open parenthesis enter
the salary data,
which is in our case has
H column close the parentheses
and press enter.
So our total salary
or the average salary
paid to the employees
in the organization
as twenty thousand three
hundred thirty four rupees or 35
as it is .90 now,
in this case,
it will only count the numbers
but also there is
another function known
as the count a function
like account Axl also
has averaged a function
which will give an average
of cells containing
numbers as well as text
or characters or both.
How is the average calculated
if no numbers are available
to calculate the average Excel
adds the numeric value
of each value together
and divides the total number
of values specified
and Reach a evaluates
true as one and false a 0 so
when I say true as one
and false is 0 that means
if there is any data
available in a Cell,
it will calculate
that as one however
if there is no data available
in a Cell it will calculate
that as 0 so it will not add up
and it will not divide it.
So we have
a very good example,
which is the employee code data.
If I'm just trying
to get an average
of this salary information.
I will just get
the same answer even
if I do it every J.
However, if I do every day
with the employee data
where I have the text
as well as the numbers it
will calculate the numbers
which I have with just
add all the numbers
plus where I have the text.
It will take it as true.
Each text will be taken as 1 and
if there is any blank space
it will take that as zero,
for example from your
it will take it as 0 so
if I do average a say
I'm doing every day
in the next column just
below the average
open the parentheses.
I select a to a as
the Close the bracket
and press enter it will give
me 6345 .87 asthma average now
for say example.
If I delete one of the data
what happens is it changes
from 6,300 6,400.
If you see the difference
six thousand three hundred
and six thousand four hundred.
Why does this happen is
because as soon as I
delete the data
from your this information
is being considered as false.
And so this becomes 0.
So once I become 0 it
automatically picks up
as a zero information,
it doesn't calculate that and it
does not divide that number
by the number of values.
Okay.
So what is the total
that numbers that I have is
1 0 1 so it will divide
that by 1 0 1 so that's
how the average
a function works.
Next is our average IF function
just like average some if
and counter functions average
of returns the average
of selected arguments
specified by a given criteria.
So In Sum,
if it will some the data
if the conditions
are fulfilled encounters,
it will count the data
if the conditions are fulfilled.
Same an average of it
will average the total data
if the condition is fulfilled.
So let's go to our example
here in our example.
It says find the average salary
paid to the employees
in the South Region.
So there is only one condition
that I have this time to start
with an average IF function
or to understand its index.
I'll put average
if open the bracket it
will give me three arguments
one is the range
second one is the criteria
and third one is
the average range.
If you remember some
it functioned is exactly
the same as the submit function
except in the last argument.
It says average range
while in somewhere
fit seed average some so
that's the only difference
that you see in the summer range
and the average rain.
So to start with the first range
is our criteria range.
What is a criteria your
employees in the south region.
Where does the region sit
in the e2e column select
e2e as our criteria range.
What is the criteria
that we are looking at South
so we will Puts out
in the double quotes again,
And then the average range.
In this case.
We are going to average
the salary data
because we are looking
at the average salary pay to the
employees in the South Region.
So I'll select H to H
as my average range
because I want to average the
salary close the bracket enter.
It will give me the average
of the salary of the employees
in the South Region.
Okay moving on
to the next function in our list
is the round function.
We will illustrate
three functions to
the round numbers in Excel.
The functions are the round
round up and round down first.
We will start with
the round function
around function will round
the number two the number
of decimal points specified
in the formula in our example.
We have number s 14.7 261 and
use the round function.
I select the number I need
to round so I go to equal.
Around I select the number
that I want to round,
press the comma
if you see the syntax,
it says the number
and then it's ask me
the number of digits.
What is this number of digits?
It is asking me
how many decimal point
do you want or
how many decimal places do you
want after the decimal point?
So say for example
you are in this case.
There are four digits
after the decimal point.
I only want to so I enter to
and close the parenthesis enter.
It will round up
or it will round
the number which is
after the second decimal point
just like in school we learned
when there were more
than two decimal points
if it is more than 5
we will add one number
to the previous digit
same thing the Excel does
for here in our case
if you see properly if you see
or one one four point,
it says 726160726
the 6 is more than 5
that one will be added to 2
which becomes one more
four point seven three
and the 61 is removed
from the list.
So Answer becomes 114 .73.
So this is how we
around function works
just for more examples
if I had put round function
and this time I select
this number comma I just do
the number of digits say I want
to do number of digits as one
and close the bracket enter
it will give me seven only why
because the second number
which is 2 is not either 5
or more than five.
That's the reason
the seven is left as 7 K.
So this is showing is 114 .7
this becomes around function.
The next one is
the Roundup function
around a function always
rounds the number up away
from zero from our example.
We want round a number
up to one decimal point.
We will input the formula
in cell C2 is equal to round
up again select the number
as a 1 comma I'll just put
one close the parentheses
and limit now
if you see properly
in our first instance
when I put only round function
is showing me as
Third one 14.7 why
because the next digit
which we are talking
about was the number two.
Now this number two is
not more than five.
That's the reason it
didn't add up in seven.
However Roundup function
what it does is it
will go away from the zero
when I say away from the zero,
it will not leave it as seven.
It will automatically
add 1 to this number
because there is some digit
which is after the number
so it will automatically add one
into the previous number
and give you one 14.8 next.
We will learn as around
down function around
and function always rounds
the number down towards zero.
So this is the other way round
for example round a number down
to the nearest integer.
Let's see in our example
again with the same.
Number one 14.7 261 let's round
down to a 2-0 decimal points.
So we say round down open
parenthesis select the number
that we want to round
out comma this time.
We don't want any digits
or Numbers after
the decimal point so I put 0
I press enter I get
the answer as 1 1 4 now
if you remember again going
back to our school days
in normal situation,
we would have done it
as 114 .7 to as 115.
Okay.
However in this case
in round down
because it is going
towards the 0
that means it will not add up
this number to the previous one.
It will remove all
the decimal points.
Just like if you see
another example also,
I will try the round down
with the two decimal points
rather former to close
the bracket enter see even
if in this case
what happened was
when I did two decimal points
because it was more
than five added one.
Number two, two and gave me
one one four point seven three,
but if you see
in this case Brown
down even with the same
two decimal points,
it is not adding up the six even
if it is more than 5
it is not adding up one
into the previous rigid
and it is giving me
the same answer as 114172.
Yeah.
So this is how we
are Roundup works.
No moving on to the next one
which is the concatenate
function concatenate function
joins two texts together.
Now when I say join
to text together,
that means it will join the text
which are into different cells.
It will join them and get
it into one cell.
For example,
if we have name and the surname
into different columns say
in our example,
like we have here name and
surname into different columns.
We've can get them in one cell
using the concatenate function.
That's the example on my sheet.
I have to start
with I want to combine
both this name
and surname in this cell,
which is the cell G by using
the concatenate function
in such a way
that the first name
in the last name has
a space in the middle.
So let's see first
how do we start
with the normal concatenate
function and get the both name
and surname in the same cell
is equal to I enter
concatenate Open Bracket.
The syntax of the concatenate
function is text 1 comma 2 x
2 comma text.
You keep on doing
that the first cell
that I will select for the text
one is e 2 why do I do E2 is
because the first name
is in the E to sell.
So I select E2 then I do comma
then I have to select
the second text
which is sitting in F2
once I select that close
the bracket enter both this name
and the surname
in the same cell now.
However, as I said earlier,
I need the data in such a way
that there has to be a space
between name and the surname
how do I get that now?
So as you are aware,
if you go back this time
and delete this part again,
I'll start with the function
again to start with
the concatenate Open Bracket.
I select the first text
which is E1 aftercoma.
I will not select
directly the text
to which is the f 2 I will have
to put a space in the middle
which will become
my text to so earlier.
The text was your surname
cause we want a space
in the middle phase
will be considered as text
to so I will have to put
in the Code why
because space is
also a character
which Excel does
not understand Excel
only understands it
in build functions numbers true
or false or its cell references
in our case.
They are none of them.
So what we'll have
to do is we'll have
to put it in the codes.
So I go back to my sheet.
I enter the space in the courts.
Now, this becomes my text
to what is my decks
three the last name,
so I select last name.
I close the bracket,
press enter if you see
now there is a space
between the text one and
the text to which is the name
and the surname now
to copy this data
to the rest of the cells.
I can use my fill handle.
What is the full handle
if I keep my cursor
on this cell?
You'll see a small square here
on the right hand side
once I keep my cursor
on that small square.
There is a cross sign
that appears as soon as
that Cora sign appears.
I Just need to double
click on that data
for it to copy it
to the rest of the cells.
Now, when I double-click
on that data,
it will only work on the cells
if there is any data on the left
or right of that cell.
So if I had entered
the same formula
in some other sense
say for example,
you're enter now.
If I try to double
click on this,
it doesn't work.
What do I do in that case?
I'll have to drag
that formula till the end
where I need to stop because
Excel will only understand
if there is any data
on the left-hand side
that it has to stop your or it
has to copy the same formula
to the rest of the cells
Till There is a data
in the previous cell or on
the left hand side of the cell.
So this is how you can do
a concatenate function
to get your information.
Now if you would have realized
the concatenate function is
a very big I'll tell
you a small trick
how you can use to avoid
any spelling mistake
when entering a function
because if you do
any spelling mistake
like this is what I did.
I start with my formula
comma first of all,
it will not give you a syntax
and the second thing
once you press on enter
it will give you an error.
So how to avoid that.
There is a very
simple way to do that.
I first start with an equal
to sign I start entering
concatenate which is conc
a as soon as I entered conc
a or even the smallest of them.
Even if I try with see
all the functions
that starts with c
will appear here,
but there are too big
so I will at least put
conc it will come down
to only two formulas.
I will select the one
that I want to use this time.
Okay double click on
that it will automatically
open the parentheses for you.
Now, you can select
E1 or E2 whichever.
The first text is
then your space
and the last name sell.
This is how you can use
any function for that matter.
You can also do
some meth with this
if I put some it will give
me all the functions
that is starting with some
so I can use summer function it
will automatically open.
Could for me this
is only easier way
or a trick to actually ensure
that you don't miss out
on opening the bracket
or you don't make
any spelling mistake
when entering a function also
the another way to do this is
if I enter concat second CA
it will give me that option.
I select the data
that I want rather than
double clicking on it.
I will press on Tab it will
automatically open the bracket
for me the similar way that we
did in the previous example.
So this is
how we are concatenate works
the next function on our list
which is the index function the
Microsoft Excel index function
returns a value in a table based
on the intersection of rows
and the columns so supposing
you have a list of orders
with their unit price
like in our example earlier.
This is an order ID unit
price and quantity.
We want to use
the index formula.
So here we have the unit price
and the quantity we want to use
the index formula to look up
for the TT of oranges sold
here is example of how to do
that in cell where you
want the result.
Let's start by typing
the index function.
So is equal to index
open the bracket.
The first requirement
for the index function
is the array as per
the syntax array is the table
where you want to pick
up the data from so
this is our table.
What do I want is the quantity
sold for the product oranges.
I want to answer as 10.
So let's start
with how we can do
that the index function 812 D7
that is my table comma
this time I need the row number
in the oranges,
which row number is oranges
sitting under everybody knows
what is row and the column
so this is my rose.
So if I'm going back just
to give you an example.
These are called Rose
these part of the cell
where you see one, two,
three this and the rows
are always numbered
or referred to by
1230 the numbers,
okay while your vertical It
is called as your column
while the horizontal
is called a zero.
So vertical part,
which is your column is referred
to by the alphabets
ABCDE throw to start
with an index function
again going to F2.
I am entering in deck is equal
to index open the bracket.
I select the array
which is my table,
which is a one to D7.
Now.
I have to provide the row from
where we need to derive
the result in our case.
It is 3 y because we are looking
for oranges oranges
are sitting in the third row
how this is one stroke.
This is second row.
This is the third row.
This is according to your table.
So I put 3 as my row number now,
I have to type the column number
because now I've got this row
I have to derive data
from buttocks and wants to know
which column do I give data from
so I'll have to tell Excel that.
I want quantity
and quantity sitting in which
column 123 and fourth columns,
so I enter for Once I close
the bracket and press enter I
will get the result as 10
which is the quantity
of oranges sold.
This is how we are
index function Works moving on
to something similar function,
which is called as
your offset function
like in the index function.
It will tell you which row
and column you have to work on
on the offset function.
It is similar
to which row and column.
However, we will
see the difference
between the index and the roof
and the offset function
what does area offset function
do offset function returns
a reference to a range
that is a given number
of rows and column
from a given reference.
For example, I am giving
a reference truer self.
This is my reference.
So I am telling from a to give
me second row second column
in the index function
if I go second row
and second column,
what will it do?
It will look at the second row
directly from your
because I am
selecting this table,
which is So this is second row.
This is third row which column
and I'm looking
for fourth column.
So it will give
me the ten answer.
However, if I did it
with the offset function,
for example, I say offset
function in this film sheet.
I cannot select the array
which is not the table here.
I have to give a reference
cell number so say I said
cell references a one
now my row number that I
have to select after that
which is 1/2.
In this case.
I have because oranges
is sitting in the second row
after this reference,
which is cell A1.
So I'll have to say 2 comma
which column I am I looking
for I'm looking for the column
which is after this reference
so column 123.
I sprut 3 close
the bracket enter.
So if you see the difference
between the index function
and the set function is
that in an index function you
give the table it also select
from the first row
and the First Column
and it gives you
whichever number you have given
accordingly select that row
and that column however
in the offset function you
give a reference to one cell
that cell is Not
counted in the row
and the column is for a 1
if I select and say second row
it is giving me one too.
It is not counting this
as one row discounting
the next one as one row
and then the next row
is the second row here.
Also the column the same way
as this column is
not selected at all
because this is
your reference cell.
It will select this as
one row the second row
and this is the third row.
This is the difference
between the index
and the offset function.
Now if you would have seen
in the offset function,
there is also
another two more sin taxes
that is given there
or the inputs that requires
is the height and the width.
Where is this useful the height
and the width means
the cell references
with the vertical data.
This is called as the height
and this is called as your with.
Where is this useful?
And how is it useful?
This will be useful
when you are using
an offset function
with the any other function
like you're using offset
function with some so I'll start
with is equal to some open
the bracket this time.
I want to some some
But instead of selecting
the cell references I will ask
the offset function to select
the cell references for me
how I will
select offset function.
I open the bracket this time.
I'm giving the reference
as the month comma
and then I'm looking
for February to make
so I'm looking for some
from February to May for East
and West together.
So I'll start
with reference as a 2
which is the month this time.
I want to give from February.
So which row I'm going
to select this one
to second row is February.
So I'll put two
so it will start from February.
However, I want to give
the column number
from the next one.
I don't want to start
because it will
not some this part.
It has to some
from your correct one
because after this cell
it will look at one you're
so I will give 2 comma 1 because
2 is in February 4 column.
It will look
for the First Column.
So now I want to tell Excel
that please add.
Ray March April and May so
how many rows on
how much height
should it have 1 2 3
for the height becomes 4 rows
because height will
be this part.
So I say one two,
three and four,
so the hide I'll give us for
because I am trying
to add up for months
February March April May
and that four months are
in four different cells.
So one two, three four,
I will put it as 4 comma Now
with this my columns.
So how many columns they
are trying to say East
and West together.
So it is February March
April May which is
the height which you
because they are rose and now
I'm looking at two columns
which is true with so I'll say
to as my with close the bracket
and close the bracket
for some press enter.
It will give me an answer
as 6218 just to cross
verify I will select
February March April,
May data see the answer
the summons or is 6218 this way
not only some you can do average
you can do product you can do.
Count you can do
any number of things
with the offset function along
with the other formulas in it.
So this was how you
are offset function works.
Next is the match function
the match function searches
for a specified item
in a range of cells
and then Returns the relative
position of that item
in the range sticking
with the same example
as we did for index.
Let's use the match to figure
out what row oranges is in.
So what we did earlier was we
were trying to find the quantity
of the oranges you
spend this time.
I am trying to find which row
does the orange products it.
So what does match do
is match will identify
if you give a name
in the match function,
like for example oranges it
will identify which row
it is sitting under
how does that work is
I will type is equal
to and the match
function name in the cell
where I want the answer the
first X or the first argument
for the match function
is lookup value.
What is my lookup value?
This is oranges.
So I will type oranges
in the double quotes,
which is my lookup value
because I'm looking
for that comma this time
I am looking to find out
which Euro it is sitting
under select all the cells
in the product column
including the header,
which is this part
why I'm doing this
because I am trying to find
which 08 dist setting under
so it is sitting on a 1-2-3 row,
but instead of writing
three match will automatically
identify for me which row
it is sitting under correct.
So what do I do is
I enter be to be this way.
It will automatically
look for it,
As soon as I put comma
there is an option for less
than exact match and greater
than in match function.
We have to look
for the exact match
of the lookup value.
So if it is oranges,
it should only look for oranges
and not anything else.
So we will select
the exact match close
the bracket enter it
will be mere Answer now.
If you go back
to your index function,
if you see the index function,
the first thing
that we gave was 822 D7
because we are telling them
that this table look
for the data in this table.
Next one was row number.
Why did we look at row number?
Because we were looking
at row number
where the Orange is sit
which is 3 so arranges
that we were sitting we found
it out by calculating it
by one two, three.
However, if we use
the match function and select
this be to be number 3 will
be automatically identified
by the match function.
So instead of the three
number year I can use
the match function
inside the index function.
This will make the index
function Dynamic.
So even if I moved oranges
to somewhere else like
if I move it to some other cell,
let's say I'm moving
this whole data a little up.
So the oranges is now
going to the last one
which is column 7,
but in our case,
it will not give you the correct
answer it is still giving you.
10 which is not correct.
It should be ideally 35 it
because we have given
as particular number.
It is going to 3 it
is going to for and giving
you that bananas quantity.
So to ensure
that that doesn't happen
we can use a match function
inside the index function
and get the dynamic answer
by replacing three
with the match function.
What will happen is this time?
You will see
that it automatically picked
up the oranges data,
which is 35 same you can use
for the answer for this time.
We are trying to identify.
Where is this quantity sit?
So what I can do I can do
a match again open the bracket.
What is it that I'm looking
for this time?
I'm looking for
the name quantity
where it is sitting now
quantity I know is sitting
in the first row.
So what I'll do is instead
of looking for quantity
in the whole database.
Where is a lookup array.
It is in the first row.
So I'll select the first row
comma exact match.
So I will put 0 close
the bracket enter.
It gives me that
for Unser again going back
to my index function
instead of using Fourier.
Replace for with
my match function
that I have done here
copy the match function
without the equal to go back
to my index function
and then replace
for with the match function
play center this time
if I move
oranges somewhere else,
it will automatically
pick the answer that
we are looking for.
Let me move it to the first one
this time so it becomes
dynamic dynamic means even
if there is any change
in the data,
it will automatically give
you that particular answer
so you should see
that the oranges
are showing as 12 say
if I move also my quantity
in the middle somewhere
in the middle like
before see it will still give
you that same answer why
because now match is looking
for the quantity
and the quantity is 3
and it is not for any more.
So that's how you can use index
and match together
in order to get your answer
or make it more Dynamic.
Okay moving on
to the next function,
which is our main function
the Min function.
Is a function that Returns
the smallest numeric value
in the range of cells or in
the range of values,
like in our example, again,
we have the salary information
of all the employees of almost
a hundred and one employees.
Now, I want to know
the minimum salary
that I am paying
to the employee.
So to identify
that I would just put is equal
to men and open the parentheses.
I will have to select
the salary column
because I am identifying
the minimum numeric value
of this salary column.
So I select that close
the bracket enter it
will give me the salary
paid to the employees.
Okay.
So five thousand nine hundred
and fifty Rupees is
the minimum salary
that I am paying
to the employee.
So the same goes
with a maximum salary.
So earlier we learned
how do I get the minimum
salary pay to the employees
same I want to know.
What is the maximum salary
paid to the employees?
So is equal to Mac open
the bracket select
the h2h column y h to H again
because it is the salary.
That I am trying to understand.
What is the maximum
salary paid edge-to-edge
close the bracket enter.
It gives me the maximum salary
paid to the employees.
So this is how we are Min
and a Max works.
This is a very easy,
but it's very useful formula
in Excel moving on
to the next function
from our data.
We've already seen the Min
and the max function this time.
We'll see the trim function
trim function removes
The Unwanted spaces
from a particular cell.
It will only remove spaces
in the front and end of the text
if any so if you want
to remove this node,
its Paces from a text.
We will see in our example say
I have some spaces in the front
and at the end of some text say
for example in a my name column.
I'm just entering some spaces
just to make it more easier
to understand so I'm putting
spaces in the front.
I'm putting spaces in the end
for each one of them.
There are two spaces
in Vienna to space in the front
and Sima one space in So
these are all the spaces
that I've entered
somewhere in pankaj.
I'm putting a space
in the middle.
So two spaces in the middle.
Okay, same goes with adri now
if I want to remove these faces
because what happens is
when I'm doing a formula
along with these faces,
it will not work.
So I have to remove the spaces.
So how to start with I'll have
to enter the trim function
is equal to trim open
the bracket the first cell
because from each one of them.
I want to remove the spaces.
So I select the first cell
which is be to close
the bracket enter.
I select this cell enter again
with the same fill handle.
I double click on it.
It will automatically take
the whole data till the end.
If you see properly all
the cells have been removed
with these pieces.
However, for pankaj only
one space has been removed.
If you see properly there
two spaces here here there is
only one space is so
in Bunker's Excel will only
remove unwanted spaces
when I say unwanted
spaces in the front.
There is no space
required at the end.
There is no space
required in the middle.
Also if there is one space
it is considered as Okay,
but if there are
more than one species
in the middle Excel
will automatically
remove those faces.
How do I ensure now
that the data without de
spaces is copied
and pasted in these cells.
What do I do for that
is I select this data.
I copy I go back to my cell
where I have the original data.
I click on this.
I right click on the first cell
and there is something
called A Space special now,
why am I doing
a paste special is because
if you see there is
a formula in this cell
if I copy this data
and directly paste
it into this cell.
It will try to copy that formula
and will give you an error.
Okay, so we want
to avoid that error.
So what we'll do is
we'll copy this cell.
I'll go back to my cell
where I want this data
to be pasted right click
on that cell click
on paste special then click
on values and press OK
once I do that you will see
that there is a formula
in these cells.
However, there is
no formula in these sense.
Okay, again, just to ensure
that we are at the right pace
will see that earlier.
We had put two spaces
pankaj only has one space just
to remove the space we can do.
Now so that's
how your dream function works.
So moving on now to the next
function on our list,
which is the Len function.
What does an end function do
Len function Returns the number
of characters in a string.
So when I say a number
of characters in a string
that means that it is looking
at the number of cells
that contain a text.
What is the number or what
is the length of the text?
For example, if I want to know
in our cell Raymond occur is
how many characters so
what I'll do is is equal
to Len function.
I'll enter that and
I'll select the cell
for which I want to know
how many characters are there
in that cell to see to it
is 11 characters.
So it says,
how do I calculate that start
with 1 2 3 4 5 6 7 8 9 10 11
now it is giving me
a Livin character text.
But if I want to put
a space the end
what happens it becomes
12 characters why
because the space is also
considered as a So in this case,
it becomes a
12-character text again,
I will use the format painter
like we are saw earlier to get
that back to the normal data
on the Normandy format.
Okay.
This is how your Len function
Works moving on to the next one.
Let's start with the next
function which is s
QR T. SQ r t function Returns
the square root of a number.
For example, I
have just one sheet
where I have put some numbers
here to 20 to 140 46745.
I need to find the square root
of these numbers.
So I put is equal
to S QR T open the bracket.
I select the number
which I need the square root
for close the bracket enter.
It is fourteen point
eight nine nine six six again,
like earlier, you can reduce
the decimal points
by just clicking on
these decrease decimal option.
You can increase
the decimal point.
So I just keep on clicking
on these options.
Okay, so we leave it
as it is now again
for the next one
or you can just Drag the formula
by using the fill handle
and it will give me the square
root for each of them your
I do not want any decimal point
so I can just reduce that.
This is how you
can use the squared.
This is a financial function,
which is more used
in case of finding data
for the financial industry.
Again, this is a demo which
you can look at to identify.
If you want to just come
back and see this examples next
is a very important function
called as the IF function.
The IF function
is very important
because it gives you an option
to get the required data.
If a particular condition is met
for example in I have a list
of employees going back
to my sheet in the
IF function treat.
I have the list
of employees data
with their Department
Information Management
has decided to give
20,000 rupees to the employees
in the sales department
and others will get
0 as a commission.
So to identify
how to get a commission
for each of the employees
will use the if formula so
against each employees
who are in the sales department
the results should show
The commission column as 20,000.
So for example in the first one
it is Edmonds here.
It will show is
0 second one into sales.
So here it should
show as 20,000.
Next one is admin again.
So again zero marketing again 0
we are only looking at 20,000
for the sales departments
of against each employee.
We should be able
to see the exact amount
that needs to be paid
as a commission.
So we will have to use
the IF function to start
with the IF function
again is equal to if as
soon as I open the bracket,
the syntax says the first data
that is required
or the first input
that is required
for the function
is The Logical test
logical test means
your condition in our case.
The condition that we have is
that we are looking to give
20,000 to the employees
in the sales department.
So what are we looking
at the department?
So the first employee
Department data is in D2
so I select D2 is
equal to what is
that we want if they are
in the sales department.
So I am saying is equal.
Sales in the double quotes So
this becomes my logical test.
As soon as I entered
the logical test now,
I have to put a comma next input
or the next argument
is value if true.
So what we are telling Excel is
if T2 is equal to sales.
That means d 2 is the department
for the first employee
if is sales,
give me what if it is true
20,000 if it is false,
that means if the department
for the first employee
is not sales give me
0 so I close the bracket
after doing this press enter.
It is giving me zero
for the first employee why
because it is admin Department
Roger Raymond occur is
an admin department.
Now again, just
like earlier we mentioned we
can drag the formula either
or using the fill handle.
I can double click
on that to get the answer
for each one of them.
Now the formula
is dragged you can see
that the formula is rack
from D1 in I to you.
CD 2 is equal to sales
while I go down the formula
changes the cell references
from D2 to D3.
Why because it is now moving
down going to the next Row
the three then D4 and D5
and D6 accordingly.
So it is automatically
calculating from the respective
cells for the second employee,
which is so mentioned a is Shri
in the sales department.
It is checking and the suman's
department is in D3.
So Excel has automatically
taken D3 as the cell reference.
Next we are checking for called
Deep Sharma cool dip sharma's
Department detail isn't D
for the cell reference is
automatically depict
as D 4 is equal to sales
because it is not
since it is admit.
It is automatically
giving it as a zero.
So I hope the IF function is
very very clear.
It's a very easy formula
but very important.
So again looking
at it the logical test
which we have given as
d 2 is equal to sales
the value of true is
because what is it that we need
if this logical test
or this condition is fulfilled.
And the value if false is
if the condition
is not fulfilled.
What is the value that we want?
This is how we are
F function works
if I'm starting
with the ifs function.
I will write down
each logical test here first.
The first logical test is
salary is less than 5,000.
Second logical test is
salary is more than 5,000
but less than 10,000.
This is my second logical test
third logical test is salary
more than 10,000 but less
than 15,000 fourth logical test
is salary more than 15,000
but is less than 20,000
and my fifth and last
logical test is salary
is more than 20,000.
These are my conditions.
However, when I start
with this condition,
I have a total employee list
of hundred and one employees
out of these hundred and one
employees if I say,
for example, I am just looking
at anybody salary
less than 5,000.
So in the first condition as
soon as I put I get an answer
as there are ten employees
who are earning salary
less than 10,000.
So what happens is out
of this hundred and one number
that I see I will have to deduct
10 employees or earning
less than 5,000 rupees.
So the answer for them will be
less than 5 K now going
to the next one.
If I start with the second one,
I realize that as
soon as any employee
that is a 10 employee
is owning less than 5,000.
They sit in the first bucket
Which is less than 5
K bucket 10 employees
are automatically removed
and we are left
with 91 employees.
These 91 employees
are definitely going
to be earning salary
more than 5,000 rupees.
Why because the
remaining 10 employees
that we identified
are earning less than 5,000.
So whatever is left is all
earning more than That so we
are not required to re-enter
an IF function that
if they are earning
more than 5,000.
We will directly start
with less than 10,000
and this time
if it is less than 10,000
will put less than 10 K.
So if for example,
there are 10 more employees
who are earning less than 10,000
so I would put 10 here and -
from this 10
so I minus 10 again.
I get how many 81 so
81 employees are left.
Now out of the
city one employees.
Everybody is owning
more than 10,000
or more than 5,000.
Why because we
have already identified
from hundred and one employees.
The 10 employees
are earning less than 5,000.
Another 10 employees are earning
less than 5,000 that ways.
We are left with 81 employees
who are earning more than 5,000
and more than 10,000.
So now going to the next one
we are not going to enter
more than 10,000
and less than 15,000
because everybody else
is warning more
than 2,000 this time.
We will select.
Listen, 10,000 say
I've identified 15 people
owning less than 15,000.
So I enter less than 15,000.
This will be written as
less than 15 K next again.
When I go to the next bucket.
So these three
buckets are decided.
What are the three buckets are
one is less than five
thousand less than 10,000
and less than 15,000.
So now out of that
I have identified
how many people are left
with the deducting the 15 number
that is 66 employees
are still left out
of a hundred and one employees
who are now owning
more than 15,000.
So we know
that these 66 employees
are not earning less than 15,000
are all learning
more than 15,000
because we have already put them
in these three buckets.
So left is all more than
15,000 now more than 15,000.
Also, there is
one more requirement
where we are identifying
if there is any employees
earning less than 20,000.
So say we have identified
25 employees owning
less than 20,000.
So we'll put it here
and our Data will be
less than 20K last one.
We have identified
that out of these 25 employees
that we removed.
We identified that only
41 employees are left
who are running
for more than 20,000 why we
are saying more than 20,000
because we have put
everyone in a bucket
where either they are earning
less than 5,000 less than 10,000
less than 50,000
or less than 20,000.
So whatever is left is all
ordering more than 20,000.
So they are 41 of them
more than 20,000
and we will put
as more than 20 K.
So this is how we are going
to split our data and see
how are we going
to get an answer.
So looking at this example
when I'm doing my f function,
I will have to go
and enter the data
into the first cell
which is against
Roger Raymond occur.
Am I didn't I am trying
to identify is Raja learning
salary less than 5,000 less
than 10,000 less than 15,000
or less than Was it
or it is more than 20,000.
So where is the salary data?
It is in the H2 for Raja.
So the first thing
that I will see is
IF function h 2 is
less than 5,000 then
give me what less than 5 K.
So for the first requirement
was less than 5,000 everything
that is less than 5,000
has automatically now sitting
in this bucket,
which is the first bucket now
all the leftover employees
have automatically come
to the next data,
which is more than 5,000
but less than 10,000.
So now I will go
to the next one again.
I'll have to select H2
because I'm now seeing
if h 2 is not less than 5,000.
Then we have to check
if h 2 is less than 10,000.
So I put h 2 less than 10,000
if h 2 is less than 10,000 then
what do we want again?
If you see The Logical value
of true to it we have
to put less than 10 K
now the Requirement
which is a logical test is
if h 2 is less than 15,000.
This is my third bucket
according to the third bucket.
If it is true,
I'll have to put less than 15 K
in the rebel quotes.
Now the fourth logical test
the fourth logical test is
if h 2 is less than 20,000
which is my fourth bucket.
If yes, give me
less than 20 K. Now.
If you see in this one,
there is no such thing
as value if false
in the ifs function we had value
if true and value of false.
So what do we do
in such case now,
we have identified
that this number is
not less than 5,000.
It is not less than 10,000.
It is not less than 15,000.
It is not less than 20,000.
What is left is it is
more than 20,000.
So what we can do is
in that case we can put
another one which is h
2 is greater than just
like we normally do
in the last case h 2 is
greater than 20,000
then give me more
than Okay close the bracket.
Now when we are closing
the bracket will have
to close only once
and you get that answer drag it
to the rest of the cells
and you will get it
according to your data
that you had now.
This is 7,000 Which
is less than 10,000.
But more than 5,000.
This is 12,000 250
which is for Vienna.
It shows less than 15,000.
Why because it is
less than 15,000.
What more than 10,000
so it is between 10,000
and 15,000 likewise.
You will see data
for each one of them.
Now.
This is less than 20,000
because it is 15,000 750
which is more than 15,000
but less than 20,000.
This is how you use
your IF function to get
a desired result.
There is also something called
as nested IF function which ifs
is replacing that nested
if ifs is available in 2016.
But if you have any version
other than 2016 you
will have to use the nested
function now going back
to the PPT and see
the next function
which is the end function
and function checks
if all logical arguments inside.
Functional true and
Returns the True Value
if they are true
and false value,
if one of them is false
for example and and function
like we are going to any sheet
and trying to identify
if logical test
that we do like enough function.
We are giving different
logical test, right?
So if you are giving
more than one logical test
if they are true,
then it will automatically
give you an unstressed true
or it will give you an answer as
for in our example.
I am trying
to identify employees
in the sales department working
for the west region.
So to start with I want
to identify all the employees
wherever they are working
within the sales department
and for the west region,
it should show me an answer
as true wherever they are
not in the sales department
and not in the west region.
It should show me as fault.
So both the condition
should be made
what are the two conditions
one is the department as sales
and the second condition is
they are region as West so only
if both the Conditions
are met then only
the answer should be true
for the result should be true.
Otherwise, it should
show me as fault.
So, how do I do that?
I'll put an end function
in the cell where I
want to answer.
So I want an answer
for each one of them
because I want true everywhere
where there is sales
and best wherever there is sales
and East or north or south.
It should be me or false
or if there is admin
and any other region
should give me a false answer.
So both the conditions have
to be met department sales
and reach invest.
So what I am saying is the first
condition is department sales.
So for Roger romantic
and I'm trying to find out
if department is sale.
So I will do d 2
is equal to sales.
That's one requirement.
That's my logical one.
Second logical is
what I go to E 2 because that's
where my region sits.
E2 is equal to West
that's my second condition.
I close the bracket enter.
I get a false answer
for the first one why
because Raja is
from the sales department.
No doubt, however the region
Is not and our condition is
that if the region
West and the department
is sales only then give
me a true answer now
when I drag this formula dunk,
it will check
if they're ever
the region is West
and the department is sales.
It will give me a true answer
if you see for huijin Imgur,
you'll see it's she's working
in the sales department and for
the region West and hence.
We are getting an answer
as true same goes
with everyone else.
So we hardly have
few such employees where both
the conditions are fulfilled.
Now, we have another function
named as the or function.
What does the or function
do just similar to the
and function it will look
at one of the conditions
being fulfilled like
in the end function
both the conditions
have to be fulfilled
but with the or function
only one of the conditions
have to be fulfilled.
For example, I want an answer
as true if the employees are
in the sales department or in
the marketing department.
So I'm saying that
If the department is sales,
I should get an answer as true.
If the department is marketing
I should get an answer is true.
Otherwise if it
is other than sales
or marketing I should get
all as false as so,
how do I do that
so I can only think
of one function
which is the or function
which will give me this result.
So to start with the function,
I will put an equal to sign
in the first employee data
and I'll put
or open the bracket.
My first logical test is
that partment should be sales.
So D2 is equal to sales.
This is or so you remember
that either it is sales
or it is marketing.
So first logical
test is Department,
which is d 2 is equal to sales.
The second logical test is again
d 2 is equal to marketing
now marketing again,
if you remember
in the earlier functions
also we were careful about
what is the spelling
of the marketing
so marketing is not
like mark2 ing.
There is a short form
that has been used
which is M KT G
you may have times
where the full form is.
When used in that case you are.
Not going to use
any abbreviation to put it
in the formulas.
You have to see exactly
what is given here
that needs to be entered
in your formula as well.
Now, I close the bracket.
Now wherever there is sales
or marketing it will
automatically give me
n true answer.
So this is how it gives so true
because this is sales.
This is sales again.
This is true because it
is marketing be nice in
the marketing department again,
I can see true for
the sales department same goes
with marketing again
for and refund on this
because they're trees working
for the marketing department.
That's the and likewise you
will see for the rest of them.
This is how we are
or function works.
Next one is your today function
today function is a very easy
and very useful function.
This function is used
to identify the current date.
So to identify the current date
I will enter is equal
to today open the bracket
close the bracket
and press enter it
will automatically change
the format of that cell
into a date format
and give me the today's date
today's date is 2nd of July.
I was in an 18
and that's the reason it
is giving me this date format.
Now.
This is today's date
because I have entered it
in my example today.
But if you are trying it
on your Excel sheet,
it will give you the date.
What is there on your system?
It is always the system date
by any chance
if your system date
is not updated the Excel
will also check this state
which is on your system
and give you that date here.
So you will have to ensure
that your system data
is also updated.
What is a quick access toolbar
quick access toolbar provides
access to frequently used
commands in the application.
When I say application.
It is our MS Excel this sits
at the top left hand corner
of the Excel worksheet.
Let's explore this
on our Excel workbook the sits
on this part of your Excel,
which is on the top
left hand corner.
You will see a small drop
down button here with an arrow
which you can click and this is
where you will get other options
that you can add
to the quick access toolbar.
So this is
where you have your ex.
Toolbar by default quick access
toolbar will have undo/redo
and save button.
So this is your save button,
which is already there
on your quick access toolbar.
This is your undo button.
And this is your redo button.
You will have
these three buttons.
Why default on the
quick access toolbar?
Other than that you can add
the rest of the buttons
on the quick access toolbar
by clicking on this button,
which you see the arrow key
and adding whichever you think
you're going to use
frequently in Excel.
I have added new workbook access
in the quick access toolbar.
I have also added an open option
in the quick access toolbar
because I keep using these two
things very frequently.
So next time
if I want to open a new blank
Excel treat I will not go
to file click on open and
when a blank worksheet instead,
I would just click
on this quick access toolbar
and where I can see this icon
for a new workbook
click on that.
It will automatically open
a new workbook for me.
Same goes with the another.
If you see there is
another open option,
which is there
in the quick access toolbar
because I have already added
that to my access toolbar
when I click on that.
It will give me an option
to browse and get the file
that I want to open
for working on it.
So this is how you can keep
adding the additional things
that you need from
the quick access toolbar.
I have also added
an email option here
when I click it will
automatically attach this file,
which I have your
to the email box
or the new email
and I just have to add
the email address of the person
who has to receive this file
and click on send
it will automatically
go to them brother
and me going to Outlook
opening a new mail then
attaching this file
this access toolbar button,
which I have added.
It will automatically open
the email for me same
goes with a swelling.
These are all added by just
clicking on this button
and all these options
that you can see here.
You just need to click on that
and it will The ad for me.
Like I said,
I have added open
I have added email.
I have added spelling so
these three things
that are added nearly.
So when I click on them,
that should be a tick
mark next to them.
So if it is a tick mark,
that means the quick access
toolbar already has those things
on the toolbar so
like for example automatic
save is already there
because there is a tick mark
new is already there
because there is a tick mark
that you can see
your open is already there.
There's a tick mark on it.
If I add something
new like I had sort ascending
it will automatically add it
to my quick access
toolbar next time
when I want to sort some data
I can just click on this
if I want to sort
in ascending order.
I can just click
on this button and it
will automatically sort my data.
For example,
if I go to the central column
that I have your click
on this sort A to Z,
which is the smallest
to largest it
will automatically sort the data
and give me data as
per the lowest Value First
and the largest value
at the end.
Isn't this easy
and very very useful.
Also, these are the things
they're showing on the quick
access toolbar easily.
However, if you
have more commands
that you want to add
which you cannot find
in this list,
you can click
on the more command button,
which I just showed you like go
to this drop-down click
on the more command button.
It will even give
you more commands
that you can add to your quick
access toolbar like create
chart conditional formatting
calculate now borders.
These all can be added
to our quick access toolbar all
depends on your requirement as
to how many times you
have to use these commands
on a frequent basis.
So if you use any command
on a frequent basis,
you can add them
to the quick access toolbar.
If you want to remove
any of the ones
which are already there
on the quick access toolbar.
You can just click
on that again.
It will go away you will see
that the save button is
not there now to add
that save button again,
I will go back and click.
And that's a burden again.
If I want to remove one
of the sort ascending
that we just added I will click
on that again that sort
ascending will go away.
So the sorted ascending
doesn't have a technique
on the quick access toolbar how
to use a quick access toolbar.
We have already seen
how you can use the quick
access toolbar by clicking
on different buttons going
back just to explain it again,
if you have the new
and open Button,
which I just showed you
how you can click on that just
click on the buttons
which are looking for their day
will have different icons
for different commands.
If you have spelling
it will say ABC
if you have a new workbook
that you want to open it
will have a small square button
with a small tent on the side.
Then you will have
a open folder kind
of a icon which will tell you
that if you want to open a new
file from your Shredder drive
or your computer.
You can just click on this.
This is how you access
the quick access toolbar next.
Like I said,
we are moving onto
our conditional formatting.
What is the conditional
formatting conditional
formatting is a feature of excel
which allows you to apply.
A format to a cell
or a range of cells based
on certain criteria.
Now.
What does that mean?
I can color a certain
range of cells.
For example, in this case.
I can color maybe only cells
that contains marketing
like these ones
and I can color them in red
or yellow or any other color
that I feel is required.
I can color them
in those colors.
So why would I color is it would
because sometimes you
just want to know
that how many people are there
in the marketing department?
So I will just color those cells
that contains the marketing
as a department.
So let's go back and see
how does that really work?
There are different things
that are available
under conditional formatting
such as colors icons data bars
to one or more cells
can be added on these
when you use conditional
formatting for doing that.
You will have to first
create a rule.
So let's see all conditional
formatting can do
conditional formatting can first
of all hi Light
the duplicate values
in your spreadsheet.
So for example in my spreadsheet
where I have the employee code,
I have the first name last name
Department region Branch hire
date and salary everything
else can have duplicate value.
Why do I say everything else
can have duplicate value
except employee code
because first name
two people can have
the same first name two,
people can have
the same last name Department
can be shared by a lot
of employees region
can also be shared by a lot
of employees same goes with
Branch hire date can also be
while it can be unique lot
of employees might have joined
on the same date same
goes with salary.
So what is the unique value
that you can see
in this database
is an employee code
every employee will have
a unique employee code.
Now we need to identify
if there is any duplicate value
in the employee code.
Why are we doing this?
It may have happened
that when a Char
was creating this file.
They mistakenly He added
the same employee code
for two employees.
So we just need to identify
if there is any duplicate
if there are then they
will need to correct it.
So instead of looking
for each one of them
and finding out
which one is the duplicate.
We will use a conditional
formatting conditional
formatting will color
only those cells
which has duplicate values.
Let's see, how does that work?
I select the whole
employee code column I go
to conditional formatting
which is under in the ribbon
under the Home tab
under conditional formatting
click on highlight cell rules
and then click
on duplicate values.
As soon as you click
on duplicate values.
There is a new window
that opens up which has
a format cells that contain.
There are two options
here one is duplicate
or unique as of now.
We are looking at duplicate
values to be highlighted.
So I click on duplicate
and then it asks me.
Which color do you want to give
for all the duplicate values I
can select from These few colors
which are given here
so it is light red fill
with dark red text.
It will look
something like this.
Then we have yellow fill
with dark yellow text.
Then we have green fill
with dark green text like this.
Then we have more options
like light red fill.
So there is no change
in a font only the fill
will change to red.
Then we have read text.
That means there will be
no film only the red text
that you can see there.
Next is red border
or red border will be added
to those cells where there
is a duplicate value.
So no other change in that
and then there is
a custom formatting.
What does custom formatting do
when I click on custom format.
It will take me to a new window
which is the or
formatting window.
You can decide what format you
want on the duplicate values,
either you want
to make it italic
or you want to make it bold
and italic or you want them
to just strike out the ones
which are duplicates
and then click on OK it
will strike out the whole values
which has a duplicate
value by also.
Don't bold and italic
plus strikethrough.
I don't think
I need a strikethrough.
I don't think I need
a bold and italic.
I can use one of the films
that I'm more comfortable with.
I will use pink color
so that it is not very bright,
but it is good
enough to identify
which one is a duplicate value.
I click on OK,
there's colored the one
that I just selected.
So what happens is now
and I'm looking at this data
you will see all the duplicates
values have been colored.
So if there are two values
that are same like
in this case we see B-24 579
which is in A4.
It also is the same number sits
in a 8 b 2 4579.
So if there are two values
having the same data
or the two cells
having the same data,
both the cells will be colored.
So it will tell you
that these two are duplicates.
So this is how you can identify
the duplicate values by using
the conditional formatting.
Next is highlight the text
that contains specific text.
Let's see.
The next one on conditional
formatting highlight cell
with the values greater than
how do you work on this?
We have already seen
that in conditional formatting
wherever there is a condition
that is met it will
give you the color
that you have chosen for it.
So in this example as well,
we will put in a conditional
formatting a number that
we are looking for.
If you have any number any cell
that fulfills the condition
of the number greater than that,
it will automatically
color that cell.
So in that case,
I want to know all
the employees awning salary
more than 10,000 rupees by go on
to conditional formatting.
I click on highlight cell rules.
There is an option named greater
than I click on greater
than your I have
to enter the number that
if it is greater
than that number it
needs to be highlighted.
So I have say
ten thousand rupees
because I am saying any employee
or earning salary
more than 10,000.
They are salary column
or their salary cell
should be highlighted in Red.
So I enter 10,000 in this car.
Or in this stable
and then I enter the color
that I want the cell
to be highlighted in.
I want it in red color.
So I'll select
the red color click on.
Ok.
Now anybody whose salary
is more than 10,000 rupees.
It will automatically give
me this color on that.
You can use different formats.
Like I said other than only
the color in this case
if I want to change say
any suburi salary is
more than 10,000 rupees.
I want to change
their format to currency
and currency should be
your Indian currency say
I go to currency
which is under numbers are use
the currency currency will be
the symbol your Rupee symbol
and then I click on OK.
So wherever the salary is
more than 10,000 rupees.
It will give me that rupee
currency with the decimal point
because it is showing as
a formatted as a currency
to see this now.
It is also sorted
in that particular order.
So it is showing
you the exact amount
after 10,000 rupees.
It is all colored in red
with the format of Given to it.
Next is these numbers
that are less than so
if I want to know
any employees awning
salary less than 10,000
should be colored
in green and also they are
number format should now be
in a currency format
with the rupee sign.
So I go to highlight
cell rules click on less
than value this time.
It has to be 10,000 again,
but my color has to change
and the color is green.
Dark green text.
Like I said,
you can always use
a custom format to change
your formatting Zone this time.
We want to change
the formatting to currency
and the current she
should be rupees.
If you want to change the
currency to any other currency
like your dollar sign
that you need you
can use one of these
now dollar sign can also be
for New Zealand US Canada,
whichever you think you
are wanting to change
the currency for in this case.
We are going to leave
it as rupees.
So I will just
select the currency.
And then rupees click on OK
and it also depends on
if you don't want have
decimal point on that.
You can always remove
the decimal point by clicking
on these things zero as
many decimal points.
You want to just keep on
increasing the number here.
The number of decimal points
that you need.
I don't want any decimal point.
So I have put it as 0 here.
Click on OK,
it will change the format
to the currency format.
Plus it will give
me a green color
because I have requested
to put a green color
for all the M cells
that has value less than 10,000.
This is how you can use
a conditional formatting.
Now next one
that we will see is
the between value.
In this case.
We have already colored the more
than salary 10,000
and less than 10,000 salaries.
However, if you want
to First remove any
of the conditional
formatting on the sheet,
you can select the cells
where you want to remove
the conditional formatting
from so I'll click
on conditional formatting
then I will click
on clear rules there.
There is an option to clear
rules from the selected cells
or clear rule
from the entire sheet.
So if I click on clear rule
from entire tree,
it will remove the rules
which are added
to the duplicate values.
Also, like the employee code
where we had the rule
for duplicate values.
So I just want to remove
the conditional formatting
in a salary column.
So I'll click on clear rule
from the selected cells
that will remove the rule there
and the conditional formatting
which we had added
earlier will be removed.
Now.
What I want to do is I want
to add up between value.
That means if the salary is
between 10,000 and 20,000.
I need those cells
to be colored in yellow now,
I'll have to select again
the salary column click
on conditional formatting
highlight cell rules
and then click on between
once I click on between then
I'll have to give this time
two different numbers
by two different numbers
because we are looking for
employees or earning salaries
between 10,000 and 20,000.
So the first value
will be 10,000.
The second value will be 20,000.
So between these numbers
if they see any cells They have
to color them in yellow color.
So I will click on.
Hello.
I click.
Ok.
Now the rule is added
to these cells wherever they
find the salary more than 10,000
but less than 20,000 it will
automatically color those cells.
So you will see
that it is only colored
till where they found
the salary as 19150.
After that when it was 20,000
125 did not color that value
because it is more than 20,000.
So now a new rule is when added
on the conditional formatting.
So not only these
that we were looking
at all the numbers as of now
we can also do a conditional
formatting as per the text
say in the first part.
I also told you that if I
want to color the department
where I can see marketing
and I want to color them
in green so I can again
go to highlight cell rules
and then click on text
that contains marketing
so I can say m KT G wherever
there is empty tgy am saying MTG
and not marketing
because the Your is M K TG
and not the full spelling
is mark820 ing.
So you will again have to be
like in the first
module we learned
when we were doing
a formula for some
if we have to write M K TG
and not mark820 ing.
Same goes with
the conditional formatting.
We will have to put
M KT G as your requirement
because then Excel
will look for em,
ktg in those cells.
Once it finds the MTG
what does X and supposed to do
it has to color them in green.
So I select the green fill
with dark green text
you can again select
like I said
from the custom formatting
whichever color you want.
But in this case we
are looking for green.
So I select green click
on OK it will color the cells
which contains M
KT G as the text
and give me a green color
on those cells other things
that you can use
under the text function is equal
to text that contains
and duplicate values.
So duplicate values like we saw
the first time it can be used
with any cells
which has numbers
or text or character.
So wherever it finds
the Same character
or the same text
into different cells.
It will color both the cells.
So this is how we are
conditional formatting works.
There is also other options
in conditional formatting
which are known as the data bars
and the color sets.
How does this data bar?
And the color sets work
data bars color scales
and icon sets set
our conditional formats
that create visual
effects in your data
these conditional formats make
it easier to compare the values
of a range of cells
at the same time.
If you see the data bars,
which has different colors
the gradient fill
and the solid fill will see
how does this data bar work
in the first place?
So just like the graphs
where we have those column
charts same thing.
You will see
in the data bars your
how do we do that is
if I want to say give a colors
or a data bar to
the salary column
where in the highest salary
should be compared
with a smaller ones as well.
Let's see.
How does that work?
So I select
my salary column I go
to conditional formatting click.
On data bars and then I click
the gradient fill for now.
If I click on the gradient fill
it is giving me a small bar
next to each of the values.
If you see the bars next to each
of the values you will see
that the bar
is changing its size.
So let's sort it in such a way
so that we can also
see the difference
between how is it
actually giving that bar?
So I sought esper salary
in a descending order
from largest to smallest now,
you will see
that the 49,000 is the salary
which is given to sheetal
is the highest salary
given to the employees
in the organization now
all the other salary amount
which is given there is compared
to this 49,000 amount
and then the data bar size
is changing according
to the 49,000 amount.
So 49,000 is highest
which is close to 50,000
24,500 is half of 49,000.
And that's the reason
you will see
that the data bar
is now become half if you go.
Went further down like go
to say 10,000 or something.
You will see
because then thousand is
even half of 24,000 data bar
has even become smaller,
which is it has gone
half of 24,000 amount.
So the data bar,
which is there that gives you
a graphical representation
of where your data sets
as compared to the highest
value in your data.
So next is the color scale
on conditional formatting
color scheme can help
you understand data distribution
and variation such as
investment returns
over time cells are shaded with
gradation of two or three colors
that correspond to minimum
midpoint and maximum thresholds.
So, let's see.
How does this color
scale work first?
I will select this color scheme.
Let me remove
any colors or rules
which are already there
on the selected cell now go
back click on color scale select
the range of the cells the table
or the whole sheet
that you want to apply
the conditional formatting
to on the Home tab tab.
Click on conditional
formatting point to the color.
Then click on the
color scale format
that you want by default
for three color scales Excel
calculates 50th percentile.
I'll tell you
how does that work?
As soon as I click
on this color?
Scale, you will see
that it has given the green
color to the highest value
then it goes down
and it gives different colors
depending on their value.
So there is this another
or becomes a little orange
and then it gets red.
So if I want to see
how is this working
is I go to check
in the manager rule box.
I will check how many colors are
there in this there are
three colors it soon
as I click on the ones which I
have were just made the rules
for I can see there
are three colors
one is the lowest value.
I have red color then
for the highest value.
I have given green color for
the midpoint in our what
is the midpoint midpoint
will be the 50th percentile.
We can also change
the percentile two numbers say,
I don't want to keep a % L.
I want to keep something
like a number say 24,000
because ha For 49,000
is ready 4,500 rather.
So let's keep 24,500 as a
midpoint and run the percentile.
I can change it to number
if I want to change it
to percentage also
that also I can do but as of now
we will change it to number
say 24,500.
So if it is the highest value
it will be made the same
which is 49,000 the lowest value
will remain the same
as read it can change the color
if you want to I'm leaving it
as it is.
The midpoint will be
a number say 24,500.
Now when I click on OK
and then click on apply
and OK it will automatically
give me the lowest value
as read the highest value will
definitely be the green color
which is 0 and the midpoint
which we entered
as 24,500 should be yellow.
So, let's see.
Where is our 24,500.
This is a 24,500.
So this is all yellow color.
So depending on which color you
give it will automatically hold
those colors and the rest
of all the other cells.
Our colored proportionately
we've also seen
how we can customize those cells
if I want to click on these
and then change the color scale.
I can click on more rules.
I can always change
the color scale
to to color scale.
So instead of three
color scale now,
it will be only two color scale
which is red and yellow
and so the lowest value
will be read the highest value
will be yellow.
And the rest of the cells
will be colored proportionately
this time you will see
that the colors I have really
changed you'll see more of red
because you are number
that we have is
the lowest value is red.
So the proportionate colors are
given to the rest of the cells.
So this is how we are
color scale works.
Next we are going
to see is the icon sets.
How does their icon
set work again?
If I click on
if I select the whole cell
where I want this icon set
I click on the icon set
option select the one
that I wanted a directional one
or if I want the shapes
like we have the signal
shapes we can use.
Those two color
and the colors will be given
in the similar fashion
as we saw in the color scale.
So if I am selecting
the directional it will tell me
which is the highest
which is the lowest
and which is the midpoint.
So for example,
you will see 49,000 is
the highest and so it is showing
in the green color
and it is showing the arrow
upwards then the midpoint,
of course like we all know
is 24,500 and hence.
It is showing me as
on the right hand side
when it is highest value.
It shows the arrow is up
when it is the midpoint.
The arrow goes on the right
and when it is a lower point
if the arrow goes down
if you again see the icon set
you will see this value.
Yo, if you don't
want those colors,
you can also use
the other options
which are without the colors.
They are all Gray colored
so they will not give
you any color on that.
So I just go back I change
any conditional formatting you
will have to click
on manage rules.
The one that you're going
to change you will click
on that particular icon
and then select the icon style
that You want to change to now
we want to change it to green
and click on ok.
Now they are all gree it
will only change the direction
of the text but everything
else will remain greatest time.
So this is how we are
conditional formatting
with icon sets will work.
Let's go back
to our slides and see
so we have already seen
that how does your conditional
formatting with data bars
conditional formatting
with icon sets work?
You can also use formulas.
For example, we have
option to use formula
in order to identify
if that condition is met only
then we need a color let's say
I want to First remove
the conditional formatting
which is there on these sheets.
So I will just click
on anywhere on the database
click on conditional
formatting clear rules.
And then there is
an option to clear rules
from the entire sheet.
I click on that
and it will remove all
the conditional formatting
from that sheet.
Now.
What are we trying to do is
we are trying to identify
or get the color on the cells
which meets a certain Condition
with the formula say I want
to identify in the employee code
how many are numbers
so if you will see
that employee codes
are either alphanumeric codes
or there are few of
the employee codes are numbers.
I only want to know the ones
which are numbered
so I will select the a column
because they are
employee code sets go
to conditional formatting then I
will click Bond new rule as
soon as I click on new rule.
There is an last option
that says use a formula to
determine which cells to format
under the formula tab
where I have to enter
the formula there.
I will enter is equal
to this time.
I am looking for all the cells
that contains numbers.
So I'm going to enter is equal
to is number open the bracket
and then select the cells
which can contain
numbers on to see
how do you highlight
cells with the formula?
So only if the condition is
what the formula
is fulfilled then the cells
will be So we'll see
that in the employee code column
you have numbers as well
as the alphanumeric data.
So the employees
who have joined 5 years back.
They are employee codes
where only numeric
data but anybody
who's joined three years back
which are more recent employees.
Their employee codes
are alphanumeric.
So I just want to identify
whoever's employee code is
a numeric data and I want
that to be highlighted
in blue color.
So what I'll do is
I'll first select the cell
where I want the color which is
the a column in our case
because I'm looking
for the employee code click
on conditional formatting go
to new rules by new rules
because this time we
are looking for to use
the formula to determine
which cell to format.
So I'll click on use
a formula to determine
which cells to format then
under format values
where this formula is true.
I will enter the formula
is equal to is number open
the bracket because I
have selected the whole cell.
I'll have to start
with the first cell
which is a 1 so I
will Is equal to is number
A 1
so I am saying
that if a 1 is a number
then give me this color.
If A1 is not a number then
don't give me the color.
So I'll just click a blue color.
I click on OK wherever
there are numbers.
It will automatically give
me that particular color.
So I will be able to identify
which are the employees
which have joined
five years prior or they
are more tenured employees.
This is have
your formula data Works.
Let's move on to our PPT.
And how does is Formula work?
So there are
different is formulas
that you can use in Excel.
One of them is z is numbers.
What does it do is
it will only identify
if it is a number it will say
that it is true.
If it is not a number
it will say false.
For example, we just learned
that if it is a number it
will automatically color
that in blue.
There is also an option
something called as is odd.
That means if it is
an odd number then it
will highlight them
in particular color
that you mention
if it is not an odd number
it will not highlight them.
So See even the salary column
if we have any odd number,
so I go to news rules.
I select the salary column
I go to new rule.
Then I click on use
a formula to determine
which cell to format then
in the format values
where this formula is true
I click on is equal to is
or open the bracket
now this time again,
I have selected from
the first cell in that column.
So I'll have to select
the first cell
which has H1 close the bracket
and now I will decide
on which color I wanted say.
I want the yellow color.
So I select the yellow color
from the custom format
and click on OK it
will color all the ones
which is odd numbers
into yellow color.
The rest of them
will be left as it is.
So these are is functions
that you can use in order
to get your data colored.
You can also use any cell
which is more than 75,000
like we use more
than data or greater
then data information.
You're in the
conditional formatting.
You can also use
the formula under new rule
where you can put c 3
is greater than 75.
Maybe I want to know.
Employee that is earning
more than 10,000 rupees.
I want them to
be colored in green.
So what I'll do is use
the formula to determine
which cell to format
under the format values
where this formula is true.
I will take H 1 again
because I'm selecting
the whole cell is equal
to H 1 is greater
than 10,000 same thing.
However, we are using
the formula to determine
that now this time I'm going to
color in blue so I click on OK.
So wherever there is in salary
more than 10,000 rupees.
It will automatically
color in blue anything
that is less than 10,000.
It will leave
the previous formatting
that we had same thing.
You can use the less than sign.
Also wherever you
need to another one
that you can see here is you
can also try the formulas like
where you see a as format
like in this example,
if you see the students
data is given here
under the marks in mathematics.
There is a score that is given
in the greatest given
according to the scores
in the grade column.
You'll see ABCD depending
on their marks that they
have been given a grade now.
We want to know anybody
who is coder a grade
in that case under the Formula
to determine the cell to format.
We will put b 2
is equal to a by b 2
because we are starting with
a be to column we are starting
from the First Column
if the data starts
from the first cell,
which is b 1 then we will have
to give b 1 as the data
but we are starting
with the second cell
where that is
where it is starting.
That's the reason we
will give the second cell.
How can we logically
use functions such as
and function and or functions
in conditional formatting.
So, let's see how we are going
to do that we can use
and and the or functions
in the conditional formatting
for us to understand
how to use and function we
will have to first decide
which are the conditions
that we have say in our example
on my spreadsheet.
I have to color all
these employee code cells
where the department is sales
and the region is not so we have
two conditions one condition is
Department as sales
and the second condition
is region s North
So these are the two conditions
that we have for
our conditional formatting.
So both the conditions have
to be fulfilled in order
to for the conditional
formatting to work.
There are three things that we
are going to learn here one is
how to identify
how to color or different cell
depending on the conditions
do different cells.
So, for example,
I want to color employee code
depending on the conditions
fulfilled in column D
and column e
so to start with I
will select a to a
because I want the color
in this column.
So I select way to a I go
to conditional formatting
I click on new rules
under new rules.
I'll go to use a formula
to determine which cell
to format under format values
where this formula is true.
I will enter is equal
to this time.
I have to condition
so I have to use
and function open the bracket
after the and function I
will select the first cell
in the database,
which is d 1.
I'll have to type d 1
and not select D1.
Why I will tell you
if I select D1
that dollar sign comes
and then not apply
to the rest of the cell
and so you will not get
the correct answer.
So it's better
to always type d 1.
So the first condition
is D1 is equal to sales.
We will always put sales
in the double quotes.
Second condition is e 1 is equal
to North so I put in
the double quotes again North
no case sensitivity
in this I close the bracket.
So my condition is has
to be fulfilled like
if this department is sales
and the region is not I want
the color as say green.
So I select the green color
in the custom
formatting I click on OK
and then I click on OK
again wherever there
is a sales department
and the North Region
it will give me a color
in the employee code sell
for that corresponding data
as a green color.
So wherever you find sales are
not the employee code cells
will be colored same goes
with an or condition
if I want to color.
This time I want to color
the department column wherever
I find sales or marketing,
so I will select the department.
I'll go to conditional
formatting I click
on new rules again use
formula to determine
which cells to format
under format rule values
where this formula is true I
click on is equal to and start
with D or function.
Why do I do an or function
because I am looking for sales
or marketing either
of these conditions
are fulfilled then
color it as yellow maybe
so I say d 1 is equal to sales
in the codes or D1 again,
right even again
because I am looking
in the same cell
if it is either sales
or marketing marketing
will be M KT G close
the quotes close the bracket.
Now my condition has
to be fulfilled as either sales
or marketing color.
It has yellow so I
select the yellow color
and then I click on OK
then I click on OK
it will automatically
color these.
Sales, and the marketing
one says yellow.
So this is how you can use
and an or function
in the conditional
formatting as well.
So let's start with
the logical functions for now.
There are four logical functions
in Excel and or not and Zoar.
Let's see, how does the not ends
or functions work
in Excel the not function
Returns the opposite
of the given logical value,
that means any value
that I give it will return
the opposite of that.
So let's see using the example
that we have on our sheet
where I have the student's name
and their scores or their marks.
They have scored
in the subject maths.
Now, let's say we have
to identify any student
that has failed in maths.
They're passing marks
is 35 here.
We can use not function.
So how do we start us is equal
to not open the bracket enter
the cell reference
where the Marxist so
for the marks is
in the cell reference be true.
So what we have to identify
that He too is not more than 35
if it is 35,
then it has to listen.
The river is fail
if it is more than 35
then it is passed.
But what I'm trying to say
is I want to understand
who all I have failed in maths.
So what I'll do is not more
than 35 I close the bracket
once I do that I'll get wherever
there is not more than 35
now get our result is true.
So for example a drag it
here wherever I can see true
those are the ones
which are failed result.
So this one G is code 32 and
that's the reason
it is showing true
for E. It is showing true
because they have only scored 30
that means it has failed
and again for see it is 28
and because they have scored
less than 35 they have faith.
So wherever you can see true,
they have all failed.
So similarly if I wanted to add
this along with the IF function,
how can I do it
with the IF function
so I can do if starting
with the logical test?
What is my logical test
that whoever is not scored
more than 35 marks has failed.
So what I am going to do
is B 2 is greater than 35
that means who has
not scored more than that
if I should be fail,
so I'll put fail as
the result for Value
if true and value
if false will be in case if
that is not the case it is fast.
So if I am using it
with the function,
this is how it would look moving
on to the next function
which is as or function.
There's our function checks
whether one and only one
condition is met so
if there are two conditions only
one of the conditions is met
if no conditions are met
that means condition
a condition B. Both are not met
or more than one
condition is met
so our function returns false.
That means if I put a condition
say condition a condition B,
if condition A and B,
both are met then those
or function will give
you a false answer
if none of them are met neither
is a neither be then also,
it will give a false only one
of the condition has to be met a
or b then it will give
you a true answer.
So let's see how we can do that.
I want to buy a new computer
and I have two Ends
of the computers
that I have to consider the size
of the hardware and the ram
I select the computer
if the hard drive has
at least 500 MB s.
So the first condition is
high drive 500 MB is
what is the second condition?
The second condition is
if the ram is greater than 6 GB
it has to be greater
than 6 GB and not 6gb.
Just remember this is 500
or above for Ram.
It is more than 60 B.
It cannot be 6 GB.
So how do I do sore?
So I go to the door
I go to the cell
where I need this information.
So I need to identify which
of these computers I can select.
So the first thing
that I'll do is sort of
logical test hard drive
which is an f 2 is greater
than or is equal to 500.
Why do I say this?
Because I need to identify
if it is 500 or more.
Then I can buy that or
if this condition
is not fulfilled is
the other condition fulfilled.
What is our the condition
if she too which is
our Ram is greater than 6
and there is no equal
to in this why no equal
to V coz Three say
that it is equal to then it
will also take the result
when the ram is 6
but we don't want six it
has to be more than 6.
So I press enter
wherever I see true.
That means these are
the computers I can buy
because one of the
conditions are fulfilled.
This is how we are
sore function work.
You can use them
with the IF function similar you
can use the nor donators
or in a not function.
You can also use
the other neurological functions
like and and or with
the IF function now see
how to do an IF function.
Let's see an example
on my sheet say
I have a data here.
I have to calculate
the bonus of the employees
or Commission of the employees
in the sales department
or the commission
of the employees.
Now our mission
of the employees is decided by
the management management says
that we are only paying
commission to the employees
in the sales department as
Commission of 10,000 rupees.
So only to the sales department.
Employees.
So how do I write I can think
of an IF function?
I have to select B to
because my department
for each employee sits
in the B column.
So b 2 is for the first
employees only I have to check
if P2 is equal to sales.
If it is sales,
then I have to give
a ten thousand amount value
if true if value
if false I will put 0
because there is no condition
given for the others assume
that if only sales department
is getting 10,000 others
are getting nothing
so I can leave it as 0
so wherever their sales
it will automatically throw
a 10,000 amount in the cell.
So this is how we are
IF function Works moving on
to using the if with an
and or an or function
similar to how we use
the not function with then
if we can use the IF function
with an ad or a or so to start
with we are looking
at our sheet on my sheet.
You can see that I have
these name of the students
the scores in math
and the score in science.
So what I'll do is I need
to know the students who?
Bost how do I know
if they're past
if the student has
scored you can see it
here the reconditioned
if the student has scored 35
or more marks in each subject,
their final result
will be passed
or the result will be fake.
So if they have cord 35 or more
marks in each of the subjects,
that means both
the subjects they are pass.
Otherwise, they are failed.
So what I have to do is I
and user and function y and
because I need to ensure
that they have scored 35 in math
and they have scored
35 in science.
So I start with an IF
function IF function
because I have a True Result
that I have to give
and a false result
that I have to give H is
if this is true give me
pause or give me fail
so I can only do
that in IF function now
with the logical test
when I had one logical test,
I put it as department
is equal to sales,
but now I have two logical test.
So I'll have to use a
and function inside
if what is
my first logical test.
I have to check
if the math score for student
a is the more than 35
and the science code for student
a is more than 35 so Potent
and function then start with
n b 2 is more than or equal
to 35 and second logical test
that c 2 is more
than or equal to 35.
So both of them
once it is Wilfred
once I complete that end,
which is The Logical test.
So I have to close the bracket
because the end is complete
once I complete
that you will see
that we are coming back
to the IF function.
I have to put a comma to get
to the next element
in this context.
What is the next
argument value if true?
What should be the value in case
if this is true,
it should be passed.
So I put bars and the inverted
comma value Falls is fail
in the inverted commas.
Now close the bracket enter.
Now when I drag this formula
down it will give me the result
for each one of them.
So if you see for students
see they had scored 28
in match Which is
less than 35 marks
while they have scored
more than 35 marks in science.
However, we are looking
at both of them to be
more than 35 and a hence.
It is giving the failed
same goes with d
and e g moving on
to the next one.
What is the question
that And see your is
if the student has passed
the certification test,
which is 80 or higher.
So we have a certification test
for the students
and they are saying
that if they have
passed the certification
in the passing marks
is 80 or higher
or has passed the preppies,
which is your next
requirement prep test
which is the passing marks
is 70 or higher.
They can register
for the next class.
So what is their question that
if they have either scored 80
or more in certification
or they have scored 70
or more and prep course,
they can go for
the next registration
or they can register
for the next class.
So here again,
I have the condition where I
have to give a true value
and a false values and hence.
I'm going to use the IF function
to start with an F function
and then it is
the there are
two different things
that I have to look
at the certification
and the prep course,
both of them will start
with an or why an or
because I have to see
if this is 80 or more
or if this is 70 or more.
So I start with the first one
which is certification
if certification is greater
than It is equal to 80
or the next one is prep course
is greater than or is equal
to 70 close the bracket.
Now you logical test is complete
give me value if true.
What if this is true
they can register
so I can click I will enter
register in the true value
or cannot register
in the false value
or I can say yes or no
rather than saying
if you see the heading
it says yes or no
so I can just put
yes or no here.
So I'll put yes comma no,
it's an easier way to just put
it but depending on what it is
you're looking for you
the free text you can use
that close the
bracket enter now,
you can drag the formula
down and depending on
whether the student has scored
more than 80 or not.
So now if you see
the children D,
it has neither scored 80
or more in certification
nor has it scored 70
or more in the prep tail course
and those does it
is showing a know
if you see student J.
It has not scored
more than 80 in certification
nor has it scored more than 70
in the prep course,
and that's the reason again.
They have data showing know.
You look at one more example.
If you see this one student
e-day have not scored
more than 80 in certification.
However, they have scored
more than 70 in the prep course.
That's the reason the result is.
Yes.
So this is how you can use
the if and not together
or other if and and
if not together now
moving on to the next one,
which is the if error function
so the iferror function
when working in Excel,
sometimes we will experience
errors these errors can be hash
and a hash value
hash reference Etc.
You must have definitely
seen all these errors
when you're working
in Excel in this tutorial.
We will look at how to use
if error function
in Excel to deal
with this errors constructively.
Let's see an example
on my sheet in this scenario.
What is happening is we have
a data set consisting
of columns of numerator
and a column of denominator.
Then we have a column
that contains the result
of dividing the numerator
by the denominator.
There are some zeros
in the denominator
like this this this so
because of this it
will cause a div error.
That is the hash div / 0 -
error for these rows
in the result column we
could go of step further
and make Excel place a 0
in those rows to replace
the error for the sake
of a cleaner look
but it should not interfere
with the calculation
of all other rows to do this.
We will use the
if error function.
So how do we do that?
So I will start by typing
and function if you see
that I have already put
the result here.
So instead of doing
that I will first do an iferror
function in a another cell
and I'll show you
how does that work?
Now?
What am I trying to do is
if you see the syntax
of the if error function
that says value value
that means which value
are you looking at
whether there is a error.
So I'm thinking looking
at c 2 comma if value
if error if it is an error,
what should the value B in case
if there's an error?
I need a 0 so then
I Loser bracket enter now
if I drag the formula,
you will see wherever
there was a div error.
It will automatically
give me a zero however,
now it is giving me a different
in a different cell
if I want to do it
in the same cell.
How do I do that?
Then wherever my formula is
after the equal
to the formula start.
So after equal to I'm going
to put iferror open the bracket
inside the bracket
where the value is I
will put the formula
for which I am looking
for the error in this case.
It is a 2 divided by B
to numerator divided by
denominator common value
if true I'll have to put
zero there was one thing
that I have mentioned here.
I have put a inverted comma I
don't need to put inverted comma
in case of numbers so here
when I put a 0 and drag
the formula you will see
that wherever there
was a div error.
It has automatically given
me a zero and result why
because now I have got
a formula for xindi pay
special your we have
so you will see
that the result I can see is 0
for each one of them wherever
there was a div error.
So moving on to the next one,
which is the if any error
if any error is one of returns
a value user specifies
in the expression,
whether the function encounters
the na error, otherwise,
the function Returns,
the cell value is similar
to the iferror function.
So one of the shortcomings
of federal function is
that it acts as
a catch-all errors.
So even if it is a div error
n a value reference it
catches everything this means
that it covers several errors
and the consequences
of this error is that
if error isn't really helpful
in helping you distinguish
between the different
types of error
and sometimes may hide
a particular error
that you would like to fix
if an IF function
is used to trap
and handle only any errors
that may arise in the formula,
especially those that perform
look up using match.
We look up a trick
of Exeter than a function
will only handle any errors
which means other errors
may be generated by
the formula will still display
so in we look up
when do you In error
when the data is
not available in the sheet,
so you'll get a error
in that case and
if you are getting thinner
like a any error,
so if you're getting
that kind of an error,
then you will have
to definitely use a function
which will tell you
that this data is not available.
If you're going to be there
if error that means there is
a referencing error.
That means the data is there
but we don't even know
if the data is there or not,
but the referencing error
means we have not given
a right reference
to that the cell reference
and that's the reason
I'm getting an error.
So to ensure
that I don't hide
the reference error.
I only highlight the any error
I am going to use the
if any function
how do I use the if any function
similar to the iferror function
I start in the another
column you will see
that in the current
should receive their
I've done a vlookup
and found the result
from this table.
We will do we look up
ahead so we'll know
how does the vlookup function
also work for as of now
with the field wake up function.
I have been able to derive data
for some but the rest of them
where I can see
the na that means
that this data is not available.
The table, so
if I am going to say to excel
that if the answer is
n a what answer is
an a this one is any then
keep me up blank data or give
me don't data available again.
Like I said,
it's a free text
but you have to put it
in the inverted commas.
Once you have
that you'll be able
to get that results on
though rate available.
Now, I can just drag
the formula down.
So for each one of them wherever
the data is not available it
will give me the result is
no data available.
Now, how do I copy
the same result
or the same formula
in the vlookup function
similar to if error I'm going
to do after the equal to
if n a open the bracket
vlookup function I have done
in the values comma
y value if any what do I need?
No data available close
the codes close
the bracket enter wherever
there is no data available.
It will automatically give me
the result has no data available
the rest of them wherever
the data is available.
If an error will give me
the correct answer only
if there is an error it
will click for that error
and replace it with
a no data available now,
let's move Want
to the nested function?
So how does the nested
a function work?
Nested-if is not
inbuilt function.
In fact, it is
not a function at all.
When you have multiple
conditions to meet
the false value in if is being
replaced by another IF function
to make a further test,
for example, let's look
at minus 2 the function.
So when I'm starting
with the IF function
any function will have a value
of true and value false so value
if true ice put a comma
here as soon as the value
false starts I start
with the next IF function.
This is what it means.
So moving on to our list
supposing you have a list
of employees in column A
which we already have here
and you have the department
in column B
and the bonus amount we pay
to their each employees decided
based on the condition
given in this table.
So to the sales department
are giving a bonus
of 10,000 for the Edmond bear
agree to give a bonus of 5,000
for marketing 8,000 RND 4000.
And for others.
It is 3000.
So looking at the nested
function I have
to do say the bonus.
That I have to identify
is in column e
how do I identify bonus?
So first and foremost
I have to do is
if I look at this I start
with the IF function
because I know
that the IF function Works
something similar to this one.
So I'm going to sew
first type only the formula.
So I start with an F
function is equal to
if why I'm using a face
because I know that
when I'm using an IF function,
the IF function has the true
and the false value here.
I have to see
if the sales is true
then give me 10,000
if it is not true then have
to look for other things.
So the first and foremost
that I'm going to do
is I need to identify
if this cell contains sales
if it contains Hills
then I'll give 10,000.
So let me put it
in a different bucket
in the first place
if it is sales,
I have to give 10,000.
So this is my first
bucket say bucket one.
I'm putting a bucket also
here so I know what
I am talking about.
This is my bucket
1 contains sales bucket
tube contains admin,
which I am going to give
5000 bucket 3 contains
marketing the marketing.
I have to remember that as well.
A little different from
what we normally write.
So 8,000 then pocket
for contains RND for R&D
that bonuses 4,000 bucket
5 contains others.
Now what is others the ones
which are not falling
in these lists are all others.
So looking at this data you
should be able to identify.
How are we going
to put it in the list
that we have here?
So this is my list to start
with any function.
I have to start
with an equal to sign.
But in this case I
have to again start
with is equal to F function
IF function The Logical test
25 first logical test is
that I have to look
if the department is equal
to sales department for who
I will look for department
for Raja Raymond occur.
And there are other Raymond
occurs Department result
is sitting in be too so
I select b 2 is equal
to what am I looking for sales?
So b 2 is equal to 0
so either I can
select this sales.
I can select this cell
which says sales
or I And put it
in the double quotes as
seeds either way it will work.
So as of now,
I am going to select
sales from your gamma.
So I say that if V2 is equal
to sales then give
me the value of true.
What should it give me
if the result is sales.
It should give me 10,000.
So again, I can either put
10,000 s1000 or I can select
this H to a z column
or the cell reference
because the 10,000
is there in the cell,
but remember if you see
remove this cell reference
or if you edit anything
in the cell reference,
the result will also
change here now moving on
to the next one so value
if true I know the value
of true is 10,000 but what
if the value if false
I know if it is sales I
have to give 10,000 but what
if it is not sales then
I am telling Excel
that please look
for the other condition.
What is our the condition
whether V2 is equal to Edmund?
That is my second condition
that I have to look for.
So b 2 is equal to admin
so I select the admin,
What if it is admin
give me 5,000.
So it is true.
What if it is false again
I start with The formula is
a B2 is equal to marketing.
Now.
I have to remember
I cannot take G to
because marketing spelling
your is different from
the marketing selling your so
what I'll have to do
is I'll have to put it
in the inverted commas
marketing comma this data
which I am typing in the double
quotes is not case sensitive.
So you can put it as
M KT G in all capital
doesn't matter now again 8,000
we can take a cell reference
because there is no swelling
thing here comma again
I start with an if y if
because this time I have
to look for be 2 again B2
is equal to RN D comma
if it is an R&D give
me four thousand or
if it is not R&D.
Just remember now again,
putting a if will not work
because if I put
if you're I will have
to say which other
departments are available.
So what are the Department's
left after Edmund sales
and marketing and R&D.
There are personal
there is director.
There is financed.
This is all the CCD is there
so all these departments
are left out.
So are we going to put Each one
of them under saying
if this given three thousand
so not for all of them.
Am I going to put three thousand
every time so how do I ensure
that in one go?
I am able
to I don't have to give all
the conditions together instead.
I can give 3,000
in the end saying
that if none of the conditions
are fulfilled then value
if false will be 3,000.
So why am I getting this error?
Because I had to put brackets
for each one of them.
So one bracket
that I put you will see
that it is the bracket is
for a first
IF function second bracket.
I put it is for the second
IF function third record.
I put for the third function
and the fourth racket.
I put for the fourth a function.
Now, when I press enter it
will go ahead and will see
that wherever there is data,
which is closer
to the we are looking for it
will guns we've done so far
if you see on the result here,
the result is showing a 3000
but if you see
our department it is admin
so it should ideally
show a result as 5,000.
Why is it giving an error?
What I have done is
when I'm looking at the list.
I have given the cell.
References when I'm giving
a cell reference.
I have to ensure that it is
exact spelling should match.
It is not case-sensitive
the but dispelling has to match.
So what has gone wrong in this
if I go and check
here I will see
that the Edmond is
without any space at all
looks all fine here also,
it says admin
but if I could
double click inside,
I'll see that the admin
as a space at the end.
So when I remove the space,
it gives me the correct result.
However, that's the reason
I always suggest
that you should rather than
using this cell references.
It is better to use
the names there.
So this was one way of doing it,
but after your cell
reference is done,
you cannot drag it directly.
You will have to actually see
which cell references
needs to be freezed.
Like you'll have to freeze g2y
because G2 will
always remain Jutsu.
So let's do one thing
rather than making
this more complicated.
Let's do another cell and try
and do it with the names
in the double quotes.
So first we have done it
with the references now,
we will do it
in the double quotes So,
how do I do
if Open Bracket logical test
now we are doing it
for the second one.
So The Logical test
is B 3 is equal
to Sales even easier to drive
the sales then give me 10,000
or give me value false again.
I'll have to start with if what
is the second one B 3 is equal
to Edmund this time.
If it is admin,
I have to get 5,000
now in the false.
We will put the
if condition again
if p 3 is equal to marketing
again marketing we will put
it as M KT G comma value
if true what do we need?
If it is the value of true many
the result has 8,000.
So I'll put 8,000 you,
what is the next one
if again B 3 is equal
to R ND then give
me four thousand.
The last one that I have we did
were there was three thousand.
So I've not given
any cell references this time.
I will immediately just taken
the result in the middle commas.
You will see
that the difference now there
you are getting all zero zeros
or some other numbers here.
You can get a better result
wherever there is admit it
will give you 5,000 marketing.
It will give you 8,000.
Let's copy the result
even in the first one
so that the formula
is copied now,
so it is always advisable
to put it with a hand so
that It becomes more constants.
So you don't have to keep
freezing those cells
which are the cell references.
This is how you do
the nested function.
Let's move on to the next one.
The next on our list
is the text function.
What are those takes functions
start with the first function
that we can see is
the lower function.
Let's move onto our list.
The lower function will convert
your text into a lower case.
Like any text
that you have in a Cell
you can use this lower function
to convert it into a lower text
in example is equal
to lower open the bracket
then I select the first name.
I close the bracket.
You will see that the Rajah
has changed the case true
or lowercase earlier.
It was our was Capital
all the others will lower
but this time it
is all lowered similarly
when you're doing an uppercase
the same function will apply
where you can use the upper
and all the characters or all
the alphabets in the text.
It will convert
into a proper case.
You will see that I have been
able to convert the first name
into all uppercase similarly.
The third one
which we have on the list is
a proper case proper case just
like lower and proper
function proper Works
in a similar fashion.
The difference is
that proper function converts
the first letter in to uppercase
while changing the rest
of the letters into floor case.
So, how do we do this CRV
or have the same cases?
So let's use
the uppercase letters
that we have you
so is equal to proper
and then select the one
in the upper column enter
you get the first character
or the first alphabet is upper
and the restaurant
will be lower.
This is how we are upper lower
and the proper
bomb functions work.
The next function
that will have
on the text function list
is the left function left
function extracts a given number
of characters from the left side
of this Supply text or string.
So if I say a is equal to left
and select the text from which
I need to extract the data
and then comma I can give
the number of characters
that I need to extract
from the left side of this text.
So I select say three
once I select three it will give
The first three characters
from the left.
This is how your left
function works now
similar to the left function.
We have the right function.
So a is equal to right.
I select the same name.
I give three characters
from the right it
will give me a ja why
because it is taking
it from the right.
This is how we are
right function will work
moving on to the next one.
What is the next function
that we have on our list?
We have mid function in the list
the mid function Works
in a similar fashion as
the left in the right.
It will not extract data
from the left or right it
will extract characters or data
from the middle of the text
where you are required
to provide a certain position
and the given number
of characters from
the starting position.
So for example is equal
to Mid then we select
the text say this time
I'm selecting the last name
because it's a bigger character.
I have to tell
where I need to start.
So it will not decide on its own
from we're in the middle.
You will have to give a data wet
as to where they need to start.
So I need to start say
from the third character
so I can give three
in the start.
Number and now the number
of characters from
the third character.
So when I say from the third
character number of characters,
it's ymo say three characters.
I want from the third character.
So I will get the answer
from the middle.
It is third character is RA Y
and Y MO is the three characters
after the third character.
This is how your mid
functions work moving on to.
The next one is
your Len function
Len function provides the length
of the characters in the cell,
for example to identify
the number of characters
in the first name.
I will enter is equal to length
and then I will select
the first name column
and close the bracket enter.
I'll get the number
of characters in
the first name column.
This is how we are
Len function work.
If you drag all these formulas,
you will get the data
for each one of them moving on
to the next function
the exact function X the Axl
exact function compares to text
in the different cells
and returns true
if they are same and false
if not exact is case sensitive.
Let's see.
How does that work?
Say, I have the data like apple
and apple I have apple
and I have small letter Apple.
I have apple all capital
and then I have apple all small.
So it's like different cases
that I have it in only
if both the characters are
the same it will give
me a True Result.
So I just put exact open
the bracket text 1 comma x
2 I close the bracket enter.
It will give me true
because both are
at the same case
and the apple apple both
the names are the same
in both the cell
and even the cases are
the same same goes with a few
if I go and try the exact
function in this one A2
and B2 close the Enter give
me a false result,
why false result
because in a to the data
that I've entered the character
that is entered
in a proper order
like we have
that proper functions.
So it is like first character
or the first letter is
capital rest are all small.
But in the B2,
we have all small letters same
if I try and copy the formula
to the third one.
It will give me a false
because all capital in the A3
while all small in B 3.
So this is the exact function
Works in this way moving on
to the next one
that we have on our list is
z RN between function.
What is an RN
between function are
in between truncation Returns
the random number
between the specified values.
So it will just give
you a random number
between the values.
For example, I give 20
and I give hundred and I use our
and between function.
I have to give two numbers
one is the bottom
and the top number so bottom
will be the lowest number
while the top number will be
the highest number between this
it will give me a number.
Every time I refresh the sheet
like I go and calculate
read it will give me
different numbers every time
between 20 and 100.
So it is a random number
that will pick
and give you this
is our viewer are
in between functions work moving
on to the next one is
the named ranges in Excel.
You can create name
that refers to cell
or cells on the worksheet
specific values or formulas
after you define the Excel names
you can use those names
and formula instead of
using a constant value
of the cell reference.
Let's see how you can name
a range or a sell easily
and how you to use them
in a formulas to make
your life simpler.
Let's see our vlookup formula
that we have on our list.
I can name the table
or a database
which in our case is
even to eaten I can go
to the name box.
Not necessary.
I can only eat select
this whole table.
I can select even
one cell for that matter.
I can select only one cell
and give the name here
in the name box.
This is called as a name box
so I can go there enter the name
as Matt which is name given.
Inside the data I
can click enter once I do
that this cell name becomes Matt
if I want to give the name
to a particular range
like this full range.
I can say Student's name
so I can just put the students.
I cannot give
a space in the name
so I can put students
and then named after
an underscore and pressing enter
what happens is it
will changes the name
of those range of cells
to Student's name.
This is how I have given
the name to this range.
Now, how do I modify any
of the named ranges
that I have given already?
If you go to formulas
in the ribbon under formulas,
you will see something
called as name manager.
If you click
on the name manager,
you will see all
the list of data
or all the list of ranges
where the names have
the Rangers have been named.
For example, I
have named this range
which you can see now
I'm looking for the named range
that I have just done
which is students named
Ike the Click on that
and then change
or modify the named range
by clicking on referred
to I can change the name.
Also if I have to by clicking
on that name column
that I can see on the wizard
once I click on that name
and then give a name there.
I will be able
to change the name
of that particular range.
Then the next one
that I can see on the list is
refers to this part is the range
which I want to give the name
to so I have selected that range
and I can give it
a name to that range.
So this is how I
have can modify it
if I have to you
can also delete it.
If you don't want
that train name click
on delete click OK same thing.
We had given
one more name here,
which is the mat.
I can also select that delete
it and it is gone.
This is how you can modify
or delete or add
any named ranges to a cell
or a column moving on
to the next function
that we have on our list are
the lookup functions
under lookup function.
The first function
that we are going to see
is the vlookup vlookup is
the most well-known.
Excel function vlookup
is an inbuilt function
that is designed
to work with data
that is organized
into columns like this
that we have here
into the columns
for a specified value.
The function finds the value
in one column of the data
and Returns the corresponding
value from another column
three conditions to be filter
for we look up to work one.
There should be a common data
which can be looked
upon to derive
the corresponding data example,
if you see your I have
the student name mentioned your
and I need to know their marks
that they have scored
in the subject maths
in another range
that I have your I can see
that I have the student's name
and they are marks
in different subjects
that I have the studio.
Now, what I need to do
is I need to derive
the marks of the student
mentioned in cell H1.
So the first condition
that has needs to be fulfilled
to do a vlookup function is
that There should be
a common data
which can be looked upon to
derive the corresponding value.
So what is that?
We can look upon the name name
is the common data.
Yo Bobby 0 Bob is you
so that's the common
data set is there
what is the second condition?
The common data is identified
should be unique in itself.
So it Bob should not be
twice in the data
if it is twice in the table,
then it will only pick up
the data for the first bomb
and the second Bob will be
ignored by vlookup function.
So we have to ensure
that it is not duplicate.
It is always unique data
that you need to derive
should always be
on the right side
of the lookup value
in the database from
where you want to derive
the corresponding value.
So it should always be
on the right hand side.
That means the data
that you need to derive.
This is the data that you
need to derive right on that.
This part should always be
on the right hand side
of the data that
you're looking for.
So you looking for Bob
and your max max
is on the right.
So this is also
condition is fulfilled.
So how do we start to do
the vlookup function
to get our result?
I do an equal to V lookup open.
Racket I have to look for Bob.
So I select mom in which table
I have to select this table,
which column index number.
So this is the first
column index number
because I'm looking for the Bob
in this column and the math.
I have to get the answer
from the second column,
which is marks in math.
So 2 comma 0 close
the bracket enter I
get the result as 88 now,
even if I change the name here
from Bob to some Maria
I'll still get a the result as
44 because there is a Formula
already entered you.
This is how we are vlookup
function Works moving on
to the next one is
your H lookup function
similar to vlookup
function H. Look up is
a horizontal look
up so we look up is
a vertical lookup White Edge
will come is horizontal look
up the Syntax for hlookup
is similar to vlookup.
However at look up with looking
for Value horizontally
the third argument and H
look up is row index number.
So with another example
where I have a similar data
with the names of the students
shown in the first row,
they are marks are shown in
the second third and fourth row.
I have to fetch
the marks and English
for the student's name
mentioned column a nine again.
The similar way I have to do
like we're doing we look
up why would do we look up
because we have to look
for data from the column.
But this time you
have to look data
in the horizontal
manner like this.
So what I will do
is is equal to H.
Look up finds T comma in what
the database comma this time.
I am Steve what I have to look
for Reza marks in English.
So 1 2 3 and 4.
So 4 comma 0 now
when I change the name
of the student,
it should still give
me the correct marks.
This is how we are hlookup work
similar to the vlookup moving on
to the next one.
What do we have on our list
is the formula auditing
under formula auditing
the errors first run
another formula auditing is
it traded settings
or dependents tracing presidents
and dependents helped
user to identify.
The reference cells used
in the formula or function?
What is the difference
between proceedings
and dependents presidents are
cells are arranged.
That affect the active
cells value dependents
are safe in syringes
that is affected by active cell.
So how does that work?
So to see what are
the sensor referenced
in the active cell formula click
the trace precedents
Worton to see
which other cell
contains a reference
to the active cell click
on the trays dependence buttons.
Let's see how that works.
I'm trying to identify
which cell is this coming
from this data
or this result is coming
from which are this link to
so I click on Trace precedents
that means where
is it coming from?
So it is saying
that this whole table
and from this cell
if I have to say that dependence
on the so I have just seen
the Precedence now,
I want to First remove
the arrows from this
because I can see the remove
so I can just click
on remove arrows and all
the rows will be removed.
So now I could not see
the president's now and to see
if there is any dependent
on this cell like a 9 so I click
on Trace dependence
and it is telling me
that this cell is dependent
on Your cell A9.
So this is a difference
between that dependence
and the president's now moving
on to the next one is
the error checking.
What is an error
checking function.
This is Again part
of the formula auditing error
checking helps the user
to identify which cell
has the error occurred.
This can be very useful
when you have a big list of data
and you have entered formulas
in different parts of the sheet
to get the desired results.
So, for example,
let's move onto our sheet
where we had the errors
we had at the know
if error function I remove
the air from your I will remove
if error so that the error
stays in this list.
Now I know
that the editor is there
in this list you div div.
These are all the errors
that I can see on the list now.
It is very much clear
because I can see
this is a small data.
But if it was a big data
and I want to identify
where the error comes
for I can click
on error checking
and it will tell me
where the errors it.
So Aaron in cell C3,
that is only one error
that I can see now I can click
on next to get on
to the next error
which I can see cell C7
next I can see cell C9.
Now.
I can either edit
in the formula bar
to change the error
so that it doesn't give you the
error or show calculation type.
When you click
on the calculation.
It will tell you
where the error happens
by clicking on evaluate.
Once you click on evaluate
it will give you
where the error
has actually happened.
This is how Wonder check error
works next on our list
that we have is
the show formulas your
I can see only the result
of the formula only
when I go on to the cell,
I will be able to see
in the formula bar the formulas
that I have entered to see
the Amla in the whole sheet.
I can just click on show formula
and all the formulas
will be displayed
automatically on the list.
So you not see the result
but you will see the formula
that I have entered
on that sheet.
This is how your shape
flow formula works
next to column allows you
to move the text from one column
to another effectively
splitting text entries
into the two separate spaces
to separate the content
of One XL send in
two separate columns.
You can use that
extra column wizard.
Let's look at your sheet
where I have an example example
in my sheet has full name
of the employee is given
in one of the columns.
I want to split them
into two different cells.
So how do I split them?
So I want to split them in such
a way that plus seems raised
in a but to these first name
moves to the B column
to perform this action.
I have to use the text to column
first thing I need to do is
to identify the separator
in the data in our case.
We can see that the first
and the last name is separated
by a comma So our separator
is a comma however
in Excel terms this separator
is called as delimiter.
So how to perform the text
to column 2 separate this list
using the still limited.
I will select the whole data
or the whole column
where I need to separate
this list from and then I'll go
to data under data
and seat X2 column click on that
at X2 column wizard will open
up under the text
to columns wizard.
I'll see something
called is delimited
which is our separator.
I have to enter the separator
in the next window.
I will remove the tab,
which is a default value
and just pick the comma as
by separator and then
click on finish.
As soon as I do
that the first time
in the further name will move
into two different cells.
So you saw that one way to do
that is was to use a delimiter
because we had a delimiter
or a separator
in our data now, however,
if you do not have a separator
in your data or rather,
you know that you have a data
that can be split
with a fixed width now
where we have a fixed width
we can use another set
of Formation on the text
to columns wizard which is
the fixed with information
you so like for example,
we have this dates
mentioned earlier.
I want to separate the Year
from the date like the first
date month and the year,
so I want to separate
the month and date
and one column in one cell
and the year should move
to the next cell values fixed
within the next window.
I will see the ruler on the top.
I'll can move the ruler
using my left click
and selecting a ruler
on the page.
Once I do that now,
I only need the date or the ER
to be in the next column.
So I separate between or put
a ruler between the ear
and the previous information
and click on finish.
It will automatically move
the year to the next column.
This is how your text
to columns wizard Works.
Let's move on to our next
function on our list
that we have is
the data validation.
What is data validation
data validation is a feature
in Excel used to control
what a user can enter
into the cell and simply display
a message to the user telling
what is Out data
validation is a feature
that allows you to perform
number of actions like make list
of entries that restricts
the values in these cell
like I'll have
this list of names.
So when I am entering any other
name other than this list,
I will get an error create
a prompt message explain
the kind of data
allowed in the cell.
We will see how we can do that.
So we are creating a message
or a kind of a comment
which tells see user
that this kind of data
is required to create
a message that appears
when incorrect data is entered.
So as soon as you entered
an incorrect data,
it will give you
a popup message saying
that this is an indirect data,
please enter a correct one check
for incorrect entries
were using the auditing toolbar
so we can do that as well set
a range of numeric values
that can be entered
in the cell and determine
if an entry is valid
based on the calculations
in another cell.
So let's see how the data
validation Feature work.
So I have this
registration form here,
which has the first
heading I can see
the registration form number
then is the name of the student
who have registered for it,
then we can see the code.
That the imprudent wish
to enroll then you
have the gender and
if they are graduate yes or no.
Now I have a list of courses
that I have mentioned
in one of the cells here
or one of the column
here for courses
that the college offers one is
a post graduation diploma master
of business management
diploma in business
management Bachelor's
of Business Administration.
So student can only select
out of these four courses.
They cannot select
any other course other
than this for courses.
So what I'll do is instead
of telling them again,
and again, please don't select
or don't enter any other
and give allowing them
to enter a free text.
I would use a data
validation technique
where I go to data
and click on data validation
on the settings.
I can put list as my data
and under Source,
I will select the information
which I have in this list
as soon as I click
on OK you will see
that a drop down list
is created in this cell.
Now the student
who is entering
the information here.
We'll enter something
like this their name
and then the courses
that they wish And roll
they cannot put anything
like they cannot even
put a short form
know they will have
to select from the list
that they can see or
if they don't put it that way.
They will get an error
now gender can be
male female or anything
that they want to enter
if they are graduate.
Yes or no.
So this is a kind of information
that they can enter
into the form however,
now we have course
that they wish to enroll.
We just went to data validation
where we put the list
and then enter these swords
from one of the columns
where we had already
entered the source now,
we also want
the students to know
that what they need
to enter before we do
that there is another cell here.
So this was only done
for this cell this
which is cell C2 if I want
to copy this data validation
in the rest of the cell
so I can just copy
that and go to select them
the place where I want this
to be copied and pasted
it control C and control
V.
So it will paste the data
validation will be pasted
in all the sense.
Next is if you see
the registration form number
where I can see is I
want the students only
who have registration form.
But still hundred and one
to be able to fill this form.
This form is to be
filled by students
who have the registration number
until 101 if they have
registration number 102,
they should not be able
to enter the formula.
So, how do I do that?
I will first select all
the cells where I'm sure
that they are going to enter the
registration form number now,
I go again
to the data validation
under data validation this time.
I'll not select list.
When do I select list is
when I have the source
with me this time.
I don't have a source.
They can enter any number
from one to hundred and one
any number they cannot select
anything below one
and anything beyond 101.
So what I'll do is I'll give
them a number as between.
So first I'll select
whole number allow whole number
between why between
because I don't want them to put
anything zero or below zero.
So what I'll do is between
minimum should be one maximum
that they can enter
should be 1 0 1
So as soon as I enter
the minimum and the maximum data
and click on ok,
now if I enter any data
below 1 I will get an error
or if I put data Beyond 101.
I'll again get an error.
Now.
How do I ensure
that the students know
that what numbers
that they have to put
if they put to that is okay
if the 42101 that is also okay.
So I have to tell students
that please enter the data only
if you have a registration form
number till hundred and one so
what I can do in again
in the data validation window,
there is something called as
in the settings you
enter whole number then
between and the numbers
now there is something
called as input message in
the data validation window here.
You can input a message
for the user who's going
to use this form.
So here in our case
it is the students.
So I will enter
registration form
or registration number till 101.
So here I am saying the
registration number 12 on 01.
I can also put an input message.
This was the title
that I'm saying or I can just
put a registration number.
And then I would say
input message as please.
Enter numbers between one 201
know forms will be accepted
if your registration number
is not between the prescribed
or number is beyond 101.
So this is how you
can make the changes
if you realize
that I have made
a spelling mistakes.
I can go back put in
the input message remove click.
Ok.
Now this is for telling them
that as soon as I
go to the cell,
it will automatically give
me that message as soon as I go
to any of the cells here.
However, I'm not selected
the whole form.
So what I can do is I can go
back and enter the message here,
or I can just copy the data
from your till the end.
So what will happen is now?
As soon as I go
to any cell in this column,
it will automatically give
me that input message here.
Next is I can do is
I know that by now
that I can only enter 101
I have already in put
a message for them.
Another thing that I can do on
it a validation is I can decide
what should be the error message
that should occur.
So if I put anything
like 1 0 2 this is
the kind of error message
that you get Microsoft Excel
this value doesn't match
with the data validation
restriction defined
for this cell
does not very clear.
It is a default message
that comes for any error
in the data validation
so we can enter a message
for the user so that they'd know
that what error they have made
there is one option you can see
in the style you can keep
it as strong because you
are stopping the user to enter
the data in the title.
You can say error
and then error messages
or you can say warning
or not wanting you
can say something error.
And then in the error message
you can say please enter
data between 12101.
Okay.
This is how you can do it.
So what happens is now
when Go on to this
and enter 102 C
or the error messages change,
please enter the number
between 12101 you
can customize the error message.
Also on the data
validation screen.
This is how you can create
a data validation and try
and make it as
per your convenience.
Let's move on to the next
one or in this case.
We can do one more data
validation that I have here.
We need to ensure that no entry
that we are seeing your
for the registration number
if anybody is going to put the
registration number your they
should not be able to put
any duplicate registration
numbers or rather.
Let me go to a new sheet.
Let's say I have
a employee code to be entered.
They are named Department.
I want to tell the employees
that please enter
your employee code your name
and the department
and the sheet.
However, I need to ensure
that no employee enters
a duplicate value
in the employee grade column
because every employee should
have a unique employee code.
They may not do it purposely
but it may happen
that there by mistake.
They would enter
a wrong employee code.
That could be a duplication
to the employee code.
That is somebody
has already entered So to avoid
that I can use the data
validation feature again,
I'll select the column
where I wanted application
to be avoided click
on data validation under
data validation in the settings.
I will use a formula
what formula depending on what
is my requirement.
I can always use a formula
so to identify
any duplicate in a Cell.
I can use a countif function.
So when I use a counter
function, I identify
how many times a particular data
is coming in that range.
So what I do is I can select
this range comma my criteria
is meta so I want to understand
how many times the meta
comes into this picture
see there are two methods
so it will give me
a result as 2 so I will use
this counter method
to my advantage
how so I will collect
the counter these employee code
column go to data validation
on a data validation.
I will select custom
in the custom.
I'm going to put the formula
is equal to countif
and to bracket.
What do I need now?
I need to identify
if these range contains
a Duplicate it.
So I select a to a comma
a the same way.
So what I am going
to say is why now,
I know that countif counts
the number of times it occurs.
Now, I want to ensure
that it doesn't go beyond one.
The data should only occur once
so what I'll do
is is less than two.
I can see or I can say is less
than or equal to 1
so it can be one time
but it cannot be more than 1 so
what I can see it has
to be less than 2
so that means
what will happen is
or less than or equal
to 1 so 1 less than
or equal to 1 that means
if this answer to this
is 0 which is okay.
That means the data
is not occurred as
yet if this is equal to 1
that means one time
that employee code has been
entered that should be fine.
But if it is more one time,
then it should be me an error.
So I click on ok.
Now when I enter the data go
to data validation
under data validation
in the Customs.
I'm going to put a formula
is equal to countif we all know
how to use the count.
If so select all column comma
because we are starting
from the first cell
so I will have to give
the first cell has also
as the one D cell is less
than or equal to 1
once I do this
I click on.
Ok.
Now if I enter the information
twice it really me an error
so you can see
that I have already entered
12345 is an employee code
for one of the employees.
If I put again one two,
three, four five.
It will give me an error.
Now, you know
that you can customize
this error message.
Also.
This is how your custom
data validation works
and you can add more
than this formulas
which we just learned
depending on what is
your requirement moving
on to the next one
on our list is the what
if tools so what
if analysis this sits
under the data tab
in the Excel ribbon your
so there are three options
under what if analysis
one is goal seek.
Another one is data tables
and a third one
is scenario managers.
So how does these
three functions work?
So let's start first start
with the gold seek function goal
seek is an inbuilt Excel tool
that allows you to see
how one data item is
in a formula.
Impacts another you
might look at this as
a cause-and-effect scenarios.
It's useful to answer
what if type question
because you can adjust
one sell entry to see
the result a tool is you
often use in finance sales
and forecasting scenarios,
but there are other uses.
Let's look at a simple example,
the current model contains
the following information number
of units 500
which is being sold.
The retail price of the unit
is $25 Spelling's discount
that we are giving on this
or we have already given
to the customers 10%
and the revenue we have
to calculate so however,
I calculate Revenue by deducting
or removing a new sale
prices retail price,
which is 25 minus 10% of $25.
And then I have multiplied
it with 500 units.
So the total revenue it comes
to $11,250 Now using
goal seek function.
I need to identify
how many units have to be sold
to reach a 20,000 Revenue goal.
So our goal is what $20,000
I want to reach this goal by
how many so my sale price
remained the same my discount
Remains the Same.
I want to understand that.
How can I achieve a 20,000 goal
how many units I'll
have to sell to do
that SN I go to goal seek.
There are three values
that I will have to enter
one is a set cell
that means which cell I need to
set to 20,000 the revenue cell
because they're I have
the formula which value
that means what value I need
in this sell 20,000 by changing
what which sell any
to change or which data?
I need to change.
I need to change
the number of units.
So I'll select the number
of unit cell I click on OK
so it will calculate
automatically and gives
me the number of units
that needs to be celebrated
symmetry 889 units.
So this is how you can use
your goals it to your advantage.
Now.
Let's see another example
for the goal seek
that I have on my list.
So I have data
for water 1/4 2/4 3/4
for the gross profit or rather.
I would say the revenue that
has been generated out of it.
Out of Revenue.
This is the percentage of
profit rest is all cost at mr.
Tiwari other calls
that is there or this are
the percentage of profit
that we have learned
out of this gross sales.
The net profit
that we learned is
the percentage of this Crossing.
So every time out of
this one lakh dollars,
I have taken 10 percent as a net
profit out of one like 50,000.
I got 15 percent as
retrofit same 50,000.
I got 12 percent
at net profit in the quota
for we do not know.
What is the gross
sale the quarter
for has not yet occurred.
So we are not sure
what it is going to be
but similar to this three.
This one is also
going to be percentage
of the gross sale percentage
will be 15 percent
this time we have decided
our goal is to get 15% So
what I did is I'll have
to enter a formula in such a way
that I get 15% of this value.
How could I do
that is equal to this
multiplied by this this this
will be a my percentage value
or this will be the profit value
from the grocery as of now.
I have entered a net
profit or the total.
It for the year that I have
until now is 38,500.
How did I get that by adding?
All the four quarters
fourth quarter value?
I still don't have
what I have added all
the four quarters and got
the answer is 38 500
Now using goal seek.
I have to find out
what is the fourth
quarter revenue must be
to reach the goal
of 50,000 profit.
So my conditioner I
have to get this value
which I can see
that is a it for them
by Wonder to 50,000.
So my cell will be 50,000.
So I go to goal seek my set cell
will be e 13 which is correct.
What should I set it
to 50 thousand and by changing
what I need to change the gross
sales this cannot be changed.
I have to change
the gross sales
and depending on the gross sale.
The percentage of
profit has resided.
Here we go.
This is how your goal
seek functions work.
The next we will see now
is the data tables instead
of entering the formulas
and variables individually
to compare results.
We can set up a data table
with 102 variables data tables
are one of the excels
what-if analysis features.
Let's see.
How does it rate up tables work?
I have the list.
Of sale price is similar data
that we had seen earlier.
We have the sale price
at Seventeen fifty dollars.
Then I have the quantity
that I have sold
with this price,
but there is also a discount
on this price that is available,
which is 10% I've given
a discount and the cost price
that I have
on my product is 1500.
Now I have to understand that.
What is the question
is find the total revenue
in the discount
percent is change.
So as of now my total revenue
would be I have
to first identify.
What is my total revenue
with this data.
So how will I do
that is 1750 is my sale price.
I can multiply
this word pretty 5
but if you see there is
a 10 percent discount.
So what I do is I'll have to -
my sale price from the discount
which is c 2 into 10 percent.
We all remember
the bottom of theory.
So we will have to use
the brackets appropriately X.
My number of quantity
after ten percent discount.
This is the revenue that
I am going to on now.
I want to identify if I
change my discount percentage.
What will be my Revenue What
you did is going to affect
so instead of going and doing
it for each one of them.
I can just select
and getting the percentage
of discount for each one
of them getting an answer.
I would rather go to the what-
if analysis and do
a data table function.
When I click on data tables.
The first thing
that it asks me is
whether you want a row
input cell or column input cell.
What is the difference you're
the data that we are looking
at is the column it
is coming in the column
so we will have to select
the column input cell
what is changing our discount
this percentage is changing as
for the discount so I'll have
to select the discount.
However, before we get into this
I will have to select my data
where I need the result.
If I don't select the data,
let's see what happens.
It just doesn't understand
why I need to paste it.
So I'll have to first select
the data go to what-if analysis.
I Duke data tables
under data tables.
I'll have to select
column input cell
because I'm getting the answers
in the column and 10%
because my percentage
of discount is changing
don't select the percentage
from here always
select the percentage
from your base data.
Click.
OK see if you'll see
that this is a car mount
of prevent you
that I'm going
to earn similarly.
We have another example here
where I have the list of data
where I have the pale price.
I have quantity discount
cost price your I have
to find the total profit
of the discount amount
and a sale price changes
as given in the table.
So what I'm saying
is if my percentage
of discount changes from
10% to 15% 15 to 20 20 to 25
and more along with that
if I also change my price
like the product price,
how am I going to work
on that or how am I
going to get a result
as what is the profit
that I'm going to earn?
So what I did was
in the first place?
I have to gain put
a formula years to identify.
What is my profit
with the current state
first time identifying
my new sale price this
minus this into 10% this this
so this is the first
how we get the sale price
from this I'll have to -
1500 which is the cost price
would per product once I -
this if to put
appropriate brackets
because it is very important
that we know where
the brackets are to be entered
or other than putting fifteen -
fifteen hundred help
select the cell
close the bracket into 25.
This is a profit
that I on in case
I have this data
or the sale price
is cost price now.
I'm trying to understand
that if I change
my discount percentage
and I increase my price.
So what happens is when you go
to a department store,
you see a big discounts given
by those departmental stores
like 30% 40% 50% but do they
cover up their losses?
They cannot give you discount
of 40% Without changing
the price of the product
because if they do that,
they will not be able
to cover the losses
on the cover the expenses
that they have.
So what they do is they increase
the price of the product
and then give you 50%
discount 60 percent discount.
So how do they identify by using
this data tables method?
So I have selected I
have entered the profit
I have selected
where I need this data.
I click on data tables.
Now this time I have
Rose as well as input.
What are my row input cell row
input cell is my selling price
and my column input cell
as my discount the
or you get the result of
if I increase my price to 2000
and a discount of 10% This
is what my amount that I get.
This is how you can use
a data table to your advantage
in order to get the result
into the next one
which is a
scenario manager here.
I have an example or other.
Let's see actually
what does a scenario manager do
your scenario manager
creates different groups
of values or scenarios
and switches between them.
So that means you have
a group of I was like
if you have decided on something
like I need a 10 percent
of the interest,
but if you want to see
that how much profit
you will learn
if you get 20% of interest
that he was an empress
that kind of thing
when you want you
can still go in keep checking
how you can do that.
So let's go ahead
and check in our data
where there we have
so in my sheet,
you will see that I have
a information where I say
that assume you have a bookstore
and have hundred bucks
in the store.
You sell a certain percentage
of the highest price of $50
and the certain percentage
for the lower price of $20.
So you've decided maybe
some percentage you will sell
for a higher price
of $60 the remaining
you'll sell it for $20
now to start doing this
what I'll have to do is
I'll have to first list down.
The number of books
that I have which I
have entered your already
to the number of books.
Then that person is sold
for the higher price which is
and six seventy percent.
I can also put 60%
if I want to now the next thing
that I have to do is I'll have
to put a formula in my list.
We're in I'm saying
that number of books
that I have sold at a higher
price and number of books
that I have sold
at a lower price.
And what is the unit price
of which is soldered
so I have started putting it
in the list where I say
that the number of books
that is been sold
at a higher price is
sixty number of books
that are sold at a lower price
is 40 why because I'm saying
that 60% of the books.
I have so little higher price.
So rather than putting 60 her I
would calculate the amount 60.
So how do I do
that by entering a formula
a hundred is a total book
multiplied by the number of
percentage of the higher price,
which is 60%
So that comes to 60
the remaining will be
at the lower price.
So what I do is is equal
to the hundred
which is e total box
where it is in the cell A4 -
the highest price books,
which is this
so it comes to 40 now.
What is the higher price value
which is $50 and the $20
which I have mentioned.
You know, what is the total
profit that I am running
out of by doing this selling
this at the higher price
in the lower price,
so I'll have to put
that in the formula again.
So I start with 60 books
at $50 close the bracket
plus 40 books at $20
close the bracket again,
and this is my total profit
that I'm learning
by selling in this way.
Now.
What if I decide
that I understand
that now I'm selling the 60%
of the books at highest price.
But later on I decided
if I increase the percentage
of the books that I sell
it at a higher price
to 70% or 80% or 90%
How is it going to look
like or what is the profit
that I am going to learn?
What I'll do is I can use is
scenario manager to do that.
I go to sort of analysis
and click on scenario manager.
There is an option
to add the scenario
so I can click
on that add option.
Once I click on ADD.
It will ask me
for a scenario name.
So let's put the scenario name
as high as truce 60% we
are leaving it is 60% for now.
What are we going
to change that means?
What is that is going
to change our percentage
of the books sold
at a higher price,
which is in B 4.
Okay, so I click on ok now
once I click on.
Enter a value for each
of the changing cell that means
what will be the value
that is going to change
which is in the before
so before has a percentage
which is 60% 60% in the sense.
The value is 0.6 0.6
because so if you do 60 divided
by hundred it would be 0.6.
That's the reason I'm putting
a value is 0.6 right click on.
Okay.
And then this is
where I come to it 60%
but we also already
have the data for 60%
Let's put the data
for 70% So I put highest 70%
and then again
the same data and the
before I click on OK this time.
I'll have to put 0.7.
Click on OK again.
I add one more why
because I have more percentages
that I am looking at now 80% We
click on OK it is 0.8.
I have three scenarios.
Let me add one more which
will be highest 90% And 0.9.
Now that I have come here.
What I can do is now I can check
for each one of them.
There is a formula
in your as soon as I
select which one I want to check
if I've said 70%
what will be the answer
or what will be the
that I get for that I
can just select the 70% data
click on show you will see
that the data is now changed
and highest price
of the book sold
at high prices 70 and the total
profit is now 4,100 similarly.
If I do to 90 and click
on show it will show me
the 90% highest price value.
This is how your data will work
in case of scenario managers.
If I do a summary click OK
it will open a new worksheet
and in the worksheet,
you will see the current value
which is there on the steps L,
which you can see it is now 90%
And so it is showing
that but it will also show
you the other percentages
that we had decided.
It is 60 70 80 90.
You can actually
click on summary
and you can get this data
in a tabular format
where you can see the data
in the format that you want.
So you can now decide
if it is 80% or 90%
that you want to sell
their books at this is how we
are scenario managers work.
Let's move on and see
to the next one.
We have already
seen the gold seek
and the data table saw is
the duplicate removals work
on an Excel sheet.
If you have a duplicate value
in any of your cells,
for example, let's go
to one of the values
that I can see here.
There is a way to
remove the duplicates
and that is one way
to highlight the duplicate.
We have already seen
the conditional formatting
how to highlight duplicates.
We select the whole
cell highlight cell rules
and then click
on duplicate values.
All the duplicate values
will be selected here.
Now.
This was only to
highlight the replicative.
I want to remove the duplicate.
I can click on data under data
in the data tools group.
I can see remove duplicates.
I can click
on remove duplicates.
It will select the whole table
that we have on the list.
You can either select
the whole table or deselect
and select only the ones
which you want to remove
duplicates from if you
select the whole table,
it will check
if both the data is
the same for example
in the first scenario,
which is a 2
As meta ends A7 has meta as
per this column this
both are duplicate.
However, if you see
the data corresponding to it,
it is radhika.
Second one has the Hara.
So if you select both
of them together,
like say I go
to remove duplicates.
I select both the cells
column B and employee full name,
which was these two lists.
It will check
if both the data is same
that means metal Medica
and this is also met
America only then it will delete
those duplicates if they are.
Not same it will not delete.
So if you want to delete
duplicates depending
on the data in column A
which is employed full name,
then you will only
select employee full name
and click on OK,
it will remove the duplicates
and it will tell you
that all the duplicates
have been removed.
It will remove the duplicates
from the next corresponding
value as well.
This is how you remove
duplicates work in the Excel.
Now, there are some demos
that you can go
through to understand
or practice data
sanitization through Excel.
What is data sanitization you
are trying to send to me.
Good look better and even work
on it in such a way
that your data looks better
and you are able to get
the desired results.
So the first thing
that we are going to see
is the data sanitization is
a sorting any data
that you have in the list
can be sorted as
per your requirement.
So for example,
if you ever go
to a departmental store,
every department has
different kinds of products
like the grocery department
will have all the grocery
items vegitables pulses
and but out of that
also the pulses will be
in one section vegetables
will be in one section truth
will be another section.
Similarly Excel also
does the same thing
when you do the sort it
will divide itself depending on
where it should sit together.
So one group will come together.
For example,
if I click on sort for this data
and then I sort as
for the employee
full name it will look
at where each one of them sit.
So you will see
that a is will come together
bees will come together C's
and D's will come
together accordingly.
They Resorted to see
a better example
that we have on our list
is I have the list
of the employees names
with the our department
and they are region
that is been given in the list.
I have this list here
where I have employee code.
First name last name Department
if I want to sort the data as
per the region I can go
to data click on sort
and then I can select
from the list.
Which data I want to sort it
through so sort by
I say region
and it was at that means
your alphabetical order.
So I click on OK it
will sort as per the region.
So all these two will be
together all the north
will be together.
Then your vest then your South.
This is how your data will
be sorted as per the request.
Next is in the list
is the filter.
How does the filter work?
So you will see the data
you must have seen
this a lot of time
that in the data you
have this filter option.
When you click
on that filter option
on filter be applied
to the or data.
There is a kind of a drop
down from which you
can select the data
that you're looking for.
So for example,
if I'm looking To get only
the data for the particular
Department say I'm looking
to filter the data is
per department sales
so I can go to department
and then click on sales
or type sales in the box,
which is given to me this way.
You will be able
to filter the data.
Now if I want the data for sales
and finance I can again
type the data in the finance
and click on ADD
current selection
to the filter and click.
OK this way.
I will also get the data
for fails and parents together.
Other than that,
there are other things
that you can do with the filter
is one is called something
called as a custom filter
custom Filter Works.
When you click on any
of the data dropdowns
that you can see
under the drop-down.
We will see something called as
a text filter under text folder.
You have the custom
filter custom filter will work
when you have
the data something like
if you want to have data
similar to saying greater
than equal to less than
and those kind of information
which is more useful
in case of the numbers.
So let's go
to the salary column and try
the number filter greater
than say I want to know
the salary employees.
I want to Employee names as
for the salary information.
So for example,
I need to know all the employees
or filter only employees earning
salary more than 10,000.
So I select is greater
than and I want salary
who's less than 20,000
in the less than column.
I would enter 20,000.
So I have selected two different
cells one was the greater than
because I'm looking
for more than 10,000
and the next one is
is less than 20,000.
So I think it should be
more than 10,000
but it should be
less than 20,000.
I click on OK only
the salary column
that contains more
than 10,000 value
and less than 20,000
will be displayed in this list
or will be filtered accordingly.
This is how your data
can be filtered.
Let's move on to the next
one reptak straps the text
which extends beyond the cell
or the boundary.
For example, I had an example
on my sheet like this one.
So I have a scenario manager
Shi T. Oh, we're in
I have entered some data
in one of the cells.
If I enter some data
in this cell,
you can see that the data
in this cell is hidden.
Why because the cell
value is going.
Beyond the length of the cell
or the width of the cell.
So how do you ensure
that you can see this data
clearly you can go to home
and under home you'll see
something called as the
under the alignment group.
You will see something
called as wrap text.
When as soon as you do webtext,
it automatically wraps itself
in that same cells makes
the row a little bigger
so that you can see the whole
data in that same cell moving
to the next one is
your merge cell.
What is what cell looking
at the same example your
I have wrap the text
but you can see that the data
has become little bigger.
Now this question is applicable
to this whole data.
If I don't want to show
that in one cell I can make
it into two or three
different cells together.
So that becomes this row
will be little smaller
and you will be able to see
it together so I can merge
these three cells together.
Now what happens is
the data is still visible
with a smaller row height
and you have merge the cells.
So that means you have made
three cells into one.
This is how you can
Larger cell again.
The demo is been given.
What is the next one
as you're inserting
a comment in the cell?
So for example,
like we saw
in the data validation
how you can insert a comment
in the data validation.
You can normally insert
a comment in the cell.
So yeah, I want to say that
this is the unit price of a book
so I can just click
on insert comment.
As soon as I do
that I will get this pop-up box
where I can say
unit price offer book
and I press anywhere outside.
My soon as I go back
to that cell just Mouse
over that cell it will give me
the cell data if I want
to just edit that comment.
I can click on edit comment
and then make the changes as
per the requirement.
If I now want to delete
the comment I can right click
and delete the comment.
This is how your comments
work moving on
to the next one insert shapes
depending on your requirement.
You sometimes will need
to insert the shapes
on your Excel sheet.
There are different types
of shapes that you can use your
under the insert command
under the illustrations group.
You will see something called
as shapes under shapes.
You have this Arrow cross
square boxes round circle,
even the block arrows.
All this information can be used
the call-outs are there
which you can use
for your advantage
so I can just use
this kind of a shape
that you are requiring
and then make the changes as
per in the shapes.
You can add comments you
can add details into that.
So for example,
if I'm adding
a call-out I want to say
that this is my unit price.
I put a call out.
I can change the call-out
Direction like this enter
a comment in there.
How would I comment
or a comment by right-clicking
and edit text?
This is the price of one unit.
See this is how you
can see the call-out.
So instead of putting
that command you can also
use a call out a new you
can do a lot of things
with the shapes moving on
to the next one is
the insert text box
similar to the shapes.
You can also insert
a text box there.
You will not get any colors
and all it is just the text box.
That means you
can add a text box
and enter your data as
per the data that you need.
So I put a text box here I
enter the data it will All
the grid lines at the back
and you will be able to see
the text box on your sheet.
Now, we will move on
to see the advanced filter.
How does the advanced filter
work Advanced filter
is very important scenario.
If it is just like the similar
to our custom filter Advanced
filter also has its benefits
and it can work with the more
than two or three conditions
that you have on the cell
where I have my example.
I have the employee code
details the first name
last name Department region
is being given to me
with the salary information.
First thing that I have to do
with the advanced filter is
to create a criteria range.
That means I have to create
it correct area table
when I created a creditable I
have to see what is my criteria.
My first criteria is department.
So I copy the department
heading you're my second
criteria is the branch.
So I select the branch
information you filtering
with the advanced filter.
So I have to create the table
but always creating the table.
I have to copy the heading
of that particular criterias
once I have selected
The Heading now I have to enter
what is the criteria.
Just ensure that
the spelling is correct.
Otherwise, it will give
you an incorrect answer.
Now I go to data
under the filter sort
and filter option.
There is something called
as advanced under Advanced.
I can select two
options first option
as the filter the list in place.
That means when you filter
it the filter will happen
in this table itself.
Another option is to copy
to another location.
First thing I'll do is copy
to another location
once I do that then it
is asking me for the Lestrange
that means which data
needs to be filtered.
My data needs to be filtered
is a 1/2 H 101 which
is already selected.
Now.
Next one I have to enter
is my criteria range.
I told you
that I have to create a table
why I have to create a table
because I have to enter
this criteria range you
so to enter the right arrange
and select the criteria table
that I have created.
Now, I have to tell Excel
because I said that I want
to copy to another location.
I'll have to tell
Excel that aware.
I need this data
to be presented.
So I will select the cell
where I want this data
and click on OK all the data
which is or all the rows
which fulfills these
two criteria will be.
A studio similarly.
I have another example here
where I have the list.
What is my condition?
I have to see first
Advanced will display
the list of employees
and marketing department.
So what is my first thing that I
have to do the department?
So again, I'll copy the
department and put it in this.
What is the requirement
what is marketing department?
So I will say m KT G.
That is what do I have
to enter your next I
have is the South Region.
So my region is
my second criteria.
So I select region
and the criteria is what
south then my third criteria
that I can see is I have
to see their earnings are
salary between fifteen
to twenty thousand
and twenty five to ten thousand.
So first thing
that I have to enter is
a salary I'll copy that salary
because it is easier to copy it
if there is
any spelling mistake,
it will not give
you that answer now
when I copy and paste it
the salary the first information
that I have to put
is more than 15,000
which is very clear
but we have another request here
more than 15,000 but it
should be less than 20,000
just like we did it
in our Advanced filter
we have to put both
but I cannot put it
in the same cell.
So I have to put another cell
where I say salary again
when in that cell
I'll say less than 20,000.
Similarly.
I have another request
where it says
that this we more than 5,000
but less than 10,000 all
the other conditions
remaining the same.
So again, I'll say marketing.
I'll say south and this
is my other two condition.
Now, I can go back
to Advanced filter.
I select those Phil range copy
to another location select
the criteria range as
this range and then
where I want to paste it,
I'll paste it in another cell
which is say this set
and click on ok.now everything
that is fulfilling.
This condition will
be pasted you.
This is how we are
Advanced Filter Works in Excel.
We are going to start
with the statistical functions.
The first one
is average function.
What is the average
function do it's
an inbuilt function in Excel
that is categorized
with statistical function
average does exactly
what it says and works similar
to some it will return
the average value
of the given series
of numbers in Excel.
Let's say we need to identify
the average salary
paid to the employees
in the organization.
Whereas our salary data
in the H column.
So what I'll do is I'll enter
the average function followed
by the Open Bracket in the cell.
We need the result in our case.
It is J2.
So I start with an equal
to I enter average
open the bracket now,
I'll have to select the range
of sin for which
I need the average
and in our case it
is the salary column
which is H to H
my close the bracket
and press enter I get the
average salary as 15,000 538.
This is the average amount
paid to each of the employees
in the organization.
The next function
in our list is the median
function median function is
again a statistical function
which Returns the median
of a given number
what is median it
is a middle number
in the set of numbers.
That means if I have
10 12 and 15,
so there are three numbers
that I have I have
to Identify the middle number
from this so is equal
to I'll use the median function
here just to identify.
What is the middle number and
then select the range of cells
that are has numbers close
the bracket enter it will give
me the middle number
which is 12 in our case.
It is not the highest
it is not the lowest
but the middle
value the same goes
if I want to know
what is the middle value
in the salary column.
I'll use the median function
in cell L2 is equal
to median open the bracket
select our salary column
which has h2h close the bracket
and enter 15 thousand seven
fifty is the middle value
of the salary column.
Next we're going to see
is the mode function
the Excel mode function
Returns the most
frequently occurring numbers
in the numeric data
set this function only
works with numbers.
It will identify
amount or the number
that occurs maximum time
in the range of cells.
So for example,
here we have the basic salary
where I can see the highest
number is 49 thousand
and then there are few.
Employees ordering
the same salary,
but I want to identify
which salary is been earned
maximum time by the employees
so I can use
the mode function as equal
to mode open the bracket.
It is only mode select
mode open the bracket.
And then select h2h column
because I want to know
from the salary column close.
The bracket 17,500 is the amount
that occurs maximum time
in this cell ranges moving on
to the next one is
the standard deviation function.
What is the standard deviation?
It's a formula is
to identify the standard
deviation of the set
of numbers the standard
deviation function calculates
the standard deviation
in this sample set of data
standard deviation is a measure
of how much variance there is
in a set of numbers
compared to the average
of the numbers standard
deviation dot s function
is meant to estimate
standard division in a sample.
If the data represents
an entire population use
the standard deviation
dot P function.
So we will look at our standard
deviation sheet your
I have a sample data.
So sample of few students
have been picked up.
Whoo.
I've scored a particular number
or a particular marks
in the different subjects.
So we have taken a sample
and we have not picked
up all the student data.
So if we are using it
for a sample only we will use
the standard deviation
which is standard deviation dot
s it estimate standard deviation
based on the sample
ignores logical value
and text in the sample.
So it has to be only numbers.
Okay.
So it's standard deviation dot s
when you have a sample data
where you just want to identify
how much variance is there
as compared to the average
you will use three
standard deviation rods s
but if you have a full
population that means
if you have all the marks
of all the students
in the school or the college,
you will have to use
standard deviation dot P,
which is also
their your calculate
certain division weight
on the entire population
given as an argument.
But your we are using
the standard deviation
for a sample data.
So I select
that and then for which I
need a standard deviation.
I'll just go and select
that particular range.
I close the bracket.
I press enter it is giving
me four point eight nine,
which is a standard
deviation value for that scores.
So this is how your standard
deviation works next function
that we are going
to see in our list
when we are going through.
Our function list is
our large function.
What does a large function
do large function Returns
the nth largest value
from the sample, for example,
if I go back to my data
where I have the salary data
or the employee salary list,
I have to identify.
What is the highest salary
that I'm paying I can do it
by using the sort and function
but it can be that as of now.
I have sorted this data,
but later on this person
as Aishwarya Trivedi
has moved to a different
department director sheetal.
Desai has left
and Russia Trivedi has moved
into being the in the board
of directors list.
Not only Asha we
have also identified.
Fried butter to move
into the board of director.
So now we have
to board of directors
but both their salaries will be
a little different say I
will change the board
of director salary
to 51,000 forwarder
and or a schwa Trivedi.
It is about 60,000.
So this is the salaries
that we have decided
for the daughter of director,
which are two
board of directors.
Now in this case
if I have to see
the first Value First find out
what was the largest value
in these salary section
without changing the two values
that I had now so is equal
to large open the bracket.
I'll select the array
which is our salary
and K when I see
what does the K mean
k mean I have to tell
which highest number that means
if I want to know
the first highest value
or the first largest value
from the list I can put
the number as 1 but
if I don't want to know
the first largest,
I want to know the second
largest salary paid
to the employee I will Is
that were to close
the bracket enter now?
If you see the our largest value
is actually 49,000.
However, the answer
that you get is 24,500.
Why because we are looking
at the second largest value
in the range of cells.
If I am looking
at fifth largest value,
I will put it as 5
and press enter it will give
me the fifth largest value
in the set of cells
which is twenty two thousand
seven hundred and fifty
like in our example
where I mentioned that ashram
very is now moving
to their board of directors.
Let's not remove were
sheetal decipher.
Now, she still there
in the board of directors.
We also move are three very
to the board of directors list
and this time the board
of directors salary
that I'm moving to a 60,000.
So when I say the board
of directors salary 60,000
when I move this
salary now and say
that which is the first largest
salary the first graduate salary
will be 60,000.
The second largest salary will
be 49,000 instead of 24,500.
Five because now there is
even one more larger salary
the first larger salary
has become ashes salary
which is 60,000 earlier.
It was 49,000 was
the largest so this way
if you keep on changing
anybody else's salary
say I make it to 65,000 just
true get a gist of how it works.
If you will see that earlier.
It was forty nine
thousand words of largest.
Now the 60,000 has become
the largest salary
or the second largest salary
same goes with a small I
am trying to identify.
What is the smallest salary
that I am paying
or the lowest salary
that I'm paying
to the employee now again,
I have to give the K value
Nucky value again.
Like I said,
it will be the value
of which smallest number
that you want.
Is it the first smallest
that you want to know is
the second smallest
that you want to know
what the law is
that you want to know is
that the third
that you want to do.
So according to what
is your requirement?
You can put the number there
as see if I put 2
so it will give me
the second lowest salary
paid to the employees
and The nation which is 7,000
if I make the lowest salary
as even more lower.
So let's see.
What is our lowest salary.
It is 5950.
Let's make one of them to 5500
what will happen is now
if I go back and see
the second lowest salary value
it is now 5950
because the first
has now changed to 5,500.
So depending on
how your value is,
which is your lowest salary it
will automatically calculate
and give you that number.
So moving on
to the next one,
which is our Coral.
What is coral or it is
the correlation you can see we
can use the correlation function
in Excel to find
the correlation coefficient
between the two variables.
I'm sure we have all learned
coefficient correlations
in our College days
when we were doing
maths in the college
if you had opted
for Madison College,
you would have done
the correlation coefficient.
However, if Not done it,
which is not a big deal.
The Excel is already
doing it for you
correlation coefficient formula
are used to find out
how strong our relationship is
between the two data
like you're in our case
the relation between the H
and the relation
between the glucose level
of the person is it
that the age increases on the
gross level increases or is it
that the HD creases
in the glucose level
decreases or is it
that the age increases
and glucose level decreases
and vice versa?
So depending we are
trying to understand
what is the relationship formula
always returns a value
between minus 1 and 1.
What is the relation ship
of minus 1 and 1 why is it
always giving you in -
on a man?
And how is it
by forget it or split
if your relationship is one
or close to 1 rather?
I would say 021 it
would indicate a strong positive
relationship a correlation
coefficient of 1 means
that for every positive increase
in one variable.
There is a positive increase
of a fixed proportion
in the other for example,
true sizes go up
in perfect correlation
with the foot length.
So when your foot length
increases your shoe sizes
increases, so it's
a proper correlation.
That is when you're
one go relation.
It is exactly how much
your foot sizes increases
your shoe size increases
if the correlation
coefficient is minus 1,
it indicates a strong
negative relationship
or correlation coefficient
of minus one means
that for every positive
increase in one variable
that is a negative decrees
of the fixed proportion
in the other.
For example, the amount of gas
in the tank decreases
in perfect correlation
with the speed every time
the amount of gas
in the tank decreases
of your car.
So when you running the car
if you're increasing
the speed your gas keeps
on decreasing why because
you're increasing the speed
so The gas is getting used
or your fuel is getting used
and that's the reason
the fuel is decreasing
but your speed is increasing.
So there is an opposite
reaction fuel decreases,
but your speed increases
that's have your coefficient
correlation ship works
if in case of minus 1
if the result is 0 it
indicates no relationship
at all zero means
that for every increase.
There isn't a positive
or negative degrees the
to just aren't related anything
that is 0 no relation at all one
where there is
a positive relation
that means with the increase the
other part will also increase
while with minus 1.
It is the opposite side
with one increase
the other one will decrease
that's the three differences
that you will see.
Of course, you will not get
the result as 1 minus 1
or 0 it will be in between
so we will try and understand
if it is between 0 to 1
then it is a positive 1
if it is between minus 1 to 0,
then it is negative 1
and if it is 0
then it is no relation at all.
So You've an example in a sheet
where I have the age considered
as the x-value age of the person
and then I have the glucose
level of that person
as considered as why we
are just trying to identify.
What is the relationship
between the human age
and the glucose level
in their body.
So if you see the human age
the X level it says
43 the glucose level goes to
99212 glucose level shows a 65,
but does it make sense
by just looking at it?
Because we can't identify.
What is the relationship is
it minus 1 is 1 or is it 0
so to identify
that I can use a function
called correlation function
which is coral
in Excel in the cell
where I need this result.
So is equal to Coral
open the bracket
and then I will select
the different arrays for which
I need to identify the relation.
So first relation
that I have to identify
for is the age so I will see.
At age against glucose level.
So the age is sitting
in B2 to be 7.
So I'll select
that as the first array
the second array is C2 to C7.
So I select that secondary
which is e 2 2 Z 7 and close
the bracket, press enter.
It is giving me a value is 0.53
with I remove the more
decimal places that I have.
So our answer is zero point
5 3 which is a positive result.
That means there is
a close relationship
between the age
and the glucose level
when it is positive.
It means that the increase
in age does increase
the glucose levels in the body.
If this was a negative point,
then it would have
been the other way
around that increase in age.
It reduces the glucose
level in the body.
So this is how your
correlation function Works.
Let's see another example
that we have on our sheet,
which is a correlation function.
We have the price
of the stock a that changes
or the They change
in the price of stock a
and then we also have
S&P 500 weekly change
or the percentage
change of S&P 500.
The S&P 500 is
a stock market index
that tracks the stocks
of 500 large gap us companies.
It represents the stock market
performance by reporting
the risk and returns
of the biggest companies.
So what are we doing here
is we are identifying
if there is any relationship
between the SNP change
and the stock a change.
So if there is a relationship
they will be able to take
a proper decision in the future
when they are making
some Investments.
So to start with I am going
to find out the correlation.
So I will do is equal
to Coral open the bracket
now we have to set of data
which is true with arrays.
So I'll select the first
array as this one,
which is C5 true c24 comma
the second are a will be
D Phi 2 D 24 Close
the bracket and press enter
you will see
that it is very close to 1
so it is 0.89 or rather 0.9.
This means that every time
there is a 1% increase
in the S&P 500.
Then there is one percent
increase in the stock
a prices same similarly
if there is a decrease
in the S&P 500 the
accordingly the stock prices
of a will decrease by 1 percent.
That's how the
exact correlation is
while in the previous example,
there is 0.5 correlation.
That means when there
is a one-year increase
in the H point five percent of
increase in the glucose level,
so that's how the correlation
is identified using
the coral function in Ms. Excel.
Next.
We are going to see
the charts in Excel
what our charts
when you want to graphically
represent some It
is very difficult
to interpret Excel workbooks
that contains a lot
of data charts allow
you to illustrate
your workbook data graphically,
which makes it easy to visualize
comparisons entrance Excel has
several different types
of charts allowing
you to choose one
that best fits your data in
order to use charts effectively.
You will need to understand
how different charts are used.
So we will start
with the column charts.
So you'll see all
these different types of charts.
So to start we will go back
to our example treat
where I have the sheet
for column chart
that means there is a data
that is already available
where I have the data
for the region along
or the other the sales data
for each region,
which is split
into two years,
which is 2016 and 2017.
This data is in millions.
So you can see
that the Mumbai has done a sales
of 65 million in 2016
while it has done the sale
of 70 million in I was
in 17 same goes with London.
It has done 55 million
in 2016 while in 2017.
They have done 65 million USA.
You can see 45 million
in 52 million
while if you see
knock poor you will see
that the sales have reduced
from 2016 to 70 yard
has a smaller data.
So you are able to even identify
where it is going wrong.
And how is it related?
How is the comparison done?
So instead of doing
that if you want to send
it to your management,
you can just select this data
and create a chart
that will show exact comparison
on your screen and it
will represent it graphically.
So, how do I do that?
I'm going to go
to insert in 2016.
There is a new option
that has come up
which is called as
a recommended chart
that means whenever
you select any data
and then click on insert.
It will give you an option
as recommended chart
where the Excel will recommend
you the types of chart
that will look good
with these kind
of data or rather.
It will properly
In this data,
which you are looking at.
So I select the data
that I want the graph
for and then I go
to recommended charts
when I go to recommended charts,
you will see
that there is a first shot
that it is recommending is
the column chart.
This is your column chart
but as we are now seeing
the column chart would start
with or each type of chart.
We will start with
the column chart for now
and then we'll come back
to the recommended charts.
So you'll see the first
one the column chart is
your even in the icon
for in 2016 first 2D chart
that you can see is
the column chart I
click on that and
if the data is coming next one
another when is the column chart
really useful the column chart
is useful in such kind of datas
where there is
a comparison required
so column charts use
vertical bars to represent data,
they can work with many
different types of data,
but they are most frequently
used for comparing information
like in our case 2016 and 17.
So I have got
this data as column.
And it is comparing
between the 2016
and 17 now looking
at this chart.
It looks very nice.
However, you will see
there is something missing as
in when I'm looking
at the data for Mumbai.
If I don't see this table,
I will not be able to identify.
What does the real sale
done by Mumbai.
I'll have to Mouse
over it to get the number
when I Mouse over it.
It says series 2016 .1
by value 65 gives
you all the data.
However, if I am just
outside this data,
I will not be able to see
the information on in one go
and just shows which one is
the smaller value in Gujarat.
I can see that in 2017.
They have done less sail
as compared to 2016
but I need the exact number.
So for exact number
what I can do is
one way of doing
that is I can go
and look at it by doing a mouse
over it another way to do
that is to just see it I
can add the data labels.
How do I add a data label?
The different ways to add
a data label one way to do.
That is first select
where you want to add
the data label say I want
to add the data label for 2016
and 17 both the bars
so I cannot do it together.
So what I can do is I
can select one at a time
by using the left click
and then right click
on that same data
and then click
on add data labels.
Once I do that it will add
those numbers on that data.
Secondly, I can do it
for 2017 also by selecting it.
However, there is
another way of doing it.
If I now want to
delete the data even
so I'll just select and click
on delete from my keyboard
and it will work now I do
not have the data labels here.
I want to add it using this
plus sign Sony available in 2016
when I select the chart.
There is an option as
a plus sign I click on +
+ I click on data labels.
It will automatically add
data labels for both the bars.
I don't have to select
each of the bars.
This is how we data
labels are added now
if you can see
there is a axis Y,
which is given Which has
some numbers why
had those numbers are there
because earlier we
did not have data labels.
And that's the reason we
could see with this numbers
where our basis it's so
just above 60 somewhere
between 60 and 70.
So I assume that it is 65.
So that is how it
was showing however now
that I have the data labels,
I will not need this information
on right not need this excess
on the left hand side.
So, how do I remove that excess
from the left hand side
that you can see
that there are again one way
of doing that is going
to the design mode which is
after you collect the chart
go to design mode and then add
under add chart element.
I will see X's
which is the x axis
that we used to call another one
is the primary vertical axis
vertical axis is the vertical
which is on the left hand side
that we were just talking
about this one.
This is your primary vertical
and this is primary horizontal.
So I have want to remove
the primary vertical as of now,
both are Lighted
that means both are there.
I want to remove
the primary vertical so I select
that it just goes away.
That's one way
of doing it easier way
or the quickest way to do
that is just select
the excess be very careful
that only the excessive
selected you will see
that for small
ball kind of a thing
that comes on the four corners
of the Texas and I select
that I can press delete deall
from my keyboard
and it will go away third thing
that I can see on this chart
is the chart title.
I have to add a title
to this chart is already
there on the list.
I just have to add the title.
I select the data there and add
the title as sales comparison
or sales for two years and in
the bracket and put in millions
so that they don't get confused
at why is it showing is
only 50 40 or whatever?
So this is how I can show
my data one more thing
that I would recommend
doing is removing.
This grid lines.
The grid lines
would be more useful
when you don't have
the data labels
because then it gives You
where exactly your data sets
with Y axis you'll be able
to see the number but
when I don't have I don't need
this grid lines at all.
So I'll just select
the grid lines and then click
on delete on my keyboard.
That's one way another way to do
that is just go
to that plus sign
and press the grid line grid
lines are there by default now,
if you see the data,
it looks even more cleaner or it
looks even more presentable
because there is
no grid lines at the back.
It's all clean.
This is how you
can tweak your data as
per your requirement.
This is how your
column chart works,
but you can do the same thing
with the other charts as well.
There are other quick things
that you can do
with the chart is why
also changing the chart type
if you want to
or changing the data
that you have selected so
you have selected this data
in order to get this chart.
However, I want
to change the data
so I can click on that design
the design you will see
under data select data.
Once I click on the select data
it will give me an option here.
To change the data range
for the chart so I can click
on this data range to change
the chart very important part
that you will see your is
which row or column
so what happens is as
of now my y-axis only
contain the numbers
and the x-axis contain
the Mumbai London
and everything and the data
which is the colors
or the bars which are there
that is 2016 and 17,
which is the e or is showing
in two different bars.
Now when I click
on this switch row and column
what will happen
is all the region
which is there will become
your color of the bar
or different bars
while your 2016 and 17
will come on the x-axis.
Let's see how that happens
by just clicking on
switch rows and columns
once I do that and click
on OK you will see
that 2016 data is showing
under the x axis
and all the bars are showing now
with the Region's this
also can be done.
However, in this case it is
a little difficult to identify.
If I had the or
do the comparison
between the two years you can do
comparison between in 2016.
What is the sales
that you have done for
each region that kind
of a comparison can
be done to identify
if you see Gujarat has done
the lowest sale in 2016
while again in 2017 Hyderabad
has done the lowest sell now.
So this kind of comparison
can also be done but depending
on what kind of a comparison
you're looking at.
You can always click
on switch row and column you
can also use this button
to switch row and column.
Otherwise, you can click
on select data
and then use this information.
This is how your charts
will work in Excel
and how you can add new data
into our data labels
how you can format your data
using the different data sets
under design moving on
to the next chart.
Our next chart in the list is
the line chart line charts
are idle for showing trends
like you have in Our list
I have the number of days
and the temperature
that is changing as per
the number of days on day one.
So we are trying to say
maybe in December.
So in USA we have this days
if you are trying to see
that on day one of December
if I start with day one,
what was the temperature
and how is it going up
or is it going down so
that kind of information
if I'm trying to identify
I can use the line chart
because the line chart
will give me a kind
of a trend on different days.
So how do I create a line chart
when we have the data
where we have to show the trend
in a particular timeframe
that time we are going to show
it as in this line chart,
like for example in our sheet.
We have the pace from day
one to D6 identifying.
What is the temperature
like in these days?
So we have a trend
like in this first date is 43
the next day's 53 third date is
50 then 438 again goes up
by fifty Seven fifty.
Going up 59 and then 60 again.
It is 67 video showing a trend
of the data in that case.
I'm going to use the line chart.
So I'll use the line chart
by selecting the data.
It is giving me the line
and it's showing you
how it is going up.
So it starts with the day one.
It is lower than goes higher
and then keeps on going
higher higher higher owning
the day three dips a little down
but it keeps on
going after that.
This is how your
line chart works.
So whenever you have
this kind of a data
where you have to show
a trend we are going
to use the line chart.
What is the next one?
The next one is
a pie chart pie chart make
it easy to compare proportioned.
Each value is shown
as a slice of pie.
So it's easy to see
which value makes up
for the percentage of the whole.
So for example,
I have this book type
which are the book types
that is being sold
by the bookstore
which is the classic
mystery romance science
fiction and spiritual.
The revenue that is generated
out of these books
are also listed here
for classic It Is $18,500
by mistreat is 78,000 970
So on and so forth.
I'm trying to identify.
What is the total revenue
that I have generated
which book type is giving
me the maximum Revenue
while which book type
is going been giving me
the lowest Revenue to do that.
I can use a pie chart
because it's going to give
me the part of a pie
like your you will see
that the orange part is actually
the mystery part mystery
is being sold maximum
time part of the time
and the revenue is also
generated now similarly
like we did it
for the column chart.
You can also use the data tables
in the pie chart as well.
How do we use the data tables
and pie chart by again using
that to plus sign
on the right hand side
or just right clicking
and selecting the add
data labels as of now,
we'll use the plus sign
and click on the data labels.
Once I do that the data labels
will be added on the list.
Now in this case.
The numbers are been added.
However, I also want
to add the chart title.
The chart title is also already
mentioned as the revenues
so we can leave it is as it is.
If you need to change
that you can always double
click inside and start changing
the name of the chart as
Or the chart title
in the data labels
that we have added.
You will see that only
the numbers have been added.
I have to check
which color belongs
to which in order to see
where does the highest
number land saved only
five different lengths.
So that is fine.
So if there are
about 10 to 12 list
and you want to see
which one belongs to which it is
a little difficult to identify
that so what I can do is I
can add the chart labels on each
of them like a data labels.
I can also add the labels
on each one of them to identify.
How does that look so
how can I do
that by going to the data labels
in the add chart elements
and then click on more data
labels option as soon as I do
that a new window
or a new level has opened up
on the right hand side.
I can select the category name.
What will happen is as soon as I
select the category name.
It automatically gives me
the category name in the list.
Now the category name you
can see is separated by a comma
and it is coming
next to each other
if you want to change.
Separator you can use any
of these as a separator
I can use newline.
So what will happen is the
number will go in the new line.
So it looks a little better
again like the earlier one
where we remove the x
axis in this case.
Now, we have added the names
on the chart itself
so I can remove this legends
from the bottom.
So it looks a little cleaner
now I can even make
this a little bigger
because we have enough space
to make it bigger
as I have removed the legends
from the bottom.
So this is how we are pie chart
will look moving on
to the next one.
The next one is
a bar chart bar chart works just
like the column chart
but they use a horizontal bars
instead of the vertical bars.
Where is it useful?
But it is useful in the data
where you have the bigger names
of the headings.
Like in this case.
Yes, the inbound marketing
demonstration at remonstrate Roi
if I insert the normal
column chart like this now
you're the data is smaller.
So it is just giving you
in that same line.
However, if you
have a bigger data,
or even it will look better
if you have Bar charts
which is shown in
the horizontal level like this
because you can clearly
read the headings here
on the left hand side.
And your bar chart goes
on the right again
removing the gridlines
adding the data labels
will remain the same moving on
to the next one.
That is a Surface chart.
What does the surface chart
and how is it useful
surface charts are useful
when you want to find
the optimum combination
between two sets of data
like in our list,
we have the marketing finance
and effort and they
are which field is used
or which department
is used to get
these details like recruitment
how much you have to recruit
in these three departments,
which is the financial
Market in the effort
how much contribution is of
the environment in the financial
Marketing in the effort
how much I sets are there
in the financial Market
in another effort
and the building
how much is there
and the expenses
that has been formed part
of these three departments.
So if I want to know
these so I'm trying
to combine the recruitment part.
I'm not Trying to
identify the combination
of marketing finance and effort
how much recruitment
has been done in the combination
of all three in this kind
of a situation.
I'll be using the surface chart.
So, how do I use
the surface chart?
Where is this located again?
If I go to the Mandate chart
also I will be able to see
the surface chart
on the recommended charts
under recommended charts.
There is a gap
called all charts.
I can select
that go to surface your I'll get
that option of 3D surface.
I click on OK you will be able
to see the surface chart your
so it gives you the effort
marketing and finance.
So you will see
that the combination
of recruitment your
which is this part is showing
your total recruitment
for all the three departments
same goes with an environment
how much environment is affected
due to finance Marketing
in effort asset
that is been used by
these three departments
and so on and so forth.
So how we are adding
the values to this.
So this is how it shows you
can again remove the grid lines
if you want from this list,
so that looks a little better
and it also gives a number
as 0 to Which is this part
because the 200 only is affected
when I say 0 to 200
is the effort is 159.
That's the reason
to showing as 0 to 200.
The second one is
marketing 200 to 400
which is 345 in case of Finance.
So that's a bit little bigger.
It is showing your orange one.
The next one is
actually your gray
which is 400 to 600
which is good
really even bigger.
So this is how your data
is identified by using
the surface chart moving
on to the next one.
So we have learned
how the charts work in Excel
and how you can make
a little bit changes in order
to identify the best chart
that fits into it.
You can also use
the change chart type option
to change a particular chart.
So if you think
that a surface chart is
not fitting into this you
can select the chart
and click on chart
and then use one
of these recommended
charts from the list.
This is how we learn
the charts today.
Let's move on to the next one
which is the pivot tables.
What is the pivot table
and how Is it useful
in your day-to-day life
many people have the idea
that building a pivot
table is complicated
and time consuming
but it's simply not true
compared to the time it
would take you to build
an equivalent report
manually pivot tables
are incredibly fast.
If you have well
organized Source data,
you can create a pivot table
in less than a minute.
So, let's see
how you can do that.
First of all,
I have a sheet with all
the data information.
They're like in our sheet.
I have the list
of employee code their names.
So Department region branch
and the salary information.
I am going to create
a pivot table in order to show
the different departments salary
how much salary are we paying
two different departments?
However, before I do that,
I'll have to show you
how to create a pivot table
to create a pivot table.
I first we'll go
to the database for which I
need to create the pivot table.
So I go to the database
I click on insert
after I click on insert.
There is an option called pivot
table I click on pivot table.
New window will pop up
in the pivot table wizard.
I have to select the table
or the range for which the pivot
table is to be created.
So I select range
a 1/2 H 101 the next one
that I have to select
as either new sheet
or the existing tree new sheet.
That means when I click
new sheet and click on OK,
it will automatically
create a new sheet
in the existing workbook.
And if the pivot table will
be placed in that new sheet.
However, if I click
on existing worksheet,
I have to give a new location
that means the sheets
that I already have
on the workbook from that sheet,
which cell do I need
the private to be placed, hence.
I will click on the location
and select the location
that I need the pivot table
on I can select from any
of the existing sheet
that I have on the workbook.
However this time I'm going
to use a new worksheet
and click on OK.
This will create a new
Excel sheet for me just
before the Excel sheet that
I would working on currently.
So it is now creating
that Excel sheet.
Once the Excel sheet
is created on that sheet.
You will see the pivot table
wizard is already available
or a blank space
where it will show you
the pivot table number
and on the right hand side.
You can already see
the pivottable fields
that are shown under
the pivottable fields
all the headings,
which were there in the database
are have already occurred.
You like employee code.
First name last name Department
region Branch hide it
in the best basic salary.
So everything is already
showing your now
before we start
with the pivot table.
We need to understand this
for fields that are there
on the pivot table.
So every field
has its important.
So let's start
with the values area,
which was this one.
The values area is
the large rectangular area below
to the right of the column
and the row heading
the values area calculates
and counts data.
That means anything
that you enter your it
will give you the calculation
of that value.
For example,
if I pick salary
which I drag and put it
on the values column.
It will automatically
give me the All
of the salad you can see
it says some of basic salary
that means the total salary
that has been paid
you can change this
to any other field.
For example, if I click on this
and click on value field
setting I can then
give other calculations
right count average Max Min
standard deviation anything
that I need in this field,
I can select
and give that option.
I don't want to some
of the salary.
I just need to know the count
can click on counter.
It will give me
the count of that cells
which has the salaries.
So I click on some
for now the data field
that you drag and drop
your are typically those
that you want to measure Fields
such as sum of Revenue count
of units or average
of price row area,
which is this part placing
of data field in the row area
displays the unique value
from that field
down the rows on the left side
of the pivot table that means
because I said
that I want to do it
or the department
when I drag it to the Rose.
It will pick up all
the unique value.
So even if admin
is coming 10 times
in our database like admin
comes a lot of times here,
it will only show Once
in the pivot table same goes
with CCD to same goes
with the director.
What does that mean?
It will group all
the admin together.
Then it will some the salary
of Edmund the next cell
where I have done the values
and it will give me
the answer you're
so it will giving me
the salary for only
for admin then for
CCD then for Director
of Finance marketing Personnel
at Wendy and sales.
This is how your
database will look
if you put the data
into the rows area.
Next is the column area
a column area is composed
of headings that stretch
across the top of the column
which is this so
if you want to give
a heading other than this
if you want things like region
to be shown in the column
if I put column
what will happen is now
it will show me the East-West
not doubt you'll be split as
per the east west north
south in the column
and it is kind of heading
that is given
on the top like for East
and Even this is a total salary.
This is how it
is giving you the split.
Next one is the filter column
or the filter air filter is
an optional set of one or more
drop-down list at the top
of the pivot table here.
The filter area contains
the region filled
the pivot table is set to show
all the region like this
if I put it on the top here,
it is actually an optional area,
but it will only show me
the region on the top then it
will not show in the column.
The filter area allows you
to easily apply filters
to the pivot table.
For example,
if I leave my region
here and try to put a branch
in the filters column.
I can select which branch
I need the data for for example,
if I need only for umbrella,
I can click on ambala and click
OK it will only give me data
for ambala same way.
If I select the rest
of other data it will give
me the less for other
if I want more than one data.
I'll just click
on select multiple items
at is can select to 3
at time 2 and then it will be
filtered whisper the branch.
Give me the rest of the data.
This is how your pivot
table actually works.
Like I said,
you can use these value field
settings to get the other
calculations to be done
with this value field setting.
This is how your data
or the pivot table works.
Now.
This was a simple part
of the pivot table.
If you ever want to sort
on the pivot table.
Let me first remove the region
to make it look easier.
So do understanding
will be more easier.
I'm reviewing the branch
from the filters as well.
I'm just leaving the department
and the some
of the basic salary.
Now, what I'm trying to do
is I'm going to filter as
per the salary.
So I want to understand
which department I am giving
the maximum salary.
I select the salary column or I
just click Mark keep my cursor
in the salary column I go
to data and the same way
as we do the sort.
I'll just click on sort there.
Once I do
that it will give me a wizard
where I have to select
whether you have to sort as
per smallest to largest
or largest to smallest I
will do is largest to smallest
and I can see
that in the sales department.
I am giving the maximum salary
which is 3 lakh Forty Five.
175 so this is
how your body works
in the pivot table.
So there are other things
that you can also use
in the pivot table which are
really really important
that we are going to see here.
There is something
called as a slicer
and pivot table.
What how what is Slicer
in pivot table?
And how does that work?
When you go to analyze
data there is an option
called as insert slicer.
If you are anywhere
outside the pivot,
you're not keeping
your cursor in the pivot.
You will not be able to see
the analyze button to go
to the analyze button.
You have to click
on the pivot table
and then click on analyze
once you click on analyze
that would be an option called
as insert slicer now
insert Slicer in the sense.
It's a kind
of filtering of data.
So the same way similarly
like I did it for putting
the branch on the top
or the region on the top
where I could filter
the data I can use
the slicer to filter this data.
So if I click on slicer
and select which field
I want Filter it with like
if I want to filter it
with the hire date,
I can select
and hide it and click.
Ok.
Now it will give me a slicer
or the list of higher dates
which I can select from.
So if I select only
this hire date it
will automatically give me data
for only the employees
who have been hired
on this particular day.
So this is how we
Otis Eliezer Works.
However, now you can see
that in the slicer, okay,
I can only select one data
or one date at a time.
But if I want
to select more dates
than there is this something
called as multi select
which is on the top right
hand corner with this is
only available in 2016 version.
So when I click on multi-select,
it will give me options
to select multiple dates
at a time in the slicer.
So it is not necessary
that you have to
only do the dates.
You can also add
some more information other
than just the dates
click on analyze I go
to slicer again and now I
can select anything else
that I want now
department is already.
Be there I can select
maybe Branch this time.
So it will give
me another slicer
so you can see
the another slicer
which is their year.
So there are two slices
now so not necessary
that you can you have
to only work with one slicer.
You can work with two slices
or multiple slices.
I select Calcutta
and the are chilling
and it gives me data
for the replica in Darjeeling
for these two dates only
if I select one more date
it will give me data
for Calcutta Darjeeling
for these three dates.
So the slicers can be
multiple slicers on the list
and it will give you information
as per your requirement
how to create a chart
from the pivot table
and it also looks the same
as a normal chart.
So when I click on the database
where I want to create
a pivot table from and I then
click on the pivot chart
by going to analyze button
and then pivot chart
it will automatically
create the chart
that I want to the similarly
as we get the column chart
in this case so I click
ok Once I do that,
it will automatically create
your chart in the chart.
You can similar
to the normal chart.
You can give the name as a chart
title that we are looking
at add the data labels
by clicking on this plus sign
and click on a data
or just add data labels.
Once you do that the data labels
will be added all these fields
which you can see some
of the basic salary Department
if you don't want
to show it on the table
because it is
a pivot tables chart.
It is giving you
these information.
Also if you want to just hide
that you can right click
and click on hide all
feel buttons from chart.
It will automatically
highlight those fees.
Now.
If I add any additional
information like in this one,
you can only see
that the sum of the basic salary
is given there is nothing
of the row column.
So what I can do is
I can also add data
in the row once I do
that it will automatically
update the pivot chart as well
and give me the information as
per the column also now again,
like we did it in a previous one
where He had used
or change the chart type
by selecting the data and
switching the row two column.
You can use a similar
function here to switch
from row two column.
So here the data will show
in a different way altogether.
So as you update
your pivot table your pivot
chart also keeps updating
and all the same functionalities
that is available
in the normal chart
is also available
in your pivot chart.
The only difference is
that it is linked
to your pivot chart.
The other data is linked
to your database.
That is the only difference
but pivot chart
when you are looking at
that it is very very useful
because your pivot charts
on updating as per the new data
that is updated their also,
you are only giving
the information or you're only
requesting the information
that you want in the chart
rather than everything coming
into the chart
and then you selecting
which parts you want to display.
So that's how your pivot table
and the pivot charts work
if they are really really useful
in your Today reporting
that you do for your company's
and it will really
help you to do that.
Now.
We have also learned
how to do the pivot table
and how to use the slicers
in the pivot table.
There is another important part
of the slicer is
that you can link
to Pivot tables to the slicer.
For example, your I
have one pivot table
which is slitting the department
and giving you the total
salaries as for each department.
Now, I'm creating
a new pivot table
from the same database.
So I just copy and paste
that will also give
me another pivot table
but I do not want
Department this time.
I want to know the salary
is given to the employees
in different region.
So this is split as
per the region the slugs are
that I can add here is
by clicking any pivot table.
I can select and then
click on analyze
and then click on insert size
it the slicer will be as
for the region or the date
or the last name
which ever you want
to as of now.
I have deselecting
the branch and click on.
Okay, once I click
on Branch it will give me
the options of the branch
with which I can select
and analyze the data
but what I need now is I want
to link this pivot table,
which is a pivot table
to to pivot table 1 that means
when I select ambala,
you will see that only
the can pivot table has given
me the data for ambala,
which is a North Region
the first pivot table stays
as it is
because this slicer is
only link to pivot table
to I want to link this slicer
to both the pivot tables.
How can I do that?
I will have to click
on the slicer right click
on that then click
on report connections.
Once I click
on report connection.
It will give me the list
of pivot tables available
in your workbook.
Now, we know
that the in their feet one
we have both the pivot table
310 is not our treat.
So both the pivot tables
are in treat one.
So I'll select both
the pivot tables
from sheet 1 and click
on OK They do that now
when I select
Bangalore one branch,
it will automatically
give me data
for that one branch just like
that coaching it
with me giving me data
for that one branch same
goes with ambala
or a liquid and
if I select more than one it
will give me all the the one
selected and you me data
for that two or three branches
that I had selected.
So basically what I'm trying
to say is you can connect two
or three pivot tables 21 slicer
so that we'll be able to get
the filters with one slicer
for both the pivot table.
So this is
how your pivot table is
or the slicers are useful
in case of trying to get
the filters for your data.
The next topic that we
are going to see today is
the data analysis using Excel.
So how to do a data
analysis using Excel.
So there's something called
as the analysis toolpak
in Excel through which you
can do the data analysis.
The analysis toolpak is
an Excel add-in program
that provides data analysis
tools for financial statistical
and Engineering data.
Analysis to load
the data analysis.
Toolpak.
You have to execute
some steps in Excel.
So how do you do that?
Let's go to our Excel
on the file tab.
I click on the options.
Once I click on the options
under the add-ins select
the analysis toolpak hand.
Click on OK.
So when I go to add ins
under the analysis toolpak
that you see here under the name
where you see add-ins select
the analysis toolpak
and click on go button
or click on OK button.
Once I click on OK button
what happens it will open
the new data for you
or the new window
for you click
on analysis toolpak
and click on go.
Once I click on go.
It will give you that option
of analysis toolpak analysis.
Toolpak.
We be a euro currency tools
and the solver add-in I
will add the analysis toolpak
and click on OK
once I do that now I
will be able to see
that under data.
There is something
called as data analysis
add it to my Ribbon,
so you can now click
on the data analysis to create
your different analysis
that you want to do
from the list that you have.
Now.
Let's see how we can create
data analysis using
the Excel data that we have.
So what do we have here
on our Excel sheet?
I have a list of data.
Like I have the quantity salt
have the quantity sold
the price of the product
and the advertising.
Now this example teaches you
how to perform
a regression analysis in Excel
and how to interpret
the summary output.
So now in the below data we
can see here on the list.
The book question
is is there a relation
between the quantity
sold and the price
in the advertisement.
So what is it?
Is there a relation
between this and this
and the output is so
this is the price and this
is a kind of advertisement
that we are doing
or this is an amount
that we are spending
on advertisement is
the quantity sold increasing
or decreasing or any relation
that we are able to see so
can we predict the quantity sold
if we know the price and Rising
so if I know only
these two things can I predict
what will be my quantity sold
that is to do such kind
of analysis we can use
this data analysis toolpak.
So I go on to the data tab
in an analysis grip
and click on data analysis
now select the regression
from this list and click on ok.
Now there is a range
that supplies some basic
regression statistic.
So how do you do this?
Now?
There are two things that we
have to give the input y range
and the input X range.
This will always be
by default blank
and we have to give
the ranges to the Excel.
So how do you give the range
and how do you decide
what should be the range?
So the first thing
that we will give to excel
as the Y range select
the Y range a 1/2 a a this
is the predictor variable
also called as
the dependent variable.
So this is something
that we have to predict it is
dependent on these two things.
We do not know
the quantity sold.
We have trying to understand
what will be the quantity sold
if we have the price
and the advertising so
your dependent variable
will be your y range.
Choose your a 2238 second one
that you have is the X range.
These are the
explanatory variables.
These are also called as
the independent variables
these columns must be
adjacent to each other.
So whichever you're selecting
so I am selecting the 8
these two should be
adjacent to each other.
Now.
I had to ensure why
I'm taking these two things
because these are
my independent variable
that means this true.
I know and this is dependent
on the quantity sold
is dependent on this truth.
That's the reason
the quantity comes old is called
as a dependent variable
while the price
in the advertisement is called
as the independent variable now,
we have to check
on the labels and click
on the output range box
and select the
where do we need the output?
So where do I need
the output to be shown?
So I click on the output range
box and then I click on wherever
I need the output say
I need the output in 11.
So I select that then
check on residuals.
The residuals will
not be checked.
You will have to check
the residuals and click on OK
once I click on OK the answer.
Be displayed in the
summary output box here.
What does that say?
So if you see the results here,
you can see that there is
a range that supplied
some basic regression statistics
including the R square.
So what is it doing?
It's giving you the basic
regression statistics regression
statistics means we have
a trying to find the regression
and that's the reason
that is what is giving
you the standard error.
The number of observations
below that information
the regression tools supplies
analysis of variance,
which is Anova
including information
about the degrees of freedom.
This is called a degrees
of freedom sum of squares
mean Square value the F value
and the significance
of f now we need
the Anova information regression
tools supplies the information
about the regression line
calculated from the data notice
are calculated from the data.
So it says that
including the coefficient
and read error that same thing
that we're looking
at the standard error here,
then you have the T stabbed
and the probability value
for the intercept As well
as the same information
for the independent value.
So this was for the dependent.
This is for the independent
value that you can see then
which is the number
of at Excel also plots out some
of the regression data using
the simple scatter chart.
So you can also add
the scatter chart
at you want to
according to Dr. Detail
that you need from this.
So this is how your
regression analysis work.
So this is more
of the statistical analysis
or the financial analysis
that you are planning to do
when you are to the financial
department using Excel.
What is VB full form
of the VBA is Visual Basic
for applications.
It is a programming language of
Excel and other Office programs.
It is powerful and sophisticated
built-in programming language
that allows you to write
your own functions are commands
in an Excel spreadsheet
this custom functions
and commands help you
to ease your tasks
and that's why using VBA you
can create your own automated
functions macros is something
which is again related to VBA.
So macros are small programs
that records the keystrokes
as you perform a task.
Set this will be saved
in the Visual Basic module
which is a type of program file.
So when you run the macro,
it will repeat the keystrokes
that are saved in the module
and thus repeat your actions.
This is why they are
great to automate
repetitive tasks in Excel.
So let's start with to see
how does a macro work.
So I have the sheet with me
where I have some data
which is entered on one
of the sheets treat is called as
a sales data on that data.
I am going to do some formatting
on the sheet and we will record
that so that we
are able to work
on that to start with recording.
We need to have a developer tab
in your ribbon
for the first time.
If you're doing VBA,
you will not be able to see
the developer tab here.
So how we a developer tab.
You can go to the
ribbon under ribbon.
You can right click
on that and you will see
something called as the
customize the ribbon
once you click on customize
the ribbon you will see
on a new Wizard Excel options
wizard on the right hand side.
You will see under customize
the ribbon main tabs
under main tabs.
You will see all the tabs
that are there under
the ribbon in that.
You will see something
called as developer
and the developer
you will see the box
which is unchecked.
If you don't have the developer
that means it is unchecked.
You have to check that box
and click on OK
the developer tab will be added
to your Excel sheet.
Once you have done that now,
you can see that
under developer tab.
You have a number of options
that you can do
with the developer tab One
is using the recording a macro
to start with in this one.
What we are going to learn is
how to record a macro.
So that means that when you
record a macro it converts
or it will save these macros
or the keystrokes
that you do like
if I'm going to this cell
I'm going to do sell
I'm changing the format
of cell C4.
All these will be recorded by
the macro in the Visual Basic
for applications.
So that is the programming
so it will convert it
into a programming
and save it under the module for
Visual Basic for applications.
And then when you re run it
it will automatically
run the whole program
or hold macro and do the repair.
Of things that you've been
you have recorded.
So let's see.
What is the PBA window first.
So to go to the VBA window
the easier way to do that is go
to your developer tab.
Click on Visual Basic
and this is
how your window looks
on the left hand side.
The first one is 0v be
a project window.
That means that whatever
workbook or whatever worksheet.
You're working on your workbook.
It will be by forget it
esper first workbook.
So if you'll see
that your workbook name
is mentioned here.
We have booked one as the name
then under the book one name.
You will see all the worksheets
that are listed in there.
So the worksheets are
sheet 1 sheet 2 sheet
3 the sheet one name
that is it is sales data sheet
to the name itself is
sheet to that's
how that has been shown
here the next to it below
that you will see
the properties window.
The properties window is Again
part of the Visual Basic window,
which also helps
to change the formatting
of a little bit on the VBA.
These are Two windows
that you can see
in the Visual Basic to start
with we are going to go
back to our sheet.
Now if you are in this window,
how can you go back
to your sheet?
Which is your Excel tree?
Okay, of course,
you can toggle
between your sheets
by using the All Tab Key.
However, you don't know
how to do that or you can see
that you can click on
this Microsoft Excel view icon
that you can see here
on the left hand side.
You can see the Excel Window
that you can see you
can just click on that.
It will take you back
to the Excel sheet
that you were working
on on the Excel sheet
for which the VBA is open.
Once you go back to your sheet.
Now, we are going to start
with the recording again
before recording we
need to do one thing is
that if you record a macro
you also need to ensure
that you are assigning
it to a button
so we will see that also
how you can assign a macro
to a button to start
with the first we will do
the recording of a macro
when you're recording
the recording is done
in two different ways.
One is the absolute referencing
and another one is
the relative referencing now
when we were walking on Excel,
we all know how The relative
and absolute referencing
work absolute referencing is
the referencing wherein
if as per the keystroke
when I'm recording a macro
if I go to cell C4 every time
in the absolute referencing
recording it will every time
take you to cell C4
while if I do a relative
referencing it will see
where I was sitting
earlier and accordingly.
It will go to that particular
cell and pick that cell
from the list will explore this
while we are recording it.
So it will become more clearer.
Let's start with the recording
on our sheet sheet.
You can see this data
to start with this.
I will go to the developer click
on record macro this time.
I am going to do an absolute
referencing recording.
So I will just not click
on use relative reference.
I'll leave it
as it is by default.
It will record is
an absolute reference
under the macro name.
I'll start workin
absolute just ensure
that you do not give
a space in the middle
when you're giving a name.
So in this case.
We are putting only absolute
next one is your shortcut key
to create a custom keyboard
shortcut to use in conjunction
with the control key to run
the macro enter the desired key
into the box next to control +
in the record macro wizard
while doing this make
sure you do not overwrite
the existing keyboard shortcuts.
Like you cannot enter see your
because it is a bad idea
because controls plus C is
in command for copying
you cannot override
that if you do not remember
all the shortcuts
for Excel you better not put
anything next to control
and just click on OK
once I click on.
Ok.
Now the recording has started
now Wherever I Go,
whichever cell I go to that
will get recorded in the macros.
So I start by going
to sell d7i color
that as yellow then
I go to c9i make
that bold and underlined
then I go to sell
which is column
f 2 If I right click
and I delete now I
come back to cell C9.
Once I've done this I
will now stop recording.
Once I click on stop recording.
The recording has stopped
now I can go and see
what is recorded
under my BB a window.
How do I go to be a window
by can alt F eleven I click
on all development.
You will notice
that there is a new window
that is open up
which is the modules
I click on modules
as you have module 1 is added
to the list in the module.
You will see on the right
hand side a new macro is added
which is called sub absolute
because we had given the name
to the macro is absolute
and then the recordings shows
as range D7 dot select
and then it says
that it needs to be changed the
color to Yellow then rain scene.
I dot select then it
makes it bold then
it makes it underline
column F to F select
and delete and now last one
it says is range C 9.
Select.
That means whatever I have done
will exactly show it your now,
what I'll do is Let
me copy this data
and paste it on this list.
What I have done is
whatever I had formatted.
Now.
I am getting the original data
back to the sheet to see
how does our macro run.
But before I run
the macro again,
I will first add
a button to the macro
so that it will be easier
for me to run the macro
and not keep on going back
to the street and run it
what I do is I will go
to my developer tab
under developer tab.
I click on insert under insert.
You will see
ActiveX your control.
I can add a command
button from that,
which is the first one.
I select the command button
a command button is added there.
I can change the command button
named by going onto this
and making it
as run absolute your I
can add a space Also.
That should not be a problem.
So I click on
absolute run absolute.
I'm trying to edit
the Can change the name bike
right clicking on that
and going to properties?
To give a name to that button I
go to command button object
and click on edit.
I enter run absolute.
Once I've done
that I've given the name
to that particular tab
after I give the name
to that tab or to
that button now I can assign
a macro to this button.
How do I assign a macro?
I select that right click on it
and then click on view code.
Once I view that code
I can add the module
that I have here
to that button have copied
and pasted it turn to the button
under the private sub
and ends up between
that I'll have to write
that's one way of doing it.
But that is in case
if you want to add
the command button now,
there is also something called
as the another button which is
the button form control.
If you do the form control,
it automatically gives
you an option to add
the macro button.
So for example,
if I go to insert I
click on Forum button,
I select where I need
that button it will
automatically ask you
whether you want to Assign
a macro under assign
macro button window.
I will just select which macro
I need to assign it
to and then I click on OK
once I click on
OK then I can change
the name to new absolute.
I can change the name
to anything your I have selected
the control button in this one.
I had selected the normal button
normal button is better
because you are able to add
the assign the macro image
would lie to that.
Once I do this now.
I will be able to run
the macro by just clicking
on this see it went
to D7 it went to dc9
and in the end.
It also went to see nine last
it had to go to the C9 button.
So that's how the
absolute referencing work.
Now if I want to I'm
just deleting this
because we don't
need this anymore.
All right, let's see that later.
So what I can do is
I'm going to now create
another button with
a relative referencing data.
So I'll select this I
go back to the sheet.
I go to any other place other
than say, I want to
I'm not in the cell I go
to any other cell like you.
Sitting in the cell
and then run the macro it will
still go back to that cell C9.
It will color the same sense
and then go back
to that sense E9 now.
I want to record a macro
in the relative referencing.
So what I do is I record
a new macro click
on record macro same instead
of microbial just put
as relative this time.
I click on OK
and then I'll click
on use relative referencing
before I do that.
I'll have to copy
and paste this.
Have this data or now.
I'm going to record as
a relative reference.
So I click on record macro
my do relative my click on OK.
And then I'll have to select
use relative referencing
because this time I'm due to the
relative reference same thing.
I'll go back to cell D7.
I color it as yellow I go
to c9i make it bold.
I make it underline I go
to cell column F to F.
I click on delete
and then go back to cell C9
now go to developer tab
and stop recording this time.
It was a relative reference.
So I'll go back
to my VBA window.
I'll see where the
recording is saved
under relative so I can see
that active cell offset.
If you see that it is offset
and those data it
is given there now.
Let's start looking at how does
the relative reference in work?
So I'll copy this data and see
if how is it giving me the data
as I will first have
to create a new button
how I could do that by going
to developer insert.
I create a new button this time.
It is relative.
So I will give the name
as relative relative.
Okay, give the name as
a relative so we know
which one to run now
when I'm running the cell.
I'll go back to sell this one.
Click on series
run the exact thing
because I have selected
the same cell
as I had selected
when I was running the macro now
if I'm going anywhere else
am copying this data again.
Now I'm not running the Mac OS
but what was already
there this time.
I'm going to run the macro
in such a way staying somewhere
else like any other cell
see what happened.
So it did the same thing.
But because I had selected
a different solid win
to this cell it made this cell
as yellow and deleted
another cell completely
which was not selected.
So it would just
check with cell.
I'm sitting under
and accordingly.
It will be relatively look
for that particular cell
which needs to be colored
and which needs to be deleted.
That's how the relative
and the absolute referencing
work in two different ways.
That's about your macro
how you record it.
Have you give a button to
that macro or assign a macro
to that button?
And then how do you record
a macro with a relative
and absolute referencing now,
let's look at how to debug
a code sometimes
what happens is
what is first of all debugging
that we need to know now,
we've already learned
how the VBA functions work.
How do you record?
Micro, how does the VBA look
when you record a macro
however as with the case
with any code even
the macro code can have defects
and macro may not run
as you expected this requires
examination of the code
to find the defects
and correct them.
The term that is used
for this activity
in software development
is debugging VBA editor
allows you to pause
the execution of the code
and perform any required
debug task following some
of the debugging tasks
that you can do which one
is stepping through the code.
How do you step
through the code?
I click on this.
If you code and your we are
in the code another way
is also macros,
whichever macro that
you were looking at.
You looking at relative.
You can click on relative
and then click on step
into it will take you
to that particular.
The first line will
be turned as yellow.
So you will know
that this is the macro that
we are talking about now.
When I am on this macro,
I want to see which one
is running properly
or which lines are running
properly and which lines
are not running properly.
So what I can do is I can make
this window a little smaller
so that I can see
how the macro is running
and how is it showing
on the Excel tree?
So I'm keeping this
on the side to side making
this a little bigger making
the properties window
a little smaller.
Once I have that now I
can also see my Excel sheet.
I can also see
my we be a screen.
So I start by the first one.
The first one is already
been immediately if I click
on this button the whole macro
will run together,
but I want to see
each line by line.
How does the macro run so that
if there An error
anywhere I will know
where the error is.
So I start with this.
It's already yellow.
I click on f8 to start
with running the macro.
As soon as I will click on f8,
you will see the yellow
which was highlighted
first this part now it
is moved to this part.
That means that this line
is going to be executed now,
so I click on f8
this line is executed.
That means it is selected
that line now I go to f8 again,
you will see that it is copying
that data is selected
the data color the data
see it is colored now it
is going to the next one.
You will see
that now it is active cell dot
offset range a one dot select.
So it is selected
active cell dot offset.
That means it is going
to rows down and -
one column behind
once it has done
that now I'm doing f8.
So whenever I'm doing f8,
it is automatically keep
on executing that data.
So if till the time dry run fa
till the time it goes
till the end it will keep on
giving you that yellow part now,
this is a lot.
Thing that it is executed
by soon as I do f8.
It has talked to you you can see
that now it is already done.
This is how you can debug
and macro in the Excel now.
I have a data with me
where you can actually
debug the macro.
What I have done is I have
the list of the employees
who have given
the test would sell.
The Excel test is given
in the esper the 10 exercises.
I want to create a new sheet,
which is a summary sheet
where I can put the marks
that they have scored in each
of this Excel test for that.
I need to earn use summary sheet
where I can see for exercise 1.
I need something like
this exercise one then exercise
to the next size 3 and so on
and so forth and then I should
be able to enter the marks you
whatever marks that they have
on 10 5 depending on whatever
the marks is.
This is how it should show
so I have written a macro
which we will see
how does this run on this sheet.
So I have written
a macro on this.
I'm going to first close
the previous sheet.
That you don't have
to go back to this.
I come here on the macro.
You can see
that I have written something
your I'll start by pressing f8.
As soon as I press f8.
The first part of the Excel
will be executed
which is the sub sheet name.
As soon as I do another f8.
It will go to the next line
This is executed.
As soon as I pressed
sheet dot a DOT name is equal
to summary a new sheet is added
and the name of that street
is summary now I click on it.
It's going to take me
to arrange a 1 then
if you see it says range a 10
lakh forty eight thousand five
hundred Seventy-Six dot select.
So it's going to the last cell
which is 10 lakh forty thousand
five hundred Seventy-Six.
Now the next one
if I execute it will see
that if the first cell is empty
if it is empty,
then it will select it
and then it will go to the next
so it is running each
and every cell
so you will see
that it will keeps on running
and giving me the number
that I'm looking for.
It will run until all
the sheet names are copied
and pasted on this cells.
Your V and so your record
or your macro is now run.
You can debug a macro
if there was an error it will
automatically pop up and say
there is an error here.
Okay.
So for example anything
that you enter your and
if there was an error,
it will automatically popped
when tell you okay.
There is an error here.
For example, I have
a summary sheet.
I'll just put a name
which is the wrong name
so that I know
that this is a wrong one.
So let me delete and I
will start our all over again.
I go back here and start
with a fate you will see
that a new summary sheet
is executed now it is giving
me an error it says because
when I'm doing this,
let me start all over again.
I'm going back.
I'm pressing f8.
Okay this one it's already
created a new sheet your
when I'm executing
this part of the code.
It will give me an error
because there is
no sheet named sheet 1
now if I click on debug it
will take me to the place
where there is a error,
even when you're running it.
If for example
if you don't really realize
that you have made that mistake,
you're not running.
By if you just click
on this it will automatically
give you an error saying
runtime error once 004.
How do you debug click on debug?
It will take you to that line
where the error is now,
in this case,
we have already created that
summary sheet is already there.
And that's the reason it
is giving you you add that item.
So let me start all over again.
I click on stop now.
I run again your it
is giving me an error
where the error is isn't this
sheet this part of this code.
That's how you can debug
a code to understand
where the errors it's
so you will know that where
the actual errors happening.
You don't need to go back
and forth every time
to understand where
the real error is happening.
Now, you know how to work
that is called as a debugging
in your Excel VBA function
moving on now,
there is something
called as breakpoint.
That's one part
of the debugging thing.
How do you do a breakpoint is
by choosing that data
and then So for breakpoint
breakpoint in the sense,
it will stop
at a particular execution.
So for example,
if I want to give
a break point here say
I want to sell to stop executing
or the VBA to stop executing
the code after this point,
which is summary sheet
dot select or range A10
48576 dot select after this.
This part should
not be executed.
So how what can I do is
either I can click on that
and click outside.
It will give me a red mark,
or I can just go to that part
and press F9 go
to that particular line,
which you don't want
to be executed.
That means that Excel will stop
till he heard it will select
the data layer and then it
will not executed next one.
How do you check now?
I can just click on run
it will stop here.
You'll see that it
has dropped your why
because I have given
a break point
because I want to see
if the VBA code is running fine.
Tell you I want to know
where does the issue starts?
So I give a break point earlier
if I think that the issue
doesn't start your may be here
so I can give a break.
They'd also by just
pressing the F9
or by clicking on that part.
Again.
That's how the breakpoints I
have been added to the macro now
if I run the whole thing,
it will automatically run
without the break point
because I remove
that Rick find from that list.
This is how your vba's
work in Excel,
which is a very
very useful tool.
But of course you
are learning the macros,
which is the recording
of a macro and how you can use
to your advantage.
Thank you for the
great session pressure.
I hope you guys
found it informative.
So guys.
If you have any queries
related to the session,
please feel free to comment
in the comment section
below until then.
Thank you and have a great day.
I hope you have enjoyed
listening to this video.
Please be kind enough to like it
and you can comment any
of your doubts and queries
and we will reply them
at the earliest do look out
for more videos in our playlist
And subscribe to Edureka 
channel to learn more.
Happy learning.
