ACC 353 Final Exam Review

Which of the following is not a comparison operator? a. & b. <> c. > d. <

&

Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years? a. Bar chart b. Line chart c. Pie chart d. Column chart

Pie chart

Which of the following characters are wildcards in Excel? (Check all that apply.) a. * b. $ c. # d. &

*

You want to create a single chart that shows the proportion of yearly sales for five divisions for each year for five years. Which type of chart can accommodate your needs? a. Pie chart b. Surface chart c. Clustered column chart d. 100% stacked column chart

100% stacked column chart

Which of the following is not an acceptable range name? a. Rate_2021 b. Apr c. Payment d. 2021_Rate

2021_Rate

Cell A2 contains the regular price $100. Cell B2 contains the discount rate 15%. Cell C3 contains =A2*(1-B2) to calculate the sale price of $85. Which of the following formulas produces the same result? a. =A2*(B2-1) b. =A2-(A2*B2) c. =A2*B2-1 d. =A2*1-B2

=A2-(A2*B2)

The date 8/3/2021 is stored in cell C1. What function is used to extract just 3? a. =YEAR(C1) b. =DAYS(B1,C1) c. =MONTH(C1) d. =DAY(C1)

=DAY(C1)

The date February 16, 2021 is stored in cell B1. The last day of the semester (May 7, 2021) is stored in cell B2. Which function calculates the number days until the end of the semester? a. =DAYS(B1,B2) b. =YEARFRAC(B1,B2) c. =DAY(B1) d. =DAYS(B2,B1)

=DAYS(B2,B1)

A formula containing the entry =J$7 is copied to a cell three columns to the right and four rows down. How will the entry display in its new location? a. =J$11 b. =M11 c. =M$7 d. =J$7

=M$7

Which of the following is not an appropriate use of the MAX function? a. =MAX(B3:B45) b. =MAX(A8:A15,D8:D15) c. =MAX(F1:G10) d. =MAX(D15-C15)

=MAX(D15-C15)

Which of the following functions should be used to insert the current date and time in a cell? a. =CURRENT() b. =TODAY() c. =NOW() d. =DATE

=NOW()

What function would most efficiently accomplish the same thing as =(B5+C5+D5+E5+F5)? a.=MEDIAN(B5:F5) b. =SUM(B5:F5) c. =COUNT(B5:F5) d. =AVERAGE(B5:F5)

=SUM(B5:F5)

Which function correctly adds data in cell D25 across multiple worksheets? a. =Monday:D25+Friday:D25 b. =SUM(Monday:Friday!D25) c.=COUNT(Mon:D25,Tues:D25,Wed:D25,Thurs:D25, Fri:D25) d. =SUM(Monday,Friday:D25)

=SUM(Monday:Friday!D25)

Which of the following is a fully qualified structured reference? a. =Purchase_Price-Down_Payment b. =[Purchase_Price]-[Down_Payment] c. =Sales[@Purchase_Price]-Sales[@Down_Payment] d. =[Sales]Purchase_Price-[Sales]Down_Payment

=Sales[@Purchase_Price]-Sales[@Down_Payment]

Which of the following is not used when creating a calculated field in a PivotTable? a. Another value to be used in the calculation b. Name of a field containing values c. A cell reference to a value outside of the PivotTable d. Operands, such as multiplication (*)

A cell reference to a value outside of the PivotTable

You use Trace Precedents for a cell containing a formula. What indicates an error in the precedents used? a. A warning message box displays onscreen. b. A red tracer arrow displays between the dependent and precedent cells. c. A Watch Window displays on the right side of the screen. d. A blue tracer arrow points to the cell containing the error.

A red tracer arrow displays between the dependent and precedent cells.

A workbook contains a list of houses and the months that they were sold in Florida. You are interested in determining the average price of sold houses in June in Ft. Lauderdale. What function is best suited for this calculation? a. SUMIF b. AVERAGEIF c. MAXIFS d. AVERAGEIFS

AVERAGEIFS

What number format places a dollar sign on the left side of the cell, includes commas to separate thousands, displays two decimal places, and displays zero values as hyphens? a. Monetary Number Format b. Dollars and Cents Format c. Currency Format d. Accounting Number Format

Accounting Number Format

What settings should you select to apply a different color scheme and display a fill color for every other row or horizontal lines within the PivotTable? a. A different PivotTable style only b. Banded Rows and Banded Columns check boxes c. Banded Columns check box and a different PivotTable style d. Banded Rows check box and a different PivotTable style

Banded Rows check box and a different PivotTable style

Which date filter option enables you to restrict the view to only dates between April 1, 2021, and April 30, 2021? a. Before b.Between c. After d. Equals

Between

What function would you use to calculate the total interest paid for the first year of a mortgage? a. CUMIPMT b. PPMT c. CUMPRINC d. PMT

CUMIPMT

You applied a chart style to a clustered column chart. However, you want to experiment with other settings to change the colors used in the chart without individually changing colors. Which of the following does not apply changes to the entire chart? a. Change colors in the Format Data Series task pane. b. Change the colors for the workbook theme. c. Select a color set from the Change Colors palette. d. Change the workbook theme.

Change colors in the Format Data Series task pane.

You have a large dataset that will print on several pages. You want to ensure that related records print on the same page with column and row labels visible and that confidential information is not printed. You should apply all of the following page setup options except which one to accomplish this? a. Adjust page breaks. b. Print titles. c. Change the print page order. d. Set a print area.

Change the print page order.

You created a PivotTable and changed some values in the dataset from which the PivotTable was created. How does this affect the PivotTable? a. The PivotTable is updated automatically when you make changes to the dataset. b. Changes in the dataset do not affect the PivotTable until you refresh the PivotTable. c. The PivotTable is deleted from the workbook because it is not up to date. d. You must create a new PivotTable if you want updated results in a PivotTable.

Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.

You want to create a spreadsheet with populations. Which of the following could not be used to produce a map chart in Excel? a. Postal codes, such as 73085, 73701, 74074 b. City names, such as Boston, Denver, Los Angeles c. State names, such as Ohio, Oklahoma, Oregon d. State abbreviations, such as TX, CO, CA, MA

City names, such as Boston, Denver, Los Angeles

A combo chart displays the number of new jobs on the primary value axis, percentage growth on the secondary value axis, job titles in the category axis, "New Job Growth by 2026" as the chart title, but no legend. Which is the least important title to include in the chart? a. The primary value axis title b.The secondary value axis title c. The chart title d. The category axis Job Titles

The category axis Job Titles

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? a. Click Layout 2 in the Chart Layouts group on the Design tab and apply a different chart style. b. Click Switch Row/Column in the Data group on the Design tab. c. Change the chart type to a clustered column chart. d. Click Legend in the Labels group on the Layout tab and select Show Legend at Bottom.

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

Why would Series 1 and Series 2 display in a chart legend? a. The selected headings are in non-adjacent cells to the values. b. The data are contained only in one row. c. The data are contained in one column. d. Column headings were not selected when creating a chart.

Column headings were not selected when creating a chart.

You want to create a PivotTable that uses fields from two Excel tables. What is the first step? a. Create a relationship on a common field between the two tables. b. Create a subtotal for each table, copy and paste the two subtotals together, and then create the PivotTable. c. Merge the two tables into one large dataset before creating the PivotTable. d. Create a PivotTable using one table and then add the second table to the PivotTable Fields task pane.

Create a relationship on a common field between the two tables.

If you want to show exact values for a data series in a bar chart, which chart element should you display? a. Value axis title b. Data labels c. Legend d. Chart title

Data labels

The Solver add-in displays on this tab in the ribbon. a. Home tab. b. Formulas tab. c. Analyze tab. d. Data tab.

Data tab.

