Excel II
What error code is displayed in a cell when a required value for the formula is not found?
#N/A
When searching for a text file to import, Excel displays a list of all files in the active folder ending with a .prn, .txt, or _____ file extension.
.csv
The text file format for use with Macintosh systems is designated by the _____ file extension.
.txt
Excel's default XML-based format (.xlsx) cannot store macro code, so a workbook with macros is saved instead with the ____ file extension.
.xlsm
When you save a workbook as a template, you save the file with the ____ file extension if it does not contains macros, and you save it with the ____ file extension if it does contain macros.
.xltx; .xltm
The RAND function returns a random number between
0 and 1
If you specify a custom number format of 000.00, typing 3.14 in the cell will display as......
003.14
You can format a range of values using an icon set to classify data into what catagories
3-5
A formula that references the same cell in a range that extends over two or more worksheets is often called a(n) ___reference
3-D
Excel provides over how many preprogrammed functions?
300
What visual representation is displayed when Excel detects an error condition in a cell?
A green diagonal triangle appears in the upper left corner of the cell
Which of the following is the function used to return the absolute value of a number (that is, the number without its sign)?
ABS
This logical function returns False if any one of the conditions test false.
AND
The ____function returns the arithmetic mean of a range of numbers.
AVERAGE
The ____ function is used to find the arithmetic mean of the cells within a specified range that meet a single criterion.
AVERAGEIF
Which of the following functions requires these arguments: (average_range,criteria_range1, criteria1,criteria_range2,criteria2...)?
AVERAGEIFS
Once you have chosen a function and entered the correct reference in the Consolidate dialog box, click the ___ button before setting up the next reference
Add
Which of the following Excel features keeps the last version of a workbook saved in a temporary file?
AutoSave
What happens when you open a workbook with a linked external reference?
Automatic updates are disabled and Excel displays an alert message from which you can enable the content
Which of the following functions is used to join the content of two or more cells?
CONCATENATE
After a chart has been pasted into PowerPoint, which of the following is not a button that you can use to modify a chart?
Chart format
The Clear Rules option at the Conditional Formatting button drop-down list contains two options for removing rules: Clear Rules from Selected Cells and
Clear Rules from Entire Sheet
Edit comparison rule criteria and/or formatting options for a conditional formatting rule by opening what
Conditional Formatting Rules Manager dialog box
Chapter 4 discusses the ____ feature as a means to summarize data from multiple worksheets or from another workbook into a master worksheet
Consolidate
When recording a macro, you have the option of assigning the macro to a(n) ____key combination
Ctrl
Features such as Text to Columns can be found in the ___ group on the Data tab
Data Tools
The Consolidate button is located in the ____ group on the Data tab.
Data Tools
____ cells are cells that contain a formula that refers to other cells.
Dependent
The _____ can reveal the presence of headers and footers.
Document Inspector
The ___ button in the Formula Auditing group steps through a formula value by value to determine where an error exist within the formula.
Evaluate Formula
You can change the name of the user associated with your copy of Excel by opening the _________ dialog box.
Excel options
When you record a new macro with the same name as an existing macro.....
Excel prompts you to replace the existing macro.
What is the keyboard shortcut for the Save As command?
F12
Customize save options by first clicking the _____tab.
File
To add a password to an open workbook, begin by clicking the ____ tab.
File
To open the Info backstage area, click the ______ tab.
File
To use a template you created, start by clicking the ____ tab
File
To customize the ribbon, click the _____ tab and then click ____
Files, Options
Excel 2016 offers a feature called ___ that extracts, joins, and inserts text, numbers, dates, and times.
Flash fill
Clicking the More Number Formats option at the Number Format list box in the Numbers group on the Home tab will cause the _____dialog box to open.
Format Cells
Custom number formats are deleted at the ____ dialog box.
Format cells dialog box
Use the ___ option at the Paste Special dialog box to paste only the source formulas to a destination cell
Formulas
You can create, edit, or delete range names at the Name Manager dialog box, which can be opened from the ____ tab.
Formulas
The Number group on the Home tab contains 3 buttons for the more popular formats. Which is not one of them.
Fraction style (Comma style, percent style & accounting number format are included)
Importing data into Excel is handled from the _____ group on the _____ tab.
Get external data;Data
To check on the availability of an existing preprogrammed function in Excel, open the Insert function dialog box, type a description of the function in the Search for a function text box, and then click the ____ button.
Go
The___ feature is useful for a situation in which you know the result you want to achieve but are not sure what value will get you there.
Goal Seek
The ____ check box on the Table Tools Design is used to show or hide the column headings row in a table
Header Row
When tracking the changes made to a workbook is no longer necessary, click the Track Changes button and then click _____ at the drop-down list.
Highlight Changes
The Edit Links dialog box contains all of the following buttons except.....
Import Source (contains: break link, change source, update values)
If you want to export Excel data to an Access database that does not have an existing table in which to receive the data, perform an _____ routine from _____.
Import;Access
In a one-variable data table, where should you place the formula for calculating the various outcomes?
In the first row above and one cell to the right of the table values
What happens when you click the Trace Precedents button a second time?
Indirect relationships will be shown
Excel provides several table styles categorized by ___ color themes
Light, Medium, Dark
If you no longer need a macro, delete it at the ______ dialog box.
Macro
This feature in Window 10 allows you to save and share documents in a storage location on the Internet.
Microsoft OneDrive
When you create a macro in Excel, the commands are written and saved in .....
Microsoft Visual Basic for Applications (VBA)
This logical function returns False if all of the conditions test false.
OR
Delete a custom template at the ____ dialog box.
Open
To open and view a(n) _____ file, the recipient of the file must have Adobe Reader.
Both _____ and _____ are fixed-layout formats.
PDF;XPS
In arguments for the PPMT function, _____ is the amount of money borrowed.
PV
Copied Excel data is embedded into a Word document from the _____ dialog box.
Paste Special
A PivotChart visually displays the data from a(n) ____ in chart form
PivotTable
A ____ is an interactive table that organizes and summarizes data based on category labels you designate from row headings and column headings
PivotTable
You can create a scenario summary report to compare scenarios side by side in a worksheet or a
PivotTable
The Excel Options dialog box contains several option groupings in the left pane. Which of the following is not one of them?Language,Advanced,Popular,Formulas
Popular
When you have protected a worksheet, the ____ button in the Changes group on the Review tab changes to the Unprotected Sheet button.
Protect Sheet
The _____ button in the Changes group on the Review tab can be used to prevent changes to the structure of a workbook.
Protect Workbook
The ____ function is used to modify the actual number of characters by rounding the value
ROUND
The Reset button that displays below the Main Tabs list box in the Excel Options dialog box offers two options: Reset only selected Ribbon tab and .....
Reset all customization
To turn on the Track Changes feature, start by clicking the Track Changes button in the Changes group on the ____ tab.
Review
The ___ feature allows you to store multiple sets of assumptions about data and then view the impact of those assumptions on your worksheet.
Scenario Manager
What formatting converts a number to exponential notation?
Scientific
What is the first step when transposing data?
Select source range
Which backstage area do you use to send a workbook via email?
Share
You can use ____ to filter a PivotTable report or PivotChart without using a filter arrow
Slicers
Chapter 4 suggest using ___ to help a reader quickly determine visually if a trend or pattern exists within data set
Sparklines
____ are miniature charts embedded into the background of cells.
Sparklines
At the Format Cells dialog box with the Number tab selected, choosing Special in the Category option and English (United States) in the Locale option, the Type option will contain each of the following options except
Student ID (it contains, phone number, SS# & zip code)
To change the line and/or marker appearance of a Sparkline, use options in the ___ group of the Sparkline Tool Design tab.
Style
Chapter 3 suggest that you can convert a table into a normal range if you no longer need the table features or in order to use the ____ feature
Subtotal
When you open the Consolidate dialog box, by default the ____ function is selected in the function option box
Sum
At the info backstage area, workbook properties such as Author, Categories, and ____ can be entered.
Tags
What happens if you assign a macro to one of Excel's built-in keyboard shortcuts, such as Ctrl-P, which opens the Print dialog box?
The macro will override the existing Excel shortcut
The PivotTable field task pane contains areas to which you can drag fields in order to build the table. Which of the following is not one of the area boxes included?
Title (includes: Filters, values, columns)
When you turn on the ______feature, Excel will automatically share the workbook.
Track changes
Which of the following options in the Paste Special dialog box alters the physical arrangement of the data to be pasted?
Transpose
Which of the following is not a way to create a PivotTable?
Use the recommended PivotTable button on the Table group of the Quick analysis button. (can: use recommended PivotTable button in the Tables group on the Insert tab, use PivotTable button in the Tables group on the Insert tab, Use the blank PivotTable button on the Tables group of the Quick Analysis button.)
Since most lookup tables are arranged with comparison data in columns, the ____ function is the more commonly used lookup function.
VLOOKUP
Excel's Goal Seek feature is available at the ___ button on the Data tab.
What-if Analysis
The method outlined in Chapter 6 for printing a workbook's change history includes clearing check marks from which two options in Highlight Changes dialog box?
Who and Where
The three types of Sparkline charts are line, column, and
Win/Loss
If you are using Windows _____, you may need to download the XPS viewer in order to read an XPS document.
XP
A small red diagonal triangle in the upper right corner of a cell indicates that.....
a comment exists for that cell.
At the Format Cells dialog box with the Number tab active, Excel provides special number formats that are specific to what?
a country and language
What kind of cell would you add to the watch window?
a dependent cell
A two-variable data table should be constructed with a column along the left containing one set of variable input values and ___ containing the second set of variable input values.
a row along the top
The COUNTIF function counts cells that meet ......
a single criterion
Conditional formatting can be applied to a cell based on what
a specific value in the cell
Excel creates a link formula using a(n) ____ reference to the source cell.
absolute
At the Excel Options dialog box, the options that affect the display of Excel are grouped _____
according to what they affect; the active worksheet, the entire workbook, or the global display settings
In the Table Style Options group on the Table Tools Design tab, the First Column and Last Column options are used to ___ these columns.
add emphasis to
By default, ____ columns are selected when you open the Removed Duplicates dialog box.
all
If you build a PivotChart from scratch, Excel will
also build a PivotTable in the backgroud
You can create ____ models with the Scenario Manager dialog box.
any number of
The COUNTA function returns a count of the number of cells in the specified range that ......
are not empty.
Change the values associated with scenario
at the Scenario Manager dialog box
You can customize the Quick Access Toolbar by clicking the Customize Quick Access Toolbar button located ______
at the right side of the toolbar
In a two-variable data table, the source formula is placed
at the top left cell in the table
When you use a data bar in your conditional formatting, the bar is added to the _____, and the length of the bar is dependent on the ________
background of the cell; value within the cell
When you create a PivotChart from an existing PivotTable, changes made to the filtering in the PivotChart will
be reflected in the PivotTable
Chapter 4 suggests that while creating a PivotTable, it is important to make sure that the source data contains no
blank rows or columns
Then you add subtotals to a range of data, Excel displays ___ along the left side of the worksheet area
buttons
Typing a formula in the first row of a new table column creates a ____ column
calculated
One drawback of a shared workbook mentioned in Chapter 6 is that it.....
cannot support all of Excel's features.
After the Document Inspector scans a workbook, the results show a(n) ____ in the section for which no items were found and a(n)____ in the sections in which items were detected.
check mark;exclamation mark
When a password is encrypted, the text you type is converted into a scrambled format called....
ciphertext
When you want to allow another user to change data in some cells in a protected worksheet, the first step in applying the protection is to
clear the lock attribute on the cells you want the user to have access to.
If you remove duplicate rows by mistake
click Undo
To add a command name to a tab at the Excel Options dialog box, start by ......
clicking the group name within the tab
The CSV text file format is _____ delimited.
comma
A(n)______ is pop-up box containing text that displays when the pointer is resting over a cell.
comment
When you save an Excel 2016 workbook in the Excel 97-2003 format, Excel automatically does a ......
compatibility check
Chapter 3 suggest that before you remove records with the remove duplicates dialog box, you ___
conditionally format duplicate values so that you can see what will be removed
Ctrl + C is the keyboard shortcut for the _____ feature.
copy
In the Name Manager dialog box, you can do all of the following except....
copy a range (you can edit, delete, and create.)
Chapter 8 suggests that to use Excel data in Word, PowerPoint, or Access, use the ____ routine since the programs within the Microsoft Office Suite are designed for integration.
copy and paste
Because Excel replaces the changing cell's contents when you show a new What-If scenario, it suggest that you.....
create a scenario using the original cell values
Which of the following is not one of the arguments required by the VLOOKUP function?
criteria (table_array, col_index_num, lookup_value are)
A ____ is a range of cells that contains a series of input values.
data table
Which of the following is one of the workbook properties that Excel adds automatically?
data the workbook was created
Excel's ___ feature allows you to control the type of data accepted for entry in a cell
data validation
In custom number formats, the # format code represents what
digit
Excel _______ when conflicts to a cell occur because two users have the same file open at the same time and attempt to change the same data.
displays a prompt
You cannot modify workbook properties that....
do not display with the message Add a [property]
The _____ feature scans a workbook for personal or other hidden information that you would not want others to see.
document inspector
A one-variable data table contains variable input data values in a series
down a column or across a row
Conditional formats can be based on date or text entries or on ______
duplicated values
By default, when a worksheet is protected.
each cell in the sheet is locked
By default, Excel bands the rows within a table when you apply a theme, which means that
even rows are formatted differently from odd rows
How often does AutoRecover save document information?
every 10 minutes
The Paste Special dialog box allows you to perform each of the following mathematical operations except.....
exponentiation (allows; subtraction, addition, division)
The formula =[Invoices.xlsx]January!B6 is an example of a(n) ____ reference.
external
When you summarize data in one workbook by linking to a cell or range in another workbook, the method is said to incorporate ___ references
external
In an Excel table, columns are called
fields
In addition to specifying the layout of digits and decimals places in your custom number format, you can also specify?
font color
When you use an IF statement in a formula to apply conditional formatting, the formatting will be applied to all cells-----
for which the conditional test returns a true result
Excel has _____ options for macro security settings.
four
The Number Format list box in the Number group on the Home tab includes date, time, scientific, text & ______ options.
fraction
In the VLOOKUP functions, the col_index-num argument refers to the column number
from the lookup table that contains the data you want returned.
In the Office 2016 suite, the charting system is _____ within Word, Excel, and PowerPoint.
fully integrated
When the structure of a workbook has been protected, the Protect Workbook button display with a(n)
gray shaded background
Timelines allow you to
group and filter a PivotTable based on specific timeframes
You can customize the ribbon by creating a new tab that includes......
groups and buttons
The first row of an Excel table contains column headings and is called the field names row or the ____ row
header
When you choose the 3 arrow (colored) icon set in your conditional formatting, the green up arrow represents what values
higher
If you use a two-color scale in you conditional formatting, you specify the shade of color that represents _____ values.
higher and lower
In the HLOOKUP function, what does the "H" refer to?
horizontal
A new group can be created .....
in either a new or existing tab
The Data Validation dialog box gives you the option of adding a(n) ____ message and a(n) _____message.
input; error alert
When transferring data from Excel to Word, you should use the copy and paste method if the data being brought into Word
is not likely to be updated after the cells are pasted
By default an Excel table displays a filter arrow button next to each ___ in the ___
label; table header row
If you need to add data near a table without having the table expand, -----
leave a blank column or row between the table and the new data
A macro name must begin with a(n) _____ an cannot contain ______.
letters;spaces
The Paste gallery includes a ____ of how the data will be pasted to assist in choosing the correct paste option
live preview
AND, IF, and OR are considered to be ___ functions.
logical
A _____ is a series of instructions stored in sequence that can be recalled and carried out whenever the need arises.
macro
The COUNTIFS function is used to count cells that ....
meet multiple criteria
Workbook properties are sometimes referred to as _____, a work used to identify descriptive information about data.
metadata
A single-file web page has this file extension.
mht or .mhtml
Which is not an icon setting available in the Icon Sets gallery in the Conditional Formatting button drop-down list?
moon phases
A range of data with a column that has ___ can be grouped, and subtotals can be created for each group automatically
multiple rows with the same field value
A worksheet can contain how many tables
multiple tables
Assigning a ____ to a range of cells allows you to reference the source by a descriptive label than the range address.
name
A ____ function is one function enclosed within another function.
nested
A share workbook is generally saved to a _____ to provide access to other individuals.
network folder
Text files contain _____ formatting.
no
When you create a macro in a workbook and then close the workbook, the macro is.....
no longer available
When you have created external references, moving the source workbook or changing its name will cause the link to
no longer work
The nper argument in the PPMT function refers to the .....
number of payment periods
How many Access tables can be imported into Excel at the same time?
one
Chapter 7 suggest you search _____ before creating a custom template.
online
When you click the Formats option in the Paste Special dialog box, Excel will paste ___ the formatting options from the source cells into the destination cells.
only
Changing the macro security setting in Excel changes the security setting.
only in Excel
When you link Excel data to a Word document, the source data exists
only in Excel
Running a macro is sometimes refereed to as ______ a macro
playing
Change made to a workbook are displayed in
pop-up box when you rest the mouse pointer over a changed cell.
The custom number format [Green];[Red] will display _____ in green and _____ in red.
positive numbers; negative numbers
The PPMT function returns the ____ of a specific payment for a loan.
principal portion
Chapter 6 suggest that before changing a shared workbook to an exclusive workbook, you should consider ______ so that you have a record of the editing actions made by all users.
printing the change history
When two users open the same workbook and make a change to the small cell, Excel _____ to resolve the conflict.
prompts the second user
The Trace Precedents button in the Formula Auditing group draws arrows to cells that
provide data to the active cell
In addition to the asterisk, which wildcard character can be used in a custom filter?
question mark ?
To create a table in Excel, enter data in the worksheet and then define the desired ___ as a table
range
Chapter 4 suggests using ____ to simplify formulas that summarize data in multiple worksheets.
range names
What three arguments are required by the SUMIF function
range, criteria, and sum_range
The data entered into rows in an Excel table are referred to as
records
Placing a checkmark in the Windows option in the Protect Structure and Windows dialog box will prevent the user from.....
resizing or changing the position of the windows in the workbook
For the range_lookup argument in the HLOOKUP and VLOOKUP functions, a value of FALSE will instruct Excel to .....
return only exact matches to the lookup value
To access the Rename dialog box so that you can change a tab name .......
right-click the current tab name.
To break a link, begin by opening the document and then
right-clicking the linked object
Once an Excel table has been defined, typing new data in the ____ will cause the table to expand automatically.
row immediately below the last row of the table or column immediately to the right of the last column in the table.
The Ungroup dialog box contains two options
rows and columns
The Transpose option in the Paste Special dialog box converts
rows to columns and columns to rows
What is the first step in sorting by color?
select the range
Delete a scenario by
selecting the scenario in the Scenario Manager dialog box and clicking the Delete button
Each macro you record is saved as a ....
separate module within a VBA Project for the workbook
When you create a formula that references a cell in the same worksheet, you do not need to include the ____ in the reference
sheet name
Chapter 3 explains how to use the Text to Columns feature to
split columns in which more than one field has been entered
In a formula with an external reference, the external workbook name is enclosed in
square brackets []
By default, Excel uses the ____ function to summarize the numeric value added to a PivotTable
sum
When creating a password, you should include a combination of uppercase letter, lowercase letters, numbers, and .......
symbols
A ___ in Excel is similar in structure to a database.
table
When you use the AutoFilter feature, rows that do not meet the criteria you specify are----
temporarily hidden from view
The Top/Bottom Rules in the Conditional Formatting button drop-down list allows you to highlight cells based on a top ___ or bottom _____ value or percent
ten; ten
A ___ file is often used to exchange data between dissimilar programs since the file format is recognized by nearly all applications.
text
If you need to exchange Excel data with someone who is not able to import a worksheet or cannot copy and paste using the clipboard, Microsoft provides several _____ file options for exchanging data.
text
UPPER and LOWER are considered what functions
text
Chapter 6 advises caution when accepting and rejecting tracked changes because....
the Undo button is unavailable after you have reviewed the cell
When you have Excel data linked to a Word document, breaking that link means that the data in
the Word document will no longer be connected to the data in the Excel workbook
You can use the Column widths option in the Paste Special dialog box to adjust
the destination cells to the same column width as the source.
The function IPMT is used to calculate.....
the interest portion of a loan
Adding a total row to a table causes Excel to add the word total in a new row at the bottom of the table in ----
the leftmost cell
In a calculated column, Excel copies the formula from the first cell to ___ as soon as you enter the formula
the remaining cells in the column
The HLOOKUP function uses ____ argument parameters as the VLOOKUP function.
the same
Custom number formats are stored where?
the workbook in which they are created
The Convert Text to Columns Wizard contains ____ dialog boxes to guide you through the steps of separating data.
three
In the SUMIF function, the range argument refers to the cells......
to be tested for the criterion
What is the purpose of the TRIM function?
to remove extra spaces between characters.
The color scales that you can apply in conditional formatting will format a range of cells using either a ____ or a _____ palette.
two-color: three-color
3-D formulas can be created by
typing directly into the cell
In the Consolidate dialog box, clicking the Create links to source data option will cause Excel to
update the data automatically when the source ranges change
Chapter 8 states that marking a workbook as final is not as secure as
using password-protected, locked ranges
When you check the Divide option in the Paste Special dialog box, the ___ are divided by the _____
values being pasted; values already in the destination cells
The error alert icon that displays as a yellow triangle with an exclamation point inside is used for a _____ error alert
warning
Which of the following situations is described in Chapter 5 as a time when the Circle Invalid Data feature would be useful?
when data validation rules have been set up after data have been entered
Chapter 7 suggests that when saving a workbook as a template, you protect the worksheets by locking all the cells except those that .....
will hold variable data
If you want your macros to be available for other workbooks, consider creating a macros ......
workbook
Templates are _______ with standard text, formulas, and formatting.
workbooks