Skip to content | Change text size

Skills and Knowledge review for Excel level 3

These questions will allow you to determine whether you have the pre-requisite skills needed to sucessfully achieve the outcomes of the Excel Level 3 course..

Some questions have more than 1 answer. Please select ALL correct answers where applicable.

You may wish to read documentation relating to the required skills at

There are a total of EIGHT questions/exercises and it should take no more than 25-45 minutes to complete depending on your skill level.

Use the scroll bars to navigate the web page.
Questions can have more than 1 answer.
Green indicates a correct answer.
Red indicates an incorrect answer.

Q1:(a): Formatting: Designed to test your knowledge of Formatting.

Create the following spreadsheet.

Format the spreadsheet as shown below.

Cell E7 contains the average of Age
Cell F7 contains the sum of Wage

I don't have a clue

(b): Commands on the Formatting toobar have been numbered.

Check the Correct answers?

1 This formats font color
2 This formats numbers with decimal points
3 This formats numbers as currency
4 This formats text as right aligned
5 This is a thousands separator for numbers
6 This underlines Text
7 This formats the spreadsheet background color
8 This formats the font face
9 This re-sizes images to fit the page.

ref:

Q2: Named Ranges

You are required to create 5 named ranges. You may do this using

Create these ranges name range - encompases cells B2:B5.
DOB range - encompases cells C2:C5.
phone range - encompases cells D2:D5.
age range - encompases cells E2:E5.
wage range - encompases cells F2:F5.
allData range - encompases cells A2:F5		  

I don't have a clue

This image shows the ranges listed in the NAME box.

Notice that the age range is selected.

I have completed the exercise.

Q3: HELP: Designed to test your ability to use HELP.

In the next questions you will use a number of functions. Can you use Excel Help to look up information on the following functions:

VLOOKUP(lookupValue, namedRange, col, type)
CHOOSE(index_num,value1,value2,...)
INDEX(array, row, col)
IF(logical_test, value_if_true, value_if_false)

I have completed the exercise.

ref:

Q4: Lookup: Designed to test your knowledge of Lookup functions.

We wish to lookup the name and wage details of an Employee (in Qusestion 2) so we can pay them for the work they have done for us.
We will use the VLOOKUP() function. In our spreadsheet we enter the Employee ID and the number of hours they worked.

I don't have a clue.

Select the TWO correct formulaes that will enter an Employees name in the cell C2 above USING the named ranges from question 1.

  =VLOOKUP(A2,allData,2,TRUE)
  =VLOOKUP(A2,allData,2,FALSE)
  =VLOOKUP(A2,allData,name,TRUE)
  =VLOOKUP(A2,name,1,TRUE)
  None of the above 

ref:

Q5: IF statement: Designed to test your knowledge of conditional statements.

The IF statement is used in decision making when the answer is either true or false.

In our example we wish to tax the employee at a rate of 10% if they earn less than $30,000 a year otherwise the tax rate is 12%.

( Formulae is entered into cell G2.)

fig 4-1

Which formulae is correct for calculating the Tax (Column G) on the money earned (Column E)

  =IF(D2<30000,10%,12%)*E2
  =E2-F2*E2
  =TAX(E2) 
  =IF(D2=<30000,10%,12%)*D2
  None of the above

ref:

Q6: WildCard Characters: Using wildcards in Filters and Searches.

Wildcard characters are used in search strings. They allow the retrieval of data that meets certain criteria.
Wild card characters are used extensively in other applications apart from Excel.
Wild card characters, used in different applications and operating systems, differ slightly in their operation.

The wildcard characters are

? Match exactly 1 character
* Match 0 or more characters

 

I don't have a clue

Select the correct answers

s* matches smith, smythe, smithy
*h matches smith, smythe, wordsmith
*s???h* matches wordsmith, smith, smythe, smithy, wordsmiths
*ds*h matches wordsmith
*y* matches smythe, smithy
?????? matches smythe, smithy

ref:

Q7: Using Formulae: Designed to test the application of simple formulae.

Create the page Shown.

Note: The formulae in cell B8 is the sum of cells B3 to B6 AND includes the number in cell A1.
The formulae must be entered and then the FILL operation used to copy the formulae from cell B8
to cells C8, D8 & E8.

I don't have a clue

I have completed the exercise.

ref:

Q8: Using Lookup/List/IF Formulae

You may need to use help for some of this. Check the correct answers

I don't have a clue

Answer Question
56 =choose(3,23,34,56,7,88,65,100)  
56 =choose(A3,23,34,56,7,88,65,100)  
blue =choose(A3,"red","blue","green","black")  
26 =if( a3<>(a5-2),26,0)

ref:

 

 

You have completed the pre-test for the Excel 2003 Level 3 training course. It is now up to you to determine whether you feel confident in the areas tested.

See you at the training course.