You moved a chart to its own chart sheet. What chart element can you add to provide a grid that contains a copy of the data source at the bottom of the chart? a. Gridlines b. Data table c. Error bars d. Axis title

Data table

Which step is not part of planning a worksheet design? a. State the purpose of the worksheet b. Decide what input values are required c. Enter labels, values, and formulas d. Decide what outputs are required to achieve the purpose

Enter labels, values, and formulas

You have a workbook that contains sales data where each sales rep's data is stored on a separate worksheet. Which task is the least likely to be done while the worksheets are grouped? a. Fill the sales categories across the worksheets. b. Format the column and row labels at the same time. c. Enter specific values for the first sales rep. d. Format the values with an appropriate number style.

Enter specific values for the first sales rep.

You want to create a hyperlink to an online dataset from the United States Census Bureau. Which type of link do you create? a. Create New Document b. Existing File or Web Page c. Place in This Document d. E-mail Address

Existing File or Web Page

What is the keyboard shortcut to open the Paste Name dialog box? a. F3 b. F2 c. F5 d. F4

F3

What is the keyboard shortcut to create an absolute reference? a. F2 b. F3 c. Alt d. F4

F4

Which of the following is not true about the VLOOKUP function? a. The col_index_num argument cannot be 1. b. The default match type is approximate. c. The lookup table must be in descending order. d. The match type must be false when completing an exact match.

The lookup table must be in descending order.

You created a PivotChart showing sales by quarter by sales rep. How do you remove a sales rep from the chart without deleting the data? a. Filter the Sales Rep field in the PivotChart and deselect the employee's check box. b. Hide that rep's row(s) in the underlying list, which automatically removes that rep from the chart. c. Make the employee's data points and axis titles invisible. d. You cannot delete the rep from the chart without first deleting the data.

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

When you start to type =AV, what feature displays a list of functions and defined names? a. Formula AutoComplete b. Function ScreenTip c. Function Arguments dialog box d. Insert Function dialog box

Formula AutoComplete

You are working with a large worksheet. Your row headings are in column A. Which command(s) should be used to see the row headings and the distant information in columns X, Y, and Z? a. New Window command and cascade the windows b. Freeze Panes command c. Split Rows command d. Hide Rows command

Freeze Panes command

This tool calculates the value required in a single cell to produce a result within a related cell. a. Scenario Manager b. One- or two-variable data table c. Goal Seek d. Solver

Goal Seek

Which Conditional Formatting rule is best suited to highlight sales value greater than $5,000? a. Less Than b. Equals c. Between d. Greater Than

Greater Than

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

Increase the column width

Assume that the data on a worksheet consume a whole printed page and two columns on a second page. You can do all of the following except what to force the data to print all on one page? a. Decrease the font size b. Increase the left and right margins c. Decrease the Scale value d. Decrease column widths

Increase the left and right margins

Which statement is not a recommended guideline for designing and creating an Excel table? a. Ensure that no blank columns separate data columns within the table. b. Include field names on the first row of the table. c. Avoid naming two fields with the same name. d. Leave one blank row between records in the table.

Leave one blank row between records in the table.

A dataset contains a list of U.S. Representatives, the states they represent, the years they were elected, and their salaries. You want to identify the highest-paid representative who was elected before 1/1/2020. What is the best function to use? a. MAX b. IFS c. MAXIFS d. YEARFRAC

MAXIFS

If cell E15 contains the function =PMT(B$15/12,C7*12,-D8), what type of cell reference is B$15? a. Mixed reference b. Absolute reference c. Relative reference d. Syntax

Mixed reference

What function would you use to calculate the total number of periods in a loan or investment? a. NPER b. FV c. PV d. RATE

NPER

What tool is used to view existing range names? a. Name Box b. Scenario Manager c. Name Manager d. Solver

Name Manager

A local police chief wants to create a rule that a person will be ticketed if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record. Which function should be used? a. OR b. AND c. MAXIFS d. SWITCH

