Deck 7: Excel

Question 1

After Solver finds a solution, you can generate a(n) _____ report summarizing the target cells, adjustable cells, and constraints.

A) Limits
B) Answer
C) Sensitivity
D) Scenario

Answer 1

Answer

Question 2

In Solver, a(n) _____ is a condition that limits the solution to a set of possible values.

A) changing cell
B) constraint
C) objective cell
D) solving method

Answer 2

constraint

Question 3

Arlo wants to use Goal Seek to answer a what-if question in a worksheet.Which button should he click on the Data tab to access the Goal Seek command?

A) What-if Analysis
B) Data Validation
C) Consolidate
D) Relationships

Answer 3

What-if Analysis

Question 4

After Solver finds a solution, you can generate a(n) _____ summarizing the target cells, adjustable cells, and constraints.

A) Analysis ToolPak
B) Solver PivotTable
C) Answer Report
D) Scenario Summary report

Question 5

Which of the following Excel features finds a numeric solution to a problem involving several input values?

A) Solver
B) PivotTable
C) one-variable data table
D) Goal Seek

Question 6

How can you tell if Solver is activated in your version of Excel?

A) The Solver button appears in the Analyze group on the Data tab.
B) A Solver tab appears on the ribbon.
C) The Solver dialog box opens when you click the Data tab.
D) Solver appears as the Add-in document property in Backstage view.

Question 7

Which type of constraint should you use to limit the answer to whole numbers, such as for employees needed?

A) bin
B) < (less than)
C) integer
D) = (equals)

Question 8

To combine the results of four separate scenarios, you create an Answer report.

Question 9

In a two-input data table, you can vary the results in two cells and see the recalculated results.

Question 10

In the Scenario Manager dialog box, the changing values are the cells that change in the worksheet to show the effect of the scenario.

Question 11

You can create a query to by adding what type of data to the data model?

A) Current worksheet data
B) Outlook address book
C) Word document
D) Word mail merge

Question 12

Which of the following Solver constraints should you delete if the range A5:F5 must contain exact dollar amounts such as $500.25?

A) A5:F5 = 500.25
B) iA5:F5 = int
C) A5:F5 = bin
D) A5:F5 >= 0

Question 13

To delete a scenario, open the Scenario Manager dialog box, select the scenario, and then click the Delete button.

Question 14

You can use Forecast Sheet to project future data based on past trends.

Question 15

A one-input data table shows the result of varying one input value.

Question 16

To analyze the results of scenarios, you can create a(n) _____.

A) Scenario Management chart
B) Scenario Overview
C) Scenario PivotTable
D) Answer report

Question 17

Excel Solver is a(n) _____, which means it must be installed before you can use it for the first time.

A) constraint
B) hidden feature
C) macro
D) add-in

Question 18

You can merge scenarios to apply scenarios from another worksheet into the current one.

Question 19

A scenario is a named set of values you use in a what-if analysis to store and observe different worksheet
results.

Question 20

If you want to edit the changing values for a scenario, apply the scenario to the worksheet, and then edit the data directly.

Question 21

A _____ is a calculated named field in Power Pivot.

A) measure
B) relationship
C) group
D) 3D map

Question 22

Where can you create a relationship between two Excel tables?

A) Power Pivot
B) Power View
C) Get & Transform window
D) Solver

Question 23

The _____ describes the structure of data tables and how they interact.

A) data model
B) Solver
C) Scenario Manager
D) Goal Seek

Question 24

If Solver finds a solution, you can generate a Solution PivotTable on a separate worksheet.

Question 25

Nathan wants to analyze a range of values that shows the results of changing a single input value in a formula.What should he create?

A) PivotTable
B) Histogram
C) Power Pivot table
D) One-input data table

Question 26

Julio wants to analyze the best, most likely, and worst outcomes for changing expense values.He should create three scenarios.

Question 27

You can use Goal Seek to determine how many products to sell to earn a targeted profit amount.

Question 28

If you change data used in the data model, what should you do?

A) Refresh the data.
B) Reset the relationships.
C) Add the new data to the data model.
D) Display the data in Table view.

Question 29

