Watch Video
Our star trainers Stevie George and Allan Escobar are at it again! After a very successful first Teachers Lounge focusing on Word, we are setting up another Teachers Lounge to focus on how to help your students pass the MOS Excel Certification Exam using TestOut Office Pro.
Join Stevie and Allan along with some amazing instructors as they discuss what has been effective in their classroom when teaching Excel charts, tables, formulas, and other questions you've had about preparing students for the MOS Excel Certification Exam, including the following:
-
How to get students more experience when all they have is a Chromebook? Since our students use Chromebooks. Is there a shortcut, other than F4, for doing the absolute cell references?
Michelle: I have never actually had a Chromebook, so I was a little bit taken aback by this specific question, but my students have them all the time. One of the things that I did to find out some of those answers was I used CertMaster . CertMaster , has a support window that says, “Hey, how can we help?” And as you scroll down the support window, you'll see that ‘Tips for Chromebook users’ is the second article. It talks about how to deal with slowness or unresponsiveness and various things about the Chromebook and different things that you can do. And then this one says “Other common issues and workarounds like if you're unable to use the function keys and like I said, I can't do this on my computer because I don't have a Chromebook.
Also, you were asking about how to get more experience out of a Chromebook. When students have a Chromebook, a lot of times they can't do every single thing that's out there. They can't just open, for example, Excel and do the harder stuff that's Excel Expert related. There are a lot of things you just can't do in a Chromebook because Chromebooks aren't strong enough. We were lucky enough in in our county that they purchased a program called Cameo, which is basically just online Microsoft Office. CertMaster has some actual Excel activities. And you [can] to save them in your computer and upload them into Cameo and they'll work even on the Chromebook.Kim: We have gone this year as one-to-one. So, all of our students do have Chromebooks. Fortunately, I still teach in a PC lab. So, when students are on campus, they can get into Excel and get real application experience. But yes, CertMaster is great to have the feature to be in a simulated environment so that [the students] can get that practice. The Excel online through Office 365 is a stripped-down version, but they can still get some practice because you can still do your functions and you can still do some formatting and, some things like that. So, [for] coming up with some of your projects that you may do on a PC, we would have to kind of water them down a little bit. But, if you had to, students could get extra practice with projects using Excel online also.
Gwen: We also are using Chromebooks, and I'm also very fortunate to have a PC Lab. At first, we were not one-to-one prior to COVID. And we were told in our county, “We’ll give you Office 365. You can do everything you need with Office 365, with your Microsoft classes.” And the first three weeks were a nightmare. And then wonderful CertMaster , came into my life. In the Word meeting somebody mentioned it just being like a blessing. And it truly was, it was a game changer for us and our whole county. It was several months before everybody had Chromebooks but, what CertMaster did was allowed it for students on MacBooks, PCs, [and] the ones that did have Chromebooks, we were all able to do the same work. For that period of time, I was strictly using CertMaster . I could not do anything interactive with actual Microsoft Office that was live, because we just had far too many scenarios. But I asked for feedback from my students about their views on CertMaster , and they all loved it. Because, for the first time, they were actually able to learn and practice, and get immediate feedback. Even for the ones that did have Excel installed on their computers, they would have to share their screen [and] I'd be trying to analyze it to see if it was correct. And it was just not very effective.
About the F4 key, where it's going to make a cell absolute. I've taught all of mine how to manually do dollar signs if they want to, as well. And so that way if that's the main thing, [that] they need the function keys for [in] Excel, then they can just do the dollar signs in the formula bar. And it’s just a nice little quick way to get to it. -
I am using Office Pro with 9th graders. I asked them what they found most confusing. To quote: "Why do they tell us what to do first as the last sentence of the directions?".
Kim:This is a very valid question, because [the course developers] don't want to come run out and give you the answer or give you too many clues. I have found that a strong vocabulary is very important, and that's why [I use] all of the CertMaster lessons. And I have quizzes in different resources that I use to help reinforce that vocabulary so that they have an understanding of the concepts and they aren't just memorizing steps. And so that's very important. I've referred back to the Word Teachers Lounge, and Kelly on there had discussed how you needed to break down the task, read the entire task, find the location on the document, find location on the ribbon, and then the action. And it works the same way with Excel and even more so. Break it down, first look and see what worksheet you're going to be on because that is common, even in CertMaster . I'm so glad that CertMaster works that way. It doesn't automatically bring up the worksheet that you're going to do the task on. Neither does the Microsoft Office test. So, you have to first verify you're on the correct worksheet. Then, look at the cell or the range that you're working with, and then what command are you looking at. As an example of how a task may be written, be sure that you read the entire task. And I always tell my students, if you're unsure about it, don't waste time because that's a big factor: You don't want to run out of time. You can pass the test without answering every task. You want to make sure you get to the task that you know how to do. If you're unsure about it, mark it for review and move on. But if you do want to tackle it, then be sure again that you look at what worksheet you're going to work on. Are you on that worksheet? If not, switch to the worksheet. Then what cell or range are you going to work on? And then, once you get that selected, you can find out what the task is. Then find the ribbon or command that you need in order to complete that. That's what I advise my students to do to just be sure to read the entire task. Don't get too bogged down on all the extra words that there might be. Just pick out the worksheet, the cell range, and the task, and you can probably figure out that you know how to do it.
Michelle: I saw the Word presentation a couple of weeks ago and one of the comments that they made was about it being difficult to understand the questions. I did not feel that way, because I take the students, and I'll highlight the words in the questions, and I'll point at the screen, and match up those words in the questions with the thing in the ribbon [that] they're supposed to look for. And if they can just take and read the whole question and I really think that's so important for all students and adults too. Reading the whole question just makes a huge difference in whether or not they will understand it. Read it all the way to the end of the period. Sometimes I even make them read it out loud.
Gwen: We are [on an] A day/B day [schedule]. On [the student’s] off days, they’re 100% [on] CertMaster . And on their on day, we are working together in the lab using Excel. I always try to use information that they understand, like payroll, or car payments, which is one thing I'm going to show in a minute. Things that they're like, “Okay, yeah, I have a job, I get a paycheck.” And I try to make it where they then can understand things like net pay. I think when they're working in CertMaster , or doing the certification test, they need to first look at their data, figure out what type of information it is. So, then when they're telling you to do a function or format it, in a way it makes a little more sense to you because you understand the data itself. But I do think it's very important to read the whole thing and then, of course, breaking down that question. The very first thing I do is say, “What worksheets should you be on? Get there first. Now, what range were they talking about? What's in that range? Look at your data, now what are they telling you to do?” And if you break it down, it really does become a totally different type of task. And one other thing that I'll say is that I frequently tell my students, “Pretend you're telling someone that knows nothing about Excel, how to do it.” You would first have to tell them to go to that area and select it. And we have to tell the computer, exactly what to do, as if it was a person that had no clue. I always try to get them to think about it that way instead of thinking [that] the computer can figure out [that] they meant a cell they didn't have selected. I do have ninth through twelfth graders, but we do require at our school that you take Word before you take Excel. So, I don't have freshmen in my Excel class for that reason.
-
How to help students with What-if/lookup functions. Anything specific you do to help students remember?
Gwen: Before I started teaching, I used both of these religiously. I frequently tell [the students that] this might not seem like a big deal, but you will love it if you can use it. I did make up a little example here. I have two different things here. The first thing was, we were talking about car payments and how much of a car you can afford. When it comes down to it, it's really how much of a monthly payment can you afford? So, we were doing this for two different reasons: one, using the payment function, and the other, to say, “how much do you think you can afford in a car payment?” Most people are like, “Oh, I'm going to buy this car, it’s 35,000, and then they calculate the payment they’re I like “Oh!”. So, we actually have a lot of fun with this because I first ask about how much money they would like to spend on a car when they're 30 years old. And then we run the monthly payment and it's very high. I think it’s like $600 or more. Then what we do is we talk about how much a month could you afford? And my students figured out they can afford about $250. That would be about where they were looking. So we already had it set up for our payment calculator under Columns A and B, and so then we use the What-If [analysis], to see, “If I can only afford $200 a month, how much can I borrow?” I try to make it first, where we start trying to figure it out ourselves. So, we get in here, and we start playing around with the price. We play around with it and they're trying to hit the right mark. And so, then I'll tell them how the What-If will tell you straightforward what that amount is. I don't know if they want me to show it work but we always get something very real for them first. And I have just set this up so that we can play with formatting, borders, and shading. When I go to my Data tab then, to do my What-If. And I'm going to use Goal Seek for this one, because my goal is to get a payment of a certain amount. And I really break this down.
To zoom in on my screen I’m going to show the Windows and Plus key shortcut. When you do the Window and Plus [key], it'll zoom in on anything. When we are doing functions, I always make them open up the dialog box, and my students in Zoom, and my students in my class, can't see it well on my board. And so, if you do the Window and Plus [key], it'll zoom in on the whole window, and your ribbons, everything. It's really nice. It's been a game changer for us.
So then I’ll use the What-If, then I use the goal seek. This seems kind of confusing unless you really just read it like a sentence, or like a question. I always have them read the whole thing first, before we begin. It says, set a certain cell to a certain value by changing another cell. When we do this as a class if I tell them to do it by themselves to begin with, they don't know where to go. If I speak it to them, they can answer the questions like that. And so, I try to point that out to them. Pretend you're talking to a person. I'll say, “Help them to tell me which number we are looking for?” We're looking for our monthly payment to be a certain amount. And so, what cell is that in? That’s in cell A6, and what value do I want it? If I want it to be a $200 a month payment, then I'm going to get 200 and what cell would need to change? And so, we talked about a scenario where the only thing that really is going to vary here is our price, not our down payment. So, that's what we have saved. Then we just click on the price and then click OK. And then, it lets us know at $17,000 with a 5000 dollar down payment for five years, that would be $200 a month. And that's with zero percent interest. When we did this project, I had a student that said, “I know I will spend the money to have Excel once I get out of school.” Because we do things that they can relate to.
And one thing I was just going to say with Lookup functions. What I do with my students live is to try to give them practice. And also, for those that are on Chromebooks, we can still do a tremendous amount with formulas and functions with the Chromebook. We can't do things like naming ranges the same way that we can in Excel. But it works out pretty well. I'll give them a spreadsheet, and they've got all the information they need. What they don't have is the unit cost. Well, that's where a Lookup function comes in the hand because I have another tab with all the unit costs. That's where they have to do the Lookup function to pull up the unit cost. And one thing that I always try to explain to them is how important it is to get a spreadsheet set up to be useful in the future. So, if this was my business and then the price of baby food went up. I want to change it in one place, not in 10,000 rows of information. We did the same thing with the payroll. We had the different payroll taxes on a separate sheet, and I make them do external references. And that's a great way for us to practice absolute cell references. With this one, this is the type of project that I'll do closer to the end the week. I'll tell them to complete my form and they're like, “Well, I don't know the unit cost. I say, “You have to do a Lookup function. And how would you calculate total revenue? And how would you do a total cost and total profit?” And it's a great way for them to see the relevance of Excel. And I always tell them there's not one correct solution. There’s one correct answer, but there's lots of solutions. I just strongly encourage them to look at my data and look at my column headings. You know sometimes they won't even look at the headings because they're kind of used to learning material that's memorization. But when they make you do word problems in math, or when they make you do a math problem and say, “How did you know how to find the answer?” I tell them, “Excel is totally right there. You have to tell the computer exactly how to do it.” We have a lot of fun with Lookup functions and with What-Ifs.Michelle: When I do Lookup functions, one of the things that I remind them is that after they type that equals If or equals Vlookup or whatever function it is that they're doing, if they'll just look a little bit below where they're typing, Excel will tell them exactly what to type in next. It'll tell you to equals If and then what to look for. Then, to put the comma, and you got to remember to not put the space, and it demonstrates after the comma. Then, it tells you what to put if it's Yes, if it's true. It says it right there, underneath what you're typing. What do you want it to say if it's true? Then, comma and value if false. What do you want to say if it’s false? Then, close the parentheses and move on. Excel is very, very user friendly in that, it seems like it's so complicated to write some of these equations, but what Gwen said about reading them out loud as you go, it will really tell you what to put next a lot of times.
Kim: Yes, I agree with trying to make it real for them. And I've had a few students who have went on to Excel Expert and have achieved that which I'm very proud of. For those [students], I have some projects that I add an extension to. Maybe assigning a letter grade to the gradebook that we have using V lookup. It’s something that they can grasp and understand. And so, that just kind of helps them understand the concepts and that they can hopefully use to achieve that Excel Expert. Or even if they're not going for that, as they get into college or work, that they can see the value of those functions.
Gwen: One thing I'd like to add onto that, is that in our district, my classes are Excel honors, so, it’s Excel and Excel Expert. And before COVID, I would spend the first six weeks doing Excel and then the remainder of it doing Excel Expert because it’s so extensive. Because of COVID, I only have my students two days a week on site where I can work with them instead of five days. What I've done this semester, and it's worked out fantastically, is I incorporate Excel Expert at the same time I'm doing regular Excel. And an example of that is when we did some functions and then I took it to some IFs and then SUMIFs. Then I made them do if statements based on the SUMIFs. And they were going seamlessly. They didn't know they were getting to an expert level. And one day after we had been doing some IFs, extensively, I asked, “Do you find this very difficult now?” They said, “No.” And I said, “Well, this is expert.” And they said, “Really? That's awesome.” I replied, “Yeah, you’re just that smart.”
-
A lot of my colleague’s students have already told her that they do not wish to take the MOS Excel exam because they fear the formulas. How do you help students understand this?
Gwen: Formulas are what's fun to me and what I try to do for my students is tell them to think about it like a video game or a new smartphone. And a lot of these students are like, “Well, we haven't learned that.” My goal is to teach them that Excel can do unbelievable things and Excel has a lot of ways to help you when you're doing a function. We can pull up the dialog box, or the arguments, and it will give us descriptions of what to do. What I want to teach them is not how to do every function in Excel. I don't have time to do that. What I want to teach them is how to use the resources in Excel. And I explain it to them in the concept of a video game or smartphone, and I ask how many of them went to the Verizon store the first time they got a smartphone and took the classes. Because you can take classes on how to use a smartphone. And they say, “Oh, Ms. Barnes, we didn't do that.” And I say, “Well did your parents teach you? Who taught you how to use that? Because it's very complicated.” They say, “I figured it out myself. And same thing with video games.” And I'm like, “You know when you get a new video game, do you just sit down for a little bit and read the manual to learn how to do it?” And they're like, “No, I just start playing and figuring it out.” I tell them to think about Excel like a video game. And it's a challenge.
With the data, for example this is about selling these different items, like baby food and it’s to different countries. And what I do is I ask them questions like “How much baby food do we sell to Europe?” And that's my question. And they have to figure out how to get an answer for me. I like for them to tackle it like a video game. I think it's in Mario Kart where you can bounce up and get little coins. Well, I don't know how you figure that out. I figured it out because my kids told me. And so, I teach them that there are all kinds of little tricks like that in Excel. And one is for example, go into the Formulas ribbon. And if I'm having to do something with text, then go look at my options in Text.
And one thing that I just want to mention, and I will use the V lookup as an example, but if I know it's Vlookup and I start my formula, once I do the open parentheses, I can see [hints] at the bottom. That's great and very, very helpful if you're familiar with it. And [for] some functions, it's very easy. But for every formula like this, I go ahead and do Ctrl A. And what that'll do is go ahead and open up the dialog box where I can see all the arguments. You can also click on the little FX to the left of the formula bar. And the key is going to open up your parentheses. It’ll go straight into the arguments for that function. I make all of my students read out loud to me what a lookup value is. And I point out to them that this information is crucial. And with that, you can do almost any function whether you've seen it or not. So, it's really nice, it works out really well. Also, a big thing with a V lookup is your data source has to be sorted and they might forget that. But if they've done what I told them, and they read these instructions, it says, “The table must be sorted in ascending order.” So, if they forgot and they read that, then they’re like, “Oh, wait, I've got to stop for a minute and make sure that my tables are in the correct order before I get started.” And also, with this pulled up, as I began filling in my information to the right of the equal sign, it should be showing information. So, for this V lookup, I want to be looking up baby food, then where it says equal, it should be showing the word baby food there. Or if it was a number, or if it was a range, it should show all the values in that range. And I tell them that that way they can tell step-by-step, if they're maybe doing it correct or not because they can get immediate feedback before they even click OK. Even if my students think they don't need a dialog box and make them open it up. So, this really helps. And I do it like a contest. I’m very competitive. I'm not an athlete, I just love a challenge. I'll have students in Zoom, and I'll have students in my class. The ones in Zoom, as soon as I get an answer, they put it in chat just to me. And I'll give a shout out to that first person with the correct answer. For those in class, one thing that's always been really fun is, I’ll say, “When you think you’ve got the right answer, turn off your monitor.” And they will turn off their monitors, they'll be jumping around and see if anybody else has their monitor off. And the one thing I do is have them all turn on their monitors before it starts to become uncomfortable for the student that doesn't have it. So, I only use it for that very first [student],… and we really do it like a game. I will tell them how to do a function to begin with, but after that, I won’t even tell him what function we're going to use if we've already studied it. I want them to learn to figure out what function should they use.
One of the more interesting things I found with Expert is that when they have been used to doing functions but [in certain scenarios] it's just a simple formula that they need, they try to overcomplicate it. And so, I frequently incorporate things that are very simple as well to get them out of thinking, “Oh, we're doing Ifs right now.” And it really works well. I really did have a good time with my class, and they find it fun. I would say, for those that are not wanting to do it on the certification test, then that to me, means that they're not understanding how to figure out what they need to use. And so, you could just make a few little setups like this sheet that I have up where we're going to be doing multiple formulas and functions, and I want them to try to figure it out. One thing that's just worked out the best for us is when I make it a challenge.Kim: Just making them comfortable with that function dialog box is a big step. Once they can learn how to use that and read the information there, and even when they start selecting their cells or putting in their greater than or less than symbol and it says that it's false and they're like, “Wait a second. That's supposed to be true. Oh, I got my symbol turned around.” If they can learn how to use that then that really helps them understand it better.
Michelle: I actually start with going over what PEMDAS is so that they'll understand that we are using the same thing that they're using in a math class. And a few of them don't remember it or didn't learn it right. But most of them are really excited about like, “Oh, this is something I can relate to.” I start with using that and then I explain the difference between a formula that would use the things that we use in PEMDAS and a function which would use ranges and, named ranges and all those other things. It's exciting for me and I know some of the students get a little bit worried about those formulas. The really good thing about them is that it doesn't matter what kind of computer you have or what kind of software you have, you put that equal sign down, and you can start typing that formula or that function. I actually had a last-minute thing where I had to walk into an Apple lab because they were doing something in my lab. All of a sudden, my computers didn’t work and the only lab available was the Apple lab. I went into the Apple Lab knowing nothing and I had to teach a Numbers class with my Excel students. And I told the teacher, “I don't know what to do with this.” She said, “What are you teaching?” I said, “Excel.” She said, “Well, just use Numbers.” And I said, “What's Numbers?” And so, it works. You put the equal sign down and it still worked even in that Apple lab. I've even used it with OpenOffice and Office 365 and all that. The formulas and functions can be done with almost any type of software.
-
What project-based assignments have you used to engage students in authentic learning?
Kim: I have a few different projects. The one that I had replied about on Facebook was using our football stats. Back a few years ago, our junior high football team was on a winning streak and we won 50 something straight games in a row. I took that real data, and we went so far as to insert the data from a text file, to creating it as a table, and then this is the solution here. I've got the actual data file here with instructions. I usually do this as with the students as a class. But of course, with COVID you may have to revise it so the students can do it on their own. But we will go through and work through this. And then over here in Column J, we have all the different functions that we would use like which function would we use to find out how many years our winning streak went through, our average score, their max score, total zero-point difference. Those are just simple formulas or functions. In the actual columns, we did a subtraction for the point difference. We figured we’d do an If function for, “Was this a mercy rule?” We don't really have enough information to know if a mercy rule is called or not. So, we just say it is a possible mercy roll if the score difference was greater than or equal to 35. And was it a shutout? Did the opponent not make any points whatsoever? And they really like seeing that. And as you can see in the solution, we even did some conditional formatting to highlight, “Yes, we had those shut outs.” And then we put stars beside the ones that were possibly a mercy rule so that they could see that. Then we broke it down to find results against one of our main opponents like how many games did we have against them? You could take it further, how much was our point difference against them? Or something like that but I just break it down so that they can see real information and it makes it so that they can understand it better. They can count how many years we had a winning streak, or we can use a function to do it. That makes it easier for them to understand.
I also do a project where I would have them type in their information. They’d put in their gender, their age, their birthday, their brothers, sisters, favorite colors, do they plan to go to college, and make up some test scores. Just different things like that. And then when we go through and we open this in Excel, and they can figure out how many pets every student has, what's our average score, who likes pizza? And what functions are we going to use for that? And I do give them hints for functions. And then on the other worksheet, we would do some text functions like the concatenate, left, right, upper, lower, and proper. And for the first ones, I give them what the function is supposed to be, what the task might actually tell them on the test, and what they're supposed to do. And then as we go on, I leave off the function and hopefully by then, they can start trying to figure out how to decipher what that function is.
One last project that I do in the spring when we’re over into baseball, I've actually pulled up the Arkansas Razorbacks roster and got their stats. I came up with some instructions in order to do projects so we could do some more functions and some projects. And I've got a URL to all of these projects for you that I'll share where you can take home and revise them to how ever you want. We even do If Error functions. We do all kinds of things in order to reinforce that information. And then the main thing I wanted to do with this one was get into charts. And so, after we did all of our functions then we started doing our charts for different scenarios. And then we took all that information and changed it to softball. So that they wouldn't have to do any of the functions or the charts again. All they had to do is just put in the new roster with the softball player stats. And then, everything changed so they could see once you get your worksheets set up, all you need is to change the information. It's just so simple. Those are the three main projects that I use. They really like the function data practice, where I showed where they use their own information, and they liked the football stats one also. And then, this [last] one has been pretty good too.Michelle: I actually start with going over what PEMDAS is so that they'll understand that we are using the same thing that they're using in a math class. And a few of them don't remember it or didn't learn it right. But most of them are really excited about like, “Oh, this is something I can relate to.” I start with using that and then I explain the difference between a formula that would use the things that we use in PEMDAS and a function which would use ranges and, named ranges and all those other things. It's exciting for me and I know some of the students get a little bit worried about those formulas. The really good thing about them is that it doesn't matter what kind of computer you have or what kind of software you have, you put that equal sign down, and you can start typing that formula or that function. I actually had a last-minute thing where I had to walk into an Apple lab because they were doing something in my lab. All of a sudden, my computers didn’t work and the only lab available was the Apple lab. I went into the Apple Lab knowing nothing and I had to teach a Numbers class with my Excel students. And I told the teacher, “I don't know what to do with this.” She said, “What are you teaching?” I said, “Excel.” She said, “Well, just use Numbers.” And I said, “What's Numbers?” And so, it works. You put the equal sign down and it still worked even in that Apple lab. I've even used it with OpenOffice and Office 365 and all that. The formulas and functions can be done with almost any type of software.
Gwen: I would like to share one thing about some useful data for the students. In North Carolina, we have a really neat program called NC Star Jobs and it gives a lot of information about jobs in North Carolina. The really neat thing about this is I tried to explain to [the students] how useful Excel is even once you get out of high school. Almost any company you go to will likely use Excel. Even most databases are set up to be used with Excel through CSV export or just with Excel in general. So, this is a great database that tells them about different clusters of occupations, and what the actual pay is in North Carolina based on taxes. It's real data, and they can dig into it and get a lot of information. I'll point out to them that at the top, we have an option to download it as Excel or as a CSV. And to begin with I always make them do CSV files. Then we later import the CSV files into Excel and leave a couple of columns out and then when we export it and put it into Excel, the students can see how useful it is to get it out of this website and manipulate it in Excel. And we do things like look at what average pay is for the jobs that are high school diploma or equivalent.
-
What additional resources or fun projects would you recommend for students to complete as they prepare for the test?
Michelle: I do have a couple of fun projects. They're not difficult. They are some things that I start out with at the beginning. One of them is to create a cash register, and the other one would be to create a board where they play Battleship. In Battleship, each person sets up their monitors back-to-back so they can't see each other. And they set up a board in Excel, and they are allowed to have one aircraft carrier that's big and takes up so many cells and other little ships that are so big and take up so many cells. And then they have to resize their cells to make them a little bit bigger and they have to use color coding and all that. They hit at each other's screens by calling different cell numbers and they have to respond, “You hit my battleship.” or “You didn't hit my battleship.” And when they hit one, they have to color code the cell so they know that they found it and whoever finds all the battleships for the other team or the other person, wins.
Another that I do like a lot is I only need columns, A, B, and C, to make a restaurant. They actually use four worksheets where very basic stuff gets them started. On the sheet one, they're going to design an entry page, so that we know what the restaurant is. Then on page two they list 10 different items: maybe a Hamburger, soda, and fries. Then, they have to put at least 10 things into their restaurant and then they have to create a price for that item. Then, they have to leave Column C blank so that they can take orders from their friends. In Column D, they have to make a formula without anything in Column C. Sometimes, if they really don't get it, I'll let them go back and put something in Column C so that they'll understand that they have to go to column D and put in a formula that lets them multiply how many times the number that gets ordered. And then they go and take orders. In the first friend sheet, they'll have to copy it and go over to the next sheet and they have to rename the sheets by the person's name that they're working with. I can send them off into groups, with the Google Meet function for that, and let them take orders from the three different people. Next, they have to format the money as accounting format. And how many hamburgers does this person want? Maybe they want two hamburgers, one soda, and they don't want any fries. And then they'll have to AutoSum and they will get a total. They'll have to figure a tax, I usually give them 7.75. There's your subtotal. And then we put tax. And they we’ll say equals the subtotal times the tax rate. And then you have to explain that that has to be an absolute cell reference if you're ever going to do anything with it like use the Fill Handle with that tax rate. So, you do want to make sure that it always goes back to that same cell. And then you get a total here. Then the most fun part is for them is to figure out the change. How much did they pay you? And then they pay them, say $10, and then they have to figure out the change. They have to know that they have to subtract the bigger number minus the little number to figure out how much change they actually give back. -
Is the CertMaster program enough to be proficient on the test?
Gwen: I was excited to get this question because my first thought was that I don't know for sure because I have used CertMaster somewhat supplemental. Like I said, I did not get it until COVID. When we first got CertMaster , I had already completed regular Excel and was approaching the expert level but did not have any way to work with the students because like I said, we weren't even one-to-one [Chromebooks/computers]. So, this is when I got CertMaster and what I did was I assigned Excel and CertMaster to my students. And they loved it. They were like, “This was great review. I was familiar with the tasks that they were asking me to do.” I was very happy about that. Then in the fall, they used CertMaster at home completely by themselves. So, I do grades in CertMaster . What I do is, each week they have a section that they have to do. They are required to watch every video. They are required to read all of the literature and complete all labs. I don't do any of this with them. What I do in class is supplemental to CertMaster now. I expect them to do all of CertMaster , not to skip over any part. We do the practice tests that are in CertMaster . We do the Form A and Form B practice test. When we come in class, we are going over these formulas and functions, but just applying what they've learned in CertMaster to something real to them, like a car payment. A lot of times, they're like, “Oh, okay, I remember that in CertMaster and now I understand it better.”
So, the question was: Is it enough to be proficient on the test? I did not know because if they didn't completely get it in CertMaster , hopefully I’ve covered it in class. Well, I had a unique situation where a student found out the college that she was going to required that she be certified in Excel, Word, and PowerPoint. And this was one of the students that did not go through CTE. She was a bright student, but she was really panicking because she was going to have to take a course [to get certified]. I got permission to let her use one of our licenses and she completed CertMaster , not in Microsoft class and with zero help whatsoever but I just told her, “You got to use CertMaster the way it was intended.” You will need to watch every video, read all the literature, and do all the labs. When you don't know how to do something, look at how you missed the lab. Don't just say, I didn't understand it. Go figure it out. And she passed Word, PowerPoint, and Excel, all on her own strictly using CertMaster . So, is it enough? Yes. Do most students use it the way it is intended? No. Because they're like, “I don't need to watch those videos, I’ll go jump to the lab.” And when they do that, and they're asking for help, the very first thing I say is, “Did you watch the videos leading up to this?” And they say, “No, I haven't watched the videos.” Then I say, “Go watch the videos and then let me know if you still need help.” They figure it out then. So CertMaster to me is an exceptional program. The detail that has been put into place for CertMaster to prepare the students for certification is one of a kind. I don't know of anything that even closely touches what CertMaster can do. I think the key is making the students use it as intended. I tell all of my students before they certify is that we do not have nearly the amount of time to teach you everything that Microsoft offers. I learn stuff every day, I'm still learning things in Microsoft. So, it's just not the kind of course where I'm going to be teaching you every single thing you need to know. I am truly trying to teach you how to use it. Excel is set up wonderfully to help you find what you want to do.
I think that if they will take it like a challenge, like a video game. I frequently say, “Are you all ready for a challenge?” And I ask them to do something I’ve never mentioned before. They love it! They want to be the first person to figure it out. To actually be what is considered a certified Excel user, I should be able to figure things out that I've never seen before. I should be able to look at the data and pretend that somebody has asked me out loud, what were our total sales in that region? And you should be able to figure out how we're going to determine that. So, when they go into it with this mindset, they feel empowered. I always tell them how great they are, because they are. They're really good, and I tell them that they're so much better than they think. I talk to them like we're in a meeting. I pretend that we're in a meeting looking at data, and I'll call on the students, and ask, “Who had the highest sales in the east region?” And they have to go find that. I teach them that that makes them very valuable as an employee, that you can help analyze data. I do think CertMaster will sufficiently prepare them if they use it as intended. If they try to skip around and just do the labs, I don't know that they should be certified because that's not the employee I want. And, the wonderful thing is that it requires them to understand their data, it's not just a memorization. You need to understand the data, and that's the kind of employee I want. That has a paper piece of paper that says, I understand how Excel works, and I can apply it to data. And if I don't know how to do it, I understand how to figure it out.Allan: If I just might add something about that. I know that Excel prep, particularly in some areas, may require some more practice exercises apart from just going through our skills, challenge, and applied labs and even, the practice exams at the end of the course. I just wanted to make everyone aware of additional spreadsheet resources that we have in our teaching aids in CertMaster. We have some applied lab sample documents, Excel spreadsheet activity files, as well as some Capstone projects that you can use apart from what we have in the course already.
-
How do I get MOS Excel certified?
Michelle: You simply go to www.certiport.com. You set up an account. You probably have to work with your school system to pay for your certification test. You may have to pay for it yourself. Hopefully, all the school systems out there realize the value of taking certification test, and they'll help you to pay for it. But one thing, I also found is that there's another certification that is available through CertMaster . CertMaster also has its own certification. Under those Facts and Tips, I found that CertMaster has a thing called the Skills Guarantee, which if you get employed after you take the CertMaster certification test, (which is PowerPoint, Word, and Excel [in one exam]), and the employer, after hiring and putting this person to work for at least three weeks, finds that the person who has gotten that CertMaster certification doesn't possess the IT skills that is applicable to that certification then CertMaster is going to pay that company up to $1000. I’m just really stunned that anybody would offer such a thing. So, there are two ways to get several certifications out there, but I was really impressed with that CertMaster certification. And one opportunity to certify comes with every license. So, each one of our students, if they took all three Word, PowerPoint, and Excel, could go and take that test.
Gwen: I wanted to point out something that when we've had some countywide meetings, some of the other teachers did not know this resource was also in CertMaster . When you're looking at what is offered in CertMaster , I found that a lot of teachers were just going straight to the chapters and they thought that was everything that was there for Excel. And when I mentioned the practice tests, they had not noticed the practice tests down at the very bottom of the course outline. But these are great ways for the students to figure out how prepared they are going into certification. And I tell them if they're not proficient on that then they can troubleshoot back to the labs. And then, once they've kind of mastered this part, then I'll tell them to go to the MOS practice exams, which is Section B, and these are timed. And it's the same number of questions, for the most part, that the certification test is, they have the 50 minutes and that helps prepare them for the time. That’s one of the things that really gets them the most, is the time. And when they get used to the time then, they can take care of that stressor before they ever began the test. And I've seen very good results from students that do well on Form A, and Form B, and then they go into certification, and they just relax at that point.
Note: Some responses have been edited for length and/or clarity.
Teacher files shared by Kim Conant:
www.tinyurl.com/excelprojects2021
Teacher files shared by Michelle Lewis: