Excel Assessment 3

A PivotChart report represents source data as a(n) graphic.

true

Dividing text into columns is also referred to as parsing text into columns.

true

The INDEX function allows you to retrieve data from multidimensional tables.

true

The Query Wizard lets you choose your data source and select the database table and fields you want to import into a workbook.

true

The Subtotal command creates summary reports that quickly organize data into categories with subtotal calculations.

true

The TRIM function removes all spaces in a text string except for the single spaces between words.

true

The easiest way to create a PivotChart report is to use an existing PivotTable report as the source data.

true

You can often use a LOOKUP function instead of a VLOOKUP or HLOOKUP function with a TRUE lookup type.

true

The syntax of the LOOKUP function is as follows: =LOOKUP(____,lookup_vector,result_vector).

lookup_value

Unlike a regular chart, you can change the layout and data displayed in a PivotChart report by ____.

moving fields

The syntax of the VLOOKUP function is as follows: =VLOOKUP(lookup_value,table_array,col_index_num,____)

range_lookup

INDEX function allows us to

retrieve data from multidimensional tables

In an INDEX function, the argument ____ refers to the number of the row in the range referenced in the first argument.

row_num

You use VLOOKUP when ____ of the lookup table contains the data you are looking up.

the first column

PivotTable is in the _______ tab

insert

The option to insert a table or pivot table is in the _______ tab.

insert

tables is under the _______ tab

insert

The PivotTable ____ task pane contains a list of fields in the data source you selected for the PivotTable report.

Fields

The syntax for the CONCATENATE function is as follows: ____.

=CONCATENATE(text1,text2,...)

FIND syntax

=FIND(find_text, within_text, [start_num])

HLOOKUP syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

INDEX syntax

=INDEX(array, row_num, [column_num])

LOOKUP syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

RIGHT syntax

=RIGHT(text, [num_chars])

VLOOKUP syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

YEARFRAC syntax

=YEARFRAC(start_date, end_date, [basis])

The ____ function returns the last character or characters in a text string, based on the number of characters specified.

RIGHT

The default field setting in the ____ area is to calculate a sum of the items in this drop area by the categories in the Row Labels area or Column Labels area.

VALUES

You can change the calculation used in the VALUES area by right-clicking a value in the VALUES area to open the shortcut menu, and then clicking Field Settings to open the ____ dialog box.

Value Field Settings

insert slicer is in the ________ tab

analyze

True

approximate match that doesn't exceed the lookup value

The first row (key values) of the lookup table must be sorted in ____ order to use an HLOOKUP function with a TRUE lookup type.

ascending

A common way of storing data so that it is usable in other programs is to save it in a ____ file.

comma-delimited

The ____ Wizard separates the values in a text string into columns or fields.

convert Text to Columns

get data (from database) is under the ___________ tab

data

subtotal is in the _______ tab

data

text to columns is in the ____________ tab

data

the sort option is in the __________ tab

data

False

exact match

If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP opens up the Lookup Wizard.

false

In a one-dimensional table, Excel searches one dimension, such as the columns, and then searches another dimension, such as the rows, to find the value at the intersection of a single row and column.

false

Placing a field in the Row Labels area of a PivotTable displays data from that field in columns and rows.

false

When you use a lookup type of TRUE, the VLOOKUP function looks only for an exact match of the lookup value.

false

A(n) PivotSheet report is an interactive report that lets you summarize and analyze a data set.

false: PivotTable

The LAST function returns the last character or characters in a text string, based on the number of characters specified.

false: it is the RIGHT function

The CONCATENATE function combines the values in a range of cells into one numeric item in a new cell.

false; one text item

The first goal when working with unstructured data is to determine the ____ you need so you can find the best way to change the unstructured data into structured data.

format

A PivotChart report represents source data as a ____.

graphic