BIA 320 Exam One

which of the following is an unacceptable name for an Excel table

(Customers)

a workbook template has which of the following file extensions

.xltx

to use the SUBTOTAL function to calculate the sum of filtered table records, set the Function_Num argument to ___

9 for SUM

The value in cell B2 cell is 65. In cell C2, the value is 75. Which of the following formulas returns FALSE if any of the conditions are false for the values in cells B2 and C2? =IF(B2>=70, C2>=80) =OR(B2>=70, C2>=80) =AND(B2>=70, C2>=80) =NOT(OR(B2>=70, C2>=80))

=AND(B2>=70, C2>=80)

The scores of a student in two subjects are inserted in cells B2 and C2. The passing score for each subject is 60. Which of the following formulas returns TRUE if at least one score is greater than or equal to 60, or else it returns FALSE? =IF(B2>=60, C2>=60) =OR(B2>=60, C3>=60) =AND(B2>=60, C2>=60) =NOT(OR(B2>=60, C2>=60))

=OR(B2>=60, C3>=60)

in the Expenses workbook, Cassie defined the range D10:G10 with the name RentExpenses. Which of the following formulas can replace the formula =SUM(Expenses!D10:G10)?

=SUM(RentExpenses)

an excel table contains columns named Salary and Commission. Which of the following formulas uses a structural reference to sum the values in the Salary through Commissions field?

=SUM([Salary]:[Commission])

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

A5:F5=int

Adele wants to filter a Customers table to show customers in Denver or customers with invoices over $2500. What type of filter should she use

Advanced Filter

which of the following will you click on the Reports list box in Solver Results dialog box to produce an Answer Report

Answer

Tracy created a table where the odd and even-numbered columns are formatted differently. She wants to remove this formatting. What setting should she change

Banded columns

which of the following is the default function Excel uses to summarize non-numeric data in a PivotTable?

COUNT

Mia inserted a plain table in a worksheet. She wants to change the black borders and white fill to make the table more attractive and easier to use. What is the quickest way for her to change the appearance of the tablw

Change the table style

Leigh-Ann used a slicer to filter a table on the department field. Which of the following buttons should she clock on the slicer to redisplay data for all the departments

Clear Filter button

Stacey filtered a table on the product type field and now wants to filter on the price field instead. What should she do next?

Clear the filter from the table

Which pf these is the default layout fir a newly created PivotTable Compact form Outline form Tabular form Chart form

Compact Form

how can you drill down a PivotTable to display detailed data

Double-click a cell in the PivotTable

A one-variable data table performs several what-if analyses by specifying one changing cell and several result cells TRUE FALSE

FALSE

In a PivotTable, the Values area contains a filter button that limits the PivotTable to only those values matching a specified criteria TRUE FALSE

FALSE

The VLOOKUP function returns the position of a value found within a row or column TRUE FALSE

FALSE

The first PivotTable created in a workbook is named PivotTableNew TRUE FALSE

FALSE

To return a true value if any one of multiple conditions is true, use the IF function TRUE FALSE

FALSE

To return a true value if any one of multiple conditions is true, use the IF function TRUE FALSE

FALSE

When you add a field to the Filters area of a PivotTable, Excel displays a slicer near the top of the worksheet TRUE FALSE

FALSE

every PivotTable includes a Totals area as one of its four primary areas TRUE FALSE

FALSE

if you want to edit the changing values for a scenario, apply the scenario to the worksheet, and then edit the data directly TRUE FALSE

FALSE

the scenario overview report lists the values for the changing and result cells within each scenario TRUE FALSE

FALSE

to combine the results of four separate scenarios, you create an Answer report TRUE FALSE

FALSE

when you create a scenarios, you identify the input cells, the cells whose values will change in each scenario TRUE FALSE

FALSE

when you create scenarios, you identify the input cells, the cells whose values will change in each scenario TRUE FALSE

FALSE

you should not move a PivotChart because it must be on the same worksheet as its PivotTable TRUE FALSE

FALSE

you use the Values area to limit a PivotChart to values that satisfy a specified criteria TRUE FALSE

FALSE

Gwen wants to locate cells that contain the date 11/14/21. Which of the following tools should she use

Find & Select

if a worksheet arranges lookup values in rows rather than columns, which function is best for retrieving data from the lookup table? VLOOKUP HLOOKUP INDEX MATCH

HLOOKUP

Helen wants to resize slicer buttons to exact dimensions. What can she use to do so

Height and Width boxes in the Buttons group

The ___ function returns the value from a range of data at the intersection of a specified row and column INDEX MATCH LOOKUP ARRAY

INDEX

which of the following buttons on the Home tab can you use to insert a row in a table

Insert

which of the following is a simpler way to write the following formula: June!C5+July!C5+Aug!C5

June:Aug!C5

Maya created a table with the totals in the last column on the right. Which of the following table style options should she apply to call attention to the totals

Last Column

