馃攳
How To Pass Microsoft Excel Test - Get ready for the Interview - YouTube
Channel: Online Training for Everyone
[0]
hi there my name is Vadim Mikhailenko
and in this quick video we're going to
[4]
learn about seven key questions asked as
part of Microsoft Excel Job interview tests
[9]
Plus one bonus question, so stick here with me to learn more
[30]
Let's take a look at the question how to make the entire row bold or italic was different color first
[36]
of all you need to understand the data
you're looking at we're looking at
[39]
Microsoft Excel with the data that's
already has been entered we have
[43]
multiple columns a through F and we have
rows 1 through 9 and just by looking at
[50]
the data it shows some sales right this
are the number of items was the
[56]
descriptions and by categories group by
categories and for multiple quarters q1
[63]
through q4 so your question is how to
make the entire row bold or italic was
[68]
different color let's pick row number
five and I think that's the good role
[72]
for this it's kind of in the middle and
will show and you see I just clicked on
[76]
the row five and it's selected the
entire row to infinity and that's the
[81]
way to do it you certainly can do it but
keep in mind that all the cells beyond F
[85]
will also be selected was that
particular formatting so sometimes it
[90]
makes sense just to select the area that
you interested in a through F but you
[95]
probably need to make a determination
how you want to do it based on the
[99]
circumstances so I highlighted Row 5 and
now what I'm going to do now I can
[104]
manipulate the data and now I can change
italic and I can put bold here right and
[112]
instructions in the text asked us to do
both so that's what we're gonna keep and
[118]
now there is a button here called font
color and that's what we're gonna click
[123]
and then there multiple colors here
available and you just pick the color
[127]
that's most relevant and related so
maybe look at the header and I think
[131]
they're related topic
might be this couple considerations when
[136]
picking the color you don't just pick
how color looks on the screen you also
[141]
think how color will look at the
printing and most likely black and white
[146]
printing so that's probably the dark
color is typically better and that's
[152]
typically a good choice
[160]
[Music]
now let's take a look at how you would
[164]
make the entire role with a different
background color and the answer he is
[169]
rather easy you select the role and
again multiple ways of selecting the
[173]
role right you can do it this way
selecting just the required columns or
[178]
you can click on the entire row and
select it to infinity we're gonna select
[183]
just the specific columns and then you
click on the background color and you
[188]
pick the specific color that you want to
highlight this is the color of our
[193]
header so we probably want to pick a
different one so let's pick a different
[196]
one maybe from this palette and that's
it
[208]
[Music]
[214]
now let's take a look how you would
create total for each quarters first of
[219]
all you need to understand where the
data is located right and we have
[223]
columns C through F that represent
quarterly sales data for different
[228]
supplies and thus of the descriptions of
the supply C in order for you to create
[234]
totals what you need to do you need to
highlight the row below and next below
[240]
row where numbers stop and then the
easiest way is just clicking Auto
[244]
somebody when you click it what Excel
does it highlights the area on top and
[251]
Excel kind of does intelligence on its
own and it's tries to predict the area
[258]
which you need to do a sum for and you
just need to hit enter and it calculated
[264]
the total but if array needs to be
different for example if you're doing it
[268]
for this column and you need to change
the area you can do it manually also I'm
[272]
just pushing the left mouse button and
dragging the selection area and that
[278]
selects the required area another way to
do it is you can just copy the cell and
[286]
copy is this button copy and then roll
paste and this will paste the entire
[291]
formula from rows 2 through 9 for the
column F and I just click paste here
[298]
right and it pasted the information and
this are the totals for all quarters
[305]
individually and what you might also
want to do is just a nice thing to do to
[310]
just take the total and that's typically
what you're doing you might also want to
[314]
do extend the formatting for this grid
and the way to do it you highlight the
[318]
area where you want to add borders and
you just click here and select all
[323]
borders and that will maintain the
formatting for the borders
[329]
[Music]
[336]
now let's take a look how you would
create total for the entire year and
[340]
again what we see here we see a data for
multiple quarters q1 through q4 right in
[348]
order for us to calculate total for the
entire year we would want to add
[352]
probably another column so the easiest
way would be just to copy this cell and
[357]
again what we do is we click copy here
and we click paste in this column and
[362]
we'll rename it the reason I did copy
and paste so it retained the formatting
[367]
and we'll just say year here extended a
little bit so it matches size-wise other
[374]
columns that's I think is important and
what we want to do we want to use the
[380]
sum function but we want to use it first
of all for every item here and then we
[385]
will do a total for each line item right
here in this column g10 so let's first
[392]
do a sum for wireless chargers and the
way for us to do it we'll click autosum
[398]
button and Excel predicts the area that
we want to do a sound for but again we
[404]
can manually control it before we click
enter but I'm good with this I'm gonna
[408]
click enter and what I'm going to do I'm
going to make this a little bit bigger
[412]
so we can see so probably need to do a
zoom out a little bit and now we have a
[419]
number so now we calculate for wireless
chargers we can do the same thing for
[425]
Apple screen protectors I'm gonna hit
enter
[429]
or we can just copy and look at this we
click the copy button a copy button and
[436]
now what I'm going to do I'm gonna
select this entire area right here rows
[442]
4 through 9 and I'm just gonna click
paste so it pastes this value this
[447]
formula right into this selection and
voila it's all set now we have totals
[455]
for a year for all different supplies
and electronics and we just need to
[460]
calculate it for the year for the entire
product lines that we're selling
[465]
and for that we'll just click autosum
again and again we got such a big number
[471]
that it doesn't fit which is one hundred
forty eight thousand eight thirty nine
[475]
dot forty three so the only remaining
thing would be to write total here to
[480]
make it look nice and also add the
formatting for that we just highlight
[485]
all the cell's and we select all borders
[501]
[Music]
[513]
[Music]
now let's take a look at how to sort
[517]
data from largest to smallest so in
order to do that you would need to
[522]
select the entire area that you would
like to sort and in our case it would be
[526]
all the cells with the data and then
we're going to click the sort and filter
[530]
button and then we are going to select
the custom sort custom sort allows us to
[536]
sort by specific column and in this case
it makes sense to sort by description
[542]
and that's exactly what we're going to
choose you're also going to sort on cell
[546]
values there are different ways of
sorting you can sort by cell color font
[551]
color conditional formatting and you can
choose a twosie you can choose z2 a or
[556]
you can even choose a custom list so
we're gonna just stick to default a to z
[560]
and let's see what's gonna happen
remember wireless chargers are on the
[564]
top and we're sorting a to z then goes
apple screen protects our voice remote I
[569]
just wanted you to pay attention here
click the sword and now we have all the
[574]
items with their associated number which
is important that's why we selected all
[578]
the values in grid from a to W and a is
Alexa and then the second letter also
[589]
goes in alphabetical order so you can
see all the sort of items here in the
[594]
list and I can definitely unselect it
[608]
now let's take a look how you would
group the data based on the categories
[613]
and the easiest way to do it is actually
apply a filter it's a very nice feature
[617]
of Microsoft Excel to do that we can
select the whole thing or we can just
[622]
select the first row and we're gonna
click sort and filter button and we'll
[626]
click filter and now look it added a
very small drop down buttons here
[632]
Microsoft Excel and now look what
happens when we click on this button it
[637]
shows us all the items and if we want to
remove something we can just unselect
[642]
all and select the ones that we want to
look at and that's what happens now
[646]
that's so cool we can also clear the
filter from description that put its
[653]
back but grouping the data in this case
makes sense for categories because there
[657]
are only two categories a lot of times
there are a lot more categories but in
[662]
this case there are only two so we will
select and let's say we only want to see
[666]
data in electronics we unzila uncheck
supplies and that's what you see only
[672]
see electronics or vice-versa maybe you
want to see only supplies and then you
[677]
uncheck electronics and click supplies
or maybe you want to see the whole thing
[682]
both electronic and supplies and here
you go
[713]
[Music]
[725]
now let's look at how you can insert a
chart into Microsoft Excel and the best
[732]
way to do that the best way to insert
the chart is to select the entire area
[737]
that you're trying to build a chart for
and then you go to insert tab in the
[744]
ribbon and then you click a lot of chart
choices as you can probably imagine but
[750]
the good thing Microsoft Excel
recommends you the chart so I click the
[754]
recommended chart and this is the chart
that Excel recommends I don't like it so
[760]
I'm gonna scroll and see if there are
other choices and I like this one much
[764]
much much better so now I just click OK
and Excel inserts the chart and I am
[773]
going to make it fullscreen so you guys
can see better the cool thing about the
[778]
chart we can even overlap the table and
I am to do it this way I am going to
[786]
zoom out a little bit
and move the chart well the down scroll
[792]
down here so we will see only chart we
will expand it a little bit here on the
[799]
screen
[802]
so we can seed make it bigger and which
you see this chart list we have
[811]
different categories we have our values
right the sales values for quarters and
[821]
every bar represents every separate
quarter so for example for HP Pavillion
[827]
we have q1 q2 q3 q4 right and this is
highlighted right here we can also
[834]
change the chart title and looking for
example annual sales and that shows us
[842]
all the values that we would like to see
we can close this so it expands the
[846]
whole chart it will be available for
printing and you can modify it maybe if
[851]
you want to not select categories next
time to make it easier and more readable
[857]
you can certainly do that chart is built
based on values that you can highlight
[862]
and you can also delete the chart you
just need to select it
[866]
you see it's selected and then you click
the delete button on your keyboard
[882]
and now let's take a look at how would
you project a revenue increase of 7%
[888]
quarter-to-quarter so just think about
it we'll start with quarter one so
[894]
quarter two should show seven percent
increase from quarter one band quarter
[899]
three would show seven percent increase
from quarter two and quarter four seven
[904]
percent increase from quarter three how
would you do that so the easiest way
[908]
let's clear out this data because this
data is actuals and does not represent
[911]
anything for us we would need to use a
formula we can foil in Excel we just
[917]
select the value and we click type equal
sign and we're gonna say value for Q 1 x
[925]
1 0 7 and 1y y 1 0 7 1 represents the
previous value we multiply just by 1
[934]
will get exactly 5700 but 0.7 is the 7%
increase for the q1 value and we're
[942]
going to hit enter and that's exactly
what its gonna be
[945]
now I'm going to copy this value and I'm
going to select the area where I want to
[951]
paste this formula and that's exactly
where I'm going to paste it and it will
[957]
calculate q2 now 7% increase in all
categories from q1 now the cool thing
[964]
about Excel look at this I'm gonna copy
this whole thing and now I can paste it
[970]
in q3 and q4 and it will calculate a
relative formula now q3 would be
[978]
calculated against Q 2 and Q 4 will be
calculated against Q 3 just look at that
[983]
there you go so you don't need to do it
one by one obviously you could and you
[988]
can retype the whole formula but because
of the power of Excel we can project the
[993]
Q 1 through Q 2 Q 2 through Q 3 and Q 3
through Q 4 that's the power of relative
[999]
formulas in Microsoft Excel
hope you have learned something and
[1005]
enjoyed this video make sure to click
the subscribe button to stay in touch
[1009]
with me on YouTube and join my email
list so I can share with you more
[1013]
information about my free training
courses hope to see you back soon again
[1018]
this is vadim lanka thanks again for
watching
Most Recent Videos:
You can go back to the homepage right here: Homepage