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