OR

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

Periodic interest rate

You constructed a loan amortization table with beginning balance, monthly payment, interest paid, principal repayment, and ending balance columns. If all the formulas are correct, which is the only column that shows an increase in value throughout the term of the loan? a. Monthly Payment b. Interest Paid c. Beginning Balance d. Principal Repayment

Principal Repayment

The SalesQtr2 workbook contains links to data in the April, May, and June workbooks. Which workbook is the destination file? a. May b. April c. SalesQtr2 d. June

SalesQtr2

The value axis currently shows increments such as 50,000 and 100,000. What option would you select to display the values in increments of 50 and 100? a. Show Right to Left Axis b. More Primary Vertical Axis Title Options c. Show Axis in Millions d. Show Axis in Thousands

Show Axis in Thousands

This analysis tool can handle multiple adjustable cells while minimizing, maximizing, or meeting goals. a. Goal Seek b. Scenario Manager c. Solver d. One- or two-variable data table

Solver

Which of the following is an Excel add-in? a. Scenario Manager b. Solver c. One- or two-variable data table d. Goal Seek

Solver

Which of the following tools can incorporate constraints? a. Scenario Manager b. Data Tables c. Goal Seek d. Solver

Solver

Which what-if analysis tool is the best option for complex calculations requiring constrained optimization? a. Solver b. Data Tables c. Goal Seek d. Scenario Manager

Solver

You want to apply a subtotal to a dataset containing names, departments, and salaries. What is the first step you need to do? a. Sort the dataset by a field containing categories b. Insert new rows where you want the subtotal rows within the dataset c. Select the function to be used for the subtotal d. Filter the dataset by salaries

Sort the dataset by a field containing categories

The label Souvenir Shop is in cell A1, and April Sales Report is in cell B1. You select the range A1:E1 and click Merge & Center. What is the result? a. Souvenir Shop is combined with April Sales Report. The new combined label is then centered over the range A1:E1. b. Souvenir Shop is centered over the range A1:E1, and April Sales Report is moved to cell F1. c. Souvenir Shop is centered over the range A1:E1, and April Sales Report is deleted. d. Excel does not let you merge and center a range of cells where those cells each contain data.

Souvenir Shop is centered over the range A1:E1, and April Sales Report is deleted.

Which data validation alert style should you use to prevent the user from entering invalid data? a. Information b. Stop c. Failure d. Warning

Stop

What is the default summary statistic for any value field added to a PivotTable? a. Sum b. Count c. Average d. Min

Sum

You want to copy the March worksheet to use it to enter data for April. After you right-click the March sheet tab and select Move or Copy, you click OK in the Move or Copy dialog box without changing any settings. What happens? a. A copy of the March sheet is inserted to the left of the original March sheet. The new sheet is named March (1). b. A copy of the March sheet is inserted to the right of the original March sheet. The new sheet is named April automatically. c. The March sheet is moved to a new workbook named Book1. The March sheet no longer exists in the original workbook. d. The March sheet is moved to the left of the first sheet tab. The worksheet is not copied.

The March sheet is moved to the left of the first sheet tab. The worksheet is not copied.

Why would you create a slicer for a PivotTable? a. To slice the data into a timeline b. To create a color-coded slice for each category c. To sort data for a field in a PivotTable d. To filter data in a PivotTable

To filter data in a PivotTable

Which tool is most effective when comparing the impact of various combinations of production expenses and new customer acquisition on net sales profit? a. Goal Seek b. Two-variable data table c. Scenario Manager d. Solver

Two-variable data table

Cell A5 is the active cell. You want to make cell B10 the active cell. Which of the following is not a method for going to cell B 10? a. Use the Go To dialog box b. Type B10 in the Name Box and press Enter. c. Type B 10 in the Formula Bar and press Enter. d. Use the scroll arrows on the keyboard

Type B 10 in the Formula Bar and press Enter

