Q&A Hero

Q: The ____________________ function adds the values in a range that meet criteria you specify.

Q: The ____________________ function is used to change the orientation of a rangethat is, return a vertical range of cells as a horizontal range, or vice versa.

Q: The _____ function adds the values in a range that match criteria you specify. a. ADDIF b. ADDAS c. SUMIF d. SUMAS

Q: The ____________________ function searches vertically down a lookup table and is used when the compare values are stored in the first column of the lookup table.

Q: The _____ function calculates the average of values in a range that match criteria you specify. a. AVERAGEAS b. AVERAGEOR c. AVERAGEAND d. AVERAGEIF

Q: The ____________________ function searches horizontally across a lookup table and is used when the compare values are stored in the first row of the lookup table.

Q: To change the background fill color of duplicate values, in the _____ group on the Home tab, click the Conditional Formatting button, and then click Manage Rules. a. Format b. Options c. Styles d. Tools

Q: ____________________ formatting changes a cells formatting when its contents match a specified condition.

Q: Each time you create a conditional format, you are defining a conditional formatting _____. a. theme b. object c. condition d. rule

Q: A(n) ____________________ specifies the type of condition, the type of formatting when that condition occurs, and the cell or range the formatting is applied to.

Q: You can edit existing conditional formatting _____ from the Conditional Formatting Rules Manager dialog box. a. themes b. objects c. tables d. rules

Q: A(n) ____________________ match lookup occurs when the lookup value is found within a range of numbers in the first column (or row) of the lookup table.

Q: The ____________________ function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if the logical conditions are false.

Q: For conditional formatting, a formula must be in the form of a(n) _____ test that results in a true or false value. a. conditional b. hypothetical c. logical d. approximate

Q: The ____________________ function can determine if a cell contains an error value and displays the message you choose rather than the default error value.

Q: In the Edit Formatting Rule dialog box, click the _____ button to open the Format Cells dialog box. a. Format Cells b. Options c. Format d. Cell Tools

Q: Using the VLOOKUP function, an exact match lookup occurs when the value entered must match one of the values in _____ of the lookup table. a. the first column b. any column c. the first row d. any row

Q: 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. a. THIS b. IF c. WHEN d. NOT

Q: 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. a. #NULL b. #REF? c. #NAME? d. #DIV/0

Q: _____ just above the header row of an Excel table to select the column data but not the header. a. Click b. Double-click c. Triple-click d. Right-click

Q: You can use a _____ function to return a value from a table that is based on an approximate match lookup. a. VLOOKUP b. VAVERAGE c. VMATCH d. VAPPROXIMATE

Q: You could test two or more conditions and determine whether all conditions are true using the _____ function. a. OR b. IF c. AND d. THIS

Q: An error value begins with a(n) _____ followed by an error name that indicates the type of error. a. number sign b. asterisk c. exclamation point d. plus sign

Q: If all of the logical conditions are true, the AND function returns the logical value _____. a. FALSE b. ON c. TRUE d. OFF

Q: The major difference between HLOOKUP and VLOOKUP functions is the way the lookup tables are _____. a. named b. organized c. saved d. opened

Q: You can include up to _____ logical conditions in the AND function. a. 25 b. 50 c. 225 d. 255

Q: The _____ error value means that a value is not available to a function or formula. a. #NAME? b. #N/A c. #NULL! d. #NUM!

Q: The Conditional Formatting button provides access to the _____. a. Replicate Values conditional format b. Manage Rules option c. Criteria option d. Organize Rules option

Q: With the DATEDIF function, the _____ interval code means months. a. m b. mo c. mon d. mth

Q: You can edit existing conditional formatting rules from the Conditional Formatting _____ dialog box. Click the Edit Rule button and make the appropriate changes. a. Rules Editor b. Rules Manager c. Editor d. Manager

Q: _____ references make it easier to create formulas that use portions or all of an Excel table because the names of headers are usually simpler to identify than cell addresses. a. Indexed b. Structured c. Key d. Logical

Q: The _____ function calculates the number of cells in a range that match criteria you specify. a. NUMBERIF b. COUNTIF c. NUMBERAS d. COUNTAS

Q: When you want to reference an entire column of data in a table, you create a column _____. a. label b. name c. variable d. qualifier

Q: When you create a calculated column, you can use _____ references to create the formula. a. indexed b. structured c. key d. logical

Q: If you are creating a calculated column or formula within an Excel table, you can use the _____ structured reference in the formula. a. basic b. advanced c. qualified d. restricted

Q: You can _____ IF functions to allow for three or more outcomes. a. loop b. cycle c. nest d. link

Q: Lookup _____ allow you to use tables of data to look up values and insert them in another worksheet location. a. functions b. formulas c. equations d. calculations

Q: The _____ function searches vertically down the first column of the lookup table. a. VLOOK b. VLOOKUP c. HLOOK d. HLOOKUP

Q: When using VLOOKUP, the _____argument is optional. a. lookup_value b. table_array c. range_lookup d. col_index

Q: The ORERROR function can determine if a cell contains an error value and then display the message you choose. a. True b. False

Q: 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. a. red circle b. blue square c. red square d. green triangle

Q: Nesting IF functions will allow for three or more outcomes. a. True b. False

Q: Two common categories of formula errors in Excel are syntax errors and logic errors. a. True b. False