When you create a scenario summary report, it is linked to the data so that if you change the data values, the report updates automatically.

Question 30

After adding tables to the Data Model, where can you create a relationship between the tables?

A) Power Pivot window
B) Connections pane
C) Query tab
D) Power View

Question 31

The Analysis ToolPak is an Excel add-in that contains statistical analysis tools.

Question 32

Which of the following statements describes tables where the EmployeeID field in the Employees table can be associated with many CustomerID fields in the Customers table?

A) They have a one-to-many relationship.
B) They are power tables.
C) They are static tables that cannot be updated.
D) They are mirror tables.

Question 33

A _____ is a calculated named field in Power Pivot that uses data analysis expressions.

A) 3-D map
B) Power field
C) measure
D) layer

Question 34

When creating a _____ in Power Pivot, you can combine fields from multiple tables in the data model.

A) PivotTable
B) 3D map
C) measure
D) PowerTable

Question 35

Adding a table to the data model is also called adding a _____.

A) pivot
B) query
C) relationship
D) transformation

Question 36

A _____ between tables in the data model lets you access data from both tables.

A) relationship
B) measure
C) query
D) diagram

Question 37

Which category do you select in the Excel Options dialog box to add the Power Pivot tab to the ribbon?

A) Customize Ribbon
B) Advanced
C) Language
D) Proofing

Question 38

To open the Power Pivot window from the workbook, click the View tab, then click the Data Tools button.

Question 39

How can you create a single PivotTable to analyze data from two tables?

A) Create the PivotTable using Power Pivot.
B) Use the same name for each table.
C) Create a measure to link the tables.
D) Group the tables.

Question 40

When you click the Add to Data Model button on the Power Pivot tab, you are adding a query to the data model.

Question 41

Joyce wants to calculate the monthly payment for a $16,000 car loan at 5.5% interest over 5 years.She should create a formula using the NPER function.

Question 42

You can use the Evaluate Formula button to identify the source of a cell error.

Question 43

Which of the following will you select as X in the series of clicks to clear data validation circles in a worksheet: Data tab > Data Tools group > Arrow next to X > Clear Validation Circles?

A) What-If Analysis
B) Data Validation
C) Remove Duplicates
D) Consolidate worksheet data.

Question 44

What information do you need to provide to Solver so it can find the best solution to a problem? Select all the options that apply.

A) break-even cell
B) target cell
C) changing cells
D) constraints

Question 45

After adding data validation criteria to a worksheet, which of the following features can you use to locate data that doesn't satisfy the criteria?

A) What-If Analysis
B) Circle Invalid Data
C) Remove Duplicates
D) Trace Precedents

Question 46

The COUNTIF function counts blank cells.

Question 47

The SUMIF function totals only the cells in a range that meet given criteria.

Question 48

Tracer arrows help you determine which cells are included in named ranges.

Question 49

Excel provides a tool for tracing formula operators.

Question 50

Which of the following are Excel what-if analysis tools? Select all the options that apply.

A) Goal Seek
B) Scenario Manager
C) Data Validation
D) Solver

Question 51

As you edit a worksheet, you can open the Watch window to watch the effect that cell changes have on selected worksheet formulas.

Question 52

Where calculated columns summarize data in each row of a table, a constraint creates an overall summary.

Question 53

Henry imported text data, which appeared in all lowercase.To convert text such as "order date" to "Order Date," he can use the TEXTJOIN function.

Question 54

Tracing dependents indicates which cells are affected by a formula error.

Question 55

You can use named ranges in formulas to make the formulas easier to build and to reduce formula errors.

Question 56

Which of the following arguments do you need to provide to the PMT function? Select all the options that apply.

A) rate
B) nper
C) fv
D) pv

Question 57

Willa created three scenarios to perform a what-if analysis.To apply the values in the first scenario to the worksheet, she can select the first scenario and then click the Load button.

Question 58

Curtis is planning to invest $50 per month for the next 60 months and expects to earn 5% in interest.Which of the following functions can he use to determine how much the investment will be worth at the end of 60 months?

A) PMT
B) NPER
C) FV
D) PV

Question 59

