Question 1

The ____ function locates data when it is displayed vertically in the lookup table.
A) FINDV
B) COUNTV
C) VLOOKUP
D) VERTLOOKUP

Accepted Answer

The VLOOKUP function is designed specifically for finding data in a vertical lookup table by looking up a specific value in the leftmost column of the table and returning a corresponding value from a specified column to the right of the lookup value. FINDV and VERTLOOKUP are not valid Excel functions, and COUNTV is designed for counting cells that meet certain criteria in a vertical range, not for looking up data in a table.

Question 2

The ____ function is used to total only cells that meet a certain condition.
A) SUMIF
B) COUNTIF
C) COUNTA
D) SUMWHEN

Accepted Answer

The SUMIF function is specifically designed to add up values in a range of cells that meet a certain criteria or condition.

Question 3

The COUNTA function counts the number of cells that do not have any numbers or text entered in them.

Accepted Answer

The COUNTA function counts the number of cells that have any data entered in them, including numbers, text, and blank spaces. It does not count the number of empty cells. The function that counts the number of empty cells is COUNTBLANK.

Question 4

When the Subtotal function is used by clicking the Subtotal button on the Data tab, data is summed within the data range to give subtotals and a grand total.

Accepted Answer

This is the correct description of the Subtotal function.

Question 5

After data is filtered, only the cells for the item you filtered will be displayed; the other cells will be hidden.

Accepted Answer

This is true. When you filter data, only the cells that meet the criteria you specified will be displayed, and the other cells will be hidden until the filter is removed.

Question 6

The function number 5 calculates SUM.

Accepted Answer

The answer of The function number 5 calculates SUM....

Question 7

You can count cells that have text with the COUNT function.

Accepted Answer

The answer of You can count cells that have text...

Question 8

Table_array is the range of cells for a lookup table.

Accepted Answer

This is correct, as the table_array is the range of cells containing the lookup table, which is used in a VLOOKUP or HLOOKUP formula.

Question 9

Excel does not accept spaces in named ranges.

Accepted Answer

This is true. Excel does not allow spaces in named ranges. If you need to use multiple words, use underscores (_) or camel case (e.g. myNamedRange).

Question 10

The SUMIF function will only total data that meets a certain condition.

Accepted Answer

The SUMIF function allows you to specify a criteria, and then add up only the values that meet that criteria.

Question 11

To use the HLOOKUP function, you will need the row number from the table that has the data you want returned to the cell with the HLOOKUP function.

Accepted Answer

This is correct. In order to use the HLOOKUP function, you will need to know the row number where the data you want to retrieve is located.

Question 12

The VLOOKUP function finds data when it is displayed horizontally in the lookup table.

Accepted Answer

The VLOOKUP function finds data when it is displayed vertically in the lookup table.

Question 13

To count the number of cells without content, you will use the ____ function.
A) EMPTY
B) COUNTE
C) COUNTA
D) COUNTBLANK

Accepted Answer

The COUNTBLANK function counts the number of cells in a given range that are blank or empty.

Question 14

To count cells with text, you use the ____ function.
A) COUNTA
B) COUNT
C) SUMIF
D) VLOOKUP

Accepted Answer

The COUNTA function counts all cells that have a value, whether it be text or numbers. This includes cells that contain only text. Therefore, it is the best choice for counting cells with text.

Question 15

You can count numerical data in a range using the ____ function.
A) COUNTA
B) SUMIF
C) LOOKUP
D) COUNT

Accepted Answer

The COUNT function is used to count numerical data in a range. COUNTA counts all data in a range, including non-numerical data, and would not be the best choice for this specific task. SUMIF and LOOKUP are not designed to count data, but rather to add or retrieve specific values based on specified criteria.

Question 16

After you name a range, Excel remembers that name.

Accepted Answer

Once you name a range in Excel, it is recognized by that name until you delete it or rename it.

Question 17

Named ranges are helpful if you need to use a range in several functions.

Accepted Answer

Named ranges make it easier to refer to a range in multiple places, which can be very helpful if you need to use the same range in several functions or formulas.

Question 18

Using the COUNT function lets you count only cells that are displayed.

Accepted Answer

The answer of Using the COUNT function lets you count...

Question 19

You use the HLOOKUP function when the lookup table is listed vertically in columns.

Accepted Answer

HLOOKUP stands for "horizontal lookup" and is used when the lookup table is listed horizontally in rows. VLOOKUP, on the other hand, is used when the lookup table is listed vertically in columns.

Question 20

For the Row_index_num of the HLOOKUP function, you will need to specify what to look for in the lookup table.

Accepted Answer

The answer of For the Row_index_num of the HLOOKUP function,...

Quiz 19: Using Powerful Excel Functions

The ____ function locates data when it is displayed vertically in the lookup table.

The ____ function is used to total only cells that meet a certain condition.