In this tutorial, we will look at how 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 tutorial, we're going to go step by step and complete all 20 steps of this test.

Picture below shows source data and list of 25 steps, we will be completing in this tutorial: 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.

 

Step 1. Extend Column B to Fit Total Cost by Month

• Expand column B so the value of Total Cost By Month fits into the column B.

To best complete exercises, it often helps to understand what kind of data you're dealing with, so let's closely 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 and these are the values related to these expenses. Knowing this information will help us to complete step number two of Excel assessment test.

Step 2. Calculate Total Cost of Expenses by Month Using Formula

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. I'll show you multiple ways how you can do it.

• Type SUM formula
• Highlight the Range for which it's applicable.
• Then Close the parentheses
• Hit Enter.

Another way to do it is;

• Click the SUM formula button.
• Hit Enter.

The third way would be when you already have the formula, you can just extend it.

• Select the formula value.
• Extend it till the end of the first quarter which is January, February, March.

And another way to do it would be copy and paste values.

• Copy value
• Highlight the area for March through May
• Then Click the Paste button.

So now we've populated total cost by month in the row 12.

Step 3. Create a Border around the Table

First, we need to understand what the table is. 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.

• Highlight the table.
• Select the borders around the table.
• Select All borders.

Step 4. Format Data as Currency

So what we need to understand here is the difference between values for the monthly expenses and total expenses versus just text values which describe expenses themselves. We need to format data values as currency.

• Highlight the data.
• Select the currency sign (accounting number format).

Step 5. Use a Formula to Calculate Total Costs

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 it. The 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 SUM. It doesn't matter; it uses the range so I am going to just use the sum button on the ribbon toolbar.

• Put the cursor into the cell H6
• Click the SUM button and Excel predicted correctly that the range I'm trying to calculate SUM for is C6 through G6.
• Hit Enter.
• Extend the Range for these 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.

Step 6. Use a Formula to Calculate Average Costs

• Extend the column I or just double-click on the line so all the text fits.
• Put the cursor into the cell I6
• Type Average formula.
• Select the values from C6 through H6 which would represent all the values for office supplies.
• Then Close the parentheses.
• Hit Enter. So our average cost for office supplies is 351.15
• Extend this formula because it's going to be the same values up to Total Cost By Month.

Step 7. Change Alignment in Column D to Right

So column D is February month and there's no alignment right now in column D.

• Select column D
• Click Align Right button

Step 8. 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 cells.

• Type Q1 cost in D13
• Type SUM formula in E13
• Select value in C12 through E12
• Hit Enter

For Q2;

• Type Q2 cost in G13
• Type SUM formula in H13
• Select value in F12 through H12
• Hit Enter

Step 9. Save the File to Documents Folder

• Click file tab
• Click Save As
• Click Browse
• Select Document folder
• Click Save

Step 10. Change Page Orientation to Landscape

• Click Page Layout
• Click Orientation
• Select Landscape

Step 11. 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. 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.

• Select the Range between K1 and K23
• Go to Page Layout
• Click Print Area
• Select Set Print Area

Step 12. Center Table Header Values

• Select row 5 values
• Click the center button

Step 13. Check the spelling of the document

• Go to Review tab
• Click Spelling
• Select yes

Step 14. Rename Sheet1 as Business Expenses

We need to rename sheet 1 and call it a business expense. Sheet names are at bottom left. You can do it multiple ways. One way is;

• Right mouse click
• Select Rename

Another way is;

• Double click on the sheet name
• Type Business Expenses

Step 15. Add a New Worksheet

• Click Plus Button

Step 16. Create a Column Chart to Show Expenses for the First Quarter

First quarter values are January through March and expenses.

• Select the area of expenses, include types of expenses and the values January through March.
• Click insert button and
• Click insert chart/column

Step 17. Change the Width of Column I and J So the Content Fits

• Double-click on the separator between columns I and J

Step 18. Bold All Headings and Change Headings Font to 12 Points

• Select all Headings from B5 through J5
• Click the bold button
• Change the font to size 12

Step 19. Merge and Center the Table Heading “Business Expenses”

• Highlight all the cells between B3 and J3
• Click on the alignment settings
• Select Alignment
• Select Merge cells
• Click OK
• Click Center button

Step 20. Forecast Lease 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, 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. 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.

• Type lease cost forecast for Q3 in cell B15
• In cell D15, add $20 to F7 then multiply by 3

That gets us to the total of $2,430. And that's estimated forecast for least cost with the assumption that it's going to increase by $20 per month in Q3.