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.**

### Tips and Tricks to Get Prepared and Pass Excel Assessment Test

### General Tips for Excel Assessment Tests

- If you can navigate between questions go through the entire test and do the easiest, shortest questions first. Save the more difficult questions to come back to later; perhaps another part of the test will help you out with that question or trigger knowledge that you didn’t think you had and you already answered the questions you know the answers for.
- Keep a positive attitude throughout the test and stay relaxed. People who are positive and relaxed do better on tests than those who are negative and stressed out.
- Consider using flashcards to help you study. You can purchase blank flashcards or create them yourself from paper and put your questions and answers. Create flashcards for the questions you have missed in the past, so you can refresh the knowledge.
- Understand how much time you have to complete the test by dividing number of question in the test to the allotted time to determine time per question average you should use. Use this calculated average as a baseline and try to limit the time you spend on each question.
- Always watch for time remaining and be prepared to submit test results, before time expires.
- Even if you have no idea about the answer you still should try to guess and answer the question, since your guess might be correct

to learn more tips and tricks to pass excel assessment test download ebook

### Next Steps

Below is the list of resources you could use to get prepared for the *Excel Interview*:

All the best on your interview!!!