Q: The IF function evaluates a(n) single condition. _________________________

Q: In a fully qualified structured reference, the table name follows the column qualifier. _________________________

Q: In the DATEDIF function, the first date argument is the latest date. _________________________

Q: You cannot nest more than one IF function to test for multiple outcomes. _________________________

Q: If any logical condition listed in the function is true, the AND function returns a TRUE value. _________________________

Q: Duplicate value highlighting helps to verify that columns of data have unique entries. _________________________

Q: In most cases, when manually creating a conditional formatting formula, the formula should use absolute references and point to the first row of data in a table. _________________________

Q: An error value begins with a(n) number sign followed by an error name, which indicates the type of error. _________________________

Q: Case 7-1 Ted knows that macros can be helpful to him in his work with Excel spreadsheets, but he also knows they have their hazards, so he comes to you for guidance about what kind of security settings he should put in place, and at what times. After a year of work, Ted is taking coursework to become a VBA programmer. He is working on developing an application that contains macros. Which should his option be in the Trust Center? a. Enable all trusted macros b. Enable all macros c. Disable all macros except digitally signed macros d. Disable a macro with notification

Q: You can calculate the number of cells in a range that match criteria you specify using the SUMIF function. _________________________

Q: Case 7-2 Rina has just completed some coursework in Excel application writing and wants to begin developing her own macros to share more widely. She knows that she has to learn more about digital signatures in order to make her plan a reality. Which of the following can Rina tell when she opens a digitally signed file? a. Who the author of the file is b. Whether the information in the file is correct c. Whether the macros in the workbook will produce accurate results d. Whether the macro will perform as expected

Q: The VLOOKUP function searches vertically down the first column of the lookup table for the value you entered, and then retrieves the corresponding value from another column of the table. _________________________

Q: All the logical conditions listed in an AND function must be true for the AND function to return a TRUE value. a. True b. False

Q: 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. a. concatenated b. calculated c. autofilled d. custom

Q: Case 7-3 Juan tells you that your work in Excel will go a lot faster if you start using defined names instead of cell and range addresses. You believe him but you have been frustrated by defined names in the past because you did not know the rules for naming them and you have run into errors as a result. Juan offers to help you. Which of the following is a true statement Juan can make about defined names as he is explaining the rules to you? a. The name can include letters, numbers, periods, and underscores b. To distinguish multiword names, use a hyphen between the words c. Defined names are case sensitive d. The name should start with a hash tag

Q: If you need to modify the formula in a calculated column, you edit the formula in one cell of the column and then you modify the formulas in the remaining cells in that table column. a. True b. False

Q: A formula that includes a structured reference can be fully qualified or unqualified. a. True b. False

Q: To get the effect of a calculated column in a range of data, you must copy and paste the formula or use the _____ feature. a. Concatenation b. Merged c. AutoFill d. Indexed

Q: A(n) _____ certificate is another name for a digital ID. a. analog b. signature c. digital d. secure

Q: A digital _____ is like a seal of approval. a. affidavit b. clause c. signature d. salutation

Q: To create a macro, you can enter a series of commands in the _____ programming language. a. HTML b. Java c. VBA d. Perl

Q: Sometimes when you build a worksheet for other users, you may decide to add a(n) _____ message to a cell even if you do not validate the data in that cell. a. value b. input c. key d. user

Q: The _____ Macro workbook is a hidden workbook opened whenever you start Excel, making its macros available anytime you use Excel. a. Universal b. Personal c. Microsoft d. XP

Q: Which of the following tabs in the Data Validation dialog box allows you to set validation criteria? a. Criteria b. Input Message c. Error Alert d. Settings

Q: You can turn off the macro recorder by clicking the Stop Recording button in the Code group on the _____ tab. a. Insert b. Developer c. Layout d. Macro

Q: When you _____ a workbook, you control the ability users have to make changes to the file. a. ensure b. protect c. secure d. seal

Q: You use the _____ tab in the Data Validation dialog box to enter the validation rules for the active cell. a. Active Cell b. Criteria c. Settings d. Input Message

Q: If a _____ error appears when you run a macro that has worked in the past, some part of the macro code no longer makes sense to Excel. a. syntax b. run-time c. logic d. baseline

Q: _____ messages appear as ScreenTips next to the active cell. a. Value b. Input c. Key d. User

Q: To view the code of a macro, you need to open the Visual Basic _____. a. Manager b. Code Window c. Reader d. Editor

Q: The following are Excel-supported error alert types: _____. a. Stop, Warning, and Alert b. Alert, Warning, and Information c. Security, Warning, and Alert d. Stop, Warning, and Information

Q: In VBA, macros are called _____ procedures. a. body b. sub c. commented d. modular

Q: The _____ alert accepts the invalid data but still allows you to cancel the data entry. a. Information b. Stop c. Security d. Warning

Q: Each sub procedure in VBA begins with the keyword "Sub" followed by the name of the sub procedure and a set of _____. a. brackets b. braces c. parentheses d. asterisks

Q: Every cell in a workbook has a(n) _____ property that determines whether or not changes can be made to that cell. a. ensured b. locked c. security d. changeable

Q: After the comments, in VBA code the next item is the _____ of the macro. a. module b. body c. procedure d. command

Q: Usually, you will want to protect a worksheet but leave some cells _____. a. open b. empty c. clear d. unlocked