1350 Quick Check Questions

What is the external reference to cell D10 of the Sunday worksheet located in the Weekly Report.xslx workbook that is stored in C:\Documents\Reports folder?

'C:\Documents\Reports[Weekly Report.eslx]Sunday'!D10

What is the external reference to cell C20 in the Final repot worksheet located in the Annual Statement.xlsx workbook?

'[Annual Statement.xlsx]Final Report'!C20

How many result cells can you display with a one-variable data table? How many result cells can you display with a two-variable data table?

one variable: as many as needed two variable: only one

What is a one-variable data table? What is a two-variable data table?

one variable: contains one input cell and any number of result cells two variable: two input cells and one result cell

What does Excel do when a hyperlink is clicked by the user?

opens the source using the application associated with the link

If you want to sort employees by the value of the Hire date field within each value if the Dept field, which field is the primary sort field? Secondary?

primary: dept field secondary: hire date

What is an add-in?

program that Addis commands and features to Microsoft applications

How do you ungroup a worksheet group that consists of all the sheets in the workbook?

select any sheet tab outside of selected range or double click on a selected sheet tab

What is an iterative process?

solver starts with an initial solution and uses that as a basis to calculate a new solution

Before you can add subtotals to a data range, what must you first do with the data?

sort the data

What happens to worksheet rows that do not match the filter criteria? What happens to the data they contain?

the data/row is hidden

What happens to banded rows in an Excel table when you insert or delete a row?

they automatically update to make sure that the table is easy to read

What are the freeze pane options?

- freeze panes to top and left of cell - freeze top row of worksheet - freeze first column of a worksheet

What are two reasons for using filter buttons rather than slicers to filter data?

- you need to use criteria filters involving text, date, or numeric values

What is a data table? What is an input cell? What is a result cell?

-data table: excel table that displays the results from sever what if analyses -Input cell: constants to be changed in a what-if analysis -result cells: calculated values that are impacted by changing the input values

Describe the difference between a variable expense and a field expense.

-variable expenses change in proportion to production value -fixed expenses must be paid regardless of sales volume

what are two reasons for using slices rather than filter buttons to filter data?

1. when data has few distinct values that can be easily listed within the slicer 2. you want to perform the data from a table on another worksheet

If you split the worksheet into panes at cell E3, how many panes are created?

2?

what is the formula to calculate the average of the Income field from the Employees table?

=AVERAGE(Employees[Income])

Write a formula that uses the MAX function to calculate the the maximum value of cell C20 of the Monday through Friday worksheet group.

=MAX(Monday:Friday!C20)

Write a formula to calculate the average of the filtered claims from the Sales Price field in the Sales_Result table.

=SUBTOTAL(AVERAGE,Sales_Result,[Sales Price])

write a formula to calculate the sum of the filtered values form the Sales Price field in the Sales_Result table.

=SUBTOTAL(SUM,Sales_Result,[Sales Price])

Write a formula to retrieve the value from the third field in the Sales_Result table that exactly matches the lookup value in cell B10.

=VLOOKUP(B10,Sales_Resut,3,False)

What is displayed in the title box for a workbook created from the EmployeeList.xltx template file?

Book#

Can slicers be used with both data ranges in Excel tables?

CNA ONLY BE USED WITH EXCEL TABLES

By default, where does Excel store workbook templates?

Custom Office Templates folder

what is the reference to the Income field from the Employees table?

Employees[Income]

What are the two types of scenario reports?

Excel table or PivotTable

What is the expression to reference the local scope defined name TotalSales form the Final Report worksheet?

Final Report!TotalSales

What is the absolute 3-D cell reference to cell C20 in Monday worksheet?

Monday!$C$20

What is the 3-D referent to cell C20 in the Monday worksheet?

Monday!C20

What is the 3-C reference to cell C20 in the Monday through Friday worksheet group?

Monday:Friday!C20

What is the structural reference to the table header row?

[#HEADER]

Why is Report-Date not a valid named range?

because there is a dash used

Why is it not a good idea for a company to treat employee record with duplicate first and last names as duplicate records?

because they could have the same and first last name and end up deleting an employee from their records

What is the difference between a binding constraint and a nonbinding constraint?

binding: must be included as in solver model because it is a limiting factor in arriving at the solution nonbonding: constraint that did not need to be included as part of the solver model

What is an objective cell? What is a variable cell?

objective cell: result cell that is maximized, minimized or set to a specific value variable cell: input cell that changes so that the objective cel can meet its defined goal

When an ascending sort order is used, how is the date-time field sorted?

oldest to most recent

What are the result cells in a scenario?

cells whose values are displayed in a scenario summary report or a scenario PivotTable report

When multiple filter buttons are used with a data range, how are criteria in different fields combines?

combined using the AND logical operator, but when it is an advanced filter, it is combined using the OR logical operator

Cell E5 contains the formula =B10. You want to display "Profits" instead of the formula's value, What custom format would you use?

custom cell format "text"

What should you do before creating a scenario report to make the entries on the report easier to interpret?

define the name of input values and scenario names

In the Solver report, what is meant by the term "slack"?

difference between the value in the cell and the value at the limit of the constraint, showing how close the cell came to being a binding constraint (binding constraint always shows a slack of 0)

What is an array formula?

excel formula that performs multiple calculations within a single step

What is a field? What is a record?

field: information stored in each column describing a characteristic of a person, place, or thing record: each row stores a record containing a group of related field values

How do you create a worksheet group consisting of sheets that are not adjacent in a workbook?

hold down CTRL button and select the sheet tabs

When would you create a template rather than just providing a coworker with the copy of your workbook?

in order to ensure accuracy and efficiency because they can just enter the data and it will be the same as everyone elses'

How do you check the status of a link within the current workbook to determine whether the link's source file is accessible and up-to-date?

in the Edit Links dialog box. "OK" means the link is updated

What are changing cells in a scenario?

input cells

What happens when you select a defined name in the Name box?

it takes you to the range of data that the defined name is applied to

What is the difference between a defined name with a local scope and one with local scope?

local scope: only recognized in the worksheet it is named in global scope: recognized throughout the whole workbook

What are three options for the objective cell using Solver?

maximize, minimize, or set to a specific value

When highlighting duplicate values with a conditional format, do the duplicate values have to be adjacent to each other?

no

Can a workbook have only one window?

no, it can have multiple

How does Excel indicate that a cell contains linked text?

underlined green font

When does a break-even point occur?

when the total revenue equals the total expenses

When would you use the filter button in a Scenario PivotTable report?

when there are multiple people working on the same PivotTable report

When should you paste a copied cell using the Past Link option?

when you are copying a range from another workbook and including it in an external reference

When would you use an advanced filter in place of the filter buttons?

when you need to use the AND logical operator and the OR logical operator

can slicers be moved to any worksheet or external workbook?

yes

What is one advantage of scenarios over data tables?

you can use more than two input cells

What are the six types of constraints you can put in a cell in a Solver model?

≥, ≤, =, int, bin (0 or 1), and dif