Excel Unit

Given the formula =B1*B2+B3/B4^2 where B1 contains 3, B2 contains 4, B3 contains 32, and B4 contains 4, what is the result? (1 point) 76 14 9216 121

14

Which of the following is not an appropriate use of the SUM function? (1 point) =SUM(F1:G10) =SUM(B3:B45) =SUM(D15-C15) =SUM(A8:A15,D8:D15)

=SUM(D15-C15)

Which of the following is a nonadjacent range? (1 point) A1:A10, D1:D10 A1:Z99 L15:L65 C15:D30

A1:A10, D1:D10

To create a calculated field select ________ located on the PivotTable Tools Analyze tab. (1 point) PivotTable Calculation Calculations Tools Insert Calculated Field dialog launcher

Calculations

You created a PivotTable and made some changes to values in the original dataset from which the PivotTable was created. How does this affect the PivotTable? (1 point) Click Refresh in the Data group on the Options tab to update the PivotTable. The PivotTable updates automatically when you make changes to the dataset. You must create a new PivotTable if you want updated results in a PivotTable. Click the Data tab, and then click Update to update the PivotTable to reflect changes you made in the dataset.

Click Refresh in the Data group on the Options tab to update the PivotTable.

Currently, a column chart shows values on the value axis, years on the category axis, and state names in the legend. What should you do if you want to organize data with the states on the category axis and the years shown in the legend? (1 point) Change the chart type to a clustered column chart. Click Legend in the Labels group on the Layout tab, and then select Show Legend at Bottom. Click Layout 2 in the Chart Layouts group on the Design tab, and then apply a different chart style. Click Switch Row/Column in the Data group on the design tab.

Click Switch Row/Column in the Data group on the design tab.

Which of the following will NOT remove a field name from a PivotTable? (1 point) Deselect the check box next to the field name in the Choose fields to add to report section. Click the field name in the Drag fields between areas below section and then select Remove Field. Click a field name in the Drag fields between areas below section and then drag it outside the PivotTable Field List Click a field name in the Drag fields between areas below section and then press the Delete key.

Click a field name in the Drag fields between areas below section and then press the Delete key.

You want to create a single chart that shows each of five divisions' proportion of yearly sales for each year for five years. Which type of chart can accomodate your needs? (1 point) 100% stacked column chart Surface chart Clustered bar chart Pie chart

Clustered bar chart

When using the =DSUM( ) database function, which argument will determine which records are included in the calculation? (1 point) Table Criteria Columns Column labels

Criteria

The Subtotal command is located on the ________ tab. (1 point) Page Layout Data Formula Insert

Data

Which tab contains commands to apply a predefined chart layout that controls what elements are included, where, and their color scheme? (1 point) Format Design Layout Page Layout

Design

To create a macro button, click the ________ tab, click Insert, then click Button in the Form Controls section of the Insert palette. (1 point) Developer View Data Insert

Developer

Currently, the House Types field is in the Row Labels area, the Real Estate Agent field is in the Column Labels area, and Sum of List Prices is in the Values area. How can you modify the PivotTable to display the agent names as subcategories within the house types in the first column? (1 point) Drag the House Types field from the Row Labels area to the Report Filter area, and then drag the Real Estate Agent field from the Column Labels area to the Row Labels area. Drag the Real Estate Agent field from the Column Labels area, and then drop it below the House Types field in the Row Labels area. Drag the House Types field from the Row Labels area, and then drop it below the Real Estate Agent field in the Column Labels area. Drag the Real Estate Agent field from the Column Labels area, and then drop it above the House Types field in the Row Labels area.

Drag the Real Estate Agent field from the Column Labels area, and then drop it below the House Types field in the Row Labels area.

You have created a PivotChart showing sales by quarter by sales rep. Before presenting it to management, you notice the name of a rep who has since been fired. How do you remove this rep from the chart without deleting the data? (1 point) You can't delete the rep from the chart without first deleting the data. Make the employee's data points and axis titles invisible. Hide that rep's row(s) in the underlying list, which automatically removes that rep from the chart. Filter the Sales Rep field in the PivotChart and deselect the employee's check box.

Filter the Sales Rep field in the PivotChart and deselect the employee's check box.

