How To Pass Microsoft Excel Test – Get ready for the Interview

How To Pass Microsoft Excel Test – Get ready for the Interview


hi there my name is Vadim Mikhailenko
and in this quick video we’re going to learn about seven key questions asked as
part of Microsoft Excel Job interview tests Plus one bonus question, so stick here with me to learn more Let’s take a look at the question how to make the entire row bold or italic was different color first of all you need to understand the data
you’re looking at we’re looking at Microsoft Excel with the data that’s
already has been entered we have multiple columns a through F and we have
rows 1 through 9 and just by looking at the data it shows some sales right this
are the number of items was the descriptions and by categories group by
categories and for multiple quarters q1 through q4 so your question is how to
make the entire row bold or italic was different color let’s pick row number
five and I think that’s the good role for this it’s kind of in the middle and
will show and you see I just clicked on the row five and it’s selected the
entire row to infinity and that’s the way to do it you certainly can do it but
keep in mind that all the cells beyond F will also be selected was that
particular formatting so sometimes it makes sense just to select the area that
you interested in a through F but you probably need to make a determination
how you want to do it based on the circumstances so I highlighted Row 5 and
now what I’m going to do now I can manipulate the data and now I can change
italic and I can put bold here right and instructions in the text asked us to do
both so that’s what we’re gonna keep and now there is a button here called font
color and that’s what we’re gonna click and then there multiple colors here
available and you just pick the color that’s most relevant and related so
maybe look at the header and I think they’re related topic
might be this couple considerations when picking the color you don’t just pick
how color looks on the screen you also think how color will look at the
printing and most likely black and white printing so that’s probably the dark
color is typically better and that’s typically a good choice [Music]
now let’s take a look at how you would make the entire role with a different
background color and the answer he is rather easy you select the role and
again multiple ways of selecting the role right you can do it this way
selecting just the required columns or you can click on the entire row and
select it to infinity we’re gonna select just the specific columns and then you
click on the background color and you pick the specific color that you want to
highlight this is the color of our header so we probably want to pick a
different one so let’s pick a different one maybe from this palette and that’s
it [Music] now let’s take a look how you would
create total for each quarters first of all you need to understand where the
data is located right and we have columns C through F that represent
quarterly sales data for different supplies and thus of the descriptions of
the supply C in order for you to create totals what you need to do you need to
highlight the row below and next below row where numbers stop and then the
easiest way is just clicking Auto somebody when you click it what Excel
does it highlights the area on top and Excel kind of does intelligence on its
own and it’s tries to predict the area which you need to do a sum for and you
just need to hit enter and it calculated the total but if array needs to be
different for example if you’re doing it for this column and you need to change
the area you can do it manually also I’m just pushing the left mouse button and
dragging the selection area and that selects the required area another way to
do it is you can just copy the cell and copy is this button copy and then roll
paste and this will paste the entire formula from rows 2 through 9 for the
column F and I just click paste here right and it pasted the information and
this are the totals for all quarters individually and what you might also
want to do is just a nice thing to do to just take the total and that’s typically
what you’re doing you might also want to do extend the formatting for this grid
and the way to do it you highlight the area where you want to add borders and
you just click here and select all borders and that will maintain the
formatting for the borders [Music] now let’s take a look how you would
create total for the entire year and again what we see here we see a data for
multiple quarters q1 through q4 right in order for us to calculate total for the
entire year we would want to add probably another column so the easiest
way would be just to copy this cell and again what we do is we click copy here
and we click paste in this column and we’ll rename it the reason I did copy
and paste so it retained the formatting and we’ll just say year here extended a
little bit so it matches size-wise other columns that’s I think is important and
what we want to do we want to use the sum function but we want to use it first
of all for every item here and then we will do a total for each line item right
here in this column g10 so let’s first do a sum for wireless chargers and the
way for us to do it we’ll click autosum button and Excel predicts the area that
we want to do a sound for but again we can manually control it before we click
enter but I’m good with this I’m gonna click enter and what I’m going to do I’m
going to make this a little bit bigger so we can see so probably need to do a
zoom out a little bit and now we have a number so now we calculate for wireless
chargers we can do the same thing for Apple screen protectors I’m gonna hit
enter or we can just copy and look at this we
click the copy button a copy button and now what I’m going to do I’m gonna
select this entire area right here rows 4 through 9 and I’m just gonna click
paste so it pastes this value this formula right into this selection and
voila it’s all set now we have totals for a year for all different supplies
and electronics and we just need to calculate it for the year for the entire
product lines that we’re selling and for that we’ll just click autosum
again and again we got such a big number that it doesn’t fit which is one hundred
forty eight thousand eight thirty nine dot forty three so the only remaining
thing would be to write total here to make it look nice and also add the
formatting for that we just highlight all the cell’s and we select all borders [Music] [Music]
now let’s take a look at how to sort data from largest to smallest so in
order to do that you would need to select the entire area that you would
like to sort and in our case it would be all the cells with the data and then
we’re going to click the sort and filter button and then we are going to select
the custom sort custom sort allows us to sort by specific column and in this case
it makes sense to sort by description and that’s exactly what we’re going to
choose you’re also going to sort on cell values there are different ways of
sorting you can sort by cell color font color conditional formatting and you can
choose a twosie you can choose z2 a or you can even choose a custom list so
we’re gonna just stick to default a to z and let’s see what’s gonna happen
remember wireless chargers are on the top and we’re sorting a to z then goes
apple screen protects our voice remote I just wanted you to pay attention here
click the sword and now we have all the items with their associated number which
is important that’s why we selected all the values in grid from a to W and a is
Alexa and then the second letter also goes in alphabetical order so you can
see all the sort of items here in the list and I can definitely unselect it now let’s take a look how you would
group the data based on the categories and the easiest way to do it is actually
apply a filter it’s a very nice feature of Microsoft Excel to do that we can
select the whole thing or we can just select the first row and we’re gonna
click sort and filter button and we’ll click filter and now look it added a
very small drop down buttons here Microsoft Excel and now look what
happens when we click on this button it shows us all the items and if we want to
remove something we can just unselect all and select the ones that we want to
look at and that’s what happens now that’s so cool we can also clear the
filter from description that put its back but grouping the data in this case
makes sense for categories because there are only two categories a lot of times
there are a lot more categories but in this case there are only two so we will
select and let’s say we only want to see data in electronics we unzila uncheck
supplies and that’s what you see only see electronics or vice-versa maybe you
want to see only supplies and then you uncheck electronics and click supplies
or maybe you want to see the whole thing both electronic and supplies and here
you go [Music] now let’s look at how you can insert a
chart into Microsoft Excel and the best way to do that the best way to insert
the chart is to select the entire area that you’re trying to build a chart for
and then you go to insert tab in the ribbon and then you click a lot of chart
choices as you can probably imagine but the good thing Microsoft Excel
recommends you the chart so I click the recommended chart and this is the chart
that Excel recommends I don’t like it so I’m gonna scroll and see if there are
other choices and I like this one much much much better so now I just click OK
and Excel inserts the chart and I am going to make it fullscreen so you guys
can see better the cool thing about the chart we can even overlap the table and
I am to do it this way I am going to zoom out a little bit
and move the chart well the down scroll down here so we will see only chart we
will expand it a little bit here on the screen so we can seed make it bigger and which
you see this chart list we have different categories we have our values
right the sales values for quarters and every bar represents every separate
quarter so for example for HP Pavillion we have q1 q2 q3 q4 right and this is
highlighted right here we can also change the chart title and looking for
example annual sales and that shows us all the values that we would like to see
we can close this so it expands the whole chart it will be available for
printing and you can modify it maybe if you want to not select categories next
time to make it easier and more readable you can certainly do that chart is built
based on values that you can highlight and you can also delete the chart you
just need to select it you see it’s selected and then you click
the delete button on your keyboard and now let’s take a look at how would
you project a revenue increase of 7% quarter-to-quarter so just think about
it we’ll start with quarter one so quarter two should show seven percent
increase from quarter one band quarter three would show seven percent increase
from quarter two and quarter four seven percent increase from quarter three how
would you do that so the easiest way let’s clear out this data because this
data is actuals and does not represent anything for us we would need to use a
formula we can foil in Excel we just select the value and we click type equal
sign and we’re gonna say value for Q 1 x 1 0 7 and 1y y 1 0 7 1 represents the
previous value we multiply just by 1 will get exactly 5700 but 0.7 is the 7%
increase for the q1 value and we’re going to hit enter and that’s exactly
what its gonna be now I’m going to copy this value and I’m
going to select the area where I want to paste this formula and that’s exactly
where I’m going to paste it and it will calculate q2 now 7% increase in all
categories from q1 now the cool thing about Excel look at this I’m gonna copy
this whole thing and now I can paste it in q3 and q4 and it will calculate a
relative formula now q3 would be calculated against Q 2 and Q 4 will be
calculated against Q 3 just look at that there you go so you don’t need to do it
one by one obviously you could and you can retype the whole formula but because
of the power of Excel we can project the Q 1 through Q 2 Q 2 through Q 3 and Q 3
through Q 4 that’s the power of relative formulas in Microsoft Excel
hope you have learned something and enjoyed this video make sure to click
the subscribe button to stay in touch with me on YouTube and join my email
list so I can share with you more information about my free training
courses hope to see you back soon again this is vadim lanka thanks again for
watching