You want to make sure employees enter data only in particular cells and not change or delete other data. What is the first step? a. Lock cells that you do not want the users to change. b. Use the Mark as Final option. c. Protect the worksheet with a password. d. Unlock cells that will be used for data entry.

Unlock cells that will be used for data entry.

If you would like to set a conditional formatting rule based on the function =AND(G6="Finance", H7<7000), which formatting rule type is needed? a. Format all cells based on their values. b. Use a formula to determine which cells to format. c. Format only values that are above or below average. d. Format only cells that contain.

Use a formula to determine which cells to format.

What should you do to ensure that records in a table are unique? a. Use the Remove Duplicates command. b. Do nothing; a logical reason probably exists to keep identical records. c. Look at each row yourself and manually delete duplicate records. d. Filter the data to show only unique records

Use the Remove Duplicates command.

A worksheet contains over 400 rows. The first 10 rows contain input data, and functions containing summary statistics are in the last 10 rows. What is the most efficient method to see the input area and summary statistics at the same time without having to scroll back and forth between sections? a. Select the summary statistics and use the Freeze Panes option. b. Open multiple windows of the same workbook and use the Arrange Windows command to tile the windows. c. Display a Watch Window for the input section as you view the summary statistics section. d. Use the Split command and adjust one pane to see the input section and another pane to see the summary statistics.

Use the Split command and adjust one pane to see the input section and another pane to see the summary statistics.

Which of the following is not an aggregate function that can be applied in a total row? a. MAX b. AVERAGE c. VLOOKUP d. COUNT

VLOOKUP

You just copied a range of data containing formulas. However, you want to preserve the formula results and the original number and text formatting in the pasted range. Which paste option would you select? a. Values & Number Formatting b. Keep Source Formatting c. Values & Source Formatting d. OptionFormulas

Values & Source Formatting

After creating a blank PivotTable, you click a field containing monetary amounts in the PivotTable Fields task pane. Where does Excel place that field? a. Filters area b. Columns area c. Rows area d. Values area

Values area

You want to monitor the results of cell formulas on a different worksheet as you change data on another worksheet. You should create a: a. Circular Reference Window. b. Watch Window. c. data validation window. d. hyperlink between worksheets.

Watch Window.

You created a worksheet that tracks data for a basketball team. The range B10:F10 contains the difference in final points between your team and the other team. What is the recommended sparkline to create to represent this data? a. Line b. Column c. Win/Loss d. Plus/Minus

Win/Loss

If you forget the password you used to protect a workbook, how do you reset it? a. You can reset it in Excel Options. b. You cannot reset the password. c. You can email the workbook to Office.com for reset. d. Use the password reset option in the Properties pane for the workbook.

You cannot reset the password.

For which situation would you use the SWITCH function? a. You want to calculate a three-tier bonus based on salaries for employees: 5% bonus for employees who exceeded their goals, 3% bonus for employees who met their goals, and 0% for employees who did not meet their goals. b. You have a list of graduating students. You want to calculate the average GPA for only students graduating with a marketing degree. c. You have a spreadsheet with a list of articles and a column containing authors' initials (JD, KM, MP, RG). You want to create another column that evaluates the initials and returns their respective names. d. You want to display a bonus if an employee earned a 5 on a performance evaluation and was hired before 1/1/2021.

You have a spreadsheet with a list of articles and a column containing authors' initials (JD, KM, MP, RG). You want to create another column that evaluates the initials and returns their respective names.

Why would you click the collapse outline symbol above a column of outlined data? a. You want to see more detailed columns that relate to the formula in that column. b. You want to hide the detailed columns to focus on the result column. c. You want to insert a subtotal row for a set of rows. d. You want to delete columns containing values.

You want to hide the detailed columns to focus on the result column.

The Header & Footer Tools tab contains commands that insert codes into a header or footer. These codes enable the header or footer text to change automatically. You can insert a code for all of the following except: a. File name b. Your name c. Sheet name d. Page number

Your name