>> If you clicked on this video
you're probably wondering, "How am I ever gonna pass
the Excel 2019 or MO-200 exam when there's just no information
out there about this exam?" But don't worry. I'm gonna share with
you this practice exam with six projects that will show you tasks that are similar to the
ones you might run into on your Excel exam, so that you can be well-prepared. We're gonna look at chart
elements, new Excel functions, and tons of other skills you
need to learn for your exam. (bumper music) Hi there, my name is Mike, and I'm a teacher who believes
in accessible learning to help you unlock new opportunities. And in this video tutorial, I'm gonna help you try
and pass your MO-200 exam or the Excel 2019 exam. So let's get started. This first project is all
about Patreon members. So I thought I would give a quick shout out and a big thank you to my Patreon members for
supporting this channel. And if you want all access
to the practice files from this video and future videos, consider becoming a
patreon of this channel. You can sign up with the link
in the description box below. So the first task on
this Excel practice exam is gonna ask us to
navigate to the range total and delete the contents
of the selected cell. Now in the Excel 2019
version of this exam, you're gonna have to locate a range and also apply a range in formulas. So it's good that you
know how to find a range. So the way you do that or find a range is go to the name box here. This is called the name
box in the Excel window, and then click the drop arrow, and then find the name
range that you want here. I'm gonna click on the total one. It's gonna take us down to this cell, and then just asking us to delete. So I'm gonna click delete on my keyboard, and now we've completed task one. Task two is gonna ask us to
on the "Patrons" worksheet, in cells H6 to H20, we're gonna format the
numbers to no decimal places. So the way you can quickly
highlight a range like that is just type it in the name box, like we did in the last step. But this time we're going
to type in the range H6 and then colon H20. So really quick way to
highlight a big range like this, and then press enter. And then from here, we can do a few things. There's a few ways to complete this task, but I think the quickest one is to go to the numbers
group in the home tab. And then for your exam, you might have to increase
the decimals to two places or decrease them. So for this one, we wanna
click decrease decimal twice. So I'll do that with my mouse. And that's how you
would complete task two. Task three is gonna ask us to remove the table row containing the
patron, "Abraham sherker." And then do not change any
content outside the table. So that last part is really hinting that it doesn't want you to
change the whole row. So this is in row 18. If we wanna delete the row contents, we would just click here
and then right-click and then delete. But this is the table. So the table starts with
Abraham and then ends with $6. So we have to highlight this area. And then I think the
quickest way to do this is to just right-click
this highlighted area, go to the delete options, and
then click the table rows. And you're gonna see that row in the table kind of disappears,
and we've deleted it. So just keep that in mind. If it says delete a table row, you're not deleting the worksheet row. You're just deleting the
row within the table. Task four is gonna ask us to
navigate our way to cell M5. And then we're gonna
calculate the average income from the total column. And I'm gonna show you
a quick way to select a column within a function. So I'm gonna click in cell M5 here and then type equal, average, open bracket, and then you
can highlight the column. But I think a quicker way to do that or to select a whole
column is to press H:H, close the bracket, and then press enter. And that would probably
be the quickest way to complete a task like task four. So task five, we're gonna use a function
to create an email list by joining the last name of every patron with the address, "@patrion.com." So in earlier versions of the Excel exams, you would use a function
called the concatenate function to join values from two
different columns or rows. But in this 2019 version, you're gonna use something
called the CONCAT function. So it's just a shorter
version of concatenate. It's just CONCAT . And they want you to use
that for the 2019 version. I'll show you what I mean. So when I start typing CONCAT, notice that they CONCAT option is fine. And then the concatenate
option has like a yield sign. So they don't want you using that one. So on the 2019 one, you're just gonna use
CONCAT, I'll do open bracket, and then I'm gonna click the
insert function button here to show you how this is gonna work. So the first text string
has to be the last name. So I'm gonna click on this cell here. And then you can either type in quotation marks @patrion.com, end quotation marks, or
you can copy and paste. So I'm just gonna press Control+V. I'm gonna paste because I
copied it from the instructions. And you can do that on your exam too. So if you're worried about
making spelling mistakes, just copy and paste. So this says @, ooh I've gotta get the
quotations right here. So open quotation and
close quotation marks. And then here's a little preview. So it says Smith@patrion.com, press OK. And then the whole table fill up. So I'm just gonna click auto fit, so you can see that it worked. And that's how you complete
a task like task five. For task six, we're gonna remove the table
functionality from a table. And that's easier than it sounds. So we're just gonna click
inside of the table. And then from here, we're gonna go to the table
design tab and click on that. And then you just click
on the convert to range in this tools group here. So if I say click convert to range, watch what happens to the
range once I click that. And it says do you wanna convert the table to a normal range? We'll click yes. And now when I click inside this range, the table options go away. So that's how I know that
I've done that correctly. So the last task for this project, we're gonna use an Excel
feature that allows row five and the title to remain visible
as you scroll vertically. Now that last part really
tips you to the point that we need to freeze
that part of the worksheet as we scroll vertically down the page. So if you see remain visible
as you scroll vertically, that should tip you off that okay, they want me to freeze
this part of the worksheet. Now the tricky part about freezing is you don't select row five
to freeze that and above. You actually select the row
below what you wanna freeze. So it would actually be row six. So I'm gonna go ahead
and highlight row six, and then click in the view tab. And then I'm gonna go to the freeze panes
options drop arrow here, and just click the normal freeze panes because it's the selection that I want. So it's the freeze panes. It's none of these options here. So it's just this one. And now when I scroll down, row five and everything
above it remains there as I scroll down the page. (bumper music) Task one, we're gonna go
to the "Orders" worksheet, and then we're gonna extend
the formula in cell G2 to the end of the table column. So the way you do that
as click on cell G2, and then just go to the
bottom right corner, use the auto fill handle
and drag the formula down. That sort of copy and paste the formula to the other cells. As you can see, it's
the exact same formula, just different numbers
that it's referring to. And that's what task
one is asking us to do. Task two, we're gonna
remove all formatting from the "Order Amount Totals" worksheet. So we'll go over to that worksheet. And then in the home tab, in the conditional formatting drop arrow, there's an option to clear rules, and we have to be sure
which option we want. And the instructions are
asking us to clear it from the not the selected cells, but from the entire worksheet. So we'll click on that option, and you can see the conditional
formatting disappear. And that's how you would
complete a task like task two. Task three is gonna ask
us to format this table so that every other row is shaded. And we're gonna use a technique that automatically updates the formatting if you insert a new row. So that's just a fancy way of saying add banded rows to this table. On your exam you might have to add banded rows or banded columns, but the wording will
sound something like this. So if I click inside of this table and then click on the table
design tab that pops up, it's in the table style option group here, and then you either have
banded columns or banded rows. This question is asking
us for banded rows. Once I click on that, you can see that the rows are
now shaded, every other row. And it would automatically
update if we inserted a new row. Task four is gonna ask
her to take two columns and perform something called a multi-sort, which is gonna allow us
to sort the two columns at the exact same time. And then we're gonna add priority to the first one that we sort. So we're going to sort
the delivered to column, and then also the customer type column. So the way you would that is click somewhere inside the table, and then go to the data tab. And there's the multiple
sort icon right here. So we'll click on that one. We can just sort by one
column or we can add a level, and that's where this
sort dialog box comes in. So the first one is the delivered to. Now there's two ways you can
complete something like this. If we know that Toronto comes, so we want Toronto entries to come first, we know that Toronto is later in the alphabet than O, Ottawa. So we could just do this, Z to A, or you could do a custom list
where you did Toronto, Ottawa, and then add it. Both ways you'd get a
correct mark for that. So Z to A would work. Or a custom list starting with Toronto and then you'd say comma Ottawa, that could work too. So that's the first
column that we've sorted. Now we have to add a level, so we can sort multiple
columns at the same time. So then we're gonna sort
the orders alphabetically in the customer type field. So the customer type column, and it says from A to Z, and then we're going to click OK. And then you can see
all the Toronto entries are grouped together and then
by the customer type second. So that's how you would
perform a multi-sort in your Excel exam. For task five, we're going to enter a formula in cell J2 that uses an Excel function to return the net amount value
of the individual order that is the highest. So if we see that word, the highest, that is the maximum function
or the MAX function. So I'm gonna make sure that I'm in cell J2 and then start typing
equal MAX, open bracket. And again I'm gonna use
that little shortcut to highlight the whole column. So H:H and then close bracket, I'll press enter. And now the highest value
in that column is 8,623. For task six, we're gonna use an Excel data tool to remove all records with
duplicate invoice number values from the table, and then we're not gonna
remove any other records. So we don't wanna remove all duplicates, just the ones from the
invoice number column. So there's two ways you can do that. You can click inside. If this is a structured range, I would use the data tab and
then click remove duplicates. You can do that here
too, wouldn't be wrong. But because it says values from the table, I would actually do that
within the table design tab, and then click the remove
duplicates button here. So I'm gonna click that, and then I'm gonna unselect all and only choose the invoice number column because we don't want to delete the values or duplicate values from these columns, just the invoice number one. So I'll press OK. It says there's one duplicate
and it's been removed, and we'll click OK. And that's how you complete
a task like task six. (bumper music) For task one, it's gonna us to configure
the profit analysis worksheet so only sells A1 to H21 will be printed. Now there's a few ways you can do this, but there's only really
one right way to do this. And I'm gonna show you that way. So the right way to do this is to click on the page layout tab. And then to make it easier, I'm gonna show you the
page break preview view. So first of all, we have to get rid of
this page two line here. So I'm just gonna drag it
off of the selection here and drop it in this gray area. And then from here, I'm
gonna type in the name box. I'm gonna type in the range that we need. So it's A1:H21, press enter. That's gonna highlight
the area that we need. And then inside of this page layout tab in the page set up group, here's
the print area drop arrow. Click on that, and then click
on the set print area option. And once I click on that, the area that we need, A1 to H21 selected, and only that area will print. And now I can go back to my normal view, knowing that only that area will print and I've completed task one. So for task two, we're gonna look at the
Quarterly Profit Analysis table. And in the product code column, we're gonna filter out anything
that doesn't start with FA. So we want these three values to show. And we don't wanna
delete these values here, but we just wanna hide them for now and kind of just filter them out. So the way you do that is
click on the filter button. And there's two ways you
could technically do this. Because this is a short list here, I could just unselect anything
that doesn't start with FA. But if you had a really long list, that might be a little bit trouble. So a quick way, if you
had a really long list, you could click on the
text filters option here and say click on begins with. And then begins with,
just type FA, press OK. And that's how you
could quickly filter out if you had a long list
values that start with FA. Okay so before we tackle task three, I want you to stop and think, which function are we
using in this scenario? Okay so I'll read it out. And you think about which function will you use in this case? So in the "Best Seller" column, use a function to display the word "Yes" if the value of the product
in the "average" column is greater than 10. Display the word "No" if
it is not greater than 10. So did you think we're
using the AVERAGE function, the AVERAGE IF function,
or just the IF function? And it's just the IF function, and we know this through the question because there's two outcomes; yes or no. So on your exam, if you have a task like
this, that has two outcomes, maybe it's something like yes/no, over budget, within
budget, that kind of thing, two outcomes, we're using the IF function. Okay and this is how we would use the IF function in this scenario. So I'm gonna click in cell H5 and then start typing equal if. And we're gonna look at the value in the cell next to this
one and see if it's over 10. Okay so equal IF open bracket, and then we're gonna click the
insert function button here. So we have three things we
need to fill out this function. So we have a logical test
which is in the question, the value of true which
is pretty straightforward, it's just yes. The value of false is no. Okay so that's the easy part. The hard part is sort of telling what is the logical test and
how can we know it's true? So look at the question, it's asking if this
cell is greater than 10. So that cell, or maybe it's a name range, you might have to deal with a name range where you type the name of range in there, and then is it greater than 10? Okay so that's our logical test, and we see that the value
is true and it should be cause if we can see the number is 18.33, so obviously greater than 10. Then we have to put in our true value, which is "Yes" with a
capital Y in quotation marks. So if you're using a text value, you have to put quotations around it. And then a "No" if it doesn't meet that criteria or criterion. So close quotation marks, and this is gonna come out as a yes. And we press OK. And then you see for the, because this is a table, it'll just fill
automatically down for you. So this one is yes, yes. And this was a no because obviously it's not greater than 10. So that's how you would
use the IF function. And I hope that makes it easier to spot if you're dealing with an IF function. So look for those two outcomes. So in task four, we're gonna
use a simple operation. So on your exam, you might be asked to add,
subtract, divide, or multiply. And it's just a straight
sort of like two values. You multiply them, you divide them, or you subtract them or add them. In this case, we're gonna add these two numbers. So the way you would
do that is type equal. And if you were adding them together, click on the first value,
and then plus this one. Or if these were named ranges, you'd just put in the name plus, and then this name range
here, and then press enter. So you might just have a
simple operation like that. So if that falls within the functions and operations portion of your test, so you might have something like that. So if you're not sure how to add, subtract, multiply, divide, you might want to review that. So for step five, we're gonna create a
clustered column chart that shows the description of the product in the months in quarter
one, so January to March. And then we're gonna use
the product description as the horizontal axis labels. And then we're gonna place the chart to the right of the table. So this one can be a
little bit challenging because we have to collect
two different ranges here that aren't adjacent to each other. So we will highlight the description area. I'll press Control on my keyboard so that I can also and simultaneously grab on to the January to March
area with the numbers. And then I'll go to
insert, I'll choose charts, and then all charts here. And the first one that comes up is the clustered column chart. So that's the one we want, we press OK. Now you can just double check. So I'm gonna put this to the right, like the question's asking. You can double check that we've got the right horizontal axis labels here. And this is obviously the right one. But just to show you just
so you can double check, when you go to select data. If you look at the horizontal axis labels, right here we have the description. So that's what we wanted. The months are the legend entries. So when you press OK, we know that we've done this correctly because the description is in
the horizontal axis labels. If it wasn't, if it was the month instead, you could always do this,
switch row and column, and then it would switch it to the months being the horizontal axis labels. But we'll just switch it back. And that's how you would
complete a tasks like task five. For task six, we'll
add the alt description "Units sold in Quarter one" to the chart. All descriptions are really
great for pictures and charts. Maybe someone has some visual challenges, so that if they had a program set up, the alt description would
describe the chart to them. So that's why you would add
an alt description to a chart, or picture, or table
in an Excel worksheet. So we'll click on this chart to do that, and then click on the format tab, and then we'll click
the alt text icon here. And then I will just copy
and paste this text in there. So just to see if it's time, I think that's the easiest way to do that. And then just leave
out the quotation marks if you're doing something
like this on your exam, and then you can close
that and it'll be there. So when you click alt text again, there's descriptions right there. So for our last tasks in this project, we're gonna insert column sparklines to represent units sold
from January to March. And we'll just start in cell I5 here. So I5, you would go click on that cell. And then in the insert tab, you're gonna look for
the sparklines group, and it wants a column
sparkline to represent. And the data range is
from January to March. So we have where the sparkline
is being put right here, and the data range that
we want, we'll press OK. And then what's great about
that is just like a formula, you can copy that down and then you have a visual representation of the first quarter and
how many units were sold just quickly within a cell. (bumper music) Task one in project four
is pretty straightforward. We're going to click on AI. And then in the home tab,
in the alignment group, just select align left. And you'll notice that the
text July sales goes over to the left of this merge cell range. For task two, we wanna generate a code
in the "code" column by using a function to
display the first two letters of the "Business Type" from column D. So that is the left text function. And the way you would
do that is equal left. And then think about the
right function as well. The right would work the opposite way, where it'd be the last
two letters of the word. So if you wanted the first two letters, you'd use the left function. So equal left, and let's see the function
arguments, boxes that we need. So the text is group, or if that was a name range,
it would be the business type. And that would be the
number of characters is two. So two from the left and you
see it like a little preview. So that's how we would generate our code from the first two letters of
the "Business Type" column. Press OK, and that kind of fills out. So now we know we have a short form code for these business types. So for task three, I thought I would try a
new conditional formatting that I haven't added
in my previous videos, which is the traffic lights
to format the values. So let's see what that looks like. So we'll highlight the amount column. So we'll highlight the amount column and then I'll click on the
conditional formatting, which helps you spot
patterns in your data. And I'm gonna use the traffic
lights or an icon set, and it's the unrimmed traffic lights. So the three traffic lights
unrimmed, that's this one. And it kind of just very
vaguely gives you sort of like which numbers are higher,
which ones are medium, and which ones are pretty low. So green is higher numbers, yellow is sort of medium numbers, and red lower numbers. So it just helps you visualize your data, maybe spot the higher numbers
and where they're coming from and that sort of thing. So that's how you would add the three traffic light
conditional formatting to this column. So for task four, we'll change the color
and style of this table to "Olive Green, Table style medium." And so we'll click inside of this table. That's gonna allow this
table design tab to pop up. Once I click on that, the
table styles gallery is here. I'll probably have to click here to look at all of the table styles. But the one we want is right here. So the olive green,
table style medium four. Wouldn't be a bad thing to
sort of just skim around and just get to know where
some of these styles are. This is the light group, the medium, and then also the dark
group as well at the bottom. But the one we want is right
here and I'll click on that. And that's how you would
complete task four. So for task five, we're gonna see if we
could match the chart color with the table color and apply the monochromatic
pallet three to the chart. So when you click on the chart, you'll click on the chart design tab. And then over here, there's an option to change colors. So click that drop arrow, go to the third color in
the monochromatic group, and it's monochromatic pallet three, and that one will help us match the colors for the table and the chart. So for our last step in this project, we're gonna go to the chart
and then display a data table under the series without legend keys. So we'll highlight the chart first, we'll click on the chart elements. So if you ever have to change
anything about a chart, it's probably in the chart elements here. We'll click on that, and then we'll click the data table, and then we'll look at the options and just make sure that
there's no legend keys. So apparently there is, we're gonna change it to no legend keys. And this is what that should look like if you've done this correctly. (bumper music) Now for tasks one in this project, we're going to import
data from a text file. And then we're gonna use the first row of the data source as headers. So in cell A1, we're going to click on that cell and then click the data tab, and then click on from text or CSV. So click that icon. And then on your exam in
your documents folder, you'll see the text file
pop up when you do this. So I'll double click on that. And then the preview looks great. It actually already has the
first row of data as header. So that's already done for us. But when you don't wanna do
this click load right away, you wanna click load to, because sometimes in the settings it'll just load to a different worksheet, and that's not what we want. We want it to load to
an existing worksheet. So just make sure that you change. If that's the setting that was there, change it to existing worksheet
in A1 and we'll press OK. And then we have our new table. For task two on this "items" worksheet, we're gonna adjust the
column width of column A to exactly 21 points. So we'll click and highlight column A and in the home tab on the right side, there's a format drop arrow. So we'll click there and that's where you'll
find the column width and how to adjust that column width. And then we'll just click
21 here and press OK. And now we've sort of
extended it just a little bit to exactly 21 points. For task three, we'll go to the sales worksheet and then we're gonna
move this pie chart here to its own chart sheet. And then we're gonna name that chart sheet "Quarterly sales". So the way you would do that
is first select the chart. So I'm gonna take my
mouse, select the chart, and then go up to the chart design tab and we'll click there, and then we're gonna
choose the move chart icon. And there's two options here. We have the move the object
to a different worksheet, or we can create a new sheet. And that's what the
task is asking us to do. And this is where the name would go. So I'm just gonna quickly type that in. So quarterly sales and then press OK. And then what you should see
is the chart kind of blow up into its own chart sheet. And it should be sort of down here where your worksheets are located. And it doesn't matter where I
placed this new chart sheet, just as long as it pops
up as its own chart sheet. Task four is gonna ask us to
go to the "Merch" worksheet, and then we're gonna modify this chart so that dollar amount or
the word dollar amount is the primary vertical axis title. So sometimes when you
get a task like this, the axis titles won't be visible. So you have to turn them on
through the chart elements. So we'll click on the chart first, and then go to the plus sign
that represents chart elements. We'll click on that. And then you might have to turn them on. So we'll turn axis titles on. And then the primary
vertical one is right here. So I'm just gonna click
somewhere in there, delete this placeholder title here, and then just retype in dollar amount. So just make sure it's
spelled the same way as dollar amount in the question. And then we can click
outside of the chart, and that's how you complete
a task like task four. Our final task for this project is gonna ask us to remove
the legend from the chart and display the values as
data labels above the columns. So again this can all be done
in the chart elements options. So we'll click on the chart, and then we have to do two
things within the chart elements. We have to remove the legend, so we can just unselect this. So the legend disappears
and then add data labels. And there's few options. But the default option is what we want. If yours wasn't like that, you could just kind of
choose your options here. It doesn't specifically say
to do anything like that. So we're just gonna leave it alone. But if it said outside end
or something like that, more specific, that's where
your options would be. But it doesn't tell us where to put them. It just says above the column. So the default gets us
exactly where we need to go. And I would just leave it like that to complete a task like task five (bumper music) For the first task in project six, we're gonna copy the formatting
of the title and subtitle on the "Documentation" worksheet to the title and subtitle of
the "Menu items" worksheet. So that sounds like copy
and paste in some way, but it's actually using the
format painter option in Excel. So I'll show you how that would work. So we're gonna go over to
the documentation worksheet, and then we wanna copy this formatting. We don't wanna copy the words, but we wanna copy the formatting and then apply this to
an already existing title and subtitle on the
"Menu Items" worksheet. So the way you would do that is highlight cells A1 and A2, that's where this typing begins. And then we're going to click
on the format painter once. So we'll click once, we'll hop on over to the
Menu Items" worksheet, and then just highlight A1 and A2 on this worksheet as well to
apply the same formatting. And as you can see, it's applied to same formatting, and we saved ourselves
some time from reformatting this title and subtitle. So for task two, we're gonna name the table on the "Menu Items"
worksheet as "Units_Sold". Then you have to be careful with this one because some people will go
do that in the name range. And when we give a table a name, it will appear as a drop arrow
option in the name range. But that's not where we create the name. So you don't create the name range here. That would be for a structured range. So if you had a range like
here that's not a table, we could create that name
range in the name box here. But when it comes to a
table and naming a table, we actually click somewhere
inside of the table. And then we have to go
to the table design tab. So we'll click on that. And then this is where we
would rename the table. Instead of table one,
we'll call it Units_Sold because you can't have two different words in the name of a table. You have to link it with an underscore or something like that. So Units_Sold, capital U capital S. We'll press enter. And now we've named our table, and we could come back to this table. Let's say we're outside of the table. And this will appear in the name box. But when you're creating a table name, you have to do it within
the table options. So speaking about name
ranges from the last task, you might have to use named
ranges within a formula on your Excel 2019 exam. So this is very unique
to the Excel 2019 version of these types of exams. They want you to use more name ranges when it comes to formulas. These can help you avoid mistakes if you set up name ranges ahead of time and then use them within a formula. So we're gonna do that
here for task three, and I'll show you how that'll work. So we have four different ranges and we're gonna add them together. And there's a few ways you could do this. But I think the easiest way
is to type an equal sum, and then open bracket, and then just kind of list
the four different ranges. So I'll start typing
them and they'll pop up. So to save time, I'll just start typing them
and then just click on them. So Speciality_Total, and then put a comma. We'll do the next number,
which is Smoothies_Total. So no space, just put
commas in between them. And then we'll do the next
one, Sandwiches_Total. And then I'll just
click just to save time. And then comma Soups_Total. So I'll just make sure I've got all four and they're different; specialties, smoothies, and
sandwiches, and now soups. And then close the bracket
and then press enter. And now we've got like a grand total and we've used name ranges in the formula instead of cell references. For the fourth tasks in this project, we're gonna go to cell K48 and we're gonna use a
function to calculate how many missing entries there are for the month of September. So again this is new to the
Excel 2019 version of this exam. And this is the COUNTBLANK function because we are trying to
find out in the column, how many blank entries there are? How many blank entries have we missed? And that kind of thing. So how many entries are we missing? So that's what we're trying to count. That's a function called COUNTBLANK. So I'll type equal COUNTBLANK right here, and then open bracket. And then I'll just select this range here. So select this range, and that's gonna look through the column, see how many entries I am missing. So I'll close the bracket and press enter, and that's how many I'm missing. So I'm missing five, five
entries in that column. And that's how you would
use the COUNTBLANK function on your exam. If you need more practice again, check out my Patreon page to get access to the practice files. Or if you wanna to take a deeper dive into what will be on your Excel 2019 exam, I've got a whole course on
Udemy that can help you out. I'll put the link in the
description box below. And one more thing. If this video helped you at all, remember to share your
success at the comments, and good luck studying.
