Excel Module 7
Quiz yourself by thinking what should be in
each of the black spaces below before clicking
on it to display the answer.
Help!
|
|
||||
|---|---|---|---|---|---|
| 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 ____ 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
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| In many situations, the value you store in a cell depends on certain ____. | conditions
🗑
|
||||
| To evaluate a condition in Excel, you use the ____ function. | if
🗑
|
||||
| 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
🗑
|
||||
| ____ just above a cell in the header row of an Excel table to select the column data but not the header. | click
🗑
|
||||
| ____ just above a cell in the header row of an Excel table to select the entire column including the column header. | double-click
🗑
|
||||
| You could test two or more conditions and determine whether all conditions are true using the ____ function. | and
🗑
|
||||
| If all of the logical conditions are true, the AND function returns the logical value ____. | true
🗑
|
||||
| You can include up to ____ logical conditions in the AND function. | 255
🗑
|
||||
| The ____ function calculates the difference between two dates and shows the result in months, days, or years. | dateDif
🗑
|
||||
| With the DATEDIF function, the ____ interval code means months. | m
🗑
|
||||
| Column ____ provide a description of the data entered in each column. | headers
🗑
|
||||
| ____ 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
🗑
|
||||
| When you want to reference an entire column of data in a table, you create a column ____. | qualifier
🗑
|
||||
| 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
🗑
|
||||
| When you create a calculated column, you can use ____ references to create the formula. | structured
🗑
|
||||
| If you are creating a calculated column or formula within an Excel table, you can use the ____ structured reference in the formula. | unqualified
🗑
|
||||
| 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
🗑
|
||||
| You can ____ IF functions to allow for three or more outcomes. | nest
🗑
|
||||
| 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
🗑
|
||||
| Lookup ____ allow you to use tables of data to “look up” values and insert them in another worksheet location. | functions
🗑
|
||||
| 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
🗑
|
||||
| Placing cell references to input values rather than constants in formulas makes a worksheet ____. | more flexible and easier to update
🗑
|
||||
| The ____ function searches vertically down the first column of the lookup table. | VLookup
🗑
|
||||
| When using VLOOKUP, the ____argument is optional. | range-lookup
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| 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 ____.
🗑
|
||||
| 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?
🗑
|
||||
| You can use a ____ function to return a value from a table that is based on an approximate match lookup. | VLookup
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| ____ 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
🗑
|
||||
| Omitting the range_lookup entry makes VLOOKUP a(n) ____ lookup. | range
🗑
|
||||
| Error values indicate that some element in a ____ is preventing Excel from returning a calculated value. | formula or cell range
🗑
|
||||
| An error value begins with a(n) ____ followed by an error name that indicates the type of error. | number sign (#)
🗑
|
||||
| The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are ____. | organized
🗑
|
||||
| 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 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
🗑
|
||||
| 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
🗑
|
||||
| The Conditional Formatting button provides access to the ____. | duplicate values conditional format, manage rules option
🗑
|
||||
| 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 ____ 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 ____ 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 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
🗑
|
||||
| ____ formatting changes a cell’s formatting when its contents match a specified condition. | optional
🗑
|
||||
| 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
🗑
|
||||
| Press the ____ keys to move to the top of the table. | ctrl & home
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| 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
🗑
|
||||
| For conditional formatting, a formula must be in the form of a(n) ____ test that results in a true or false value. | logical
🗑
|
||||
| A formula must start with a(n) | =
🗑
|
||||
| In the Edit Formatting Rule dialog box, click the ____ button to open the Format Cells dialog box. | format
🗑
|
||||
| The COUNTIF function is sometimes referred to as a(n) ____ count. | conditional
🗑
|
||||
| You can use ____ to reference cells within an Excel table. | structured references , cell and range addresses
🗑
|
Review the information in the table. When you are ready to quiz yourself you can hide individual columns or the entire table. Then you can click on the empty cells to reveal the answer. Try to recall what will be displayed before clicking the empty cell.
To hide a column, click on the column name.
To hide the entire table, click on the "Hide All" button.
You may also shuffle the rows of the table by clicking on the "Shuffle" button.
Or sort by any of the columns using the down arrow next to any column heading.
If you know all the data on any row, you can temporarily remove it by tapping the trash can to the right of the row.
To hide a column, click on the column name.
To hide the entire table, click on the "Hide All" button.
You may also shuffle the rows of the table by clicking on the "Shuffle" button.
Or sort by any of the columns using the down arrow next to any column heading.
If you know all the data on any row, you can temporarily remove it by tapping the trash can to the right of the row.
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
Created by:
galvanl
Popular Computers sets