What should you do if you see a column of pound signs (###) instead of values or results of formulas? (1 point) Increase the zoom percentage. Delete the column. Increase the column width. Adjust the row height.

Increase the column width.

If you want to balance a title over several columns, what do you do? (1 point) Enter the data in the cell that is about midway across the spreadsheet. Click Center to center the title horizontally over several columns. Merge and center the data over all columns. Use the Increase Indent command until the title looks balanced.

Merge and center the data over all columns.

The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C7? (1 point) APR Loan amount Periodic interest rate Number of payment periods

Number of payment periods

What is the typical sequence for creating a chart? (1 point) Select the data source, size the chart, select the chart type, and then position the chart. Select the data source, select the chart type, and then size and position the chart. Click the cell to contain the chart, select the chart type, and then select the data source. Select the chart type, select the data source, and then size and position the chart.

Select the data source, select the chart type, and then size and position the chart.

Which is the best tool for managing multiple filters within a PivotTable? (1 point) Refreshing the data Slicers PivotChart Sorting

Slicers

You created a PivotTable to summarize salaries by department. What is the default summary statistic for the salaries in the PivotTable? (1 point) Sum Average Count Max

Sum

To add a PivotTable, choose Insert PivotTable command from the ________ group. (1 point) Chart Layout Format Tables

Tables

What does a sheet tab display? (1 point) The name of a worksheet within a workbook The worksheet and workbook name All formulas included on that worksheet The address of the current cell

The name of a worksheet within a workbook

A workbook is defined as: (1 point) an unchangeable picture of data. similar to a spreadsheet but hard copy rather than electronic. a file containing related worksheets. the address of the current cell.

a file containing related worksheets.

The strength of a PivotTable lies in its: (1 point) ease of use. complexity. one point access. ability to easily and quickly rearrange data.

ability to easily and quickly rearrange data.

By default, a macro records cells as ________ references if they are selected while recording the macro. (1 point) absolute mixed relative flexible

absolute

Database functions allow you to perform ________ on the records in the database. (1 point) sorts grouping calculations conditional formatting

calculations

A multiple data series chart (1 point) groups or clusters similar data in columns to compare values across categories. compares two or more sets of data in one chart. places stacks of data in segments on top of each other in one column, with each category in the data series represented by a different color. is a key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chart.

compares two or more sets of data in one chart.

A mixed cell reference: (1 point) occurs when a formula directly or indirectly refers to the cell containing the formula. contains both an absolute and a relative cell reference. causes a potential error. contains absolute or relative cell references, but not both.

contains both an absolute and a relative cell reference.

Using the fill handle on a cell containing a formula (1 point) changes the background color of the selected cells to yellow. copies the formula in the active cell to other cells and adapts it based upon the type of cell references in the original formula. cannot complete a sequence of dates in a column. has two or more sub-commands related to the command.

copies the formula in the active cell to other cells and adapts it based upon the type of cell references in the original formula.

The Formula Bar: (1 point) is located at the intersection of a column and a row. identifies the address of the current cell in a worksheet displays the name of a worksheet within a workbook. displays the formula or contents of an active cell.

displays the formula or contents of an active cell.

The IF function: (1 point) contains another function embedded inside one or more of its arguments. looks up a value and returns a related result from a lookup table. contains data for the basis of the lookup and data to be retrieved. evaluates a condition and returns one value if the condition is true, a different if false.

evaluates a condition and returns one value if the condition is true, a different if false.

A clustered column chart (1 point) is a key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chart. places (stacks) data in one column per category, with each column having the same height of 100%. places stacks of data in segments on top of each other in one column, with each category in the data series represented by a different color. groups or clusters similar data in columns to compare values across categories.

groups or clusters similar data in columns to compare values across categories.

The Name Box (as it applies to Excel 2016) (1 point) is located at the intersection of a column and a row. displays the name of a worksheet within a workbook. identifies the address of the current cell. displays the content of the active cell.

identifies the address of the current cell.

One benefit of using range names in formulas is (1 point) it contains both an absolute and a relative cell reference. it directly or indirectly refers to the cell containing the formula. it identifies the present value of the loan. if you copy the formula, you do not have to make the cell reference absolute.

if you copy the formula, you do not have to make the cell reference absolute.

A cell (as it applies to Excel 2016) (1 point) cannot be used for labels or headings. must have formulas entered into it. must have text entered into it. is the intersection of a column and a row.

is the intersection of a column and a row.

People are more cautious about ________ workbooks that contain macros because of concerns over macro viruses. (1 point) saving opening deleting moving

opening

A function is a (1 point) predefined formula that performs a calculation. set of rules that govern the structure and components for a formula. cell reference or a value. list of values and defined names as you enter a spreadsheet.

predefined formula that performs a calculation.

A pie chart (1 point) shows a relationship between two variables. shows each data point in proportion to the whole data series as a slice in a circular pie. separates one or more pie slices from the rest of the pie chart. emphasizes magnitude of changes over time by filling in the space between lines with a color.

shows each data point in proportion to the whole data series as a slice in a circular pie.

You must ________ the recording of the macro when you have completed the tasks it was designed to do. (1 point) begin copy paste stop

stop

The COUNTA function (1 point) displays the current date. tallies the number of cells in a range that are not empty. evaluates a condition and returns one value if the condition is true and a different value if the condition is false. evaluates true or false.

tallies the number of cells in a range that are not empty.

A line chart (1 point) uses a line to connect data points in order to show trends over a period of time. separates one or more pie slices from the rest of the pie chart. shows each data point in proportion to the whole data series as a slice in a circular pie. emphasizes magnitude of changes over time by filling in the space between lines with a color.

uses a line to connect data points in order to show trends over a period of time.

You should use cell references in formulas instead of constant values so (1 point) you can control the sequence in which Excel performs arithmetic operations. you can change the input values without changing the formulas. you can easily include letters, numbers, and spaces. it is easier to debug the errors.

you can change the input values without changing the formulas.