Hi, and welcome everybody today
I'm going to be doing a video request on Microsoft excel 2016 and how to pass your mos excel exam?
so emily asks can you maybe do a video on the functions that we're going to encounter on the mls test and how they
might be seen in G metrics
so I just did a video on G metrics make sure you check that out if you have it already and then let's take a
Look at what we're going to see on the Mls exam
if you're not aware the Mls exam is the certification exam for Microsoft excel and
Basically I'm going to this test last week
And so I'm going to go over
What I encountered on that test and the best
Practices for you to make sure that you could pass that test as well as what you're going to see on it
so let's take a look at my screen here and
You can see that. I have a spreadsheet right here
What's on the spreadsheet isn't too important?
But these functions and different things that I'm going to cover throughout this video are basically what you're going to see on
The mos Exam so just to get us started
the
First thing that you might not know where it's at is it asks you to change the backstage
Title to something else you could find that in the file tab and you just go right here to title and I'll just call it
Mos Prep, whatever they ask you to change it to in the exam is fine. You just go [right] [there]
You change it you press [enter] and you'll get the points for that version or that part of the exam
Another area that it asks you to change that's not necessarily a function or formula is right here
it'll ask you to basically hyperlink a website, so if I wanted to make a website for City of specific [abay]
I would type out city of pacific a bay right there, and then I'll go to insert
Tab links group and click hyperlink okay in the test it will bring up a page that looks like this
And you can display the text that you want to display right there, and then right down here
You [would] display that website, so you would just type in the website that it tells you to
In the exam, so I'll just type in whatever website you click okay, and then you'll see it'll change the hyperlink, okay?
So that's a little bit about backstage
View and the Hyperlink now. Let's look at some of the different things that it asks us in terms of the
Formulas and functions that you need to cover
So one [of] the ones that kind of gets people a little confused is
Asks you in the test for the average over a specific number
And so I put that inside of this column here
[and] so basically we have our quarter one through four sales, and then we have all of these different
Products over here, so let's say I wanted to get the average sale for every sale. That's over
400,000 well right here. I would click inside this cell, and I'm going to create an average if formula
So I'm going to go to the formulas tab
It's in my recently used, and it's right there average if you could probably find it also in the more functions
statistical area and then you go down to
Average if you click that then it's going to ask you for the range that you want to choose
So you'll choose the range that is in your test in this case
I want the [quarter] [1] 3/4 for for salary and benefits and then the criteria
I'm going to basically just tell it to look for everything over
400,000 right
Ok and then I click ok and it's going to give me the average for all of the numbers [that] are over
400,000 in this case, that's all of them, but down here
We'll see that that formula will work out a little bit differently so [we] see that since there's no numbers above
400,000 here is that you can't divide [by] 0 same thing here
[so] you should see the difference between the two and that shows you a little bit about what average if does as well as
really how to
Work that function and complete that function all right, so let's see here. Let's also. Take a look at the
that what it asks us to do in terms of
if functions, okay and also
Let's look at one of the main functions before that let's look at the [autosum]
So if you highlight an entire area plus the one below it or the one to the side
One cell two below or one cell to the side you could use autosum. So you just go to home tab
Editing group and you click autosum
And it's going to fill in all of those numbers, okay now keep in mind
This number is not going to work right there because you can't divide by zero, so I'll just get rid of that
but now we have our
our total numbers four quarters one through four and then one of the
functions that it definitely has mos test is the if
Function so let's see where we would find that you're going to go ahead and click on this area for goal status
And you'll see that right here. Let's say. I had a goal of
[two]
2.3 million, okay, and
So I want to hit that 2.3 million goal
Based off of this number right here, [so] I would go to the formulas tab [I] would go to
recently used
but you could also find it in logical and go to if now if it's definitely on the mos test so you'll do a
Logical test all right, and so you'll see right here
[I'll] click on the cell [that] I want it to
be based off [of] and so that's [b] [10] and so if [B] 10 is larger than
[2.3] million
If that's true, then my goal is a success
success and
My goal is a failure. If it is not true. Let's call that right, so there we go
All right, so you see how that's set [up] right there, and then you can basically see that
Right there. It should come back with a failure right, so I?
Click ok and then I see failure is now
Shown right there now you can take that formula use the autofill move it all the way down [three] [or] four quarters
success failure success failure so on and so Forth all right
So that's how an if function works now
Finally it's going to ask you to do
Conditional formatting so make sure that you do know how to do conditional formatting before you take the mos test
I have a video on conditional formatting so make sure that you check that out if you haven't already done that and
Then let's take a look at how conditional formatting works, so I'll select these four cells right here
and I'll go to the home tab and
In the styles group you're going to see this button for conditional formatting now basically I wanted to return a positive
Conditional Format, if it's a success and a negative if it's a failure
So I'm going to go to conditional formatting and I'll choose highlight cells rules
And then I'll go text that contains and any text [that] contains the word success
should be basically with a green fill or
Something positive right so I go to green fill and now we see every time that word success appears down there
we're going to get that green fill and now let's do the same for failure only with a red fill so text that contains and
I'm going to type in the word failure oops failure and
Then I'll make that one light red fill keep in mind in the test
It might ask you to do custom format, and you could do that right there
And you could change it to whatever style that you want in that area but for this one to keep the video short
I'm just going to do a red to show a
success and
failure and how that looks with conditional formatting all right now what I'm going to do is I'm going to create a graph and
Show you the different graph
or chart
options that it's going to have or
questions that we'll have in the mos test so I'm going to zoom out [just] a little bit so you [could] see more of the
Screen, and I'm going to just create a column chart based off of this okay, so I'm going to go to the insert tab
and then I'm going to go to
Charts, and I'll just choose a column chart one that looks good
We'll just do that one alright, so in the test it definitely asks you to
change the chart title so I'll click right there on chart title and I'll just call it a
yearly
Let's see yearly sales
Okay, and so that's how you change the chart title you just type it out and press enter and then let's say I we click
Back on our chart area now what it will ask you to do is change something down [here]
So let's say I wanted to change quarter one quarter two and quarter three and quarter four to just say q one two
Three and four how I would do that is I go to the chart
tools design tab and I go over here to the tate data group and I click on select Data and
Then I could actually edit these quarter numbers, so instead of it saying quarter one
I'm just going to type Q1 as the serious damn, and then I'll do it with these [other] ones as well
You click on quarter [2] you click edit and you do [cue] to
click on quarter 3 edit
Q3 so on and so forth and you [can] see that
it's changing in the graph below as well, so they will ask you to change the series name in the mos exam, so
Now that that one's done that one's looking good, and so now let's also
take a look at how they want you to format tables okay, [so] I'm just going to create a table right here, and
let's say I create a table based off of
Let's go with all this information right [here], okay now
it'll ask you to create a table and so you go to insert tab and table and
So I create a table right there my table has headers, and so now it turns it into a table
[alright], so inside this table
It might ask you to display a total row you just click right [there] on
table tools design Tab table Style options
And you choose total row you could use this total row to total up different areas like you know, maybe
Count the different number of or let's see what would the maximum in all of the quarters and [you]?
[could] use this total road to do different things like that, [so]
It will ask you to use the total row it will also ask you to
Create banded rows right here, and so a home tab or sorry a table tools design Tab banded Rows
You can turn those on and off and you can see how those [bink] the chart appear with different colors in between each Row
Okay, so I'll just leave them on and then finally it'll ask you to remove duplicates
So let's say I look through here on Column 1 and I want to say hey let's remove any duplicates
So [you] could go to table tools design Tab tools group remove duplicates?
And it will look through all the columns and remove the duplicates [okay] [in] this case
I had no duplicates in that lem os test. You will have some duplicates to remove [ok] alright
So that should help you out with everything that you need [to] know for a table
Except you also need to know how to convert that table back to your range so go to table tools design Tab
Under the tools group and you click convert to range and [that] will just remember back to normal cells, so there we go
finally as far as charts and
Sparklines go we're going to go to this area that I put down here called Column Sparkline and
Let's say I wanted to create a [sparkline] for it. I would go to the insert tab
Sparklines group create a Column Sparkline and then I can choose that data range of quarters 1 through 4
Click ok and it will ask you to do that and that gives you a little graph just to show on one
Page there or on one cell rather and you can copy that down to check out all of the different [Sparklines] throughout that area
So that should be everything that you need [to] know for [Sparklines]
And then finally let's say that you have to have rows that repeat at the top
for instance you got to print this out [or] you print out a
Large piece of data and let's say we wanted Row forward to repeat at the top of every time we print
Then I would go to the page layout tab and I would go to the page setup
group dialog box in the bottom right of that group and
Then right here under the sheet tab I would click rows to repeat at top and then I click
Column 4 or sorry Excuse me Row 4 right there
And now that [will] repeat at the top every single time that I do a print preview [ok] all right good deal
So I try to get through that kind of quickly. I hope you enjoyed it
I hope it helps you [out] for your mls exam
That's pretty much
The formulas that I could think of off the top [of] my head that you're going to [definitely] need to know how to use
for the mos test
that you have coming up, so
Emily thank you so much for the question
I hope this helps you out and for everybody else give the video a thumbs up please
Remember to subscribe to the channel
And you can have a video request yourself if you just put something in the comment box below and I can make a video
Based off of your question. Thank you so much for watching and I hope you tune in again soon