ALL OF LEVEL TWO Flashcards
1
Q
How will numbers display when the format code is [Red];[Blue]?
A
Positive numbers will display in red; negative numbers in blue
1
Q
To create your own number format, select _____ in the Category list box at the Format Cells dialog box with the Number tab selected.
A
Custom
2
Q
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
A
conditional formatting
3
Q
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?
A
M*
4
Q
_____ formatting converts numbers to exponential notation (E + n).
A
Scientific
5
Q
Which of these formatting Type options is not available for US English at the Format Cells dialog box with the Number tab selected?
A
Credit Card Number
6
Q
Icon sets allow you to…
A
classify data into three to five categories
7
Q
Which of these custom number format codes aligns numbers vertically on the decimal point?
A
????.??
8
Q
Which of the following numbers is represented by 2.60E + 05?
A
260,000.00
9
Q
Which of the following buttons is not available when you click Manage Rules at the Conditional Formatting button drop-down list?
A
Copy Rule
10
Q
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?
A
Alphabetical
11
Q
When applying conditional formatting using predefined rules, the first step is to…
A
select a range of cells
12
Q
How can you tell that a filter has been defined for a column?
A
A filter arrow appears in the column heading
13
Q
How can you format a cell based on the value in a different cell?
A
Use a formula
14
Q
In the Select a Rule Type section of the New Formatting Rule dialog box, you…
A
choose the type of condition to have Excel check before formatting
15
Q
At the New Formatting Rule dialog box, begin creating a new rule by choosing which of the following?
A
the type of condition for Excel to check before formatting
16
Q
Changing the appearance of a cell based on a condition is called…
A
conditional formatting
17
Q
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.
A
the same; separate
18
Q
The _____ Excel feature is used to display only the rows that meet specified criteria.
A
Custom AutoFilter
19
Q
Excel provides special number formats that are specific to which of the following?
A
countries and languages
20
Q
Conditional formatting cannot be applied based on which of the following?
A
errors in data entry
21
Q
If a custom number format is deleted, the custom formatting will be removed from which of the following?
A
all the cells to which it was applied in that workbook
22
Q
When filtering a range using an advanced filter, start by…
A
adding five or six rows at the top of the worksheet
23
Q
The Edit the Rule Description section of the New Formatting Rule dialog box varies depending on…
A
the active option in the Select a Rule Type section
24
You can filter values by more than one criterion at a time by using...
a comparison operator
25
In the Conditional Formatting Rules Manager dialog box, the default setting for the Show formatting rules for option is...
Current Selection
26
If the custom number format code is ###.##, which value will display if the value 23.959 is entered?
23.96
27
Which of these questions would best be answered using conditional formatting?
Which salespeople exceeded their goals last month?
28
Values in rows that do not meet the criteria in the Custom AutoFilter feature are...
temporarily hidden from view
29
Using _____, you can classify data into categories based on threshold values for the selected range.
icon sets
30
The first step in creating a custom number format is to...
select the range
31
Which of the following is not an example of a conditional formatting rule?
Format only values in exponential notation
32
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
33
How do you open the New Formatting Rule dialog box?
click New Rule
34
You can edit or delete conditional formatting rules in the _____ dialog box.
Conditional Formatting Rules Manager
35
Select the option(s) _____ at the Custom AutoFilter dialog box to specify two criteria by which to filter.
And or Or
36
The custom number format allows you to do each of the following except...
combine arabic numbers and roman numerals
37
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
38
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
39
Excel can add text such as GX- before all the values typed in cells if the cells have been formatted using the _____ category.
Custom
40
You can create custom rules based on each of the following except...
cell position
41
As a visual guide to see the variation of values in a range, Excel provides each of the following except...
animated graphics
42
Which of the following is not one of the options on the Formatting tab of the Quick Analysis button?
Highlight Cell Rules
43
If the appropriate formatting has been applied, data can be filtered by all the following except
font type
44
The IF function’s logical test returns _____ result.
only a true or false
45
Which of the following is not an option in the Conditional Formatting button drop-down list?
Font Sets
46
Exponential notation is often used by engineers and scientists for...
very small and very large numbers
47
With conditional formatting, cells that do not meet the criteria specified
remain unchanged
48
You _____ create your own conditional formatting rules.
can
49
Which of the following is not one of the 13 categories of functions mentioned in Chapter 2?
spatial
50
Which of the following represents the proper structure of the arguments for an HLOOKUP function?
(lookup_value, table_array, row_index_num, range_lookup)
51
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
52
The ABS, RAND, SQRT, and SUMIF functions are in the _____ category.
math and trigonometry
53
Which of these functions uses an argument containing multiple criteria?
COUNTIFS
54
The VLOOKUP argument that identifies a range of cells, rather than a value, a single cell, or a column number, is...
table_array
55
The AND and OR functions use _____ logic to construct a condition test in a formula.
Boolean
56
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
57
A _____ is a preset formula.
function
58
What is the first step in creating an AVERAGEIF formula?
Make active the cell in which the formula will be inserted
59
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
60
The lookup_value argument in the VLOOKUP function specifies which of the following?
the value that Excel searches for in the reference table
61
Excel’s Lookup & Reference functions provide a way to...
look up values in a range
62
Excel stops evaluating an IF formula...
once the logical_test has been answered as true
63
In a PPMT function, the argument fv refers to the...
balance at the end of the loan
64
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
65
If a range name used in a formula is deleted, cells that used the name will display this error message.
#NAME?
66
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
67
A function includes two parts: the name of the function and the...
argument
68
The _____ function will take the value –7 as an argument and return the value 7.
ABS
69
The _____ function will find a value in a column of data and use it in a formula.
VLOOKUP
70
The _____ function contains these arguments: (logical_test,value_if_true,value_if_false).
IF
71
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
72
The _____ argument of the PPMT function can contain only a 1 or a 0.
type
73
Which of the following functions will result in a number rounded to the nearest tens value?
=ROUND(145,-1)
74
Use the _____ function to find a value when the table contains comparison data organized in rows.
HLOOKUP
75
To make a formula easier to read, place each logical test on a new line and then...
expand the Formula bar
76
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
77
Use the _____ function with an IF function to test multiple conditions.
AND
78
A nested IF formula can be used...
when more than two outcomes are possible
79
Excel will add the required syntax automatically when you create a COUNTIF formula...
at the Insert Function dialog box
80
MAX and MIN are statistical functions that return the _____ and the _____ value in the range, respectively.
largest; smallest
81
The syntax of the COUNTIF and COUNTIFS arguments requires that the criteria _____ if it is not a cell reference.
be enclosed in quotation marks
82
The result of an IFS function must test...
true
83
The _____ Excel function calculates the principal portion of a loan payment.
PPMT
84
What is the keyboard shortcut for opening the Name Manager dialog box?
Ctrl + F3
85
The argument (range,criteria,average_range) is required by the _____ function.
AVERAGEIF
86
In Excel, an argument can include any of the following except..
a specified format
87
The _____ function is in the math and trigonometry function category.
SUMIFS
88
Which of the functions discussed in Chapter 2 requires the argument (range1,criteria1,range2,criteria2)?
COUNTIFS
89
Unlike a nested IF function, an IFS function does not require each argument to be enclosed in...
(?)
NOT parentheses (( ))
90
The COUNTIF function requires two arguments: range and
criteria
91
The number of right parentheses needed to end a nested IF statement equals the number of times
IF appears in the formula
92
The “range to calculate” argument comes first in all the following functions except the _____ function.
SUMIF
93
What argument follows sum_range in the SUMIFS function?
criteria_range1
94
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
95
Which of the following formulas uses the correct syntax?
=COUNTIF(Expenses,">700")
96
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
97
Which of these lookup functions is most commonly used?
VLOOKUP
98
Which of the following is not one of the arguments in the PPMT function?
pmt
99
If you type a formula in the first record of a new table column, Excel automatically creates a(n)
calculated column
100
The _____ guides you through the steps of separating data into columns.
Convert Text to Columns wizard
101
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
102
By default, where does Excel display filter arrows in a table?
next to each label in the header row
103
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
104
What is the first step when splitting text into multiple columns?
Insert a blank column(s) next to the source data
105
The Table Tools Design tab contains options for _____ the table.
formatting
106
Excel can compare records within a worksheet and automatically delete duplicate rows based on
the columns selected that might contain duplicate values
107
On the Table Tools Design tab, which of these boxes contains a check mark by default?
Banded Rows
108
Which of the following is not one of the three tabs at the Data Validation dialog box?
Add Level
109
To convert a table to a normal range, use the
Convert to Range button in the Tools group
110
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
111
Add a Total row to a table by clicking the
Total Row check box on the Table Tools Design tab
112
The Data Tools group on the Data tab does not contain which of the following buttons?
Sort & Filter
113
Which of the following is not one of the types of error messages that appears when incorrect data is entered in a cell?
Fatal
114
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
115
_____ is not one of the data validation criteria available in the Allow option box at the Data Validation dialog box.
Fraction
116
Use the keyboard shortcut Shift + Alt + Right Arrow key to _____ data.
group
117
When groups and subtotals are added to a range, Excel automatically adds a _____ at the bottom of the range.
grand total
118
A PivotChart can be created directly from a PivotTable or from
the data in a worksheet
119
What are Sparklines?
miniature charts embedded in the background of cells
120
Which of the following areas does not appear in the layout section of the PivotTable Fields task pane?
Slicers
121
Slicers allow you to
filter without using a filter arrow
122
To change a Sparkline chart from a line to a column format, you will use tools in the _____ group.
Type
123
What will happen to the link to an external reference if you move the source workbook?
The link will not work
124
How does Chapter 4 suggest that you use Sparklines?
to show high or low values within a range
125
In a formula, a(n) _____ is used to separate a worksheet reference from a cell reference.
exclamation point (!)
126
To create a PivotChart in a worksheet without a PivotTable,
select the data range, click the PivotChart button arrow, and then click PivotChart
127
To permanently remove a linked reference, click the _____ button at the _____ dialog box.
Break Link; Edit Links
128
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
129
When a table automatically expands to include new entries, the _____ displays.
AutoCorrect Options button
130
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
131
The Table button is in the
Tables group on the Insert tab
132
The Stop Automatically Expanding Tables option appears when you click the _____ button.
AutoCorrect Options
133
What is the keyboard shortcut for adding a Total row?
Ctrl + Shift + T
134
What is the first step in creating a PivotTable?
Select the source range
135
The point-and-click method of creating a link to another workbook creates an _____ reference to the source cell.
absolute
136
When using a 3-D reference, it is a good idea to set up the data in each worksheet in
identical cells
137
When you create a PivotTable, you can select to place it in the existing worksheet or in
a new worksheet
138
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
139
Excel’s _____ feature can be used to summarize data from multiple worksheets or another workbook into a master worksheet.
Consolidate
140
As changes are made to the PivotChart,
the PivotTable associated with it also updates
141
The _____ function is active by default when you open the Consolidate dialog box.
Sum
142
The Insert Slicer button is in the _____ group on the PivotTable Tools Analyze tab.
Filter
143
What becomes visible when you activate any Sparkline cell?
the Sparkline Tools Design tab
144
When you open a workbook with an external reference, the _____ feature is disabled and a(n) _____ is displayed.
automatic updates; security warning message
145
Delete the dollar symbols ($) in the cell reference if the formula is to be copied and the source cell needs to be
relative
146
What is the first step in consolidating data?
Make the starting cell active
147
What does a Slicer pane contain?
all the unique values for the specified field
148
A formula that refers to the same cell in a range that includes several worksheets is known as a(n)
3-D reference
149
Creating a PivotTable allows you to do all the following except
delete data
150
When a Total row is added to a table, which of the following does not occur?
The table columns are automatically banded
151
Click the _____ button if you remove duplicate rows by mistake.
undo
152
The method of formatting even-numbered rows in a table differently from odd-numbered rows is called
banding
153
Why would you want to convert a table to a normal range?
to use the Subtotal feature
154
The first row of an Excel table contains
field names
155
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
156
Which of the following is not one of the banding options available in Excel?
add random bands to columns
157
There are no ____ within an Excel table.
(?)
NOT column labels
NOT text data
158
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
159
How are subtotals displayed when they have been created for a range of data at the Subtotal dialog box?
using the Outline feature
160
At the Data Validation dialog box, choose the type of data to be validated in the _____ option box on the Settings tab.
Allow
161
When writing a formula, how should you enter the workbook name reference?
enclosed in square brackets: [ ]
162
To begin building a PivotTable, click the PivotTable button in the Tables group on the _____ tab.
Insert
163
_____ Timeline pane(s) can be open at a time, but/and data can be filtered using _____.
More than one; one Timeline at a time
164
Which of the following include the worksheet reference by default?
a range name
165
What happens after you click the Insert Timeline button?
The Insert Timelines dialog box opens
166
When must you include the sheet name in a formula?
when it references a cell in a different worksheet
167
When validating or restricting data entry, _____ the added characters if a custom number format adds punctuation or text that appears in a cell.
ignore
168
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
169
The Use labels in section of the Consolidate dialog box contains these two options.
Top row and Left column
170
To begin formatting a PivotTable, make active a cell inside it and then click the _____ tab.
PivotTable Tools Design
171
How can you restore a broken link to an external resource?
Recreate the linked formula
172
To move a PivotChart to a new sheet, use the _____ button in the Actions group.
Move Chart
173
A table in Excel is similar in structure to a
database
174
In Excel, a range that can be managed separately from other rows and columns in the worksheet is called a
table
175
Which of these steps should be performed first when creating a table?
Select the range
176
A PivotTable is a(n) _____ table that organizes and summarizes data based on fields and records.
interactive
177
The workbook that contains the data that is linked to the destination workbook is called the _____ workbook.
source
178
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
179
Create a PivotChart with the active cell anywhere in a PivotTable by clicking the PivotChart button in the _____ group.
(?)
NOT PivotTable
180
When a PivotTable is filtered using a Timeline, which of the following time periods is not an option?
weeks
181
To create a Sparkline, begin by
selecting the empty cell range in which to insert the Sparklines
182
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
183
Click the _____ button to change the summary function in a PivotTable.
Field Settings
184
Using buttons in the Group group on the Sparkline Tools Design tab, you can do each of the following except _____ Sparklines.
copy
185
Before creating subtotals for a range of data, be sure to remove any _____ within the range to be grouped and subtotaled.
blank rows
186
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
187
To delete duplicate records from a table, click the Remove Duplicates button in the _____ group on the _____ tab.
Data Tools; Data
188
When data in a worksheet is entered as a table, each row is a _____ and each column is a _____.
record; field
189
What two options are presented at the Group dialog box?
Rows and Columns
190
Which of the following is an example of a structured reference formula?
=[@[Billable Hours]]*[@Rate]
191
Which two buttons are common to all three error alert message boxes?
Cancel and Help
192
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
193
Use this keyboard shortcut to create a table.
CTRL + T
194
What does the Text to Columns feature do?
It takes data from a single column and separates it into multiple columns
195
To change an external reference to link to a different workbook, begin by clicking the _____ button at the _____ dialog box.
Change Source; Edit Links
196
To select more than one item in a Slicer pane,
click the Multi-Select button
197
To filter a PivotTable,
(?)
NOT right-click in the report
198
Linking to a cell in another workbook incorporates _____ references and requires adding a workbook name reference to the formula.
external
199
Excel’s built-in auditing features help you make sure that
a worksheet is functioning accurately
200
Use Excel’s _____ feature to test data that was entered before validation rules were set up.
Circle Invalid Data
201
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
202
To test the accuracy of key figures in a worksheet, enter a _____ outside the main worksheet area.
proof formula
203
You can do all the following at the Paste Special dialog box except
copy the active cell, plus all others with the same value
204
To use the Scenario Manager, start by clicking the Data tab and then the_____ button.
What-If Analysis
205
Which of the following is not one of the Excel error codes mentioned in Chapter 5?
a red diagonal triangle
206
Which types of data tables does Excel offer?
one-variable and two-variable
207
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
208
To create a data table, begin by clicking the _____ button.
What-If Analysis
209
What advice does Chapter 5 give regarding creating scenarios?
create the first scenario using the original values
210
Which of the following buttons is not available at the Scenario Manager dialog box?
OK
211
When you select Formats at the Paste Special dialog box, Excel will paste
only formatting options from the source
212
Cells that contain formulas that reference other cells are called _____ cells.
Dependent
213
Which of the following options is not provided at the What-If Analysis button drop-down list
Evaluate Formula
214
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
215
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
216
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
217
When using the Goal Seek command, enter the target value into the _____ box at the Goal Seek dialog box.
To value
218
To clear cell tracer arrows, click the _____ button.
Remove Arrows
219
The _____ error code indicates that the formula contains an unrecognized entry.
#NAME?
220
When viewing the results of a scenario at the Scenario Manager dialog box, click the _____ button to apply the current values.
Show
221
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
222
Which of the following is not one of the options in the Operation section of the Paste Special dialog box?
Count
223
How many models can you create using the Scenario Manager dialog box?
As many as you want
224
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
225
Use the _____ button to work through a formula value by value to determine where an error exists.
Evaluate Formula
226
To test three different what-if conditions using the Scenario Manager, you must
create three scenarios
227
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
228
Use the _____ option at the Paste Special dialog box to convert data arranged in rows to data arranged in columns.
Transpose
229
To circle invalid data, click the _____ button arrow and then click the Circle Invalid Data option.
Data Validation
230
To calculate a value, the Goal Seek feature requires that cells have a(n) _____ relationship.
dependent
231
With a Watch Window, you can keep track of multiple
dependent cells
232
How is the Scenario Manager different from the Goal Seek feature?
It allows you to examine several different answers to your question
233
If you hear a beep while tracing cell relationships, it means that
no more relationships exist
234
Use Excel’s _____ feature when you know the result but don’t know what input value will achieve that result.
Goal Seek
235
Enter _____ in the Changing cells text box at the Add Scenario dialog box.
the range cells that will change when the scenario is applied
236
To compare scenarios side by side, display the Scenario Manager dialog box and click the _____ button.
Summary
237
By default, Excel stores _____ in the Comment text box at the Add Scenario dialog box.
the user name and date the scenario was created
238
Cells that provide data to formula cells are called _____ cells.
precedent
239
Using data tables provides a means of analyzing various outcomes in a calculation without
creating multiple formulas
240
In a two-variable data table, where is the source formula placed?
in the top left cell of the table
241
The _____ group on the Formulas tab contains buttons that are useful for viewing relationships between cells in formulas.
Formula Auditing
242
Which of these is not a file extension for a text file?
.tab
243
Use the _____ function to join the content of two or more cells, including text, numbers, and cell references.
CONCAT
244
The text function PROPER
capitalizes the first letter of each word
245
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
246
What will be returned by the formula =LEFT("1st Quarter Sales",3)?
1st
247
What is the keyboard shortcut for using the Flash Fill feature?
Ctrl + E
248
If cell A3 holds the text JD Enterprises, the formula =LEN(A3) will return which of the following?
14
249
To exchange Excel data with someone who does not have Excel, save the workbook as a
text file
250
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
251
When data is imported into Excel, the data is imported in a table format and a(n) ____ is created.
query
252
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
253
Before transferring data from Excel to an existing table datasheet in Access, make sure that
the column structures in the two programs matc
254
To redisplay the Query & Connections task pane,
click the Queries & Connections button
255
UPPER and RIGHT are both _____ functions.
Text
256
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
257
The formula =RIGHT("1st Quarter Sales",5) will return _____ in formula cell.
Sales
258
Flash fill can be used to
join parts of the contents of cells
259
In a tab delimited text file, Excel _____ at each tab.
begins a new column
260
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
261
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
262
When you edit an Excel chart embedded in a PowerPoint presentation,
the groups and buttons available on the Excel chart tabs become active
263
The formula =MID("JD Enterprises Inc."4,11) will return _____ in the formula cell.
Enterprises
264
Use _____ functions to extract the data when only some of the characters in a cell need to be copied.
Text
265
The TEXTJOIN function uses a ____ delimiter.
constant
266
One of the options at the _____ dialog box is to have Excel refresh the data when you open the file.
Query Properties
267
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
268
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
269
Use the Power Query Editor window to
transform data before it is imported into Excel
270
What should you do to refresh a query?
Click the Refresh All button in the Queries & Connections group
271
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
272
The formula =CONCAT("-",519,555,1234) will return which of the following?
-5195551234
273
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
274
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
275
What does the text function SUBSTITUTE do?
inserts new text in place of old text
276
From where do you delete a scenario?
the Scenario Manager dialog box
277
Which of these options is not found in the Paste section of the Paste Special dialog box?
As hyperlink
278
If you see a green diagonal triangle in the upper left corner of a cell, you should activate the cell and then
(?)
279
A link to Excel data from a Word document will no longer work if you _____ the source workbook.
move or rename
280
How many Access tables can be imported into Excel at a time?
one
281
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
282
The formula =TEXTJOIN("-",TRUE,519,555,1234) will return which of the following?
519-555,1234
283
Why are text files useful for sharing data with others?
The text file format is supported by almost all applications and computers
284
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
285
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
286
Which of these is not a text file format supported by Excel?
.bin
287
To import a text file into Excel, use the _____ button in the _____ group.
From Text/CSV; Get & Transform
288
To import an Access table into a new worksheet after selecting the table at the Navigator dialog box, begin by clicking the _____ button.
OK
289
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
290
Import data into Excel using buttons in the _____ group on the _____ tab.
Get & Transform Data; Data
291
To test the various inputs on the worksheet model in the Scenario Manager, you can
switch scenarios
292
Removing the connection between the source file and the destination file allows which of the following?
capturing the data at a specific time
293
When tracing relationships between cells and formulas, Excel draws _____ if an error is detected.
red tracer arrows
294
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
295
A range of cells containing a series of input values is called a
data table
296
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
297
By default, the Macro dialog box displays all the macros in
all the open workbooks
298
To create a custom view, begin by
applying the desired settings to the active worksheet
299
You can create a macro from scratch in Excel or using which of the following?
VBA Editor
300
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
301
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
302
When you make changes to workbook display options, the changes
are saved with the workbook
303
By default, AutoRecover automatically saves information every
10 minutes
304
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
305
Macros in Excel are written and saved in the _____ programming language.
VBA
306
By default, Excel saves the macro in
the current workbook
307
Which of the following is the final step when using a customized template?
Double-click the template
308
Where is the Customize Quick Access Toolbar button?
at the right side of the Quick Access Toolbar
309
How do you start creating a macro?
Click the View tab, click the Macros button arrow, and then click Record Macro
310
What is the first step in inserting a form control?
Click the Developer tab
311
To have your macros available when creating additional workbooks, you should consider
creating a macros workbook
312
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
313
To customize the ribbon, start by
clicking the File tab and then clicking Options
314
What is the keyboard shortcut to minimize the ribbon?
Ctrl + F1
315
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
316
To run a macro, view the list of macros by clicking the Macros button in the Macros group on the _____ tab.
View
317
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
318
While a macro is being recorded, the mouse clicks to select ribbon tabs are
not recorded
319
Which type of form control includes a drop-down list?
combo box
320
To view the Trust Center options,
click the File tab, click Options, and then click Trust Center
321
A custom _____ saves the display and print settings for the active worksheet.
view
322
A custom view cannot be created for any worksheet that contains a
table
323
When a workbook that contains a macro is opened, the default macro security setting is
Disable all macros with notification
324
A macro name _____ space(s) and _____ letter(s).
cannot contain; must begin with a
325
The Shortcut key section of the Record Macro dialog box holds the keystrokes that will be used to
execute the macro
326
To use a template you have created, click Personal at the
New backstage area
327
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
328
Where is the Ribbon Display Options button?
in the upper right corner of the screen
329
The Rename dialog box for a group name contains the Display name text box and the_____ list box.
Symbol
330
What displays when you click the Edit button at the Macro dialog box?
a Microsoft Visual Basic for Applications window
331
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
332
Macros are created using Excel’s
macro recorder
333
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
334
When creating an XML schema, you should provide code for at least _____ records.
two
335
A title added to a web page displays in the ______ of the browser window.
Title bar
336
For a workbook that has been marked as final, all the following statements are true except which one?
The workbook is encrypted
337
To mark a workbook as final, begin by clicking the _____ tab to display the _____.
File; Info backstage area
338
To turn on worksheet protection, display the Protect Sheet dialog box and then select
(?)
NOT the password to be used
339
The first step in importing an XML file is to click the _____ button in the _____ group on the Data tab.
Get Data; Get & Transform
340
Which of the following is one of the workbook properties that Excel adds automatically?
date the workbook was created
341
How can you provide a workbook to users with an Excel version earlier than Excel 2007?
Save it in Excel 97-2003 format
342
In an XML schema, the closing tag includes a(n)
forward slash (/)
343
To unlock cells, click the _____ in the Cells group on the Home tab.
Format
344
The _____ feature saves versions of your work at a specified time interval.
AutoRecover
345
When the schema file is added to the workbook, each in the XML code appears as
an XML element in the XML maps in the workbook list box
346
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
347
How can you remove protection from a worksheet?
Click the Unprotect Sheet button in the Protect group on the Review tab
348
To protect the structure of a workbook, use the Protect Workbook button in the _____ group on the _____ tab.
Protect; Review
349
For a workbook that has been marked as final, all the following statements are true except which one?
The workbook is encrypted
350
Workbook properties can be viewed and edited at the
Info backstage area
351
What does XPS stand for?
XML paper specification
352
To remove worksheet protection, click the Unprotect Sheet button or click the _____ in the Info backstage area.
Unprotect hyperlink
353
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
354
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
355
If a password was entered when a workbook was protected, clicking the Protect Workbook button will display the _____ dialog box.
Unprotect Workbook
356
When you publish a worksheet as an HTML web page,
Excel creates files for supplemental data
357
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
358
Which company developed the PDF standard?
Adobe
359
Where is the Encrypt with Password option?
at the Info backstage area
360
A password should contain four types of characters. Which of the following is not one of them?
spaces
361
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
362
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
363
At the Macro dialog box, you can do all the following except _____ a macro.
copy
364
How do you add a command to the Quick Access Toolbar from the Excel Options dialog box?
Double-click the command name
365
A _____ is a workbook with standard text, formulas, and formatting.
template
366
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
367
At the Save As dialog box, click the _____ button to open the Publish as Web Page dialog box.
Publish
368
The term _____ refers to descriptive information about data.
metadata
369
Use Excel’s _____ feature to scan a workbook for personal data and hidden information.
Document Inspector
370
Remove a password from a workbook at the _____ dialog box.
Encrypt Document
371
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
372
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
373
What does PDF stand for?
Portable Document Format
374
What is the name for the scrambled text Excel uses to encrypt passwords?
cipher text
375
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
376
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
377
To determine in advance what areas of a worksheet might cause problems for users with earlier versions of Excel,
run the Compatibility Checker
378
By default, when a worksheet is protected, you
cannot delete any content
379
Which company developed XPS?
Microsoft
380
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
381
Which of the following is not one of the workbook properties listed at the Info backstage area?
User
382
Which of the following is not one of the steps taken to protect a worksheet?
Click Lock Cell
383
Which of the following is not one of the steps for deleting a macro?
Click the Formulas tab
384
When you save a worksheet containing macros created in Excel, what will the file extension be?
.xlsm
385
When you create a new tab for the ribbon, the tab is given which of the following names?
New Tab (Custom)
386
When you send a worksheet as an XPS document, the recipient must have _____ on his or computer to use the file.
XPS viewer
387
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
388
By default, when a new workbook is created, Excel inserts in the _____ property box the name of the computer user.
Author
389
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
390
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
391
To delete a macro, _____, select the macro name, and then click the Delete button.
open the Macro dialog box
392
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
393
At the Excel Options dialog box, _____ displays by default in the Customize the Ribbon option box.
Main Tabs
394
What does XML stand for?
Extensible Markup Language
395
You can only apply a custom view to the worksheet that
was active when the view was created
396
When displayed in the Visual Basic for Applications window, the first few lines of a macro are preceded by apostrophes ('). These lines are
comments
397
Can you create your own conditional formatting rules? If so, how?
Yes; use the New Formatting Rule dialog box
398
A workbook can be saved as a template by changing Save as type to Excel Template or
Excel Macro-Enabled Template
