Hey everyone, welcome back to a whole new session
from Edureka. My name is Wajiha. And in this session, you'll be preparing
for the 50 most important Excel interview
questions and answers. So this session will be divided
into three different sections that is general
questions, questions relating to pivot tables and then questions
dealing with formulas and functions in Excel so before we begin just
make sure you subscribe to our Channel and hit
the Bell icon to stay updated with all the latest
Edureka videos. Also if you're looking for an online training
certification in any of the trending Technologies, check out the link given
in the description box below. So now moving on
towards the first question that is explained Ms. Excel
in brief Microsoft Excel as we all know is a spreadsheet
or a computer application that allows the storage of data in the form of table Excel
was developed by Microsoft and can be Various operating systems such as
Windows Mac OS iOS or Android Etc. Some of the important features of Ms. Excel are
the availability of graphing tools built in functions
such as some date count if excetera Excel allows data analysis through
tables charts filters Etc. It also provides the Visual Basic for
applications flexible workbooks and worksheets operations
and also allows data validation. So these were a few
features of Ms Excel. So now let's move on
to Towards the next question that is what do you mean
by cells in Ms. Excel? So as you can see
on the screen the area that falls as an intersection of the columns and the rows
is known as the cell. The total number of rows that are available
in Excel are 10 lakhs 48,000 576 and the total number
of columns are at 16,000 384. So the total number
of cells will be 10 lakh forty eight thousand five hundred
and Seventy-Six cross 16384. So I hope you guys are clear with this so Let's move on
towards the next question, which is explain. What is a spreadsheet
spreadsheets are basically collections of cell that help you manage the data
a single workbook may have more than one worksheet. So as you can see over here
at the bottom of the workbook, you can see all the sheets that are available
in this workbook. So by default,
they are named as sheet, 1 sheet 2 Etc in case you
want to change the name of any of these sheets
just double-click on them and give any name
of your choice. I'll just say example. So now let's move on
towards the next question what exactly is cell address? So the address of a cell
refers to the address that is obtained by combination of the row number
and the column letter. So each cell of an MS Excel sheet
will have a unique address. So I hope you guys
are clear with this. So now moving on
towards the next question of the session, which is can you add new cells
to an Excel sheet. The answer is yes,
you can insert new cells into a sheet to add a new cell
what you have to do. Simply select the cell where you want to insert
a new cell and then right click on it go to the insert option
and then select the place where you want
to insert a new cell. So in case you want to shift all
these cells towards the right and then insert the cell before this you have
to select the first option in case you want to insert
a cell above some lines. All you have to do
is choose the second option. So right now I'm not going
to insert any cells are just cancel this. So now moving on
towards the next question, which is can you format
ms-excel cells and if yes then how so the answer is yes
Ms. Excel cells can be formatted in order to format the cells
you can use the commands present in the font group
of the Home tab. So what I'm going to do
is just open this up here. So as you can see the number group allows
you to format cells to be of any type such as currency accounting date
percentage Etc alignment allows text control alignment and setting of the
direction of the text that's present in the cell. Font enables various
fonts Styles sizes colors Etc border allows cell borders
to be changed removed colored and so on you can
also fill the cells with any desired colors. And finally you can also
protect yourself from locking and hiding them. So I hope you guys
are clear with this. So now let's move on
towards the next question, which is can you add comments
to a cell yes comments can be added to add
comments to yourself. All you have to do is
right click on that. Cell and choose
the new comment option as you can see over here
a dialog box will open wherein you can type
any comment of your choice. So I'll just write
down comment over here. Okay, so as you can see, I've added the comment
to the cell. Now just a notify that there is a comment
present in this cell. There's a purple symbol
present in the top right corner of the cell. So in case you do not want
this comment anymore just click on the three dots that are present
in the dialog box and then delete the tread. So one more important thing
that you guys have to note is that these comments will be
visible to all those people who have access
to this Excel sheet. Not just that they can also
answer to your comments. So now moving on
towards the next question, which is can you add new rows
and columns to an Excel. Cheat yes, you can add new rows
and columns to an Excel sheet while going to do over here is
Select this column right click over it and then select
the insert option. So as you can see the cells
have just shifted towards the right now
for a more clear picture. Let me just type
in something over here. I'll say example and then I'll select
this column right click on it and say insert. So as you can see a new column
has been inserted before this column. Similarly. You can also insert a row. So I'll just write
an example of here. Select this row. Go to the insert option. So as you can see a new row
has been inserted. Okay, so I hope you
guys are clear with this. So now let's move on to
what's next question, which is what is ribbon. And where does it appear
the ribbon is basically a key interface with Excel and it appears at the top
of the Excel Window. So as you all can see
on the screen, this is nothing
but the ribbon tab it consists of file home insert page
layout formulas Etc. The ribbon tab, basically allows users
to access many of the important commands directly. So in case you want to access
any of these commands, all you have to do is
just click on them. Not just this Excel also
allows you to customize the ribbon now in order
to customize the ribbon tab. All you have to do is
right-click anywhere on this and then choose the customize
the ribbon option. So as you can see on the screen, you can choose any option
of your choice to be added to the ribbon tab. So, let me just choose
another option over here. I'll say I want to include
the developer tab and I'll click on OK. So as you can see over here
a new tab has been added to this ribbon similarly. You can select
or unselect any option of your choice to be present
in the ribbon tab. So now moving on
how do you freeze panes in Excel Ms. Excel allows
you to freeze panes that will help you
to see headings of rows and the columns even if you scroll a long way on the sheet now in order
to freeze the cells. All you have to do is select
the rows and the columns that you intend to freeze
then go to the view Tab and select the fries. Inception now here you'll
be able to see three options that is freeze panes
freeze top row and freeze First Column you can give
any option of your choice. So what I'm going to do is
just delete this and over here. I'll just give some new titles. It's a name and ID. So now what I'm going
to do is freeze this so I'll go to the view Tab
and select freeze panes option. I'll freeze the top row. So now even when I scroll down
this top row has been frozen and I'll be able to see it. Even if I scroll a long way
down the sheet. So I hope you guys
are clear with this. Okay. So now moving on
towards the next question. How do you add a note to a cell? So in order to add a note? All you have to do is select
the cell right click on the same and then select
the new note option. Now you can type in any note
of your choice of here. I'll just type a note
and then I'll hit enter. So as you can see over here
a new note has been added to my cell and it
is indicated by a red triangle that is present in the top
right corner of the cell. Also one more thing to note. Is that the name that is shown over here is
the default users name. Okay. So now in case you want
to delete this node, all you have to do is
right-click on the same and choose the delete note option. So now moving on
towards the next question, which is can you protect
workbooks in an Excel sheet. Yes workbooks can be protected
Excel provides three options for this passwords
can be set to open workbooks. You can protect the sheets
from being added deleted hidden or unhidden and protecting window sizes
or positions from being changed. So up next is how do you apply a single format
to all the sheets present in the workbook Excel allows
you to apply the same format to all the sheets of a workbook. So to do that. All you have to do is
right click on any of the sheet that is present in the workbook
and then click on the select. Cheats option now
after doing this if I apply some format to any of the sheet present
in this workbook that format is going to be applied
to all the sheets that are present
in this workbook. So as you can see, I just have one
workbook over here. Let me just add
on a new workbook. And then I'll select all sheets. And what I'm going to do over
here is go to the Home tab font. I'll just fill these cells
with some color. So now let me move on towards the next sheet
and see what happens. So as you can see over here, I made some changes
to this sheet and it's also been reflecting
in the next sheet. So I hope you guys
are clear with this. So now let's move on
towards the next question, which is what do you understand by relative cell addresses
whenever you copy formulas in Excel the addresses
of the reference cells get modified automatically
in order to match the position where the formula
has been copied now, this is done by a system
called as the relative. Addresses, so let me get back to excel in order
to show this to you guys. So what I'm going to do over
here is Type in some numbers. I'll say four and five here. I'll say six. and seven eight and nine So
what I'm going to do over here is Type
in the formula in this cell and then I'm going
to copy the same formula to these two cells. So what I'm going to do is type
in an equal to sign and then I'll just give the cell
addresses as a 3 plus a 4. And then I'll hit enter. So as you can see, I've got the result for a 3
and a 4 over here now in case I want to copy
the formula over here as well. What I'm going to do is
simply select the cell and drag it down till the cell that I want to copy
the formula to so as you can see over here. I have copied the formula
and Excel by default has changed the cell reference
according to the position. So what if you do not want
to modify the cell addresses when you have copied
the formula so in yes, you want to copy
the formula without modifying the cell addresses
you'll have to make use of absolute cell addresses when you use absolute
cell references the row and the column addresses
do not get modified and they remain the same. So basically when you have
to use absolute referencing, you'll have to make use
of the dollar symbol before the column
and the row numbers. So what I'm going to do over
here is just change this formula or let me just take a new cell and then I'll add
in all these cells. So what I have to do is
insert a dollar symbol before each of these. And then I'll hit enter. So now what I'm going to do
over here is copy this formula to the other cells
and see what happens. So as you can see over here, the same result has
been copied to this cell which means the formula
has not been modified in case I would have used
relative cell addresses. Then the formula
would have been modified according to its position. So now let's move on
towards the next question, which is what will you do if you want to change
either the column letter or the row number but not both. To do this you must make use of the mixed cell referencing
here either the row number or the column letter will change
but not both of them. So to show this example
to you guys. What I'm going to do is
get back to my Excel sheet and over here. Okay. Let me come towards this side. Let me select a new cell. Okay. What I'm going to do over here
is Type in an equal to sign and then I'll type in a dollar
three then after that. I'll type in a plus sign
and then be Dollar Tree. So now what I'm going to do
is copy this formula and see what happens. So as you can see over here when I copy the formula
the column Remains the Same and the value of the Roll
number has not changed because I've given
absolute referencing of here. So I hope you guys
are clear with this. Okay. So now let's move on
towards next question, which is can you protect
the cells of a sheet from being copied. So the answer is yes, you can do it by protecting
the required cells or the complete sheet. Not to do this. All you have to do
is select the cells that you want to protect go
to this font group of here and then select protection from
here check the hidden option and then click on OK. So as you can see
there are no changes that are made
to my sheet the formulas that are present in the cell are
still visible now in case I want to apply the changes that I just made all
I have to do is go to the review Tab and then
select the protect sheet option. Excel will ask me
to enter some password. So I'll just give
some random password over here. And then click on okay. So as you can see previously when I used to click
on these cells, it would show me the formula
for both of these now when I click on either of these
cells the formula is hidden when I click on any
of the other cells that are not protected
the formula is still visible. Okay. So I hope you guys are clear
with this now in case you want to unprotect the sheet. All you have to do is Select
this unprotect sheet option and then enter the password
that you had specified. So now moving on
towards the next question, which is how do you
create named ranges? In order to add named ranges. All you have to do
is select the area which you intend to give a name. And then from the ribbon tab, click on formulas from here go
to the Define name command and give any name
of your choice. So I'll just give vegetables
as the name of this table or I'll just say veggies. And then I'll click on okay. So as you can see
or hear the name given to this range is veggies similarly. If you want to create a name
for this range as well. All you have to do is Select that go to the formulas
Tab and select on Define name then give any name
of your choice and over here. I'll just say fruits. And then I'll click on OK. So as you can see I
have created two new ranges that is vegetables and fruits. So now moving on
towards next question, which is what do you mean by macros Excel allows
you to automate the tasks that you do regularly
by recording them into macros. So a macro is basically
an action or a set of them that you can perform
a number of times. So for example, if you have a record
of sales of each item and at the end of the day, you have to calculate
The sales profits loss Etc. So if you create a macro
it will automatically calculate all these values and then you can use the same
for future purposes. So now moving on
towards the next question, which is how do you create
drop-down lists in Excel? So now in case you want
to create a drop-down list, all you have to do
select on the data tab that is present in the ribbon. And then from this data
tools group over here select the data validation command. Okay from the given options
select list provide the source. So I'll just provide
the vegetable names as a source and then I'll click on OK. So as you can see over here, I have a drop down list
which contains the name of all the three vegetables
that are specified over here. So I hope you guys
are clear with this. So now let's move on
towards the next section which is pie with tables
and pivot charts. So the first question
of this session is explained by wood tables along with their features pie with tables are
statistical tables that condense the data of those tables that I
have extensive information. The summary can be based on
any field such as sales average. Somes Etc that the pivot
table represents in a simple and an intelligent manner some
of the important features of pivot tables. Are they allow the display
of exact data that you have to analyze
the provide various angles in order to view the data. They allow you to focus
on important details comparison of data is very handy
and pivot tables can detect different patterns
relationships data Trends Etc. They can also create
instant data accurate reports and serve as the base
for Pivot charts. So now let's move on
towards the next question, which is how do you create
pivot tables in order to create a pivot table? You will first need to prepare
the data in the tabular format. Now while preparing this data you will have to keep
a few points in mind. The first thing is you have
to arrange the data in two rows and columns. The first row should
contain unique heading for each of the columns and the columns
should have only one type of data present in them. The rose must have a data
for single recording. And there should be
no blank rows columns should not be completely empty and the data for creating
pivot table should be separated from the other data that is present on that sheet. So what I'm going to do
over here is copy of table that I've already created. So as you can see over here, the first row has
a unique heading and each column has
the same type of data the following rows contain
the data for a single recording. So I hope you guys
are clear with this. So now let's move on and see
how you actually create private. Abel's so what I'm going to do
over here is select the stable. And then I'll click
on the insert tab from here. I'll choose the
pivot table option. I'll select the existing
worksheet option. I'll give some random cell
as the target place and then I'll click on OK. So as you can see over here and empty pie with table
has been created. Also, the pivottable
fields pane has opened that contains all the fields that are present
in my pie with table now to create a pivot table. All you have to do
is drag and drop any of these fields present
in this four areas over here that is Filters columns rows
and the values. So what I'm going
to do over here is drag and drop some Fields
into these four areas. I'll drop names into the rose. I'll drop the items
into columns amount to values. And the filters will
be based on the city. So as you can see over here
a pivot table has been created which contains all
the names as rows and all the item names
as the columns also, you can see all the amounts that are collected
for each item bit by each of these vendors and also the grand total
of each of them. So now moving on
towards the next question, which is what our pivot charts Ms. Excel charts are
data visualization tools that help you visualize the data
in various ways these Charts can be of any type such as
bar pie area line donut Etc. So in case I want
to create a pie chart for this pivot table over here, what I have to do is go
to the insert Tab and then from the charts group. I'll select any chart
of my choice. And then I'll click on okay. So as you can see
a pivot chart has been created for this pivot table. You can also filter out the data according to your choice
by selecting any of the data that you want
to see selectively. Let me just select for Chicago
and I'll click on OK as you can see over
here by pie chart has been modified accordingly. Not just this you can also
format this chart by selecting this option and from here, you can select any type of chart that you intend to see so I hope
you guys are clear with this. So now let's move on
towards the next question, which is can you create pivot
tables using multiple tables. The answer is yes, you can create pivot tables
using one or more based tables, please note that both
the tables should be present in the same worksheet. So now to show this to you guys or what I'm going to do is
insert a new sheet and over here. I just called behalf
of the state data. So what I've done over
here is separated the table that I've created previously
into two different tables. So what I'm going to do
here is Click all plus d and then I'll click on pain. From this pivot table
and pivot chart wizard. I'll select multiple
consolidation ranges option and then I'll click
on next then. I'll choose the option. I will create the page fields
and over here. I'll specify the first range
to be the order ID is date name and the item I want one field. I'll just name it as field one. and I'll add this so now
for the next field what I'm going
to do is Select this I'll add it and I'll give
the name as field to and then I'll click on next. So Excel will ask you where you want to create it
and I'll say the existing sheet and I've selected some cell over
here then I'll click on finish. So as you can see over
here a new pivot table has been created by taking
the information present in both these tables. So now let's move on
towards the next question, which is what happens when you check the differ
layout update option present in the pivottable fields window. So in case you
check this option, you will not see Dynamic changes while interchanging
the table field. So as you can see
by default this option is off or unchecked all
the changes will appear only when you click
on the update button when you check this box, so I'm going to do over
here is first show you guys what happens when I make changes
without checking this box. So just remove the rows
and I'll put it back over here. So as you can see, I've just removed the rose
and put it back over here and by pie with table
has changed accordingly. Now, what I'm going
to do is put it back. And we'll check this box
and then I'll do the same. So as you can see previously
where I remove the rose and I put it back
in this list over here. The pivot table
changed accordingly. Now only when I hit the update button it
is going to change. So I hope you guys
are clear with this. Okay. So now let's move on
towards the next question, which is can you create
pivot tables using tables from different worksheets. Yes, if both the sheets are
from the same workbook. You can create pivot table
from the tables present in different sheets to create
five tables from two different. Follow the same steps shown
in question 24 and when you're adding the tables go to the respective sheet
and select the tables that you intend to merge. So is it possible
to see the details of the results display it
in the pie with table? Yes. It is possible to see
the details of the results shown by the pivot tables in Excel in order
to see the details of any of the results. All you have to do is
double click on any of them. And you will see that Excel creates a new sheets
with all the details about that result. So as you can see over here, I double-clicked on
the grand total 400 obtained by Brat and you see that Excel created a new sheet giving me all
the details regarding this. So now let's move on towards
the next question which is how our pivot tables
used to filter data Excel pivot tables allow you to filter data according
to your requirements to do this. All you have to do
is place the field based on which you
wish to Did the data and then from this pivot
table filter the data according to your choice? So as you can see over here, I've placed the Citi Field
in the filters area and over here. I can filter out the data
for all the cities or choose any city of my choice. Okay. So let me just choose
New York and click on OK. So as you can see over here, I have a private table created
only for the New York city. So now moving on
towards the next question, which is how do you change
the value field to show some of the result
other than the sum? In order to change
the value field to show results other than the some all you
have to do is right click on any of these values
present of here or from this Pi
would feel stable. Click on this drop-down
list present over here, then select the value
field settings option and from the given list choose
any total of your choice. Let me just change
from some to count and I'll click on OK So as you can see over here, the sum of values has been
changed to the count of amount. So now moving on how do you stop automatic
sorting in pivot tables? So as many of us know Excel
automatically sorts the data present in pivot tables in case you do not want to do
this open up the drop-down list present for the row labels or the column labels click
on more sort options, then go to more options
and over here and check this box that says sot automatically
every time a reporter. It is updated
once you're done with that. Just click on okay. So that was the last question
present in the section. So now moving on
towards the third section that is question
based on formulas and functions in Excel. So coming towards the first
question under this section. What do you understand
by Excel functions functions in Excel are used to perform
specific tasks Excel has many built-in functions that are used to calculate
results of various formulas. Thereby helping in
time conservation. Also these functions make it
very easy to execute formulas, which would have been If you
go to manually write down, so what are the various
categories of functions available in Excel? So as you can see
on the screen functions present in Ms. Excel
have been categorized in this list of options that are present over here. So you have categories based
on the date and time Financial functions functions
based on mathematics and trigonometry statistical
functions lookup and reference functions database text
logical information and Engineering functions
Cube functions compatibility and web functions. So no talking about
a very important question, which is what is
the operator precedence of formulas in Excel formulas in Excel are executed according to the body
mass roles board mass as many of us know
stands for brackets order division multiplication
addition and subtraction. So that means in every formula
brackets are executed first, if any of them are
present followed by multiplication division Etc now
to show an example of this, let me get back to excel. Let me just given
some values of Here, I'll say three. Four five now in this cell. I just type in some expression. I'll say a 1 star b 1 plus c 1. And now I'll hit enter. So as you can see over here, the result has been calculated
by first adding B1 and C1 and then multiplying
the same with a 1. So basically 5 plus 4 will give me 9 and 3 times 9 is 27 now
in case I modify this formula, which is removing the brackets. And now let's see what happens. So as you can see previously
I had a result saying 27 now. The result is 17. So what Excel has done over here
is first multiplied A1 and B1 and then the result
is added to see one. So basically 3 times 4 is 12
and 12 plus 5 is 17. So I hope you guys
are clear with this. So now let's move on
towards the next question, which is explained the sum
and the summer functions. So now let me just open up
the sum function over here in order to show you
guys the syntax of this. So as you can see over here, the sum function
takes an N number of values as its parameter and it Returns the sum
by adding each of them. So now what I'm going to do is
just cancel this and over here. I'll type in an equal to sign
and I'll use some function. I just specified this range
to be all the values to be added using the sum function
and now I'll hit enter. So as you can see over here, the sum function
has added all the values that are present between A1 and D1 similar to
the sum function is a sum of function one difference is that this function is used
to calculate the sum of values that comply to a given condition
now in case I just change this from some to some if I'll have to specify the range followed
by the criteria. Now. What I'm going to do
is given some criteria. I'll say calculate
the sum of values that are less than 15. And then I had Dental
so as you can see over here, it has added only those values that are less than 15
and it has discarded 17. So now moving on
towards the next question which is what are
the different types of count functions available in
Excel Excel provides five types of count functions. That is count count a count
black count if and count if s so talking
about the first function that is the count function
this function Returns the total number of cells. That have numbers in the range that are specified
to it as a parameter. So now to show y'all
how this works. What I'm going to do is type
in an equal to sign over here and then I'll make use
of the count function. So as you can see over here, it takes all the values
as a parameter. So what I'm going to do
is just specify this range and then I'll hit enter. So as you can see over here, the count function
has returned five which means five cells
in this range contain numbers. So now moving on
towards next function, which is Count a if I just change this from count to count a it
is going to count all the cells that are not empty
within this range. So as you can see over here, I had given a very big range that has cells that contain
information plus the cells that are empty but county has returned
only the number of cells that contain data in them. So now talking about count if the countif function
counts the number of cells that comply
to a given condition. So let me just change it
from count eight count if and within this range,
I'll just specify some criteria. I'll say count the values
that are greater than 10. And that I hit enter so as you can see
within this range, there are two cells
that contain values that are greater than 10
in contrast to count a is Count blank count are counted
the number of cells that contain information on the other hand count blank
will count the number of cells that are blank
within a given range. So what I'm going to do over
here is change this from count if to count blank And there are just remove
this condition from you. Okay. So as you can see, there are seven empty cells
present in this range. So now talking about count if s this is a very
special function that allows you to specify a set of conditions
in order to count them. So what I'm going to do
is go to the table that I've created of here. So what I'm going
to do over here is Type in an equal to sign. Then I'll choose
to count as formula and then I'll have
to specify the range for criteria one the range
for criteria 2 and so on. So what I'm going to do
is just select the names. And the name that I want
to select from here is Yvonne. After this, I'll give
criteria to range. And I'll specify
the quantity as 24 now. I'll hit enter. So as you can see over here
within the given range, there's just one cell that has a one and
has the quantity of 24 present in it. So, I hope you guys
are clear with this. Okay. So now let's move on
towards the next question which is how do you
calculate the percentage in Excel percentages as we all know are the ratios that are calculated as
a fraction of hundred mathematically percentage
will be equal to the part by whole multiplied by hundred. In Excel percentage
can be calculated similarly. So now to calculate the percentage of marks obtained
by each of these students. What I'm going to do
is just select the cell that I want to
calculate the percentage. I'll type in the equal to sign
and then it is passed by whole so it will be b 2 divided
by C2 now hit enter and once I've obtained the result, I'll click on Home tab
and from here. I'll click on the
percentage symbol similarly. I'll just copy this formula
to the next cell. l So as you can see
Yvonne has got 96% and Sergio has caught
in 78 percent. So now moving on
towards the next question, which is explain how to calculate
compound interest in Excel to calculate
compound interest in Excel. You can make use
of the FV function the FV function returns
a future value of an investment based on periodic constant
investment rates and payments. So let me get back to excel so
as you can see over here, I've already created the table. The investment amount that I have over here
is 500 at the rate of 10% for five years and
the compounding periods are 12. So now to show the syntax
of this function to you guys. What I'm going to do is
just type in an equal to sign followed by f v so as you can see over here Excel provides a description saying
this Returns the future value of an investment based
on periodic constant payments and constant interest rate. So now to find the rate, I'll divide the annual rate
by periods and to find n / I'll be multiplying
Number of years with the periods PMT over here can be
any value including zero first. I should type in the rate
to calculate the rate. Like I've already told
y'all I have to divide annual rate by periods. So that will be be
2 divided by B 4. after this, I have
to specify number of periods that will be obtained by
multiplying B 3 and b 4 The periodic payments
value I'll specify a zero and then I'll specify - of B1 as the investment rate. Now. This is because the money
has been taken from my end. So as you can see over
here the future value for an investment
of 500 at the rate of 10% for five years and the compounding periods
being 12 is eight hundred and twenty two point
six five dollars. So the future value
for an investment of $500 at the rate of 10%
for five years and 12 compound. Spirits is eight hundred
and twenty two point six five dollars. So I hope you guys are
here with this. So now moving on
towards the next question, which is how do you find
averages in Ms. Excel in order to find the averages you can make use
of the average function? So as you can see over
here this function Returns the average or the arithmetic mean
of its arguments which can be numbers
names erase or references that contain numbers. So now in order to find
the average obtained by each of these students over here, what I have to do is
just select the range. And then I'll click enter. So as you can see over here,
the average of Dave is 83.4. Similarly if I have to calculate for Ava all I have to do
is copy this formula. So I hope you guys
are clear with this. So now let's move on towards the
next very important question, which is what is
V lookup in Excel. The vlookup is a function
present in Excel used to look up and bring forth data
from a given range v stands for vertical and to use this function data should be arranged vertically
vlookup is very useful when you have to
And some piece of data from a huge amount of data. So how does this
vlookup function work the vlookup function
in Excel takes a lookup value and begins to look for the same
in the leftmost column when it finds
the first occurrence of the given lookup value. We look up starts to move. Right that is in the row
where the value was found. It goes on until the column
specified by the user and Returns the desired value. This function can be used
to match exact as well as approximate lookup values. However the default Match
is an approximate match now to show y'all the syntax
of this function. What I'm going to do
is get back to excel. So as you can see over here,
I've already created a table. So now just to show y'all
the syntax of this function. What I'm going to do is type
in an equal to sign. And then I'll choose
the vlookup function. So as you can see over
here the vlookup function takes in four parameters, which is the lookup
value the table array the column index number
and the optional parameter, which is range lookup the lookup value gives the value
to be looked for table. Index is the table where in the data
should be taken from column number specifies
the column from which you want to fetch the value and the range lookup
is a Boolean value, which is either true
or false true will find the closest match and faults
will find an exact match. So now explain exact match with an example
for an exact match. You'll have to set the range
lookup value to false. So let me just take
one example of this the value that I want to look
over here will be Leo and then I'll specify
the table array. I'll give the column number as for since I want to take
down the salary of Leo and then I'll specify
faults as the Boolean value. So as you can see over here,
I fetch the value of Leo salary. So what we look up does over
here is it will start to look for Leo Finn this leftmost
column present in the stable. So once it traces Leo, it will start moving right
in this row and it will go on till that column which is specified as
a parameter over here. So I hope you guys
are clear with this. So now explain the approximate
match with an example for an approximate match. We look up will fetch values. Well, there are no exact matches
of the given Up value now in order to make vlookup search
for an approximate match. You will have to set the Boolean
value to True remember that the table must be sorted in ascending order for vlookup
to do an approximate match. So what we look up basically
does over here is it starts to look for an approximate match
of the given lookup value and then it stops at the value which is the next largest
of the given lookup value. Then it starts to move
right in the same row and fetches the value
that is desired. So what I'm going to do is type
in an equal to sign and Then select
the vlookup function. I'll specify the lookup value
to be 80 and the table array. The column I'll specify as
to and the range lookup will be true. So as you can see over here, we look up has fetched
be as the result 480. So what we look up is doing over
here is it starts looking for 80 in this leftmost column and then it stops
at the next largest value and then moves right
in the same row. So I hope you guys
are clear with this. Okay. So now moving on
towards the next question, which is can you use vlookup
for multiple tables? Yes, you can use vlookup
for multiple tables as well. In case you have to look up
values create named ranges for each of the table and then use the IF function
to select between each table based on some of the given condition
to know more about this. You can check out the vlookup
in Excel video from Eddie raker. So now moving on how do you perform
a horizontal look up in Excel to perform
a horizontal look up you'll have to make use of the hlookup function
the H look up function just like the vlookup function takes
a lookup value the table array and instead of the column
index number it takes Is the row index number
as a parameter and the range lookup value. So now to perform
an edge look up what I'm going to do over
here is take a new cell. I'll just type in Edge Look up. And over here,
I'll specify that. I want to see the designation. And then I'll specify the range. followed by the row index number and since I want a perfect match
or an exact match, I'll type in false. I've just made a mistake
in this range over here. So as you can see over here, I have the output
as senior developer. So just like the vlookup
function the H look up function will first
do a horizontal search and then it will move downwards. So I hope you guys
are clear with this. Okay. So now let's move on
towards the next question, which is how will you fetch
the current date in Excel to fetch the current
date you can make use of the today function. This function will return
the current date in Ms. Excel date format. So, let me just choose
a new cell and over here. I'll just in today So
as you can see over here, it doesn't turn the date
in Ms. Excel date format. So moving on towards
the next question, which is how does the
and function work the and function in Excel
is used to check whether a given condition or a set of conditions
are true or not. In case the
conditions are satisfied. This function will return
a Boolean true. So as you can see over here, I've already created a table now
to make use of the and function what I'm going to do is type
in an equal to sign. And then I'll just type
in and for condition 1 I'm going to check if 230 is greater than 200
and less than 500. So what I'm going to do
over here is select the cell and I'll see if this is greater than 200
and less than 500. Now let's hit enter. So as you can see 230
is greater than 200 and less than 500 similarly if I have to calculate
for the rest of the values. I'll just copy the formula. So I hope you guys
are clear with this. Okay. So now moving on
towards the next question, which is what is the what-if analysis what-if
analysis is the technique of Performing changes to one or more formulas present
in the cell in order to see how it affects the result
of those formulas in the worksheet Excel provides. Three types of
what-if analysis tools that is scenarios goal seek
and data tables scenarios and data tables take a set
of inputs to check for the potential
results scenarios can work with many variables, but input values can be
at the max 32 data tables on the other hand work
with just one or two variables, but can accept many
distinct values for each of those variables goal seek
in contrast to scenarios and data tables
takes the outputs and determines the possible. It's for the same. So can you create shortcuts for
most frequently used formulas? Yes, you can do it by customizing the quick access
toolbar right-click anywhere on this quick access toolbar and then choose customize quick
access toolbar option from here. You can add any function
of your choice to this quick access toolbar. So what is the difference
between formulas and functions in Excel formulas
are defined by the user that is used to calculate
some results formulas can either be simple or complex and they can consist of values functions
defined names Etc. A function on the other hand
is a built-in piece of code that is used to perform
some particular action Excel provides a huge number of built-in functions such as
the sum function product if some if count Etc, so now moving on
towards the last None of the session and that is how do you use wild cards with
vlookup wild cards can be used when you're not sure
of the exact lookup value in order to use
wildcards in Excel. You should make use
of the star symbol. So for example in this table over here in case I do not know
the complete name of Sergio. What I'm going to do is
just type an ERG over here. And then I'll make use
of the vlookup function. The first thing that
I will be specifying over here is wild card symbol. That is star. And then I'll type
in an ampersand symbol. And the reference will be f13 and Ampersand symbol
again and elastic. After this, I'll specify
the table array. The column index number
as usual and then false. for an exact match So
as you can see over here, I found the result for Sergio
as Junior software engineer. So I hope you guys
are clear with this. Okay, this brings us
to the end of this session. I hope you guys have enjoyed
and learned something new in case you have
any doubts or queries. Please do let me know
in the comment section and I will revert
to you at the earliest. Good bye and take care. 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.
