Do you know your Excel (Intermediate) skill level?

Try out our Excel Intermediate Assessment

/15

EXCEL INTERMEDIATE TEST

This test is developed to test your skill and knowledge related to Excel as Intermediate User. There are a total of 15 questions for which the passing mark is set to 80%. Upon passing, you will be automatically given an e-Certificate thru your email, so please make sure to enter a valid email address.

Topics covered in this test are:

Working with Multiple Worksheets
Working with Text Formulas
Working with Date & Time Formulas
Working with Conditional Formulas
Conditional Formatting
Data Validation
Creating Formulas That Look Up Values

GOOD LUCK!

Flash Fill

1 / 15

Category: Excel: Working with Text Formulas

1. You have a list of constant text data as shown and want to join them into an output statement e.g.: “January Sales” in the Result column. How can you efficiently generate the desired output?

Match the appropriate steps and processes. PLEASE PAY ATTENTION TO THE STEP LABELS, THE ORDER MIGHT BE JUMBLED UP!

1. Step 2

2. Step 3

3. Step 1

2 / 15

Category: Excel: Working with Text Formulas

2. You are given a list of text data as shown in the image. How can you change the alphabets into UPPERCASE letters?

Use Change Case command in Home ribbon

Use UPPER function

Use UPPERCASE function

Copy all data and use Paste Special -> UPPERCASE

3 / 15

Category: Excel: Working with Date & Time Formulas

3. This function returns the year number from a date data.

YEARS()

YY()

YEAR()

YYYY()

4 / 15

Category: Excel: Working with Date & Time Formulas

4. This function returns the month number (1..12) from a date data.

MMM()

MONTH()

MONTHS()

MM()

5 / 15

Category: Excel: Working with Conditional Formulas

5. Refer to the image. How can you teach Excel to make decision in Cell G5, so that whenever the value in Cell F5 changed to “ON SALE”, the value in Cell G5 automatically changed to 50% else, it will keep showing 0%?

=IF( = , , 0%)

6 / 15

Category: Excel: Working with Conditional Formulas

6. Zoom in to the image and consider this scenario. Determine what is the first month having ALL employees achieving target of $1,500?

Jul

May

Dec

Aug

7 / 15

Category: Excel: Conditional Formatting

7. Which conditional formatting can you use to represent the Total Sales figures as shown in the image?

Data Bars

Color Scales

Highlight Cells Rules

Icon Sets

8 / 15

Category: Excel: Conditional Formatting

8. Consider this scenario: You want to highlight any cells containing “re” letters in it as shown in the image. Choose the acceptable text pattern(s) that you can use in Conditional Formatting > Highlight Cells Rules > Text that Contains

r_e

r.e

r*e

r:e

r?e

r??e

9 / 15

Category: Excel: Working with Multiple Worksheets

9. Assuming you have worksheets as shown in the image. What will happen if you enter this formula in the ‘Totals – by 3D Reference’ worksheet cell B4?

=SUM(‘PAVILION:SUNWAY PYRAMID’!B4)

It will add values of cell B4 from worksheet PAVILION & worksheet SUNWAY PYRAMID

#VALUE!

#NAME?

It will add values of cell B4 from worksheet PAVILION until worksheet SUNWAY PYRAMID

10 / 15

Category: Excel: Working with Multiple Worksheets

10. This is Data Consolidate window, primarily used to consolidate data from multiple tables into a summary table.

Why do you need to use the option highlighted in RED?

To overwrite all source data with the results in summary table

To make sure any changes in the source tables are reflected in the summary table

To make sure the summary table’s values will not change even though the source data changes

No purpose

11 / 15

Category: Excel: Data Validation

11. Which statement is TRUE by applying this Data Validation settings?

Multiple answers

Any numbers between 0 until 10 can be used

Negative numbers are not accepted

Only 1, 2, 3, 4, 5, 6, 7, 8, 9 can be entered, 0 and 10 are not included

Only integers are acceptable as input

12 / 15

Category: Excel: Data Validation

12. Which type of Data Validation can you use to obtain the dropdown result as shown in the image?

Whole number

Text

List

Custom

13 / 15

Category: Excel: Creating Formulas That Look Up Values

13. Vlookup function has an argument called Range_lookup as shown in the image which you can choose to enter either TRUE or FALSE. Match the correct meaning of TRUE and FALSE for this argument.

1. FALSE

2. TRUE

14 / 15

Category: Excel: Creating Formulas That Look Up Values

14. Cell marked with RED contains the formula as shown in the image. What will be the answer if value in cell A10 changed to RM 7369.45?

15%

18%

#N/A

20%

15 / 15

Category: Excel: Creating Formulas That Look Up Values

15. Zoom in the image and observe the table highlighted in RED very carefully.

What is the table orientation?
What is the suitable lookup function using this table?

Please fill-up the information fields should you require an e-Cert sent to your email. You will receive occasional update news about our training opportunities unless you choose to opt out from our email blast list. To opt out, you can leave the form blank and go direct to the result.

Thank you so much for participating in the online assessment.

NameEmail

Your score is

Anonymous feedback

Thank you for your review!

By Wordpress Quiz plugin

Discover more from Cadisc Enterprise

Subscribe to get the latest posts sent to your email.

Cadisc Enterprise