Advanced_Excel -

Advanced_Excel

1 / 20

What is the role of the MATCH function in Excel?

Sort values

Join text

Find the relative position of an item

Lookup multiple ranges

2 / 20

Which Excel function helps create named calculations in formulas?

VAR

LET

DEFINE

NAME

3 / 20

What does the ISBLANK function check for?

Text in cells

Error in cells

Empty cells

Numeric values

4 / 20

Which function finds the last non-blank value in a column?

LOOKUP

XLOOKUP

INDEX with COUNTA

MATCH

5 / 20

What is required for VLOOKUP to work correctly?

Data must be sorted

Lookup value in first column

Exact match only

Table must be filtered

6 / 20

What function would you use to detect errors in formulas?

ISERROR

IFERROR

ERROR.TYPE

All of the above

7 / 20

Which function is best to replace VLOOKUP for better performance and flexibility?

HLOOKUP

XLOOKUP

INDEX

MATCH

8 / 20

What is the main benefit of dynamic arrays in Excel?

They reduce file size

They remove duplicates

They spill multiple results automatically

They format text

9 / 20

What does the VALUE() function do?

Converts text to numbers

Counts values

Validates data

Returns currency

10 / 20

What is the primary use of the SEQUENCE() function?

Create repeating values

Create an array of sequential numbers

Sort values

Join cells

11 / 20

What function can be used to create drop-down lists?

VALIDATE()

CHOOSE()

Data Validation

TEXTJOIN

12 / 20

What is the role of the LAMBDA function in Excel?

It sorts the data in ascending or descending order

It allows creating custom reusable functions without VBA

It links data between different workbooks

It encrypts the worksheet formulas

13 / 20

What is the purpose of the row_num parameter in the OFFSET function in Excel?

It defines the number of rows to include in the result range

It specifies the row number in a table to return data from

It sets the number of rows to move from the starting reference

It counts the total number of rows in a worksheet

14 / 20

What is the main difference between the HSTACK and VSTACK functions in Excel?

HSTACK merges cells horizontally, while VSTACK merges them vertically

HSTACK stacks arrays horizontally (in columns), while VSTACK stacks arrays vertically (in rows)

HSTACK combines text strings, while VSTACK performs vertical lookups

HSTACK removes duplicates, while VSTACK filters arrays

15 / 20

What is the shortcut key to open Manage Rules in Conditional Formatting in Excel?

Ctrl + Shift + M

Alt + O + D

Alt + H + L + R

Ctrl + 1

16 / 20

What does the table_array argument represent in the VLOOKUP function in Excel?

The value you want to search for in the table

The range of cells that contains the data for the lookup

The column number from which to return the value

A list of functions used in the lookup

17 / 20

What is the main difference between a Filter and a Slicer in a Pivot Table?

Filters can only be used with charts, while slicers can’t

Slicers are visual and interactive, while filters are drop-down based

Filters work faster than slicers in all cases

Slicers can only be used in Excel Online, filters in desktop version

18 / 20

What does the "field" parameter represent in the DSUM function in Excel?

The range of criteria used for filtering data

The name or index of the column to sum values from

The total number of records in the database

The worksheet name where the data is stored

19 / 20

What does the "criteria" parameter represent in the DCOUNTA function in Excel?

The number of columns to count in the database

The specific rows where counting should stop

The condition or set of conditions used to filter records before counting

The text value to be returned if criteria is met

20 / 20

What is the purpose of the INDIRECT function in Excel?

To automatically sort a range of values

To convert text into a valid cell or range reference

To find the position of a value in a range

To validate data entry in a cell

Your score is

The average score is 0%

Skill Checkpoint: See Where You Stand

Category: Advanced_Excel