Excel Skills Assessment

Instructions

Complete the 20 questions to obtain a rating of your Excel skill level.

Please provide the following information and accept the terms and conditions first and then you can start the quiz.

NOTE- You will receive your results via email so please check that it is correct.

Required information

Your email address:

Your name:

Company name:

You agree to receive this report via email and 1 more email with customised tips and tricks, after which you can unsubscribe. If you were sent here by a supervisor/ company, you agree that we can send these results to the supervisor/ company.

Page 1 of 4

1. To create a formula you start by typing

Don't know

2. To link a cell to another sheet or spreadsheet you

Type the name of the sheet, a ! and the cell reference

Type the name of the sheet and the cell reference

Don't know

Type the name of the sheet, a ' and the cell reference

Type = and click on the other sheet or spreadsheet cell

3. If you copy the cell in B4 to C5, what will the formula look like and what will the answer be

'=A$1' and OK

'=B$1' and OK

'=B1' and 0

'=B$1' and 0

Don't know

4. In order to change the name of a Excel file the best option would be to

Don't know

Click the Save icon (or use CTRL S)

Change the name shown in cell A1 of Sheet 1

Use the 'Save As'

In folder change name of the file while the file is closed

5. In order to change the way the printer prints (portrait to landscape) you would

Don't Know

Use the Print Option and then change the Printers Properties

Choose the Print Area option and Set the print Area to be landscape

Go to Print Preview and choose page setup and choose the landscape option

Go to Page Setup and choose the Landscape option

Page 2 of 4

6. If you've copied this cell (A1 with =1/3 in it) and you pasted as values into cell B1 (which has no formatting) what could it look like

Don't Know

=1/3

0.333333333

1/3

0.33

7. If you insert a row at Row 1, 2 and 4 in the first picture (pic A), resulting in the second picture (pic B), what will the answer be in cell B7?

PIC A

PIC B

8. How do I make cell A1 look like cell A3

Go to the Format Cells option and Wrap Text

Go to the Format Cells option and Merge Cells

Hold ALT down and click enter at each new line point

Don't Know

Go to the Format Cells option and Shrink to Fit

9. If a row is hidden e.g. rows 5 and 6 in the picture, and you copy the formula in C4 by dragging it down from C4 to C7 the following will happen

The formula in C7 will say = B4+C4

The formula will be copied into C7 only

The hidden cells will be overwritten with the new formula

Don't Know

The formula will be included in the hidden cells

10. If you sort the following list in ascending order what will be the result

A1, A2, A10, A11, A20, A100

A20, A2, A11, A100, A10, A1

A100, A10, A1, A11, A20, A2

A1, A10, A100, A11, A2, A20

Don't Know

Page 3 of 4

11. In order to get this data set to go back to showing all the data items you would need to

Use the menu items to Show All or Clear all

Set the filter in column C and D to show all

Don't know

Set the filter in column C to show all

Set the filter in column A, B, and E to show all

12. Which of the following comments makes the most sense to you

Linking spreadsheets is only sensible when no other option exists. In this case you must make sure that all the linked spreadsheets are open when you make changes to any of them.

Ensuring that you are consistent with your formula is critical especially with regards hardcoding of numbers into formula (specifically not doing it)

The structure of your spreadsheet (columns, rows and sheets) is critical in order to enable easy and safe changes to be made on your spreadsheet

Keeping a history of the different versions of your spreadsheet will enable you to easily go back to a version that meets your requirements

Separating the areas where your inputs, calculations and reports are is the most important thing you can do when working with spreadsheets

13. The result of the formula in cell B6 will be

0.08

Don't know

0.04

14. The VLOOKUP function in D3 will have the following answer (note that the last '1' could also say True)

Toni

Don't Know

Adrian

Anna

Trevor

15. The answers that will be shown in cell A15 and A16 (respectively) are

6 & 30

8 & 30

8 & 20

Don't know

6 & 20

Page 4 of 4

16. In order to make the TRANSPOSE function work (NOT the Transpose via Paste Special) you need to do the following. Highlight the areas where you want the information to appear, create a TRANSPOSE formula and

Highlight the cells where it is coming from and click SHIFT & ENTER

Highlight the cells where it is coming from and click SHIFT & CTRL & ENTER

Highlight the cells where it is coming from and click ENTER

The TRANSPOSE function does not exist

Don't Know

17. If the result of a formula is #N/A, you can improve the formula by using a combination of which functions

Don't Know

IF & ERROR.TYPE

IF & CELL

IF & NA

IF & ISERROR

18. To build a spreadsheet that automatically removes duplicates (another way to describe it would be to extract unique items) from a list the following functions should be considered (either as one formula or over multiple cells)

SORT

VLOOKUP & RANK

SUMIF & RANK

COUNTIF & VLOOKUP & RANK

Don't Know

19. In the below Pivot Table, what is the best way to calculate the percentage each item makes up of the total e.g. MTN is 21.6% i.e. 484.23 / 2 237.93

Copy and paste (as values) the Pivot Table to another part of the spreadsheet and then create the formula

In cell D4 type equals and click on cell B4, type / and click on B9 so that the formula says =GETPIVOTDATA('Bill',$A$3,'Destination Network','Cell C')/GETPIVOTDATA('Bill',$A$3). Copy it down

Click in the Pivot Table, go to 'Value Field Settings', click on 'Show Value as' and choose the appropriate option

In cell D4 type the formula =B4/$B$9 and copy it down to the other cells

Don't know

20. To determine if a record is unique in a list you can use the following tool or function

COUNTIF

Don't know

DATA AUTOFILTER tool

ADVANCED FILTER tool

DATA SORT tool

Next MS Excel Training Dates

What is your MS Excel Skill Level?

What are you looking for?

Instructions

Required information