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

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

Channel: Online Training for Everyone

Views: 1,332,486

Keywords: excel for job application, excel 2016 tutorial, excel quiz, excel tutorial, excel exam, interview questions, excel for beginners, top excel interview questions, excel certification, excel interview questions, interview questions and answers, office 365, excel online training, how to use excel, excel job test, interview tips, excel interview, excel assessment, Excel for job applicants, excel questions, excel help, prepare excel exam, excel tutorial free, excel quick guide

Id: 6J4U-dXYPqA

Length: 17min 3sec (1023 seconds)

Published: Sat Apr 21 2018