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!

1 / 15

Category: Excel: Working with Text Formulas

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

Use LOWERCASE function

Copy all data and use Paste Special -> LOWERCASE

Use LOWER function

Use Change Case command in Home ribbon

2 / 15

Category: Excel: Working with Text Formulas

2. How can you transform the sensitive data from the 1st column to look like the masked data in the 2nd column?

Use CTRL F, substitute the required digits into *

Use SUBSTITUTE function

Use REPLACE function

Use CTRL H, substitute the required digits into *

3 / 15

Category: Excel: Working with Date & Time Formulas

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

YEARS()

YEAR()

YY()

YYYY()

4 / 15

Category: Excel: Working with Date & Time Formulas

4. This function returns the day number (1..31) from a date data.

DAY()

DD()

DAYS()

DDD()

5 / 15

Category: Excel: Working with Conditional Formulas

5. Zoom in to the image and consider this scenario. Determine what is the first month having at least ONE employee achieving target of $1,500?

Jan

Feb

Apr

May

Nov

6 / 15

Category: Excel: Working with Conditional Formulas

6. What function can you use to determine if the month have ALL employees achieving target of $1,500?

ONE

AND

ALL

7 / 15

Category: Excel: Conditional Formatting

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

8 / 15

Category: Excel: Conditional Formatting

8. How can you keep only the colors from the Data Bar in the 3rd column?

Highlight the values in 3rd column -> press Delete key

Highlight the values in 3rd column -> Conditional Formatting -> Manage Rules -> Edit Rule -> Show Bar Only

Highlight the values in 3rd column -> CTRL C -> CTRL SHIFT V

Highlight the values in 3rd column -> CTRL C -> CTRL V

9 / 15

Category: Excel: Working with Multiple Worksheets

9. 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?

No purpose

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

To overwrite all source data with the results in summary table

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 these options highlighted in RED?

To make sure the summary table is always created starting at cell A1

To make sure all data from multiple tables are consolidated into the proper location following their top row and left column labels

To change all the column headers and row labels of the summary table into "Top row" and "Left column"

No purpose

11 / 15

Category: Excel: Data Validation

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

Multiple answers

Negative numbers are not accepted

Only integers are acceptable as input

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

Any numbers between 0 until 10 can be used

12 / 15

Category: Excel: Data Validation

12. Which statement is TRUE regarding Data Validation?

Multiple answers

It is used to ensure only correct data is entered into a cell

It is available in Data ribbon

You can specify your own Error Message for invalid data

It cannot be removed once created

13 / 15

Category: Excel: Creating Formulas That Look Up Values

13. Zoom in the image and observe the table highlighted in RED carefully.

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

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 1369.45?

#N/A

15 / 15

Category: Excel: Creating Formulas That Look Up Values

15. Refer to the problem shown in the image. The formula is entered correctly but returns #N/A. What may be the reason for this particular scenario?

Multiple answers

Product name in column ‘Product’ is having extra space at the end of the text

Different data types between B13 and column ‘Product’

Typo in the word "brown", should be "Brown"

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