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