hi there this is Vadim Mikhailenko and in
this quick video we're going to look at the
most typical excel interview questions
We're gonna start by looking at vlookup
formula, then we we'll look at the differences
between desktop version of Excel and
Excel for Office 365 we'll also look at the
different functions and formulas what
are they how they used what is the
sequence of evaluating formulas in Excel
and a lot more so stay here with me on
this channel
[Music]
let's start by looking at vlookup
formula because this is one of the most
frequently asked Excel interview
questions but snart was the quick
example in this example we need to find
order amount based on the known order ad
and we have a table of existing orders
to do this old type equals vlookup and
then provide four values first value is
the known value that we're searching for
in our case this is cell a7 was the
order ID number 839 293 second value is
the range in which we'll be searching
third value is the column index for the
column that we need and in our case this
is column D which contains amount but
numerically this is column number four
because order ID is column number one
name is column number two email is three
and an amount is column number four and
then we'll provide the last value false
for exact match once we finalize the
formula you see that the value found is
four hundred twenty three dollars and 38
cents and as you can see this is the
amount for the order eight three nine
two nine three the view cut function in
Excel performs a vertical lookup by
searching for a value in the first
column of the table and returning the
value in the same row based on the index
number position for example if your
table contains list of orders you can
find customer name email amount ship
date or other available attributes of
the order based on a known order ID for
example if your ID is eight three nine
two eight seven you can run look up to
find customer name which would be Ivana
Weber or email which would be Ivana
Weber at trade entered calm or find the
order amount in amount of four hundred
and six dollars and ninety two cents
which was shipped on September 7th 2018
you
[Music]
to add vlookup function you need to
navigate to formulas tab and then click
on an insert function button then you
type vlookup and find the formula there
are 4 values we need to provide lookup
value is the known value we'll be
searching for table array is the area
which will be searched
lookup index number is the index of the
column which will be returned that
contains the value we're looking for and
then range specifies the way we will be
searching false indicates exact match
and true would be not exact match we
will be using false let's add vlookup
formula using this functions arguments
dialog box for the lookup value will
provide a 7 for table array we'll select
the range between a 2 and a 51 for the
column index number since we're looking
for amount we'll select 4 and for range
lookup will select false for exact match
and you can see that we've selected
order 8 3 9 2 9 3 in the amount for this
order is 423 dollars and 38 cents the
hookup formula in Microsoft Excel helps
you find specific value based on a one
non well for example we're looking at
the list of orders in this table we have
already named email amount and should
date in our business case is we need to
find customer email based on a specific
order number or we need to find order
amount based on a specific order number
using vlookup formula we can supply
order ID and find any of the remaining
attributes here listed here in this
table this is the syntax of vlookup
formula you need to supply known value
in our case it would be order ID then
you need to supply range for searching
and you need to identify that they need
to specify the column we're searching
for because different column represents
different value and then you need to
define the match type how exactly are
you gonna match the value now as we know
the syntax let's use vlookup formula to
find email address of the customer for
the order eight three nine two eight
four to do that I'm gonna type vlookup
then the order ID 839 to 84 then I need
to identify the range of the cells where
I will be searching for the information
and the ranges between the cells of a
two to e twenty-three then I would need
to specify the email column in this
range and the email column is number
three because the first one is order ID
second one is the name and the third one
is the email and then I need to specify
the type of net true is an approximate
match and false is the exact match so
I'm gonna use false for exact match
if you're interested to learn more about
Microsoft Excel
I recommend training course on udemy
just navigate to how to analyze data
that met slash Excel to learn more I've
carefully selected this course and you
get substantial discount from the retail
price just to let you know this isn't a
few a blink and they get small
commission if you make the order
don't make purchases though if you don't
need it but thank you for your
consideration and support of this
channel based on the information I
provided system found email for
Katherine Morfitt let's look at how
vlookup can help us solve specific
business problems for example how can
you find customer name or email based on
specific order number or how can you
find order amount based on order number
or how can you find shit babe first
let's look at name and email below cap
can only return one value so we would
need to do name first and then email as
I started typing please pay attention
that instead of putting specific order
number I put in the cell reference which
is a six in this case and we'll see how
this will impact copying and pasting of
vlookup formula in solving later
business problems we supply order number
by putting the reference to a six cell
then we specified the range and then we
specify the column ID for where the
information should be taken from and the
last step is we specify how we're gonna
match the data we picked an exact match
by supplying the value falls in the name
of a Leah Reeve has been returned by the
vlookup which is the exact correct value
question number five what is the
difference between Microsoft Excel for
the desktop and Excel office 2016 for
desktop is the newest version of office
productivity suite which includes Excel
Word PowerPoint and other office
applications which is which are
typically accessed via the desktop
office 365 on the other hand is the
cloud-based subscription to a suit of
programs including office 2016 of his
files can be accessed in the browser and
on mobile devices which allows
businesses and individuals for more
flexibility you can also download and
install office 2016 including Excel 2016
on your desktop as part of office 365
subscription full desktop version of
office which includes Word PowerPoint
Excel OneNote and publisher is included
with license for up to five computers
per user another big difference isn't
pricing office 2016 is the standalone
version was the one-time cost per user
so you just make your purchase one you
pay a large amount of money up front but
you don't have to pay monthly fee after
office 365 on the other hand is the
subscription plan meaning that you won't
pay anything upfront but you pay a set
of monthly fees per user per month and
if you don't want to spend a lot of
money upfront paying monthly might be a
better option for you because you can
cancel at any time also question number
four what are the most useful functions
one of the most useful excel functions
is some which allows you to quickly sum
a range of cells by using autosum button
it automatically enters the some
functions in the selected cells the sum
function tolls one or more numbers in
the range of cells other useful
functions are average Max and min
average calculates average value from
the range of cells max calculates the
maximum value and min calculates the
minimum value in the range of cells
Excel has a lot of functions available
to you and based on the work that you're
doing you will be selecting and favoring
some of the specific functions based on
the job you also will be able to write
your own functions and calculate some
specific values based on the work that
you need to accomplish number three what
is the function in Microsoft Excel
functions are pretty fine formulas that
allow you to do calculations and they
are already available in Excel formulas
and functions in Excel are useful to
perform various mathematical statistical
and logical operations you can type in
formula though you have to be sure it's
exactly right or you can use excels
preset formulas called functions if you
type in formula you must start with an
equal sign so Excel knows that the data
in the cell is a formula after the equal
sign what comes next depends on what
you're trying to do Excel offers you
some suggestions and shows syntax for a
given function Excel also corrects you
and provides suggestions if you've made
a mistake while typing question number
two what is the sequence of operations
being executed
well formalists are executed in
Microsoft Excel Excel uses specific
order when it evaluates operations in
formulas first it goes and looks at the
parentheses then it looks at exponents
multiplication division addition and
then subtraction you might recognize the
term PEMDAS PE m da s and that stands
exactly for what we just described when
evaluating formula Excel always
processes operations in this order if
you find yourself receiving an
unexpected result from the mathematical
operation double check to make sure that
parents is's are used properly to
achieve the results that you want in
question number one what is the most
important data types used in Microsoft
Excel there are four important data
types in Microsoft Excel and they are
numbers dates percentages and strings
numbers are one of the most frequently
used data types in Excel they can be
formatted with specific number of
decimal places and they can appear with
or without commas separating the
thousand digits numbers can also be
added subtracted divided multiplied or
included in formulas and functions that
accept numerical inputs dates represent
specific date and time dates are sorted
differently than numbers because they
represent different values percentages
are very special numbers that multiply
given number by 100 and add percentage
sign in then in strengths represent
freeform text and they're stored as text
could be letters and numbers and
punctuation signs and also could be
stored in your cells Excel needs to be
able to differentiate between those key
data types because it needs to know how
to sort the data and how to do
calculations and they are all done
differently for those different data
types
have you enjoyed this episode make sure
to subscribe so you will not miss the
next one and tell your friends about it
you can find links and downloads in the
description section of this video there
are a lot of helpful links and a lot of
other helpful information you will
benefit from make sure to check out my
other relevant videos and subscribe to
my youtube channel we have a lot of
great stuff planned in the pipeline and
I don't want you to miss any of it and
if you'd like to get notified about all
the new stuff that are coming out make
sure to subscribe to my email list as
well all links are here in the screen
make sure to click to stay in touch
thanks again for watching