Test 1
To highlight duplicate values, in the Styles group on the ____ tab, click the Conditional Formatting button, point to Highlight Cells Rules, and then click Duplicate Values.
home
The ____ button on the Ribbon is used to create a hyperlink.
hyperlink
When you click a(n) ____, the computer switches to the file or portion of the file that it references.
hyperlink
You can insert a ____ directly in a workbook file
hyperlink
____ are usually represented by colored words with underlines or images.
hyperlinks
The ____ function is a logical function that evaluates a condition and then returns one value if the condition is true and another value if the condition is false.
if
To evaluate a condition in Excel, you use the ____ function.
if
If you need to use data located in one workbook in the other workbook, you have to create a(n) ____ between the two workbooks.
link
When two workbooks are ____, it is important that the data in the destination file accurately reflects the contents of the source file.
linked
Office Web Apps are a part of Windows ____, which is a collection of services and Web applications that you can use to be more productive.
live
For conditional formatting, a formula must be in the form of a(n) ____ test that results in a true or false value.
logical
An alternative to using several nested IF functions is to use a ____ table.
lookup
When you retrieve a particular value from a lookup table, the ____ value is the value you are trying to find.
lookup
With the DATEDIF function, the ____ interval code means months.
m
As part of the Excel security system that attempts to protect against ____, links to other workbooks are not updated without your permission.
malicious software.
In the Styles group on the Home tab, click the Conditional Formatting button, and then click ____ to open the Conditional Formatting Rules Manager dialog box.
manage rules
Choosing to ____ update values is useful when you want to see the older data values before updating to the new data.
manually
You can create a(n) ____ reference by deleting the $ from the row reference in the cell reference $B$6.
mixed
Placing cell references to input values rather than constants in formulas makes a worksheet ____.
more flexible and easier to update
If you cannot see the sheet tab of a worksheet you want to include in a group, use the sheet ____ controls to display it.
navigation
You can ____ IF functions to allow for three or more outcomes.
nest
You can filter a table by color. Display the filter arrows, click a filter arrow, point to Filter by Color, and then click a color to display only cells with that fill or click ____ to display only cells without a fill.
no fill
To delete the data values, you can click the Clear button in the Editing group on the ____ tab, and then click Clear Contents.
none
An error value begins with a(n) ____ followed by an error name that indicates the type of error.
number sign (#)
If Excel successfully accessed the link and updated the values from the source document, the status in the Edit Links dialog box will be ____.
ok
____ formatting changes a cell's formatting when its contents match a specified condition.
optional
The ____ function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if all the logical conditions are FALSE.
or
The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are ____.
organized
For the col_index_num value, be sure to enter the number that corresponds to the column's position rather than its column letter
otherwise, the VLOOKUP formula may return ____.
When you want to reference an entire column of data in a table, you create a column ____.
qualifier
Omitting the range_lookup entry makes VLOOKUP a(n) ____ lookup.
range
When using VLOOKUP, the ____argument is optional.
range-lookup
Like cell ____, a worksheet group can contain adjacent or nonadjacent sheets.
ranges
To delete the hyperlink but keep the text, right-click the hyperlink cell, and then click ____ Hyperlink.
remove
The default template places the formula results aligned to the ____ of a cell.
right-side
Each time you create a conditional format, you are defining a conditional formatting ____.
rule
You can edit existing conditional formatting ____ from the Conditional Formatting Rules Manager dialog box.
rules
To create a template, you simply save an Excel workbook in template format using the ____ dialog command.
save as
To select adjacent worksheets, you use the ____ key.
shift
When you reference cells and ranges in other worksheets, if the worksheet name contains spaces, you must enclose the sheet name in ____.
single (' ') quotation marks
If you break a link, to undo the action you'd do ________?
Retype the external reference formula
You can edit existing conditional formatting rules from the Conditional Formatting ____ dialog box. Click the Edit Rule button and make the appropriate changes.
Rules Manager
The worksheet range ____ references the worksheets, "Sheet1," "Sheet2," "Sheet3," and "Sheet4."
Sheet1:Sheet4
The ____ function adds the values in a range that match criteria you specify, such as adding the total salary paid to Austin employees.
SumIf
The ____ function searches vertically down the first column of the lookup table.
VLookup
You can use a ____ function to return a value from a table that is based on an approximate match lookup.
VLookup
The general format of a 3-D cell reference is ____.
WorksheetRange!CellRange
A link is a connection between two ____ allowing information to be transferred from one to the other.
files
Using the VLOOKUP function, an approximate match lookup occurs when the value entered falls within a range of values in ____ of the lookup table.
first column
Using the VLOOKUP function, an exact match lookup occurs when the value entered must match one of the values in ____ of the lookup table.
first column
In the Edit Formatting Rule dialog box, click the ____ button to open the Format Cells dialog box.
format
Error values indicate that some element in a ____ is preventing Excel from returning a calculated value.
formula or cell range
Lookup ____ allow you to use tables of data to "look up" values and insert them in another worksheet location.
functions
If you edit a cell in a calculated column so it is no longer consistent with the other formulas in the column, a ____ appears in the upper-left corner of the cell.
green triangle
A worksheet ____ is a collection of two or more selected worksheets.
group
Rather than retyping the same formulas across a series of worksheets, you can enter them all at once by creating a worksheet ____.
group
Column ____ provide a description of the data entered in each column.
headers
When you reference a cell or range in a different worksheet, the ____ separates the sheet reference from the cell reference.
!
The ____ error value means that a value is not available to a function or formula.
#N/A
The ____ error value occurs when an invalid value is specified in the LOOKUP function.
#N/A
If you see ____ as the result of a VLOOKUP formula, you might have entered a letter for the col_index_num instead of a number.
#Name?
The ____ error value means that Excel does not recognize text in the formula or function.
#Name?
The ____ error value means that, for example, a function name is misspelled.
#Name?
The ____ error value means that a formula or function requires two cell ranges to intersect but they do not.
#Null!
The ____ error value means that invalid numbers are used in a formula or function.
#Num
If you want to create a cell reference to the B6 on the Summary Info worksheet of the Sales.xls workbook, you would use the following external reference: ____.
'[Sales.xlsx]Summary Info'!B6
All template files have a(n) ____ extension.
.xltx
You can include up to ____ logical conditions in the AND function.
255
A(n) ____ cell reference specifies not only the rows and columns of a cell range, but also the names of the worksheets on which the cells appear.
3-D
The default template has column widths is set to ____ characters.
8.43
A formula must start with a(n)
=
The ____ function calculates the average of values in a range that match criteria you specify, such as calculating the average salary paid to employees in each city.
AverageIf
Each time you start Excel, a blank ____ workbook opens.
Book 1
The ____ function calculates the number of cells in a range that match criteria you specify, such as counting the number of company employees located in Austin.
CountIf
The ____ caption indicates a worksheet group.
[Group]
The general form of an external reference is ____, where Path is the path of the workbook file on the computer or network, Workbook Name is the filename of the workbook, and Sheet Range and Cell Range are worksheets and cells in the workbook, respectively
[WorkbookName]WorksheetName!CellRange
Formulas created using the point-and-click method contain ____ references.
absolute
When a lookup table is used with a range of values, the compare values must be sorted in ____ order if they are text and in low-to-high order if they are numbers.
alphabetical
You could test two or more conditions and determine whether all conditions are true using the ____ function.
and
____ lookup tables are commonly used to find a taxpayer's tax rate in a tax table, find a shipping charge based on the weight of a package in a shipping charges table, or determine a student's letter grade from a table of grading criteria.
approximate
To set up an approximate match lookup table so it works in Excel, the leftmost column in the lookup table must represent the lower end of the range for each category, and the lookup table must be sorted in ____.
ascending order based on the value in the first column
To get the effect of a calculated column in a range of data, you must copy and paste the formula or use the ____ feature.
autofill
The active worksheet in a group is indicated by ____ text.
bold
If you receive a destination workbook but the source files are not included, click the ____ Link button in the Edit Links dialog box to replace the external references with the existing values.
break
When Excel automatically fills the rest of a table column with a formula entered into an empty table column, this is referred to as a(n) ____ column.
calculated
The default template contains the default formatting you start out with in every new workbook, including ____ 11-point font.
calibri
The default template contains the default formatting you start out with in every new workbook, including the ____ number format.
calibri
The ____ arrangement option layers the open workbooks on the screen.
cascade
The table that stores the data you want to retrieve is called a lookup table, and it organizes the data you want to retrieve into different ____, such as by product ID.
categories
____ just above a cell in the header row of an Excel table to select the column data but not the header.
click
If a sheet range includes more than one sheet, you specify the first and last sheet in the range separated by a ____.
colon
Using the VLOOKUP function, when the lookup value matches a(n) ____ value, the corresponding value from the lookup table is returned to the cell with the lookup formula.
compare
The COUNTIF function is sometimes referred to as a(n) ____ count.
conditional
In many situations, the value you store in a cell depends on certain ____.
conditions
To select nonadjacent worksheets, you use the ____ key.
ctral
Press the ____ keys to move to the top of the table.
ctrl & home
After a calculated column contains an inconsistency, any other edits you make to that column are no longer automatically applied to the rest of the cells in that column because Excel does not overwrite ____ values.
custom
The ____ function calculates the difference between two dates and shows the result in months, days, or years.
dateDif
When you start Excel and use the blank workbook that displays, you are actually using a workbook based on a template known as the ___ template.
default
When you save a workbook that contains external reference formulas Excel stores the most recent results of those formulas in the ____ file.
destination.
____ just above a cell in the header row of an Excel table to select the entire column including the column header.
double-click
The Conditional Formatting button provides access to the ____.
duplicate values conditional format, manage rules option
The ____ dialog box lists all of the links in the current workbook.
edit links
If you trust the provider of source files, you can choose to ____ the content, which allows the external reference formulas to function and updates the links in the destination workbook.
enable
A formula can include a reference to another workbook (called an ____ reference), which creates a set of linked workbooks.
external.
If the destination file is closed when you make a change in the ____ file, you can choose whether to update the link to display the current values when you open the destination file.
source
In the Edit Links dialog box, Type identifies the type of each ____ file.
source.
In the Edit Links dialog box, ____ indicates the file to which the link points.
source.
In the Edit Links dialog box, the ____ option shows whether Excel successfully accessed the link and updated the values from the source document.
status
When you create a calculated column, you can use ____ references to create the formula.
structured
____ references make it easier to create formulas that use portions or all of an Excel table because the names or headers are usually simpler to identify than cell addresses.
structured
You can use ____ to reference cells within an Excel table.
structured references , cell and range addresses
To change the duplicate values background fill color, in the ____ group on the Home tab, click the Conditional Formatting button, and then click Manage Rules.
styles
To ungroup worksheets, you can click a sheet ____ of a sheet not in the group.
tab
If you are not sure of a table's name, click in the table, click the ____ tab on the Ribbon, and then click the Table Name box in the Properties group.
table tools design
To move a worksheet or worksheet group to another location in the same workbook, select the worksheets and then drag and drop them by the selected sheet ____.
tabs
A ____ is a blank workbook that you can open with the text, formats, and formulas already built into it.
template.
In regard to open workbooks, a workspace saves all of the following EXCEPT ____.
the workbooks themselves
The default template has ____ worksheets inserted in the workbook.
three
The ____ template in Excel creates an online time card to track employees' work hours.
timesheet
When a worksheet group is selected, the ____ includes text to indicate this.
title
If all of the logical conditions are true, the AND function returns the logical value ____.
true
To remove a worksheet group, you can right-click the sheet tab of a sheet in the group and then click ____ on the shortcut menu.
ungroup
If you are creating a calculated column or formula within an Excel table, you can use the ____ structured reference in the formula.
unqualified
To manually update the link and see the new data values, click the Update Values button in the Edit Links dialog box.
update values
