ALL OF LEVEL TWO Flashcards
How will numbers display when the format code is [Red];[Blue]?
Positive numbers will display in red; negative numbers in blue
To create your own number format, select _____ in the Category list box at the Format Cells dialog box with the Number tab selected.
Custom
Suppose you have a worksheet in which you enter monthly expense figures and compare them to monthly budget figures. To have an expense figure automatically display in red if it exceeds the budgeted amount, you should use
conditional formatting
Which of the following would you use to set up a filter to display the names of all US states beginning with the letter M?
M*
_____ formatting converts numbers to exponential notation (E + n).
Scientific
Which of these formatting Type options is not available for US English at the Format Cells dialog box with the Number tab selected?
Credit Card Number
Icon sets allow you to…
classify data into three to five categories
Which of these custom number format codes aligns numbers vertically on the decimal point?
????.??
Which of the following numbers is represented by 2.60E + 05?
260,000.00
Which of the following buttons is not available when you click Manage Rules at the Conditional Formatting button drop-down list?
Copy Rule
In the conditional formatting icon sets gallery, the available icons are organized into four sections. Which of the following is not one of the sections?
Alphabetical
When applying conditional formatting using predefined rules, the first step is to…
select a range of cells
How can you tell that a filter has been defined for a column?
A filter arrow appears in the column heading
How can you format a cell based on the value in a different cell?
Use a formula
In the Select a Rule Type section of the New Formatting Rule dialog box, you…
choose the type of condition to have Excel check before formatting
At the New Formatting Rule dialog box, begin creating a new rule by choosing which of the following?
the type of condition for Excel to check before formatting
Changing the appearance of a cell based on a condition is called…
conditional formatting
To filter data using an advanced filter, enter criteria on _____ row(s) when using the And logical operator and enter criteria in _____ row(s).when using the Or logical operator.
the same; separate
The _____ Excel feature is used to display only the rows that meet specified criteria.
Custom AutoFilter
Excel provides special number formats that are specific to which of the following?
countries and languages
Conditional formatting cannot be applied based on which of the following?
errors in data entry
If a custom number format is deleted, the custom formatting will be removed from which of the following?
all the cells to which it was applied in that workbook
When filtering a range using an advanced filter, start by…
adding five or six rows at the top of the worksheet
The Edit the Rule Description section of the New Formatting Rule dialog box varies depending on…
the active option in the Select a Rule Type section
You can filter values by more than one criterion at a time by using…
a comparison operator
In the Conditional Formatting Rules Manager dialog box, the default setting for the Show formatting rules for option is…
Current Selection
If the custom number format code is ###.##, which value will display if the value 23.959 is entered?
23.96
Which of these questions would best be answered using conditional formatting?
Which salespeople exceeded their goals last month?
Values in rows that do not meet the criteria in the Custom AutoFilter feature are…
temporarily hidden from view
Using _____, you can classify data into categories based on threshold values for the selected range.
icon sets
The first step in creating a custom number format is to…
select the range
Which of the following is not an example of a conditional formatting rule?
Format only values in exponential notation
One way to remove conditional formatting is to select the range, click the Conditional Formatting button, point to Clear Rules, and then choose
Clear Rules from Selected Cells
How do you open the New Formatting Rule dialog box?
click New Rule
You can edit or delete conditional formatting rules in the _____ dialog box.
Conditional Formatting Rules Manager
Select the option(s) _____ at the Custom AutoFilter dialog box to specify two criteria by which to filter.
And or Or
The custom number format allows you to do each of the following except…
combine arabic numbers and roman numerals
When you delete a custom rule at the Conditional Formatting Rules Manager dialog box, what happens to cells already formatted by the rule?
The formatting is removed
Which of these custom number format codes will round values to fit the number of digits after the decimal point and fill in leading zeros?
000.00
Excel can add text such as GX- before all the values typed in cells if the cells have been formatted using the _____ category.
Custom
You can create custom rules based on each of the following except…
cell position
As a visual guide to see the variation of values in a range, Excel provides each of the following except…
animated graphics
Which of the following is not one of the options on the Formatting tab of the Quick Analysis button?
Highlight Cell Rules
If the appropriate formatting has been applied, data can be filtered by all the following except
font type
The IF function’s logical test returns _____ result.
only a true or false
Which of the following is not an option in the Conditional Formatting button drop-down list?
Font Sets
Exponential notation is often used by engineers and scientists for…
very small and very large numbers
With conditional formatting, cells that do not meet the criteria specified
remain unchanged
You _____ create your own conditional formatting rules.
can
Which of the following is not one of the 13 categories of functions mentioned in Chapter 2?
spatial
Which of the following represents the proper structure of the arguments for an HLOOKUP function?
(lookup_value, table_array, row_index_num, range_lookup)
Which of the following scenarios would be the most likely application of the VLOOKUP function?
looking up a number grade and returning a letter grade
The ABS, RAND, SQRT, and SUMIF functions are in the _____ category.
math and trigonometry
Which of these functions uses an argument containing multiple criteria?
COUNTIFS
The VLOOKUP argument that identifies a range of cells, rather than a value, a single cell, or a column number, is…
table_array
The AND and OR functions use _____ logic to construct a condition test in a formula.
Boolean
If you divide the interest rate by 12 for a monthly rate with the PMT or PPMT function, you might have to multiply the _____ by 12.
number of payment periods
A _____ is a preset formula.
function
What is the first step in creating an AVERAGEIF formula?
Make active the cell in which the formula will be inserted
You can automatically create range names based on column or row labels by clicking the _____ button in the Defined Names group on the Formulas tab.
Create from Selection
The lookup_value argument in the VLOOKUP function specifies which of the following?
the value that Excel searches for in the reference table
Excel’s Lookup & Reference functions provide a way to…
look up values in a range
Excel stops evaluating an IF formula…
once the logical_test has been answered as true
In a PPMT function, the argument fv refers to the…
balance at the end of the loan
Which function should be used if you want to count the number of teams that have won more than 10 games and have fewer than 25 people on the roster?
COUNTIFS
If a range name used in a formula is deleted, cells that used the name will display this error message.
NAME?
By default, what happens when the VLOOKUP function does not find an exact match in the first column of the VLOOKUP table?
it looks in the first column of the table for the largest value that is less than the lookup_value
A function includes two parts: the name of the function and the…
argument
The _____ function will take the value –7 as an argument and return the value 7.
ABS
The _____ function will find a value in a column of data and use it in a formula.
VLOOKUP
The _____ function contains these arguments: (logical_test,value_if_true,value_if_false).
IF
In a nested IF function, how does Excel help you keep track of the parentheses that belong to each IF function?
It color-codes them
The _____ argument of the PPMT function can contain only a 1 or a 0.
type
Which of the following functions will result in a number rounded to the nearest tens value?
=ROUND(145,-1)
Use the _____ function to find a value when the table contains comparison data organized in rows.
HLOOKUP
To make a formula easier to read, place each logical test on a new line and then…
expand the Formula bar
To place an argument or logical test on a new line in the Formula bar, place the insertion point immediately before the logical test and then press which of the following?
ALT + enter
Use the _____ function with an IF function to test multiple conditions.
AND
A nested IF formula can be used…
when more than two outcomes are possible
Excel will add the required syntax automatically when you create a COUNTIF formula…
at the Insert Function dialog box
MAX and MIN are statistical functions that return the _____ and the _____ value in the range, respectively.
largest; smallest
The syntax of the COUNTIF and COUNTIFS arguments requires that the criteria _____ if it is not a cell reference.
be enclosed in quotation marks
The result of an IFS function must test…
true
The _____ Excel function calculates the principal portion of a loan payment.
PPMT
What is the keyboard shortcut for opening the Name Manager dialog box?
Ctrl + F3
The argument (range,criteria,average_range) is required by the _____ function.
AVERAGEIF
In Excel, an argument can include any of the following except..
a specified format
The _____ function is in the math and trigonometry function category.
SUMIFS
Which of the functions discussed in Chapter 2 requires the argument (range1,criteria1,range2,criteria2)?
COUNTIFS
Unlike a nested IF function, an IFS function does not require each argument to be enclosed in…
(?)
NOT parentheses (( ))
The COUNTIF function requires two arguments: range and
criteria
The number of right parentheses needed to end a nested IF statement equals the number of times
IF appears in the formula
The “range to calculate” argument comes first in all the following functions except the _____ function.
SUMIF
What argument follows sum_range in the SUMIFS function?
criteria_range1
Range names can be created, edited, and deleted at the Name Manager dialog box, which is opened by clicking the
Name Manager button on the Formulas tab
Which of the following formulas uses the correct syntax?
=COUNTIF(Expenses,”>700”)
In the VLOOKUP function, what does the col_index_num argument indicate?
the column number from the lookup table that contains the data to be placed in the formula cell
Which of these lookup functions is most commonly used?
VLOOKUP
Which of the following is not one of the arguments in the PPMT function?
pmt
If you type a formula in the first record of a new table column, Excel automatically creates a(n)
calculated column
The _____ guides you through the steps of separating data into columns.
Convert Text to Columns wizard
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
By default, where does Excel display filter arrows in a table?
next to each label in the header row
By inserting a check mark in the First Column or Last Column check box in the Table Style Options group, you can
format that column differently
What is the first step when splitting text into multiple columns?
Insert a blank column(s) next to the source data
The Table Tools Design tab contains options for _____ the table.
formatting
Excel can compare records within a worksheet and automatically delete duplicate rows based on
the columns selected that might contain duplicate values
On the Table Tools Design tab, which of these boxes contains a check mark by default?
Banded Rows
Which of the following is not one of the three tabs at the Data Validation dialog box?
Add Level
To convert a table to a normal range, use the
Convert to Range button in the Tools group
What happens if you type new data in the row immediately below the last row of a table?
The table automatically adds a new row
Add a Total row to a table by clicking the
Total Row check box on the Table Tools Design tab
The Data Tools group on the Data tab does not contain which of the following buttons?
Sort & Filter
Which of the following is not one of the types of error messages that appears when incorrect data is entered in a cell?
Fatal
To select only specific subtotals and/or grand totals in a collapsed outline, click the Find & Select button, click the Go to Special option, click the _____ radio button to insert a bullet, and then click OK.
Visible cells only
_____ is not one of the data validation criteria available in the Allow option box at the Data Validation dialog box.
Fraction
Use the keyboard shortcut Shift + Alt + Right Arrow key to _____ data.
group
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
A PivotChart can be created directly from a PivotTable or from
the data in a worksheet
What are Sparklines?
miniature charts embedded in the background of cells
Which of the following areas does not appear in the layout section of the PivotTable Fields task pane?
Slicers
Slicers allow you to
filter without using a filter arrow
To change a Sparkline chart from a line to a column format, you will use tools in the _____ group.
Type
What will happen to the link to an external reference if you move the source workbook?
The link will not work
How does Chapter 4 suggest that you use Sparklines?
to show high or low values within a range
In a formula, a(n) _____ is used to separate a worksheet reference from a cell reference.
exclamation point (!)
To create a PivotChart in a worksheet without a PivotTable,
select the data range, click the PivotChart button arrow, and then click PivotChart
To permanently remove a linked reference, click the _____ button at the _____ dialog box.
Break Link; Edit Links
Insert a check mark in the Header Row check box in the Table Styles Options group to
show or hide the column headings in the table
When a table automatically expands to include new entries, the _____ displays.
AutoCorrect Options button
Before creating a subtotal for a range of related data, you must first
sort the data by the fields in which the records are to be grouped
The Table button is in the
Tables group on the Insert tab
The Stop Automatically Expanding Tables option appears when you click the _____ button.
AutoCorrect Options
What is the keyboard shortcut for adding a Total row?
Ctrl + Shift + T
What is the first step in creating a PivotTable?
Select the source range
The point-and-click method of creating a link to another workbook creates an _____ reference to the source cell.
absolute
When using a 3-D reference, it is a good idea to set up the data in each worksheet in
identical cells
When you create a PivotTable, you can select to place it in the existing worksheet or in
a new worksheet
By default, non-numeric fields are added to the _____ box of the PivotTable Field task pane, and numeric fields are added to the _____ box.
Rows; Values
Excel’s _____ feature can be used to summarize data from multiple worksheets or another workbook into a master worksheet.
Consolidate
As changes are made to the PivotChart,
the PivotTable associated with it also updates
The _____ function is active by default when you open the Consolidate dialog box.
Sum
The Insert Slicer button is in the _____ group on the PivotTable Tools Analyze tab.
Filter
What becomes visible when you activate any Sparkline cell?
the Sparkline Tools Design tab
When you open a workbook with an external reference, the _____ feature is disabled and a(n) _____ is displayed.
automatic updates; security warning message
Delete the dollar symbols ($) in the cell reference if the formula is to be copied and the source cell needs to be
relative
What is the first step in consolidating data?
Make the starting cell active
What does a Slicer pane contain?
all the unique values for the specified field
A formula that refers to the same cell in a range that includes several worksheets is known as a(n)
3-D reference
Creating a PivotTable allows you to do all the following except
delete data
When a Total row is added to a table, which of the following does not occur?
The table columns are automatically banded
Click the _____ button if you remove duplicate rows by mistake.
undo
The method of formatting even-numbered rows in a table differently from odd-numbered rows is called
banding
Why would you want to convert a table to a normal range?
to use the Subtotal feature
The first row of an Excel table contains
field names
Suppose that you have received a table containing first and last names in one column. What is the easiest way to split the names into separate columns?
Use the Text to Columns feature
Which of the following is not one of the banding options available in Excel?
add random bands to columns
There are no ____ within an Excel table.
(?)
NOT column labels
NOT text data
If a cell within a table is referenced in a formula in another cell outside the table, then the _____ is included in the formula.
table name
How are subtotals displayed when they have been created for a range of data at the Subtotal dialog box?
using the Outline feature
At the Data Validation dialog box, choose the type of data to be validated in the _____ option box on the Settings tab.
Allow
When writing a formula, how should you enter the workbook name reference?
enclosed in square brackets: [ ]
To begin building a PivotTable, click the PivotTable button in the Tables group on the _____ tab.
Insert
_____ Timeline pane(s) can be open at a time, but/and data can be filtered using _____.
More than one; one Timeline at a time
Which of the following include the worksheet reference by default?
a range name
What happens after you click the Insert Timeline button?
The Insert Timelines dialog box opens
When must you include the sheet name in a formula?
when it references a cell in a different worksheet
When validating or restricting data entry, _____ the added characters if a custom number format adds punctuation or text that appears in a cell.
ignore
Before removing duplicate records from a table, how should you first check to see what records will be deleted?
Click Duplicate Values in the Highlight Cells Rules at the Conditional Formatting drop-down list
The Use labels in section of the Consolidate dialog box contains these two options.
Top row and Left column
To begin formatting a PivotTable, make active a cell inside it and then click the _____ tab.
PivotTable Tools Design
How can you restore a broken link to an external resource?
Recreate the linked formula
To move a PivotChart to a new sheet, use the _____ button in the Actions group.
Move Chart
A table in Excel is similar in structure to a
database
In Excel, a range that can be managed separately from other rows and columns in the worksheet is called a
table
Which of these steps should be performed first when creating a table?
Select the range
A PivotTable is a(n) _____ table that organizes and summarizes data based on fields and records.
interactive
The workbook that contains the data that is linked to the destination workbook is called the _____ workbook.
source
Which of these examples shows the correct syntax for a formula that links to a cell in the budget.xlsx workbook?
=[budget.xlsx]January!A3
Create a PivotChart with the active cell anywhere in a PivotTable by clicking the PivotChart button in the _____ group.
(?)
NOT PivotTable
When a PivotTable is filtered using a Timeline, which of the following time periods is not an option?
weeks
To create a Sparkline, begin by
selecting the empty cell range in which to insert the Sparklines
You can change the summary function in a PivotTable by _____ any numeric value in the PivotTable, pointing to Summarize Values By at the shortcut menu, and then clicking a function name.
right-clicking
Click the _____ button to change the summary function in a PivotTable.
Field Settings
Using buttons in the Group group on the Sparkline Tools Design tab, you can do each of the following except _____ Sparklines.
copy
Before creating subtotals for a range of data, be sure to remove any _____ within the range to be grouped and subtotaled.
blank rows
Which of the following is not one of the options in the Table Style Options group on the Table Tools Design tab?
(?)
NOT Filter Button
NOT Header Row
To delete duplicate records from a table, click the Remove Duplicates button in the _____ group on the _____ tab.
Data Tools; Data
When data in a worksheet is entered as a table, each row is a _____ and each column is a _____.
record; field
What two options are presented at the Group dialog box?
Rows and Columns
Which of the following is an example of a structured reference formula?
=[@[Billable Hours]]*[@Rate]
Which two buttons are common to all three error alert message boxes?
Cancel and Help
In a _____, Excel copies the formula from the first cell to the remaining cells in the column immediately after the formula is entered.
calculated column
Use this keyboard shortcut to create a table.
CTRL + T
What does the Text to Columns feature do?
It takes data from a single column and separates it into multiple columns
To change an external reference to link to a different workbook, begin by clicking the _____ button at the _____ dialog box.
Change Source; Edit Links
To select more than one item in a Slicer pane,
click the Multi-Select button
To filter a PivotTable,
(?)
NOT right-click in the report
Linking to a cell in another workbook incorporates _____ references and requires adding a workbook name reference to the formula.
external
Excel’s built-in auditing features help you make sure that
a worksheet is functioning accurately
Use Excel’s _____ feature to test data that was entered before validation rules were set up.
Circle Invalid Data
At the Scenario Summary dialog box, enter in the Result cells text box
the formula cell or cells that change when the data is applied in various scenarios
To test the accuracy of key figures in a worksheet, enter a _____ outside the main worksheet area.
proof formula
You can do all the following at the Paste Special dialog box except
copy the active cell, plus all others with the same value
To use the Scenario Manager, start by clicking the Data tab and then the_____ button.
What-If Analysis
Which of the following is not one of the Excel error codes mentioned in Chapter 5?
a red diagonal triangle
Which types of data tables does Excel offer?
one-variable and two-variable
A range of cells in a copied source range can be multiplied by the cells in the destination range by
using Paste Special and clicking Multiply
To create a data table, begin by clicking the _____ button.
What-If Analysis
What advice does Chapter 5 give regarding creating scenarios?
create the first scenario using the original values
Which of the following buttons is not available at the Scenario Manager dialog box?
OK
When you select Formats at the Paste Special dialog box, Excel will paste
only formatting options from the source
Cells that contain formulas that reference other cells are called _____ cells.
Dependent
Which of the following options is not provided at the What-If Analysis button drop-down list
Evaluate Formula
You can convert data from a column to a row arrangement by clicking the Transpose button at the Paste button drop-down gallery or by clicking the Transpose option
at the Paste Special dialog box
After data has been copied to the Clipboard, use options at the _____ dialog box to perform a mathematical operation in the destination range based on values in the source range.
Paste Special
With a large worksheet, you can keep track of dependent cells that may not be visible while other cells are being changed by
opening a Watch Window
When using the Goal Seek command, enter the target value into the _____ box at the Goal Seek dialog box.
To value
To clear cell tracer arrows, click the _____ button.
Remove Arrows
The _____ error code indicates that the formula contains an unrecognized entry.
NAME?
When viewing the results of a scenario at the Scenario Manager dialog box, click the _____ button to apply the current values.
Show
The cell in which Excel is to calculate the target value using Goal Seek must be referenced by a formula in the _____ text box.
Set cell
Which of the following is not one of the options in the Operation section of the Paste Special dialog box?
Count
How many models can you create using the Scenario Manager dialog box?
As many as you want
Convert data in columns to rows and vice versa using the _____ button in the Paste button drop-down gallery or using the _____ option at the Paste Special dialog box.
Transpose; Transpose
Use the _____ button to work through a formula value by value to determine where an error exists.
Evaluate Formula
To test three different what-if conditions using the Scenario Manager, you must
create three scenarios
What can you do to see a descriptive reference next to the input text box, rather than the cell address when adding a scenario?
Create a range name for each changing cell
Use the _____ option at the Paste Special dialog box to convert data arranged in rows to data arranged in columns.
Transpose
To circle invalid data, click the _____ button arrow and then click the Circle Invalid Data option.
Data Validation
To calculate a value, the Goal Seek feature requires that cells have a(n) _____ relationship.
dependent
With a Watch Window, you can keep track of multiple
dependent cells
How is the Scenario Manager different from the Goal Seek feature?
It allows you to examine several different answers to your question
If you hear a beep while tracing cell relationships, it means that
no more relationships exist
Use Excel’s _____ feature when you know the result but don’t know what input value will achieve that result.
Goal Seek
Enter _____ in the Changing cells text box at the Add Scenario dialog box.
the range cells that will change when the scenario is applied
To compare scenarios side by side, display the Scenario Manager dialog box and click the _____ button.
Summary
By default, Excel stores _____ in the Comment text box at the Add Scenario dialog box.
the user name and date the scenario was created
Cells that provide data to formula cells are called _____ cells.
precedent
Using data tables provides a means of analyzing various outcomes in a calculation without
creating multiple formulas
In a two-variable data table, where is the source formula placed?
in the top left cell of the table
The _____ group on the Formulas tab contains buttons that are useful for viewing relationships between cells in formulas.
Formula Auditing
Which of these is not a file extension for a text file?
.tab
Use the _____ function to join the content of two or more cells, including text, numbers, and cell references.
CONCAT
The text function PROPER
capitalizes the first letter of each word
What does it mean if a yellow triangle with an exclamation mark appears next to a query in the Queries & Connections group?
The source file cannot be found
What will be returned by the formula =LEFT(“1st Quarter Sales”,3)?
1st
What is the keyboard shortcut for using the Flash Fill feature?
Ctrl + E
If cell A3 holds the text JD Enterprises, the formula =LEN(A3) will return which of the following?
14
To exchange Excel data with someone who does not have Excel, save the workbook as a
text file
To start the process of exporting data from Excel to Access, open the Excel workbook, select the cells to export, _____, and then start Access.
click the Copy button
When data is imported into Excel, the data is imported in a table format and a(n) ____ is created.
query
If the Excel data being exported to Word will often change and you want to keep the Word document up to date, copy the data to the Clipboard in Excel, open Word, place the insertion point in the desired location,
click the Paste button arrow, click Paste Special, click Microsoft Office Excel Worksheet Object, and then click Paste link
Before transferring data from Excel to an existing table datasheet in Access, make sure that
the column structures in the two programs matc
To redisplay the Query & Connections task pane,
click the Queries & Connections button
UPPER and RIGHT are both _____ functions.
Text
You can import data into Excel from a variety of sources. Which of the following is not one of the sources discussed in Chapter 6?
PowerPoint
The formula =RIGHT(“1st Quarter Sales”,5) will return _____ in formula cell.
Sales
Flash fill can be used to
join parts of the contents of cells
In a tab delimited text file, Excel _____ at each tab.
begins a new column
To use Excel’s editing features to edit data embedded in Word, copy cells to the Clipboard in Excel, open Word, place the insertion point in the desired location,
click the Paste button arrow, click Paste Special, and then click Microsoft Office Excel Worksheet Object
How do you make the Edit button appear in a comment?
Click in the cell and then position the mouse pointer inside the comment box
When you edit an Excel chart embedded in a PowerPoint presentation,
the groups and buttons available on the Excel chart tabs become active
The formula =MID(“JD Enterprises Inc.”4,11) will return _____ in the formula cell.
Enterprises
Use _____ functions to extract the data when only some of the characters in a cell need to be copied.
Text
The TEXTJOIN function uses a ____ delimiter.
constant
One of the options at the _____ dialog box is to have Excel refresh the data when you open the file.
Query Properties
To separate a field into two fields during an import process, click the Edit button, select the column to be split, and then
click the Split Column button in the Transform group
To open a non-native file directly in Excel, use the _____ option to navigate to a specific folder and then click the File Type option box to display a drop-down list of all the files types that Excel can open.
Browse
Use the Power Query Editor window to
transform data before it is imported into Excel
What should you do to refresh a query?
Click the Refresh All button in the Queries & Connections group
Once Flash Fill has recognized that the first word of the adjacent column is to be extracted and suggests doing the same for the remaining cells, you should _____ to accept the suggestion or continue typing to reject the suggestion.
press the Enter key
The formula =CONCAT(“-“,519,555,1234) will return which of the following?
-5195551234
At the Import Data dialog box, which of the following is not one of the options for viewing imported data in a workbook?
Excel Chart
At the Query Properties dialog box, click the Refresh every check box to insert a check mark and then adjust the number of minutes in the measurement box if you want to Excel to
refresh the query at a specific time period
What does the text function SUBSTITUTE do?
inserts new text in place of old text
From where do you delete a scenario?
the Scenario Manager dialog box
Which of these options is not found in the Paste section of the Paste Special dialog box?
As hyperlink
If you see a green diagonal triangle in the upper left corner of a cell, you should activate the cell and then
(?)
A link to Excel data from a Word document will no longer work if you _____ the source workbook.
move or rename
How many Access tables can be imported into Excel at a time?
one
One of the methods for deleting a query is to right-click the query at the _____ and then click the Delete option at the pop-up menu.
Queries & Connections task pane
The formula =TEXTJOIN(“-“,TRUE,519,555,1234) will return which of the following?
519-555,1234
Why are text files useful for sharing data with others?
The text file format is supported by almost all applications and computers
If you have linked an Excel object to a Word document and decide to keep the data but not maintain the link,
open the Word document, right-click the linked object, point to Linked Worksheet Object, click Links, and then click the Break Link button
To export Excel data to an Access database that does not have an existing table in which to receive the data, you should
perform an import routine from Access
Which of these is not a text file format supported by Excel?
.bin
To import a text file into Excel, use the _____ button in the _____ group.
From Text/CSV; Get & Transform
To import an Access table into a new worksheet after selecting the table at the Navigator dialog box, begin by clicking the _____ button.
OK
To finish the process of exporting data from Excel to Access, open the Access database in Datasheet view,
click the Paste button arrow, click Paste Append, and then click Yes
Import data into Excel using buttons in the _____ group on the _____ tab.
Get & Transform Data; Data
To test the various inputs on the worksheet model in the Scenario Manager, you can
switch scenarios
Removing the connection between the source file and the destination file allows which of the following?
capturing the data at a specific time
When tracing relationships between cells and formulas, Excel draws _____ if an error is detected.
red tracer arrows
You must provide three pieces of information via a dialog box when using the Goal Seek command. Which of the following is not one of them?
confidence level
A range of cells containing a series of input values is called a
data table
If the data being copied from Excel to Word is not likely to need editing or updating, copy it to the Clipboard in Excel, open Word, place the insertion point in the desired location,
and then click the Paste button
By default, the Macro dialog box displays all the macros in
all the open workbooks
To create a custom view, begin by
applying the desired settings to the active worksheet
You can create a macro from scratch in Excel or using which of the following?
VBA Editor
If you enter an uppercase X in the Shortcut key option box at the Record Macro dialog box, Excel will define the shortcut as
Ctrl + Shift + X
What happens if you assign an existing Excel keystroke combination, such as Ctrl + p (print), to a macro?
Your macro will override Excel’s shortcut
When you make changes to workbook display options, the changes
are saved with the workbook
By default, AutoRecover automatically saves information every
10 minutes
When you create a new tab in the ribbon, where is the new tab placed?
after the tab name you have clicked in the Main Tabs lis
Macros in Excel are written and saved in the _____ programming language.
VBA
By default, Excel saves the macro in
the current workbook
Which of the following is the final step when using a customized template?
Double-click the template
Where is the Customize Quick Access Toolbar button?
at the right side of the Quick Access Toolbar
How do you start creating a macro?
Click the View tab, click the Macros button arrow, and then click Record Macro
What is the first step in inserting a form control?
Click the Developer tab
To have your macros available when creating additional workbooks, you should consider
creating a macros workbook
To save your current ribbon and Quick Access Toolbar settings, click the File tab,
click Options, click Customize Ribbon, and then click the Import/Export button
To customize the ribbon, start by
clicking the File tab and then clicking Options
What is the keyboard shortcut to minimize the ribbon?
Ctrl + F1
When you click the Reset button in the Excel Options dialog box with Customize Ribbon selected, these two options are displayed.
Reset only selected Ribbon tab and Reset all customizations
To run a macro, view the list of macros by clicking the Macros button in the Macros group on the _____ tab.
View
Click the Reset button below the Main Tabs list box in the Excel Options dialog box with Customize Ribbon selected to
reset the selected ribbon tab
While a macro is being recorded, the mouse clicks to select ribbon tabs are
not recorded
Which type of form control includes a drop-down list?
combo box
To view the Trust Center options,
click the File tab, click Options, and then click Trust Center
A custom _____ saves the display and print settings for the active worksheet.
view
A custom view cannot be created for any worksheet that contains a
table
When a workbook that contains a macro is opened, the default macro security setting is
Disable all macros with notification
A macro name _____ space(s) and _____ letter(s).
cannot contain; must begin with a
The Shortcut key section of the Record Macro dialog box holds the keystrokes that will be used to
execute the macro
To use a template you have created, click Personal at the
New backstage area
Which of the following is not one of the options in the Choose commands from option box at the Excel Options dialog box?
Visual Basic Commands
Where is the Ribbon Display Options button?
in the upper right corner of the screen
The Rename dialog box for a group name contains the Display name text box and the_____ list box.
Symbol
What displays when you click the Edit button at the Macro dialog box?
a Microsoft Visual Basic for Applications window
You can rename a tab by _____ the tab name, clicking Rename at the shortcut menu, and then typing a new name at the Rename dialog box.
right-clicking
Macros are created using Excel’s
macro recorder
If a feature is not available in any tab on the ribbon, search for it in the ____ list box at the Excel Options dialog box.
All Commands
When creating an XML schema, you should provide code for at least _____ records.
two
A title added to a web page displays in the ______ of the browser window.
Title bar
For a workbook that has been marked as final, all the following statements are true except which one?
The workbook is encrypted
To mark a workbook as final, begin by clicking the _____ tab to display the _____.
File; Info backstage area
To turn on worksheet protection, display the Protect Sheet dialog box and then select
(?)
NOT the password to be used
The first step in importing an XML file is to click the _____ button in the _____ group on the Data tab.
Get Data; Get & Transform
Which of the following is one of the workbook properties that Excel adds automatically?
date the workbook was created
How can you provide a workbook to users with an Excel version earlier than Excel 2007?
Save it in Excel 97-2003 format
In an XML schema, the closing tag includes a(n)
forward slash (/)
To unlock cells, click the _____ in the Cells group on the Home tab.
Format
The _____ feature saves versions of your work at a specified time interval.
AutoRecover
When the schema file is added to the workbook, each <Tag> in the XML code appears as</Tag>
an XML element in the XML maps in the workbook list box
At the dialog box that allows you to protect a workbook’s structure, you can also prevent the user from
resizing or changing the positions of the windows
How can you remove protection from a worksheet?
Click the Unprotect Sheet button in the Protect group on the Review tab
To protect the structure of a workbook, use the Protect Workbook button in the _____ group on the _____ tab.
Protect; Review
For a workbook that has been marked as final, all the following statements are true except which one?
The workbook is encrypted
Workbook properties can be viewed and edited at the
Info backstage area
What does XPS stand for?
XML paper specification
To remove worksheet protection, click the Unprotect Sheet button or click the _____ in the Info backstage area.
Unprotect hyperlink
If the Accessibility Checker determines that a workbook will be difficult in some cases for people with accessibility issues to understand, which of the following accessibility issues will result?
Warning
Create an XML _____ by dragging each element from the list box at the XML Source task pane to its respective column header in the worksheet.
data map
If a password was entered when a workbook was protected, clicking the Protect Workbook button will display the _____ dialog box.
Unprotect Workbook
When you publish a worksheet as an HTML web page,
Excel creates files for supplemental data
How can you prevent a user from making changes to the sizes or positions of windows in a workbook?
Click the Protect Workbook button and then click the Windows check box
Which company developed the PDF standard?
Adobe
Where is the Encrypt with Password option?
at the Info backstage area
A password should contain four types of characters. Which of the following is not one of them?
spaces
When publishing a workbook as a web page, specify whether to publish the entire workbook or _____ at the Save as dialog box.
only the active worksheet
How can you tell that a selected cell is locked?
At the Format button drop-down list, the icon next to Lock Cell is highlighted
At the Macro dialog box, you can do all the following except _____ a macro.
copy
How do you add a command to the Quick Access Toolbar from the Excel Options dialog box?
Double-click the command name
A _____ is a workbook with standard text, formulas, and formatting.
template
Before distributing a workbook, use the _____ to determine whether any of the workbook’s features may make it difficult for someone who requires assistive technology to read it.
Accessibility Checker
At the Save As dialog box, click the _____ button to open the Publish as Web Page dialog box.
Publish
The term _____ refers to descriptive information about data.
metadata
Use Excel’s _____ feature to scan a workbook for personal data and hidden information.
Document Inspector
Remove a password from a workbook at the _____ dialog box.
Encrypt Document
The first step in exporting a worksheet as an XML file is to click the _____ button in the XML group on the _____ tab.
Source; Developer
If you forget the password assigned to protect a worksheet, what can you do?
Nothing; the worksheet cannot be unprotected if the password is forgotten
What does PDF stand for?
Portable Document Format
What is the name for the scrambled text Excel uses to encrypt passwords?
cipher text
At the Document Inspector dialog box, remove the check marks from the check boxes next to
items that are not to be scanned or removed from the workbook before distributing it
At the Info backstage area, a description of the protection features applied to the workbook and/or worksheets is provided
next to the Protect Workbook button
To determine in advance what areas of a worksheet might cause problems for users with earlier versions of Excel,
run the Compatibility Checker
By default, when a worksheet is protected, you
cannot delete any content
Which company developed XPS?
Microsoft
How can you prevent people from making additions, deletions, or changes to a workbook you plan to distribute?
Click the File tab, click Info, click the Protect Workbook button, and then click Mark as Final
Which of the following is not one of the workbook properties listed at the Info backstage area?
User
Which of the following is not one of the steps taken to protect a worksheet?
Click Lock Cell
Which of the following is not one of the steps for deleting a macro?
Click the Formulas tab
When you save a worksheet containing macros created in Excel, what will the file extension be?
.xlsm
When you create a new tab for the ribbon, the tab is given which of the following names?
New Tab (Custom)
When you send a worksheet as an XPS document, the recipient must have _____ on his or computer to use the file.
XPS viewer
When you send a worksheet as a PDF document, the recipient must have _____ on his or her computer to use the file.
Adobe Acrobat Reader DC
By default, when a new workbook is created, Excel inserts in the _____ property box the name of the computer user.
Author
Suppose the Document Inspector identifies personal data in a workbook that should remain confidential. To remove this information before distributing the workbook, use tools at the
Info backstage area
After running the Accessibility Checker, refer to the Accessibility Checker task pane on the_____ of the screen for a list of the inspection results.
right side
To delete a macro, _____, select the macro name, and then click the Delete button.
open the Macro dialog box
At the Record Macro dialog box, the letter entered in the Shortcut key section is used in conjunction with the _____ key to run the macro.
CTRL
At the Excel Options dialog box, _____ displays by default in the Customize the Ribbon option box.
Main Tabs
What does XML stand for?
Extensible Markup Language
You can only apply a custom view to the worksheet that
was active when the view was created
When displayed in the Visual Basic for Applications window, the first few lines of a macro are preceded by apostrophes (‘). These lines are
comments
Can you create your own conditional formatting rules? If so, how?
Yes; use the New Formatting Rule dialog box
A workbook can be saved as a template by changing Save as type to Excel Template or
Excel Macro-Enabled Template
