Master the MOS Excel Practice Exam 2 with Voiceover || GMetrix Practice 2023 || Excel 365-2019

welcome back everyone thank you for joining me in this video where we'll Master the second Emirates Excel exam practice I will link the first video in the description or in the card showing on your screen right now so you can watch the first video in the first video I shared with you the Essential Software valuable tips and useful resources to excel in the mwx professional exam and I also do that here in the second video by the time we conclude you'll be fully prepared for the exam and also tackle the most challenging tasks the professional exam presents and before we start please take a moment to subscribe and like this video the support will help us reach more people who may benefit from the content now without wasting any more time let's jump right into mastering the second MOS Excel exam practice okay guys welcome back to the exam practice 2 the practice that we have to go to the geometrics and we'll input our login credentials into it okay so to practice the exam 2 you have to go to test and before you go to test make sure that you redeem your code to be able to access all tests required to take the professional exam this code will be provided to you by your Clash reps in your University or school or your lecturer so make sure to redeem your code to be able to access the test to go back to test and go back to New test go to new test 2019 Excel 2019 and this is the one we're taking right now Excel associate 2019 practice exam 2. in the first video we did this the training mode I was called 100 over 100. if you want the video for this you can see it in the card displaying above your screen right now or you check description box below you'll be able to find the video for this and these are the only two practice exam that you need which is the official practice test so to take the practice exam 2 all you have to do is click on training mode and start the new tests so we have five tags here as usual tax one tax two three four five and we also have projects one of seven understood the way it goes is once you're done with this tax this is the instruction for the tags and this is the worksheet you'll be working with everything above here is geometrics and everything below here is your Microsoft Excel 2019 so once you're done with each Stacks Your Mark completed and you go to the next stacks and so on once you're done with the fifth Stacks all you have to do is grade your project so that it takes you to project two that's all you need to do let's start this and good luck guys so the first question says navigate to the named cell range up adding 2 and delete only the content leaving the empty cells all you have to do is click this drop down this is where you find the cell range click this drop down and select up adding 2. this is the content in app editing tool to delete the content inside and not the sales all you have to do is right click on your mouse and click content click content will clear the content inside but not delete the cells moving on Mark completed and go to the next start this tag says on the downloads worksheet adjust the height of row 27 you can scroll down and go to row 27 here click it this will select the entire row to select the entire column you select this to select the row click this and it says adjust the row 27 to height of 78. to do this all you have to do is right click your zero right here click it and change this value to 78. click OK and you can see the full content of the cell you're done with this tax Mark completed go to the next task this task says apply the cell style light blue 40 Ascent 2 to cell 827 to apply a particular style to a cell you have to go to the cell first a27 go to 827 you're on the cell and you can see the Styles here for the cells all you have to do is click this drop down and select the right style for the cell click it as you can see it has been applied all you have to do is Mark the tags and go to the next stocks this stock says create a table with adders from cell range A3 B24 you have to input the cell range first A3 B24 that's correct so everything you will do to turn into a table with headers to turn this into a table while you're in the own panel go to format as a table click it then light you have to go to 10. so you hover over each of these and you see if you're in 10 this is nine this is 10 and it is blue as you can see blue so you click it and it says format as a table this and this checkbox it says my table has errors in yours it might be unticked you have to take it because the instruction says a table with headers all you have to do is Click ok and your table will be created Mark completed go to the next Stacks this stack says insert a footer that displays today's date on the right and then return to normal view good to create a photo editor in a worksheet all you have to go to is Click any cell in the table itself go to insert go to your right and you see a down footer select Adder on footer and currently now it says Ada the instruction says footer so up here still under design you go to go to footer click on it and it will take you down to the footer and it says on the right you have to double click this and if you go to the right if it says on the left you click this in the middle you click this so on the right we thought to add today's date to our today's date this is current date so at current time this is what you do now in the professional exam the question might be different a little bit but the concept is that you know where everything is so current date and we are totally return back to normal view to return back to normal View all you have to go to is view as you can see here from the question View click outside of the foot Tab and go to normal view that's all you have to do more completed and once we're done with these five tags to move on to project two or seven all you have to do is grade your project it will check these questions one by one see if you got it right we scored everything correctly if you didn't all you have to do is retry I need to take you back to the worksheet whatever question you miss maybe stocks too once you retry you go back to tax two and redo it understood moving on as you can see project two or seven has been loaded and it says here in tax One Import Pet Foods dot txt located in the geometrics template folder as a table on a new worksheet to import any data you go to the data panel and you pick from text you scroll down to geometrics template folder double click it petfood.txt and here we don't need to be bothered about selecting anything else just click on next click on next click on finish and it says here on a new worksheet you have to select this if you click on existing worksheet it is going to create it here we don't want that we want a new worksheet click OK and you're ready that is all for this tax Mark completed go to the next stocks this tag says on the feed inventory worksheet feed in Virtual worksheets remove the iPad link functionality but leave the text in cell c27 we have to go to c27 First tomato to remove this iPad link here currently now if you click it it will take you to this header right here but water to remove the link here so all you have to do is go to c27 again right click it and you see this icon remove hyperlink once you click it that's all if you click it again it will not take it to where it's linked to which shows that the link has been removed Mark completed go to the next stacks this tag says remove the conditional formatting from the inventory column on the feed inventory worksheet when the field eventually worksheet all we have to do is go to feed to the inventory column all you have to do is Select this and highlight everything I like everything and go back to home panel go to conditional formatting as you can see a conditional formatting click this go to clear rules once the hover over is all you have to do is clear rules from selected cells click it and to remove the conditional formatting from here Mark computed and go to the next Stacks this tag says on the organic feed worksheets these worksheets format the data range A3 F10 it says A3 F10 A3 F10 has a table that has adders to create a table with errors you know where to go you go to home format as a table and we're told to apply this style so you click this I will apply medium 7. medium we over by it is 11 this is seven click on this I need to bring this pop-up which tells you or asks you you want a table with header and yes we want click on this I have your table Mark completed and go to the next stacks on this Stacks it says on the feed inventory worksheet go back to this apply style 11 to the chart this is the chart here to apply a particular style to this or to change it all you have to do is once you click the chart go to design you will see the different style here click on this drop down to see the full style click on it however to pick Style 11. so we go to style 11. go to style 11 and that's all you need to do once you're done with that Mark completed and grade your project grade your projects and see the results we scored everything correctly moving on to the next Stacks in the tax one it says display the cost worksheet in the page layout View when the cost worksheet display it in page layout View go to view I'll click page layout view then insert a page break between row 20 cracker Jacka and row 21 raspberry chocolate so all you have to go to is row 21 and we have to insert a page break all you have to do is go back to page layout and you can see breaks here the answer to the question is in the question already page break all you have to do is Page and you can see breaks here break click it and insert a page break Mark completed and go to the next stacks in this tax it says use a built-in Excel feature to copy all the formatting of cell range a to G2 located on the profit worksheet make sure you're on the right worksheet and enter the cell range A2 G2 so we're going to copy this formatting here and copy to cell A2 on the customer sheet here so to copy this styling here all you have to do is go back to home and click format painter if you copy the Styles here and it will be ready for you to paste it anywhere you want to paste the kind of formatting that is here so go back to cost worksheet and we are told to go to where what do to go to cell A2 on the cost worksheets this is cell a and A2 all you have to do is click it and the style we applied here good more completed go to the next stocks on this tax it says on the profit worksheet go to the profit worksheet enter a formula in cell 829 so 829 there is is an Excel function to return the average cost of the cookie dough flavors based on values in the cost column to start the formula enter equal to and as instructed a average cost that's the formula type average and you see this drop down double click on average and if you open this bracket and we are told to get the average of the value in the cost column click it the first one then highlight everything here once you've highlighted you can see it here you can see your formula here or you can also see it here while you're still right here close this and click enter to get the average of the costs on this column all that is done Mark completed and go to the next stacks on this tax is set on The Profit worksheet include the total profit data on the flavor expenses income and profit charts this is the chart they're talking about click it and to add any data to this guys whenever they talk about chat in the professional exam guys all you have to know is that to add any data to this chart or to remove click on the charts and go to design when the design you can see the style of the charts and also select data this select data will add any data to this chart so all you have to do is Select data I want to add total profits data selecting it we add it to this chart let's see that once you click OK you see this data change and it will include the total profit as well so Mark completes move on to the next Stacks this taxes filter the profits worksheet to display only flavors with the course that is above average to filter The Columns guys you can see a little icon over each column here to filter each column you click on costs and you go to numbers filter they go to above average as instructed if the instruction says below average that is what you do click on above average and this data will change that is all you have to do Mark completed great project discard everything correctly click on next and this instruction the tax one under project forces add open to the quick access toolbar this right here they're talking about yeah everything here these are the quick access toolbar to add open to it you click this little drop down click on it and you can see customizing quick access toolbar all you have to do is check this and it will add this folder which is the open toolbar we're done with that tax Mark completed go to the next stacks this stack says add the value new rates to the title property of the document all you have to do to edit the property of this particular document is go to file this is the properties here and we are thought to adjust the title click this and change this to rate change this to rate new rate rather new rates that's all you have to do go back more complicated and go to the next stacks this tax is set cells A1 F10 input this cell range A1 F10 click enter so that they will be the only cells that print so if you want only this cell to print as you can see from the instruction print you go to the page layout print it is here click this little icon here you can see if you ever write it says page setup click on it bring this here go to Sheets then print area is the one that will set which area you want to print so print area select this click on everything that we've highlighted so far understood as you can see A1 F10 has instructed a quick hits back again and it will still remain here click OK and you're done with that tax Mark completed go to the next stacks this taxes save the rooms worksheet this worksheet save as a web page named rent on the scoreprices.atm to the geometrics template folder so we have to receive this file click this save us the geometry template click this put this as the name so all you have to do is enter rent once you're done with that click this drop down again to change this file format to HTM web page as you can see a web page click it and this will change understood you have to change this if you only edit the value here and don't change this that's an error so you have to save and you're done with it and you can see here if you know that okay it really changes you can see rent underscore prices Mark completed and go to the next Stacks this taxes on the rooms worksheet configure the adding Row in table row 2. so that entry is wider at the column wrapped to multiple lines the instruction here is basically just wrap the column and as you can see Row 2 is where the access to go Row 2 go to home to wrap the text to be able to see the entire content in this row go to home and click wrap text wrap text as you can see in the instruction here it will show more information the full information so if you click it it will show everything here properly Mark completed and grade your projects discard everything correctly click on next online project five of seven this instruction on the taxon says on the sold worksheets on the sold worksheet insert a new column before column A this is column A is column B this is Row one is row two in column A we are going to add the column before this to do that right click on this and insert if you insert a column before the column A normally this was column A before but since we added a new column this return to column A and this change to column B you know with that Mark completed go to the next stacks this taxes split the view on the sold worksheet so only rows one through six appear in the top Pane and the remaining rows appear in the bottom pane the instruction is a split you go to the sold worksheet and you go to row seven anything below row 6 is to be splitted from the rest up here click on row 7 and to split that all you have to go to is view go to view and you see split here so this will divide row seven and the remaining below with Row 1 and 6. click on split and there you have it Mark completed go to the next stacks in this next tags it says configure the vehicle's worksheet go to Vehicles worksheets so rows one through three rows one through three remain visible as you scroll vertically this we've done this in the first video and the instruction here is basically to freeze the frame so Row one two three you click on row four click on rule 4 and while you're still in the view panel what you have to do is free space and phrase it so whenever you scroll vertically this Row one two three will still remain there Mark competed go to the next stacks these taxes on the sold worksheets on the sold worksheets format the table so that every other row is shaded similar to the table on the vehicles worksheet use a technique that automatically updates the formatting if you insert a neural what the access to do is on this vehicle worksheet as you can see the tables it has a unique kind of styling you have the gray you have the white gray white so whenever you add a new column here or a new row here it maintains The Styling we are told to do the same thing here to copy the styling here click on any cell here and check the design check the design and check the table style options as you can see here the other row is checked the bounded row is checked and filter button is checked to apply this click on anywhere in the table and activate this activate this and you have the same styling as you have in this vehicle table understood Mark completed go to the next Stacks here it says on the vehicles worksheets insert the function in cell B4 cell B4 insert a function to insert the function you enter equal to of the code column when the code column but extract the two leftmost letter of the vehicle style displayed in cell D4 to do that the formula here is left first that's the first thing we have to do left double click it then if you open a bracket as you can see here as well if you open a bracket and it says the value in D4 where is d and this is four click this and put a comma once you put the comma you now have to put in these numbers too because this two left most two then close the bracket that's all you have to do two leftmost click on enter and you can see every value here the first two letters is displayed here if it is three left most you are instructed in a professional exam you have to put left three here they select whichever cell you have to select it Mark completed and grade your projects we scored everything correctly click on next and we're in the project six of seven this instruction says on the qtr2 worksheet in cell F10 qtr2 worksheets in cell F10 go to cell F10 insert a column sparkline showing the sales trend from April to June applied gold sparkline style Ascent 2 darker 25 to this pipeline copy this pipeline to the remaining cells in column f11 to F14 so all you have to do to create the column sparkline is click here go to insert and as you can see here under sparklines you see lines column winner loss in the exam you may be told to add the line sparkline but here in order to add the column click on column and to create the column spark plug you have to select the data first and as instructed a April through June click on this to select the data and we are told to select April through June I liked it and you can see a B10 to d10 click on it again and click on enter we have create the column sparkline now to apply this styling to it all you have to click is this drop down click this drop down and select this sparkline Style async 2 that card 25 click on it and there you go now that's not the last of it copy the sparkline to remaining cells in the column f11 to F12 as you can see here this is f11 I want to take it here also to f2f to copy this exact type of styling to remaining of column all you have to do is click on the first one and you can see this dotted line at the edge all you have to do is click it and drag it down click it and drag it down and it will show the kind of styling you want here as well understood Mark completed go to the next Stacks this instruction says using the move chart feature move the pie chart on the ktr2 worksheet this to its own chart sheet named qtr2 chart so to move this all you have to do is click on this go to design and you can see move chart here so if you click on move chart if you bring a dialog click on new sheets and rename this to qtr2 charts all you have to do is heal CR 2 charts and there you go make sure it is the same value as instructed here click ok and this chart will be moved to a new worksheet Mark completed put to the net stocks in this tax it says on the qtr1 worksheet apply a number format to display the numbers in column B through e GTR one from column B through e apply a number format to two decimal places with the US dollar symbol left aligned and the decimal points aligned to do that go to home go to General go to more number format you could have just selected here and select the decimal also about to be specific go to more numbers format go to number as you can see decimal places decimal point it is 2 as instructed here and as instructed it is two decimal places go to the currency as well and you can see the dollar sign so whenever you click each of this it will keep adding it to the numbers so we have the decimal places we have the same dollar sign click on OK and you can see here that has been added the last sign and the decimal points two decimal places that's all click on Mark completed and go to the next stacks on this tax it says in the QT R1 worksheet still here change the configuration of the QT R1 location Revenue charts these charts click it so that I displace the month on the x-axis this month you want it to be up here x axis and the revenue still on the y-axis the revenue Still Remains here to select any data to add any data to the charts like I said go to design select data and to switch the rules just switch rows click on OK and you see that that has been reflected you're done without tax Mark completed next stacks so the fifth Stacks under project six it says display all of the formulas when the QT are two worksheets go to ktr2 worksheet to display all the formulas here all you have to do is go to formulas click on formulas or you can see show formulas click on show formulas and to display all the formulas for this worksheet Mark completed and great project we've got everything correctly click on next and we're in Project seven of seven the final one on the tax one it says on the average call time worksheets average call time worksheets use an XL feature to copy the sparkline into all the vacant sales of the trend column so this existing spot line we are told to copy to the rest of the column cells like I said the other time to copy that you click on this and this little dot icon here click it then bring it down bring it down and it copies all the sparkline for each cells we're done with that one Mark completed go to the next stacks on this stack it says on the employee hours worksheet employee our worksheets add a row to the table that automatically calculates the total hours worked by all employees so to add these total hours to the table all you have to do is go to design and you'll be able to add the total row to it currently night is not there to add that to it just click it and you see it's total here it is added Mark completed go to the next stocks on this tax it says add a function to the overtime column over time column whereas it is overtime column in cell H4 input the cell number H4 on the employees our worksheets when the employees are worksheets that will display the word yes listen carefully guys that will display the word yes if the formula here starts with if it is an if else statement but in this case it's just if if the value in cell G4 this is J and four it displays yes here if the value here is higher than 40. so if this value is higher than 40 displayed yes this one is higher than 40 it display yes if this value is lower than 40 displays no understood so to do that double click here enter equal to to start the formula then type if if double click it you pick the cell G4 as you can see here J pick this then if the value is greater than 40 put a comma equal to if then the value I will try to pick here so G4 if it is greater than 40 I said here put yes yes put a command also if this is greater than 40 years else no so input the value of no and close the brackets understood again equal to if the cell is greater than 40 yes else no as you can see here you have equal to which is the start of the formula you have the if which is the formula we have this value which is j4 and we have the greater than sign if it is greater than 14 comma then we have double quotes we input yes put double quote comma then we put double Quotes no then double quotes that will close it once you're done with that click enter and you can see is 38 greater than 40 no is 50 greater than 4 40 yes it's 24 greater than 14 no and so on Mark completed go to the next task this task says in cell I4 on the employee's hours sheets I4 I4 use a function to copy the name from cell A4 so to copy that and format the name so that all letters are Upper Keys it's also a function double click is enter equal to and the formula is upper the click it and they sell the cell A4 all you have to do is find A4 click it it is displayed here and that is all you have to do close it then enter every value here will be displayed in capital letter here again put equal to Upper which is the function here open the bracket select the row which is this row and close the bracket Mark completed go to the next stacks and the last Stacks under project seven it says on the average call time worksheets which is the average call time worksheets create a 3D clustered column charts that shows only the call time that shows only the code name for Friday the call time this go down for Friday for each salesperson position the new chart to the right of the table and change the color of the chart to colorful palette4 to do that you have to first select all the call time for all the sales person here so click on the first one highlight it to the last person now go to insert whenever I want to create any column any pie chart you have to go to insert go to insert click on this icon beside the pie chart click on it and go to more pie charts 3D crosstart column is not right here go to more charts I go to column I go to column as you can see here 3D cross that column this is 3D stacked column in the exam we might be asked to add 3D 100 stack column but in our case this is what we are told to do click on this and click on OK well done with that move it to the right move it to the right and that's not the last thing we have to add the colorful palette to it so add the colorful palette to this why are still selecting this go to change colors four you have to add four click on this and you see the value changed here Mark completed and grade your projects amazing guys so we're done with the practice and our score is 100 over 100 1000 over 1035 questions over 35 questions we got everything correctly so thank you guys for tuning in to this video focused on March 2016 practice exam I hope you find it interesting and valuable and also insightful remember preparation is key and with the knowledge you've gained there and the skills you're well on your way to succeed in the exam if you have any questions or thoughts feel free to leave them in the comment section below and I'll be more than available to answer all your questions don't forget to subscribe and hit that like button guys if you find this video helpful stay dedicated keep practicing and I'll see you in the next video

Channel: Tech Rated

Views: 13,025

Keywords: mos excel 2019 exam, mos excel 2019 training, excel 2019, microsoft excel, excel tutorial, #excel2019, gmetrix practice exam 1, #gmetrix, MO-200 Excel, mos 2019 excel, mos excel 2019 exam 2, excel 2019 (mo-200) practice exam, mo 200 excel 2019, excel 2019 mo-200, mosexcel2019

Id: v8V4nas--0s

Length: 37min 39sec (2259 seconds)

Published: Thu Aug 10 2023