You want to apply a subtotal to a dataset containing names, departments, and salaries. What is the first step you need to do?

(a) Filter the dataset by salaries

(b) Select the function to be used for the subtotal

(c) Insert new rows where you want the subtotal rows within the dataset

(d) Sort the dataset by a field containing categories

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.

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) Values area

(d) Rows area

What is the default summary statistic for any value field added to a PivotTable?

(a) Average

(b) Count

(c) Min

(d) Sum

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) You must create a new PivotTable if you want updated results in a PivotTable.

(d) The PivotTable is deleted from the workbook because it is not up to date.

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) Banded Rows and Banded Columns check boxes

(b) Banded Columns check box and a different PivotTable style

(c) Banded Rows check box and a different PivotTable style

(d) A different PivotTable style only

Why would you create a slicer for a PivotTable?

(a) To filter data in a PivotTable

(b) To sort data for a field in a PivotTable

(c) To slice the data into a timeline

(d) To create a color-coded slice for each category

Which of the following is not used when creating a calculated field in a PivotTable?

(a) Operands, such as multiplication (*)

(b) Name of a field containing values

(c) Another value to be used in the calculation

(d) A cell reference to a value outside of the PivotTable

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) Make the employees data points and axis titles invisible.

(b) You cannot delete the rep from the chart without first deleting the data.

(c) Filter the Sales Rep field in the PivotChart and deselect the employees check box.

(d) Hide that reps row(s) in the underlying list, which automatically removes that rep from the 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 PivotTable using one table and then add the second table to the PivotTable Fields task pane.

(c) Merge the two tables into one large dataset before creating the PivotTable.

(d) Create a subtotal for each table, copy and paste the two subtotals together, and then create the PivotTable.

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 task?

(a) Set a print area.

(b) Print titles.

(c) Adjust page breaks.

(d) Change the print page order.

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) Freeze Panes command

(b) Hide Rows command

(c) New Window command and cascade the windows

(d) Split Rows command

Which statement is not a recommended guideline for designing and creating an Excel table?

(a) Avoid naming two fields with the same name.

(b) Ensure that no blank columns separate data columns within the table.

(c) Leave one blank row between records in the table.

(d) Include field names on the first row of the table.

Which of the following characters are wildcards in Excel? (Check all that apply.)

(a) *

(a) #

(a) &

(a) $

What should you do to ensure that records in a table are unique?

(a) Do nothing; a logical reason probably exists to keep identical records.

(b) Use the Remove Duplicates command.

(c) Look at each row yourself and manually delete duplicate records.

(d) Filter the data to show only unique records.

Which Conditional Formatting rule is best suited to highlight sales value greater than $5,000?

(a) Equals

(b) Between

(c) Greater Than

(d) Less Than

Which date filter option enables you to restrict the view to only dates between April 1, 2021, and April 30, 2021?

(a) Equals

(a) Before

(a) After

(a) Between

Which of the following is a fully qualified structured reference?

(a) =[Purchase_Price]-[Down_Payment]

(b) =Sales[@Purchase_Price]-Sales[@Down_Payment]

(c) =Purchase_Price-Down_Payment

(d) =[Sales]Purchase_Price-[Sales]Down_Payment

Which of the following is not an aggregate function that can be applied in a total row?

(a) MAX

(b) AVERAGE

(c) COUNT

(d) VLOOKUP

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) Format only cells that contain.

(c) Use a formula to determine which cells to format.

(d) Format only values that are above or below average

The date 8/3/2021 is stored in cell C1. What function is used to extract just 3?

(a) =YEAR(C1)

(b) =MONTH(C1)

(c) =DAYS(B1,C1)

(d) =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) =DAYS(B2,B1)

(c) =YEARFRAC(B1,B2)

(d) =DAY(B1)

For which situation would you use the SWITCH function?

(a) You want to display a bonus if an employee earned a 5 on a performance evaluation and was hired before 1/1/2021.

(b) 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.

(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 have a list of graduating students. You want to calculate the average GPA for only students graduating with a marketing degree

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) AVERAGEIFS

(b) AVERAGEIF

(c) SUMIF

(d) MAXIFS

You want to create a spreadsheet with populations. Which of the following could not be used to produce a map chart in Excel?

(a) State names, such as Ohio, Oklahoma, Oregon

(b) City names, such as Boston, Denver, Los Angeles

(c) Postal codes, such as 73085, 73701, 74074

(d) State abbreviations, such as TX, CO, CA, MA

What function would you use to calculate the total interest paid for the first year of a mortgage?

(a) CUMIPMT

(b) PMT

(c) PPMT

(d) CUMPRINC

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

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) Beginning Balance

(b) Monthly Payment

(c) Interest Paid

(d) Principal Repayment

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) AND

(b) MAXIFS

(c) OR

(d) SWITCH

What function would you use to calculate the total number of periods in a loan or investment?

(a) NPER

(b) RATE

(c) PV

(d) FV