If the answer to a what-if question requires data from two tables, you can use Power Pivot to create a PivotTable that uses fields from both tables.

Question 60

Richard retired recently and is receiving $400 per month from an investment at a rate of 7% for 20 years.Which of the following functions can he use to calculate the current value of the investment?

A) PMT
B) NPER
C) PV
D) RATE

Question 61

After you define the scope for a named range, you can no longer edit the defined name.

Question 62

Tricia created a defined name for a range but no longer wants to use it.What dialog box can she use to delete the defined name?

A) Define Names dialog box
B) Name Manager dialog box
C) Scope dialog box
D) New Name dialog box

Question 63

When you enter a formula such as =SUM('Chicago:San Francisco'!A6), which references cell A6 on four worksheets from the Chicago worksheet to the San Francisco worksheet, what type of reference are you using?

A) structured reference
B) absolute reference
C) 3-D reference
D) mixed reference

Question 64

Charlie imported data that displays "Albany, NY" in cell A24.What formula can he use to display "NY" in cell D3?

A) =LEFT(A24, 2)
B) =RIGHT(A24, 2)
C) =LOWER(A24)
D) =EXTRACT(A24, 2)

Question 65

In the formula =SUM('San Francisco'!A6:D6), the exclamation point is a(n) linked reference indicator.

Question 66

Cell D1 contains the text "Office Locations." Min wants to convert the text to "OFFICE LOCATIONS." Which of the following formulas should he use?

A) =LOWER(D1)
B) =PROPER(D1)
C) =UPPER(D1)
D) =CONCAT(D1)

Question 67

Which of the following functions can you use to combine two or more strings into one text string? Select all the options that apply.

A) SUBSTITUTE
B) CONCAT
C) TOGETHER
D) TEXTJOIN

Question 68

After you circle invalid data on a worksheet, the circles remain in place until you correct the circle values.

Question 69

Which of the following are math & trig functions? Select all the options that apply.

A) SUMIFS
B) SUMIF
C) CONCAT
D) MATCH

Question 70

Which of the following are statistical functions? Select all the options that apply.

A) TEXTJOIN
B) NPER
C) COUNTIF
D) AVERAGEIF

Question 71

Which of the following are financial functions? Select all the options that apply.

A) PMT
B) PROPER
C) NPER
D) FV

Question 72

Which of the following functions are text functions that convert the case of text? Select all the options that apply.

A) UPPER
B) LEN
C) LOWER
D) PROPER

Question 73

If you want to combine data from multiple worksheets and display the results on another worksheet, you can summarize the data.

Question 74

Which of the following Excel tools can you use to track and correct errors in a worksheet? Select all the options that apply.

A) Error Checking dialog box
B) Evaluate Formula button on the Formulas tab
C) Calculation Options button on the Formulas tab
D) Watch Window

Question 75

Which of the following can you do using the Name Manager dialog box? Select all the options that apply.

A) Create a new name.
B) Apply a defined name into a formula.
C) Change a defined name.
D) Filter the defined names.

Question 76

To display the first four characters from a cell containing text, you can use the MID function.

Question 77

In the formula =SUM('San Francisco'!A6:D6), what do the single quotation marks around the worksheet reference indicate?

A) The sheet name includes a space.
B) The cells are in another workbook.
C) The cells are in another worksheet.
D) San Francisco is the name of a table column.

Question 78

Lucinda's small business earned $15,000 in profit in August.She wants to calculate the profit she will earn in 12 months if profit grows by 1.5% each month.Which of the following can she do to make the calculation?

A) Use Flash Fill.
B) Fill a series with a growth trend.
C) Create a custom AutoFill list.
D) Use the Consolidate tool.

Question 79

Stephanie added data validation to a worksheet column after entering a few records of data.Which Excel feature can she use to locate data that doesn't satisfy the data validation criteria?

A) Tracer Arrows
B) Error Check
C) Watch Window
D) Circle Invalid Data

Question 80

Hannah is preparing to take out a mortgage to buy a house.She wants to compare the monthly payments for the loans she is considering, which have interest rates ranging from 3.5% to 5.25%.She can quickly fill in the values from 3.5% to 5.25% using a sequential series.