Which of the following functions would you use to find the column location of specified text? MATCH INDEX VLOOKUP HLOOKUP

MATCH

which of the following functions do you often use with the INDEX function to fund data from a specified row and column of data MATCH OR HLOOKUP RANGE

MATCH

Evelyn wants to work on different parts of a workbook at the same time by displaying worksheets in sperate windows. Which option on the View tab should she select

New Window

which of the following do you need to provide when creating a Solver Model? Select all the options that apply Report range Objective Cell One or more variable cells Type of what-if analysis to perform

Objective Cell One or more variable cells Type of what-if analysis to perform

which of the following is the default name assigned to the first PivotTable in a workbook?

PivotTable1

what can you do to store the parameters of the current Solver model in a worksheet so you do not lose the model details when you create a new model?

Save the Solver model in the worksheet

what can you do yo store the parameters of the current Solver model in a worksheet so you do not lose the model details when you create a new model?

Save the Solver model in the worksheet

Michelle finished a 5 kilometer run in 180th position. The organizers shared an Excel spreadsheet with the names of all the participants and the time they took to complete the race. The top 15 finishes are listed in rows 2 to 16. Michelle wants to compare her time against theirs. How can she do so

Select row 17 and click View tab > Windows group > Split

Darryl has a worksheet that displays a product's net profit in cell C20. He is using Solver to determine how he can achieve the most profit by changing the values in other cells. Where should he enter a reference to cell C20 in the Solver Parameters dialog box

Set Objectives box

Will has a table that includes a field with four product types. What kind of filter would be best to apply to the table

Slicer

which of the following Excel features finds a numeri solution to a problem involving several input values?

Solver

Lucy is creating a solver model to determine the lowest total expenses she would incur producing 10,000 helmets. Where should she specify the limit of 10,000 helmets in the solver parameters dialog box

Subject to the Constraints Box

Add a numeric field to the Values area to sum values for each intersection of a row category and column category TRUE FALSE

TRUE

IF a PivotTable field is expanded, you can click the minus button to collapse the field and hide field details TRUE FALSE

TRUE

If you are working with scenarios on one worksheet, you can merge scenarios from other worksheets or workbooks TRUE FALSE

TRUE

If you create a PivotTable and then add data to its data source, you must refresh the PivotTable to update the data TRUE FALSE

TRUE

The PivotTable Fields pane lists the fields available to the Pivot Table TRUE FALSE

TRUE

To create a PivotTable layout, you can drag fields from the list to an area box in the PivotTable Fields pane TRUE FALSE

TRUE

To hide the PivotTable Fields pane, deselect the Field List button on the PivotTable Tools Analyze tab TRUE FALSE

TRUE

To remove a field from a PivotTable, clear its check box in the field list TRUE FALSE

TRUE

Xan wants to change the number format in a PivotTable and its PivotChart. To do so, she can use the Number Format button in the Value Field Settings dialog box TRUE FALSE

TRUE

You can hide the PivotTable Field pane by deselecting the Field List button on the PivotTable Tools Analyze tab TRUE FALSE

TRUE

a scenario is a defined collection of changing cells used to perform a what-if analysis TRUE FALSE

TRUE

a two-variable data table lets you view the relationship between tow input cells and one result cell TRUE FALSE

TRUE

because Solver is a(n) add-in, you must activate it before you use it TRUE FALSE

TRUE

if you add two fields to the Rows area of a PivotTable, you can click the plus box to expand the fields and display their values TRUE FALSE

TRUE

if you create a PivotTable and then add data to its data source, you must refresh the PivotTable to update the data TRUE FALSE

TRUE

in the scenario manager dialog box, the changing values are the cells that change in the worksheet to show the effect of the scenario TRUE FALSE

TRUE

one can create a one-variable data table in excel to test a series of values for a single input cell and see the influence of these values on the result of a related formula TRUE FALSE

TRUE

to delete a scenario, open the Scenario manager dialog box, select the scenario, and then click the delete button TRUE FALSE

TRUE

to remove a field from a PivotTable, clear its check box in the field list TRUE FALSE

TRUE

when you add fields to the Columns area of a PivotTable, they appear as headings at the top of the table TRUE FALSE

TRUE

while creating a two-variable data table in Excel, you need to enter two ranges of possible input values, one in a row and another in a column TRUE FALSE

TRUE

a(n) ___ is a range of data Excel treats as a single object that can be managed independently from other data in the workbook

Table

which of the following boxes will you check after clicking on a table and then selecting Table Tools > Design, to add a total row to a table

Total Row

Shelley creates a table containing the marks of Language Arts students in her class with these columns: Names of Students and Marks. She now wants to see the names of students who scored exactly 60 marks. What will Shelley do after selecting the column headers arrow for the column with heading Marks

Uncheck (Select All), select the box beside the number 60 and click ok

Roberta added a calculated field to the Values area of a PivotTable, where it appears as "Sum of ORDERS." Where can she change the text displayed for this feild?

Value Field Settings dialog box

