### Step by Step Tutorial - Learn Excel in 20 Simple Steps

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

In this article we will look at **Basic Microsoft Excel Skills **test tutorial and you will have an opportunity to download work files to follow along. If you would like to jump to file download right away, navigate to the button of this post, follow instructions to complete the form and you will be re-directed to download page.

As part of **Basic Indeed Microsoft Excel Skills test **candidates are being tested for their ability to navigate the Excel user interface quickly and efficiently in order to access the right features and perform simple tasks – from determining the best way to open a recently closed Workbook to using the correct formula to perform automatic calculations. Candidates will be required to answer multiple-choice questions based on Excel spreadsheets, charts and tables filled with sample data, as well as images of Excel buttons and tools, testing their ability to:

- Apply simple fill
**patterns**,**formatting**, and**styles**to**cells**. - Identify the proper
**keyboard shortcuts**to accomplish tasks more quickly. - Follow the correct steps to make one
**spreadsheet**look like another. - Choose the right
**formula**and**cell references**to**automatically calculate values**. - Perform basic tasks like
**hiding rows**,**preventing cell overflow**, and**pasting data**. - Since each question comes with a strict time limit (one minute or less), the test will also measure a candidate’s ability to quickly
**format**,**isolate**and**manipulate data**under a significant amount of pressure without any outside help.

## What is being tested

- Ability to navigate and modify an
**Excel workbook**by identifying common tools, applying**basic keyboard shortcuts**, and**manipulating columns and rows** - Ability to
**apply basic sorting**and**filtering options**as well as**statistical functions**to explore and**summarize values** - Ability to customize the look and feel of a
**workbook**using**cell formatting tools**