How to Pass Excel Assessment Test For Job Applications - Step by Step Tutorial with XLSX work files

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


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


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


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


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