Site Loader
Microsoft Excel Tutorial for Beginners | Excel Training | Excel Formulas and Functions | Edureka


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.

Reynold King

100 Replies to “Microsoft Excel Tutorial for Beginners | Excel Training | Excel Formulas and Functions | Edureka”

  1. Stay updated in trending technologies with Edureka YouTube Channel, Subscribe now: http://bit.ly/2JIfN5O Below is the timestamp:
    1:08 Excel Functions

    1:08 SUM Function

    3:58 SUMIF Function

    13:27 COUNT Function

    17:27 COUNTIF Function

    19:37 COUNTIFS Function

    22:22 AVERAGE Function

    27:17 ROUND Function

    31:52 CONCATENATE Function

    37:17 INDEX Function

    40:02 OFFSET Function

    44:57 MATCH Function

    51:07 MAX Function

    51:12 TRIM Function

    53:57 LEN Function

    55:07 SQRT Function

    56:12 IF Function

    1:07:42 AND Function

    1:12:32 TODAY Function

    1:13:17 Quick Access Bar

    1:18:37 Conditional Fomatting

    1:37:58 Color Scale

    1:38:33 Using Formulas for Conditional Formatting

    1:47:48 Logical Excel Functions

    1:57:58 IFERROR Function

    2:00:43 IFNA Function

    2:03:41 NESTED IF Function

    1:11:46 TEXT Functions

    2:11:56 LOWER Function

    2:13:16 UPPER Function

    2:16:46 RANDBETWEEN Function

    2:17:31 NAME RANGEs IN Excel

    2:20:11 Lookup Functions

    2:20:26 VLOOKUP Function

    2:22:51 HLOOKUP Function

    2:23:56 Formula Auditing

    2:24:01Trace Precedents & Dependents

    2:25:26 Error Checking

    2:26:46 Show Formulas

    2:27:16 Text To Column

    2:29:51 Data Validation

    2:40:16 What-if Tools

    2:49:42 Scenario Manager

    2:54:27 Data Table

    2:56:17 SORT

    2:56:27 Filter

    3:00:02 WRAP Text

    3:01:22 Merge Cell

    3:01:27 Insert Comments

    3:11:52 Large Function

    3:23:32 Charts In Excel

    3:41:12 Pie Charts In Excel

    3:41:32 Pivot Tables

    3:58:38 Slicers In Pivot Tables

    3:56:18 Data Analysis Using Excel

    4:02:09 VBA & Macros

    4:15:19 Assign A Macros

    4:15:24 Record A Macros

    4:15:29 Debugging

  2. It's a complete Excel training Video very helpful video it's video better than Excel classes.thank you very much.

  3. Can anyone tell me how can I highlight cells if that is 10 % greater than from total average.

  4. How come my Excel 2016 doesn't support IFS function??? I cant even see in the IF list when i stat writing formula.It supports other all IF functions.

  5. Thanks fro uploading amazing videio .. kindly can you provide link for these files for the paractice

  6. Salute Your all team members that you guys have brings such a Wonderful educational videos Nobody in YouTube have explain such a level of excell .Really Thank you .I will subscribe your cannel its great .☺☺☺☺

  7. Hi! I'm hoping you could help me out with what I would like to do as a collaboration tool with Excel. I want to create and share via Google Sheets a single excel spreadsheet for my office with specific sheets assigned to a specific staff member. Each staff can only access and edit the specific sheet assigned to him/her. Full access and editing privileges will be given only to me and my Department Heads. I do not want to simply hide sheets and protect them, but be able to customize access/editing privileges for each staff. Is this possible? Kindly advise. Thank you!

  8. Mam the video is very very very good with crystal clear concepts……… keep it up the good work… 🙂

  9. I havn't seen the video yet. Can you share the workbook of this video? Practicing with that along with this tutorial will be much more helpful.

  10. Thanks for uploading this video….really helpful, could you please also upload this spreadsheet for practice?

  11. 1:08 Excel Functions
    1:08 SUM Function
    3:58 SUMIF Function
    13:27 COUNT Function
    17:27 COUNTIF Function
    19:37 COUNTIFS Function
    22:22 AVERAGE Function 27:17 ROUND Function 31:52 CONCATENATE Function 37:17 INDEX Function 40:02 OFFSET Function 44:57 MATCH Function 51:07 MAX Function 51:12 TRIM Function 53:57 LEN Function 55:07 SQRT Function 56:12 IF Function 1:07:42 AND Function 1:12:32 TODAY Function 1:13:17 Quick Access Bar 1:18:37 Conditional Fomatting 1:37:58 Color Scale 1:38:33 Using Formulas for Conditional Formatting 1:47:48 Logical Excel Functions 1:57:58 IFERROR Function 2:00:43 IFNA Function 2:03:41 NESTED IF Function 1:11:46 TEXT Functions 2:11:56 LOWER Function 2:13:16 UPPER Function 2:16:46 RANDBETWEEN Function 2:17:31 NAME RANGEs IN Excel 2:20:11 Lookup Functions 2:20:26 VLOOKUP Function 2:22:51 HLOOKUP Function 2:23:56 Formula Auditing 2:24:01Trace Precedents & Dependents 2:25:26 Error Checking 2:26:46 Show Formulas 2:27:16 Text To Column 2:29:51 Data Validation 2:40:16 What-if Tools 2:49:42 Scenario Manager 2:54:27 Data Table 2:56:17 SORT 2:56:27 Filter 3:00:02 WRAP Text 3:01:22 Merge Cell 3:01:27 Insert Comments 3:11:52 Large Function 3:23:32 Charts In Excel 3:41:12 Pie Charts In Excel 3:41:32 Pivot Tables 3:58:38 Slicers In Pivot Tables 3:56:18 Data Analysis Using Excel 4:02:09 VBA & Macros 4:15:19 Assign A Macros 4:15:24 Record A Macros 4:15:29 Debugging

Leave a Reply

Your email address will not be published. Required fields are marked *