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.

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

Thank U so much

Thanks for upload This Video.

Hindi main bhi bna do video

Good deeds.

Thanks for uploading ,nice video.Is this complete video of advance excel 2016 from edureka??

It's very helpful to all closely working with Excel.. awesome explain..

It's very helpful to all closely working with excel. Thanks for the video.

Thank you for your effort. Could you prepare some videos about conditional formatting?

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

thanks for this video.

everything is contain in this vedio

Thanks for the video.

i need your excel sheet for practice

is it possible to share it??

Thank you so much for this video

Great video

Hi.. thanks for the video. It will be helpful for us if you upload the data also.

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

Thanks for the hard work

mam will u give me the file for practice plsssssssss

your videos are awesome

Fantastic Video.. Explanations with with examples

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.

We can do the excel 2010 versions.

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

can I get the template excel file in which you made this video

can u please provide the data in excel which shown in video

Thank you, it was very useful. Nice job. Thanks.

Felt like classroom training , very clean and clear training ever…Great work

Hi, great great work. Where can i download this workbook please?

Can I use LibreOffice Calc or does it HAVE to be Excel?

Can we get certificate too ?? Plz let me know about it .

Subscribed

thank you @edureka

This is very helpful video for all learner thanks

Does the pivot table update automatically, if I change the data?

quite useful but wish to have data documents to practice better.

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 .☺☺☺☺

❤️❤️❤️

Good Work….Can you share the sample file

Thanks all team members

Thank you for detailed explanation. I request you to do a video on how to make excel dashboard.

learnt a lot

how to get the same sample files

Do you have the example spreadsheets we can download and practice with?

Thank you for video, but where is exercise file?

very nice

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!

Wow thanks for this

Great effort

Thanks for Excel video in English. Liked and subscribed..👍

Can i get the sample file that u r using

At least provide with the data on which we can apply while learning it in the video

Thank you for this video

It really helped me

Can I get the worksheet u used for practical.

Hindi me ye vedeo chaiye

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

Thanks for this video, it's very informative.

Hi, Can you share with me the practice sheet?

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.

Nice explanation….

Please share sample file please

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

Thanks for sharing…kindlycan you provide link for the worksheet used in this tutorial

I want pdf about this total pls provide me , my mail I'd [email protected]

Please provide used excel workbook link it will be very beneficial in learning..

[email protected]

I watch your every video

How can i get this file to practise?)

A very helpful video. A very useful session thanks to the brilliant instructor Trisha and team.

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

Do you have the example spreadsheets we can download and practice with? I badly need it.

awesome video ma'am, really helpful

Thanks

Can I get the worksheet.

will u provide worksheet …

Hi can I have the worksheet? (should I write my Email here?)

hi,could u share me the workbook of this video.

Thanks for the video superb hard work.please can share that excel file .

Thanks

THAT WAS VERY HELPFUL

please share excel sheet.

Can you please share all excel sheet for practice

please share the workbook link

Thank you for this video!!! I really appreciate it, you makes we want to explore excel more.🥰🥰

How can i get practice. Please help for the same.where to share email id?

Very informative. If you could share an Excel file to practice then it would be more helpful.

Can you guys make another course explaining VBA in detail.

How can i get practice. Please help for the same.where to share email id?

This video is amazing, thank you team Edureka

Thank you

Can I have practice sheets which were displayed in this video

Too good to follow… the clarity of the voice and content.Keep the good work on.Pl send me the file if possible.

Maidam can you make videos in hindi language ?

Hi, please i need the template, kindly share your mail add

Hii..very good video..i want ur excel worksheet for practice. Im requesting to share …

Thank you guys

You got the new Subscriber 😊

Very informative.i love it ! If you could share an Excel file to practice then it would be more helpful.

Can you help me with the worksheet

Great work

Thanks bro

#Osm yrr

After watching this video I came to know that excel is an ocean the more you explore the more you can learn