You May Also Like

About the Author: Oren Garnes

100 Comments

  1. lol this is so basic. Tests are whole new level! conditional formatting, pivot, sumif ,count if ,data validation etc etc!!

  2. I just took the test a month ago and this is way too little information to past the Excel Certification test! This is barely a beginner's introduction!

  3. The constant self promotion was very annoying and took away from the point of the video, to learn! If you did it once or twice it would have been acceptable but it made the video longer and more frustrating to view. Thank you for the information, but consider your audience, I don't think children are watching and that's how I felt the heavy handed self-promotion was being delivered to me.

  4. Excellent teacher! I like your accent. Your demeanor comes across patient, eager to teach, ability to give relatable examples. English is very good well spoken. Comprehensive knowledge of the subject being taught with multiple strategies to teach.

  5. I have a exel test for school in 7 days, its going to be 20 questions. Can a make a video on passing your school exel test. I think the question might be of a program called simnet if you have heard of it.

  6. Very helpful video. I want to use excel for creating reports. Please advise if you have videos for data analysis

  7. Vadim, outstanding classroom instruction methodology, sir, very helpful, love the soothing background music, your meticulous attention to detail makes for indelible subject matter retention, Bravo! Bravo!

  8. OMG. This was perfect. I'm about to go to a job interview. This was a great refresher for any questions I may be asked. Thank you again!

  9. I need to assign specific numbers to specific letters, say i need to assign V as 1, I as 2, N as 3, O as 4, D as 5, can you please teach me how to do that in excel?

  10. This is a nice, simple video but you really should check your spelling and your totals before, or at least while, teaching. Wireless is spelled wrong (no, I'm not a spelling nazi but this is supposed to be a teaching video) and as dreamsicle8593 said below, your totals are wrong.
    If you look in the formula bar at the top of the Excel screen, you will see the formula for the total in E10 is D10*1.02 and the total for F10 is E10*1.02. That is clearly not a sum of the data in each column. (at 4:46 and 4:59)

  11. Thanks, I needed this to help me pass the certification. Wish me luck!
    Just one more thing, my test included stuff like:

    >Average values
    >Replace the words "Amber" to "Gold" in a category
    >Finding a hidden sheet tab
    >Sort category by color

    If I remember correctly it should be the 2016 version. I only managed to figure out a quarter of what's going on , it was so complex compared to my practice test 😓

  12. I took an excel test and they did not allow to use the mouse and short cuts. Do you have a video on how to do it that way?

  13. Yea, why is the sum of column F wrong? And why did you sort by alphabetical order and not value? largest to smallest is what the question asked for.

  14. i had an interview recently and one of the questions was how to add zero to a telephone number :)….so go to "format the cell"- "custom", chose "0" and add 10 or 11 zeros, until you see a 0 appears in front of the telephone number :). basically this type of info does not appear anywhere, so…there… there was also a question to copy the whole sheet and name and save into the same folder like the previous one…. :). one more question was "grouping" some data, there is a function under "data" functions.

  15. Well, that's 17 minutes of my life that I'll never get back. Besides these all being very basic Excel functions, which would certainly never be asked at interview, you didn't even follow the instructions.

  16. Thank you so much! I will be going for a job interview soon and I believe they will ask me if I know excel. I've taken notes on your video. It was easy to understand and follow. Thank you again!

  17. Thanks. At 15.50 to calculate 7% Q2 increase why did you multiply previous cell values with 1.07 . Can you please explain the logic behind this? Why are you multiplying with 1.07. Thanks again

  18. Thank you!! I just passed my excel test that I needed to be promoted!! 🎉🎉🎉🎉🎉😊👏😊

  19. https://www.youtube.com/watch?v=2Xobe36tPIA&list=PLSLKu1UaawspJqKX7RV0jzqXmdX-Z7UDO&index=5

    Need your support to grow ….

  20. You didn't sort from largest to smallest…you sorted by A-Z not the values. I could do without all of those corny faces for sure. It looks like you are pointing somewhere else. Just sayin.

  21. How do you figure out the percentage of Subscribers compared to the # of Views??? Hint: You aren't happy with the answer

  22. Don't listen to the negative people here! This was helpful for me, however my one critique was that you did not list from largest to smallest as you stated. But other than that thank you!

  23. I think he seems like a really nice guy and explains things without rushing. I need to take an assessment online and don't know anything about Excel, so he's great to start out with and I am also checking out other youtube channels. Keep up the good work!! 🙂 Some of us need the basics, not everyone is experienced at this.

  24. Hi Sir,

    i want to count unique number or text in file with if function in offline PLl Help

    formula of countunique is in google sheet but looking for same in off line sheet

  25. Great Video tutorial thank you for sharing your knowledge with us, I have a question do you have more Excel questions? the reason I asked is because the tests are actually 25 to 30 questions!!!

  26. Unless you are interviewing for an associate, entry level, or junior level position, excel is never this easy. I think the main things they want to know you know is the whole formulas, pivots, vlookups, etc.

  27. It help me a lot because I have take a lot of notes on the video and I have to take excel test in two weeks!! Wish me a good luck!!

  28. With all due fairness, the excel test can be much harder than this. Not really sure for which job this test would be for.But, if you have applied for analyst position, then rest assure they would test your knowledge of functions such as index/match, 3-d vlookups, rows, indirect…ect.

Leave a Reply

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