Final Exam review
Which of the following error values indicates that the formula divides by zero (0) or an empty cell?
#DIV/0!
Which of the following error values indicates that a value or an argument is missing?
#N/A
Which of the following error values indicates that the formula uses unrecognized text such as a misspelled function, sheet, or range name?
#NAME"
Which of the following error values indicates that the formula refers to an intersection of two cell ranges that do not intersect?
#NULL!
Which of the following error values indicates that the formula uses an invalid numeric entry?
#NUM!
Which of the following error values indicates that a cell reference is empty?
#REF!
Which of the following error values indicates that an incorrect data type is used?
#VALUE!
Which of the following extensions identifies a macro-enabled workbook?
.xlsm
Which of the following extensions does Excel assign to templates?
.xltx
Which of the following represents the short date format?
10/14/2019
How many types of reports can you create using the Solver Results dialog box?
3
Which of the following is an example of an external reference with all of its identifiers?
='[WorkbookName]WorksheetName'!CellRange
Which of the following shows the correct syntax for the AND function?
=AND(LogicalN)
Which of the following statements about SmartArt graphics is not true?
A SmartArt graphic is linked to worksheet data
Which of the following statements is not true about calculated fields?
A calculated field is a field in the source data.
Which of the following functions joins or combines data strings?
CONCAT function
The header or footer is one command that requires all grouped sheets to be identical.
FALSE
Use Solver to find the combination of procedures that will result in the maximum possible profit per week in cell B7. Use the range name Optimal as the variable cells. Add these constraints in order: values in the named range Optimal are less than or equal to the values in the named range Max; values in the named range Optimal are whole numbers; values in the named range Procedures are less than or equal to values in the named range MaxAllowed. Run Solver and accept the solution.
In the Data Ribbon Tab in the Analyze Ribbon Group, you clicked the Solver button. Inside the Solver Parameters dialog, you clicked the DialogHeader view, clicked the dialog undefined button, typed B7 in the Set Objective input, typed Optimal in the By Changing Variable Cells input, and clicked the Add button. Inside the Add Constraint dialog, you typed Optimal in the Cell Reference input, pressed the Tab key, typed Max in the Constraint input, and clicked the OK button. Inside the Solver Parameters dialog, you clicked the Add button. Inside the Add Constraint dialog, you typed Optimal in the Cell Reference input. Inside the Add Constraint dialog in the Operator drop-down, you selected int. Inside the Add Constraint dialog, you clicked the OK button. Inside the Solver Parameters dialog, you clicked the Add button. Inside the Add Constraint dialog, you typed Procedures in the Cell Reference input, typed MaxAllowed in the Constraint input, and clicked the OK button. Inside the Solver Parameters dialog, you clicked the Solve button. Inside the Solver Constraints dialog, you clicked the OK button.
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Social Media, Direct Mail, and Other. Do not include links to the source data.
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Consolidate button. You clicked the Social Media tab, clicked cell B3. Inside the Consolidate dialog, you clicked the Add button. You clicked the Direct Mail tab. Inside the Consolidate dialog, you clicked the Add button. You clicked the Other tab. Inside the Consolidate dialog, you clicked the Add button, clicked the OK button.
Apply data validation rules to the selected cells to allow only decimal numbers between 2.5 and 4.0. Do not allow blanks.
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Data Validation... menu item. Inside the Data Validation dialog in the Allow drop-down, you selected Decimal. Inside the Data Validation dialog, you typed 2.5 in the Minimum: input, typed 4.0 in the Maximum: input, unchecked the Ignore blank check box, and clicked the OK button.
Use Goal Seek to find the value for cell C2 that will result in a value of $550,000 for cell B15. Accept the solution.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Goal Seek... menu item. Inside the Goal Seek dialog, you typed 550,000 in the To value: input, typed C2 in the By changing cell: input, and clicked the OK button. Inside the Goal Seek Status dialog, you clicked the OK button.
Create a new scenario to reflect a change in cell B9 to a value of 0.01 Name the scenario Low COLA.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the DialogHeader view, clicked the dialog undefined button, and clicked the Add... button. Inside the Add Scenario dialog, you typed Low COLA in the Scenario name: input, clicked the OK button. Inside the Scenario Values dialog, you typed 0.01 in the text box input, clicked the OK button.
Edit the code for the FormatPieChart macro in the Visual Basic Editor.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog from the Macro name: list, you selected FormatPieChart. Inside the Macro dialog, you clicked the Edit button.
Run the FormatPieChart macro.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Run button.
Begin recording a new macro.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button.
In cell A8, enter a formula using AND to display TRUE if sales in 2016 (cell B2) are greater than 1500000 (one million, five hundred thousand) and sales in 2017 (cell C2) are greater than 1500000 (one million, five hundred thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the AutoSum button arrow, clicked the Recently Used button, clicked the Financial button, and clicked the Logical button. In the Logical menu, you clicked the AND menu item. Inside the Function Arguments dialog, you typed B2>1500000 in the Logical1 input, typed C2>1500000 in the Logical2 input, and clicked the OK button.
Enter a formula in cell G1 to calculate the standard deviation of the values in cells D2:D13. Assume this array is a sample of a larger set of values
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the STDEV.S menu item. Inside the Function Arguments dialog, you clicked the DialogHeader view, clicked the dialog undefined button, typed D2:D13 in the Number1 input, and clicked the OK button.
Add a hyperlink from the selected cell to link to cell B4 in the Social Media worksheet. Don't forget to use B4 as the cell reference
In the Insert Ribbon Tab in the Links Ribbon Group, you clicked the Link button. Inside the Insert Hyperlink dialog from the Link to: list, you clicked the Place in This Document item. Inside the Insert Hyperlink dialog from the Or select a place in this document list from the Cell Reference list, you selected 'Social Media'. Inside the Insert Hyperlink dialog, you typed B4 in the Type the cell reference: input, clicked the OK button.
Display all the comments in this worksheet at once.
In the Review Ribbon Tab in the Comments Ribbon Group, you clicked the Show All Comments button.
Complete the one-variable data table in cells E3:F8 to calculate the breakeven sales point for varying owner withdrawal amounts. The formula has been entered for you in cell E3. It references the original owner withdrawal value in cell B12. The substitute values have been entered for you in cells E4:E8.
Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Data Table... menu item. Inside the Data Table dialog, you clicked the DialogHeader view, clicked the dialog undefined button, typed B12 in the Column input cell input, and clicked the OK button.
Which of the following ungroups all sheets in a workbook?
Right-click any sheet tab and choose Ungroup Sheets
Which function displays columnar data in rows and vice versa?
TRANSPOSE
Which of the following does not occur when you open a read-only file?
The worksheet requires a password
Which of the following is the programming language that underlies Excel macros?
VBA
Remove the hyperlink from cell B3.
You clicked cell B3, right clicked cell B3. In the Right-Click menu, you clicked the Delete... menu item. Inside the Delete dialog, you clicked the Close button. In the Insert Ribbon Tab in the Links Ribbon Group, you clicked the Link button. Inside the Insert Hyperlink dialog from the Link to: list, you clicked the Place in This Document item. Inside the Insert Hyperlink dialog, you clicked the Cancel button. You right clicked cell B3. In the Right-Click menu, you clicked the Remove Hyperlinks menu item.
Ungroup the grouped sheets.
You clicked the Dec 11 tab, clicked the Dec 18 tab, and right clicked the Dec 18 tab. In the Tab Right Click menu, you clicked the Ungroup Sheets menu item.
Mark the workbook as final.
You opened the backstage view, clicked the Protect Workbook button. In the Protect Workbook menu, you clicked the Mark as Final menu item. Inside the Microsoft Excel dialog, you clicked the OK button. Inside the Microsoft Excel dialog, you clicked the OK button.
Enter a formula in cell E2 to calculate the absolute value of C2-D2.
You typed =ABS(C2-D2) in cell E2, pressed Enter
In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon in between. The result of the formula should look like this: Rent:Parking
You typed =CONCAT(B2:C2) in cell D2, pressed Enter.
In cell F9, enter a formula using the INDEX function to return the ending loan balance for the row listed in cell F8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6.
You typed =INDEX(Schedule, in cell F9, clicked cell F8, typed =INDEX(Schedule,F8,6) in cell F9, and pressed Enter.
Which of the following best describes a Trusted Location in Excel?
a folder
Which of the following is not true when using Goal Seek?
absolute value is typed in the to value box
Which of the following is not true regarding the Compatibility Checker dialog box?
access the compatibility checker by clicking the review tab
Which of the following is a step-by-step procedure to find a solution?
algorithm
Which of the following add-ins performs statistical and engineering functions?
analysis toolpak
When you run a macro that was recorded with absolute references, where should the active cell be before running the macro?
anywhere in the worksheet
How does Excel display a sheet that is being used in a dynamic consolidation?
as an outline
Which of the following is not true for unlocking cells?
cells are not locked by default
Which of the following creates a drop-down list from which you select one option?
combo box
In VBA code, what is the name of each line that is preceded by an apostrophe and shown in green?
comment
Which of the following is data that is the same in each query and allows for a relationship between queries?
common field
which command links workbooks
consolidate
Which of the following is the keyboard shortcut for inserting a hyperlink?
ctrl+K
Which of the following is defined as a range of cells that shows calculated results for one or more formulas
data table
Which of the following commands is a quick and easy way to prepare a summary report about a set of values?
descriptive statistics
Which of the following should you use when data on source worksheets might be edited after you have created the consolidation worksheet?
dynamic consolidation
A static consolidation summarizes data and displays a result on the consolidated sheet that updates automatically.
false
Always type commas as thousand separators in formulas.
false
By default, the Developer tab displays on the Ribbon.
false
Forecast means knowing the results and determining the value needed to reach those results.
false
It is best to create data validation settings after all data is entered.
false
It is not possible to remove metadata from a workbook.
false
Macro names must begin with a number and cannot contain spaces.
false
Once a shortcut has been assigned to a macro, it cannot be changed.
false
The Circle Invalid Data command corrects cells with data that does not conform to validation criteria.
false
Up to three scenarios can be displayed at a time in a worksheet.
false
Use the Page Layout tab to insert a picture in an Excel worksheet.
false
When worksheets are grouped, the title bar displays [Document1] after the file name.
false
Which of the following is a worksheet with an Excel table and a chart that illustrates past data and predicts future values generated from data in the worksheet?
forecast sheet
Which of the following moves the pointer to another workbook or web page?
hyperlink
Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected.
in the mini toolbar you clicked the format button in the format menu you clicked the lock cell menu item
Which of the following displays warning information yet allows users to make an entry?
information message box
Which of the following commands sets a read-only file property?
mark as final
Which of the following calculates a series of averages for a set of values?
moving average
When using Solver, which of the following parameters is a cell with a formula to be calculated to reach a desired result?
objective cell
What should you do before creating a new scenario in Excel?
save the current data in a worksheet as a scenario
Which of the following tasks is not performed using the Excel Options dialog box?
set data validation rules
Which of the following is used to select adjacent sheets?
shift
Which of the following creates a visual filter for a field in a PivotTable?
slicer tool
Which of the following displays in the upper right corner of a cell after a comment has been added?
small red triangle
Which of the following prohibits a user from making an entry, and provides a choice of either canceling the task or retrying?
stop warning
Which of the following are messages that identify code that Visual Basic doesn't recognize?
syntax errors
How many arguments do all database functions have?
three
A macro records every step and every keystroke, including incorrect or unnecessary steps.
true
A nested function is a function within a function.
true
A source workbook includes data that is referenced in a dependent workbook.
true
An external reference formula refers to cells in another workbook and establishes a link to the source workbook.
true
Basic form controls do not require any knowledge of Visual Basic for Applications (VBA) coding.
true
Decide whether to use absolute or relative references in AND or OR functions by determining if the formula will be copied
true
If you edit a scenario after creating a summary report, the report is automatically updated.
true
In an OR function, if any one of the arguments is true, the result is True.
true
Logical functions determine if one or all conditions are met and display True or False as the result.
true
Not all commands work for grouped worksheets.
true
The functions in the Lookup & Reference group perform calculations
true
The result for an EXACT function is either True or False.
true
To save your macros, you must save the workbook as an Excel macro-enabled workbook.
true
You can dynamically consolidate data by using 3D references in your own formulas.
true
You don¿t need to key an equals sign when you use the Function Arguments dialog box to create a function.
true
Which of the following runs in a window that is separate from Excel?
vbe
Which of the following does not occur after a dynamic consolidation is created?
worksheets are automatically grouped
