Create Your Billing Sheet in Excel for Administration Effortlessly

Billing sheet in excel for Administration

hello and welcome in this video I'm gonna show you how you can pass excel assessment test when you come to employment agency a lot of times they want to validate your Excel skills by giving you a test they give you a lot of different tests this is just one of the examples and in this video we're gonna go step by step and complete all 20 steps of this test what you see on the screen is the practice document on the left and you see list of steps on the right with the status so we will go one by one and complete each step of this excel assessment test for the best results I recommend you download the sample file from my website and follow me so this way you can learn and be successful as part of the test let's focus on the step number one so the step number one asks us to extend column B to feet total caused by month so column B is this column and the total caused by month is this value so as part of this step what we need to do we need to expand column B so the value of total caused by month fits into the column B and we'll mark this step as completed so what I'm gonna do I'm gonna change background in the status column to green to best complete exercises it often helps to understand what kind of data you're dealing with so let's close the look at the data this is the table with business expenses and looks like expenses categorized by different items we have office supplies lease utilities phone expense computer and internet expenses then we have values by month we have January through May in this as the values related to this expenses knowing this information will help us to complete step number two of Excel assessment test to complete this let's go ahead and read it we need to calculate total cost of expenses by month using formula we already extended the column to fit total cost by month now we need to populate the values here in the row 12 and the best formula to do this work is the sum formula you can type in some formula I'll show you multiple ways how you can do it you can type formula sum and then you just highlight the range for which it's applicable and then you close the parentheses and hit enter so this is the total value another way to do it is there is a sum formula button so you click it and Excel anticipates very correctly or often correctly in this case definitely correctly what the range is so I'll hit enter again and the third way would be when you already have the formula you can just extend it you see this I selected the formula value and value shows right here in the formula box we can extend it and I'll just extend it till the end of the first quarter and first quarter is January February March and another way to do it would be copy and paste values so you can copy value and copy is through this copy button and then we highlight the area for March through May and then we click the paste button so now we've populated total caused by month in the row 12 and we can mark this step as complete let's go and continue to the step number three and step number three is create boarder around the table so first we need to understand what is the table and in this case table is all expenses even the ones that we have not calculated yet with the total cost and an average cost which we have steps to calculate later so what we need to do we need to highlight the table select the borders around the table and the best value for this selection is all borders so this is what I'm going to select and this completes step number three let's go to step number four format data s currency so what we need to understand here is the difference between values for the monthly expenses in total expenses versus just text values which describe expenses themselves to and what we need to do we need to format data values as currency to do that we need to highlight the data and select the currency sign accounting number format it's a dollar sign for me because I'm located in the United States might be different for your country and we've completed step number four so now we can mark it as complete now let's go ahead and continue to step number five and in step number five we need to use formula to calculate total costs so formula would be the same the best way to calculate total costs would be to use the sum formula and we already looked at how to use add only difference would be we used it vertically to calculate values for the column now we'll be using it horizontally to calculate values for the rows but the concept is the same for some it doesn't matter it uses the range so I am going to just use the button on the ribbon toolbar and I put their cursor into the cell I six click the button and Excel predicted correctly that the range I'm trying to calculate sum for is C six through h6 so I'm gonna hit enter and I'm just going to extend the range for this values up to the total cost by month and you see total cost by month doesn't fit as well as our header so it's better for us to extend it a little bit and we can mark step five as completed as you can see I have to expand the values so it doesn't fit anymore so I'm going to zoom out a little bit so we can continue to see all the values as well as the steps in assessment tests and I use the zoom zoom out bar at the bar right corner of Excel let's go and continue into step number six and step number six asks us to use a formula to calculate average costs for average cost let's extend the column J a little bit and I'll show you another way to extend the column so all the text fits you can just double-click on the line and that separates J and K and that expands the column up to the size of the text that's in the column J so what we'll do now we will use average and average formula remember I showed you how to use some formula to type it in manually so we can use equal and then say average and then we have to select the range and the trick here is that we do not want to include total cost by into calculation of the average we just need to select the values from c6 through h6 which would represent all the values for office supplies and then we close the parentheses you see the formula here and hit enter so our average cost for office supplies is 351 15 can extend this formula because it's going to be the same all the values up to the internet there's no reason to calculate average total cost by month I guess we can so we'll just extend it in Woolmark step six as complete let's continue to step number seven and in step number seven it asks us to change alignment in column D to right so column D is February month we can select column D and there's no alignment right now in column D but this is the right alignment and that button by clicking align right completes the step seven in our checklist and we'll mark step seven as complete question number eight calculate quarterly costs for q1 and q2 so q1 is January through March and q2 is April through June and the best way for us to calculate total cost for the quarter would be to sum up total cost by month so we will use two columns two cells actually one would be to say that this is q1 cost so we're adding new values here and here we will calculate the values it's going to be some sum would be calculated customly by selecting values in the row 12 see 12 through e 12 and then we'll just hit enter so this would be q1 costs q2 costs we will report here 2 2 costs in the cell H 13 will calculate it the same way select the sum button and q2 would be April through June six thousand eight hundred twenty three dollars and thirteen cents our costs for q2 and we can mark step eight as complete let's continue to the next step step number nine in step number nine we need to save the file to Documents folder to do that we click on the file tab and we click save as and we click browse and documents folder B right here so we select documents folder um and the name of the file we just checked that everything's good and we click Save button let's mark step number nine as complete now we can move to step number ten and step number ten is change page orientation to landscape um so page orientation is defined and page layout tab so we click on the page Layout tab and we click on orientation right now you see orientation is portrait so we just need to switch orientation to landscape and we can mark this step as complete go back to the Home tab and fill the color for this cell let's continue and go to step number eleven in in step number eleven we need to fit work table into single page for printout so first let's define work table work table is the table that we're working with obviously which does not include the steps for assessment test to do that the best way to complete this part would be to select the print area in to select print area we need to first see how our document turns out so in print preview to do that we need to click file and then print and it shows print preview and you see that it does show our work table but this might be misleading because there is a second page here since there is a scroll bar on the right and if we click scroll bar we see this table it's ultimately the objective is to eliminate this table was the steps and purposes only have business expenses table select to do that let's go back to our work document and we need to define print area so we need to select the area that we'll be working with and I'll expand the area a little bit just in case we need to add more values around our work table and I'll select the range between a 1 and K 23 and I'll go to page layout and this is where we define print area and we select set print area button so click on the print area button first and then set print area so now we have print area defined and let's go and verify our print area by navigating to file tab print and as you can see we now only have one page with our work table ready for print out this way we can mark step 11 as complete we go back to the Home tab and mark it as complete let's go to step number 12 and step number 12 here we need to Center table header values so table header um is this row 5 to Center all this values we need to select them and then click the center button and that completes step number 12 so we can mark it as complete let's go to step number 13 and step number 13 we need to spellcheck the document to do spell checking we can go to review tab and there is a spelling button so let's click spelling and it asks us do you want to continue checking at the beginning of the sheet and we say yes spellcheck is complete everything looks good one thing I'd like to show you here if you don't know where function is located because it's not obvious where a spell check would be let's go back to the Home tab which you can do there is a search box and you can just type for the function here the one that you're trying to find and spelling would be one of them so we click spelling and it shows actions and it click spelling and it actually goes through the same set of actions you can find any Excel function in the search box I'm gonna click no because we've already done it and it will mark this step as complete let's go to step number fourteen and in step number fourteen we need to rename sheet 1 and call it a business expense so sheet names are at bottom left so we have sheet one right here to rename it you can do it multiple ways one way is right mouse click and click select rename there is also option of just double clicking on the sheet name and you can start typing right away that's kind of a shortcut so we need to mark it as business expenses and you can just click anywhere outside of this title and rename is complete and we can mark step 14 as complete let's continue and in step number 15 we need to add in your worksheet this is easy there is a plus button highlights and allows you to add new worksheet we just click on this and by default it's gonna take the name sheet 1 but there are no instructions of renaming it or anything so just go back to business expense and mark this step as complete let's continue and go to step number 16 and then step number 16 we need to create a column chart to show expenses for the first quarter so first quarter values are January through March and expenses are listed here so to do that we need to select the area of expenses include types of expenses and include the values January through March so it's basically arranged between the cells b5 and E 12 so once we selected all this area we need to click insert button and insert chart and it's asks us for column chart right and when we click this extension arrow it shows 2d column of 3d columns so we can I guess select any one of those it does not indicate which one you liked so the fancier looking ones are 3d columns so if you like you can select that I'm gonna just choose the basic one and now the chart is created there are no additional instructions what we need to do here you certainly can play with this but what I'm going to do one important considerations make sure it fits into the print area and print area is highlighted you see it ends at the row 23 so I'm gonna make this chart a little bit smaller by resizing it and move it I'm gonna move it here into this area so it fits into print there and make it even smaller so this way it fits into the print range area and this way we can mark step 16 complete let's go and complete step number 17 and in step 17 we need to change the width of column I and J so the content fits you see that column I has total value by month hidden that's what this pound sign means that we don't see the whole value to expand it we just need to double-click on the separator between columns I and J and that extends the column so all the values of it now and we can mark this step as complete moving on to step number 18 we need to bold all headings and change headings font to 12 points so first of all let's define headings headings are is a range b5 through rj5 so let's select it first we need to make it bold by clicking the bold button that's one way in other ways if you do right mouse click bold button is available here as well right on the context-sensitive menu so let's choose it here and then we will change the font size right now its size font 11 of Calibri font we'll change it to size 12 in this way we can highlight and mark step 18 as complete let's go to step number 19 and in step 19 we need to merge and center the table heading business expenses so first let's locate business expenses business expenses are in self cb3 and we need to emerge and center them so to do that we need to merge all the cells between b3 and j-3 to do that we need to highlight them and we need to click here on the alignment settings and select alignment itself and click merge cells click OK so this merges all the cells now it becomes one single cells and it will behave as one single cell and we just need to Center business expenses here to do that we select the cell and click center button and now business expenses are centered we can mark step 19 as complete and let's move on to step number 20 the last step in our exercise here we're gonna forecast least cost for third quarter by calculating q3 total it's a little bit tricky question but let's see how we can address it let's find the lease costs so lease costs if we look they are fixed and similar so January through March the amounts are the same not everything is the same as you can see on the lease is the same and then we have internet costs are the same but internet costs go same from q1 and q2 lease costs increased from q1 to q2 they increased by $20 so and the exercise asks us to forecast lease costs for third quarter by calculating q3 total so we'll make an assumption that because they increased from q1 by $20 to q2 we will have the similar increase from q2 to q3 and we will make calculations based on this assumption to do that let's move this chart a little bit to the right and we will say lease cost for a cast for q3 and here in this cell will calculate the value and the value would be will have 790 we'll take the value from April let's say we'll add $20 and we'll put it in parentheses and we will multiply by 3 and that gets us to the total of two thousand four hundred thirty dollars and that's estimated for a cast for least cost with the assumption that it's gonna increase by twenty dollars per month in q2 q3 and this allows us to mark step 20 as complete if you like the content please make sure to click the like button and share with your friends also there's tons of information in the description of this video make sure to check it out make sure to check out my other relevant videos and subscribe to my youtube channel we have a lot of great stuff planned in the pipeline and I don't want you to miss any of it and if you'd like to get notified about all the new stuff that are coming out make sure to subscribe to my email list as well all links are here in the screen make sure to click to stay in touch thanks again for watching