Kevin formatted the values in a PivotTable by selecting the values and then clicking the Accounting Number Format button on the Home tab. When he changed the layout of the PivotTable, he lost the number formatting. Which dialog box should he use instead

Value Filed Settings dialog box

To which of the following locations can you move a PivotChart

Worksheet in the current workbook

when you save a workbook as a template, which of the following file name extensions does Excel apply

XLTX

which of the following is an acceptable name for a range in Excel

_TotalExpenses

if you click Existing File or Web Page in the Insert Hyperlink dialog box, select a workbook, click the Bookmark button, enter a cell reference, and then clock OK, what are you linking to?

a cell in another workbook

when you insert a defined name into a formula, Excel treats the defined name as a(n) ____

absolute cell reference

Calista wants to provide additional information about a hyperlink she is creating. Which of the following can she do

add a ScreenTip

What must you do to have a filter button appear in the upper-left corner of a PivotTable?

add a field to the filters area

Antonio wants to calculate the sum of values in the Order Amt column in a table. What should she do

add a total row and then choose Sum in the Order Amt column

a(n) ___ is a program that adds commands and features to applications such as Microsoft Excel

add-in

When you freeze the top row of a worksheet, what part of the worksheet can you scroll

all rows below the top row

Edwin wants to insert a PivotChart to summarize sales data. On which of the following does he need to base the new PivotChart

an existing PivotTable

to use defined names in existing formulas, click the Define Name arrow, and then click ___

apply names

Josh wants to insert a column in an excel table to add the values from two other columns. Which of the following methods can he use to insert a table column?

click a column header and then click the Insert button on the home tab

after beginning a formula, what can you do instead of typing the syntax of a 3-D reference

click a sheet tab, click a cell range, and then press ENTER

How can you remove the split bars from a worksheet

click on the View tab, and then click the Split button in the Windows group

how can you hide the filter buttons in a table

click the Filter Button check box in the Table Styles Options group

how can you hide the filter buttons on a table

click the Filter Button check box in the Table Styles Options group

to add a field to a table, what can you do

click the column header and then click the insert button

in Solver, a(n) ___ is a condition that limits the solution to a set of possible values

constraint

Ricki has an Analysis workbook containing many worksheets of sales data that she wants to use in a new workbook. What is the easiest way for her to use the sales data in the new workbook

copy the worksheets from the Analysis workbook to the new workbook

you can edit several worksheets at the same time by outlining the worksheets true or false

false

when you select a cell in a worksheet and then click the Split button on the View tab, how many panes appear in the worksheet window

four

Joe wants to format several worksheets at the same time. What is the easiest way for him to perform this task

group the worksheets

a ___ is a text or an image you can click to open a webpage or a file

hyperlink

where does Excel list the fields available for a PivotTable

in the PivotTable Fields pane

which type of constraint should you use to limit the answer to a while number, such as for employees needed

int

which type of constraint should you use to limit the answer to a whole number, such as for employees needed?

int

What does the Highlight Duplicate Value conditional formatting rule do when you apply it to a range

it highlights duplicate values in the range

Kylie created a table with a Skill Number column that contains values from 400 to 500. What type of filter can she use to select records with skill numbers less than 450

number filter

Jin wants to insert a recommended PivotTable. After clicking a cell in a data range, what should he select?

recommended PivotTables button on the Insert tab

how do you select a cell containing a hyperlink without activating the link

right-click the cell

which of the following techniques can you use to remove hyperlink from a cell

right-click the hyperlink and then click Remove Hyperlink on the shortcut menu

you can use the Name Manager dialog box to view and manage named ranges, including the ___, which indicates where the named range is recognized

scope

when you arrange windows in a Vertical or Side by Side layout, you can use synchronized ___ to more easily compare the two worksheets

scrolling

if you want to sort data first by department, and then by last name, the last name field is the ___ sort field

secondary

before Barry inserts new formulas in the range A5:F5, he wants to clear the cell contents. How can he do so?

select the range and then press DELETE

Dmitri created a table of employee data and wants to list the records in order by most recent hire date. Which of the following should he do

sort the records on descending order by hire date

in the formula =[SalesPrice]*.05, what do you call [SalesPrice]

structural refernce

to explicitly indicate that a range contains fields and records, you create a(n) ___

table

which of the following can help you quickly format a cell range with labels in the left column and top row, and totals in the bottom row

table style

to create a workbook containing text, formulas, macros, and formatting that you use repeatedly, you create a ___

template

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

the Solver button appears in the Analyze group on the Data Tab

In the formula =SUBTOTAL(1,Customers_Table(ID]), what does the 1 refer to

the number of the function to use

Gabriel wants to see how changes to the sales price and number of units sold would affect his company's profit. Which of the following should he create?

two-variable data table

what happens when you click a link to a word document while working in Excel

word starts and opens the linked document

what happens when you click the sheet tab of a worksheet not included in a worksheet group

you ungroup the worksheets