# Accounting Proficiency Exam

## Unlock all answers in this set

question
Add a total row to this table and display the maximum value for the KD column.
On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. Click in the KD column in the Total row, and select Max.
question
Edit the formula in cell B9 so the reference to cell B8 will remain constant if the cell is copied.
Double-click cell B9 to edit the formula. Change the formula to be =E2*\$B\$8. Press Enter.
question
Apply the Accounting Number Format to the selected cells.
On the Home tab, in the Number group, click the Accounting Number Format button.
question
Change the Office background for the application to Calligraphy.
Click the File tab. Click Account. Click the Office Background drop-down list. Click Calligraphy.
question
Use Goal Seek to find the value for cell B3 that will result in a value of \$400 for cell B7. Accept the solution.
On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Goal Seek... Enter 400 in the To value box. Enter B3 in the By changing cell box. Click OK. Click OK.
question
Enter a formula in cell B3 using the VLOOKUP function to find the total sales for the date in cell B2. Use the name DailySales for the lookup table. The total sales are located in column 4 of the lookup table. Be sure to require an exact match.
On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select VLOOKUP. Type B2 in the Lookup_value argument box. Type DailySales in the Table_array argument box. Type 4 in the Col_num argument box. Type False in the Range_lookup box. Click OK.
question
This workbook has two named ranges with the same name: BonusRate Rename the one that is limited in scope to the Bonus worksheet to: BonusRange
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click the first BonusRate name in the list, and then click the EditÂ¿ button. Type BonusRange in the Name box. Click OK. Click Close.
question
Delete this row.
On the Home tab, in the Cells group, click the Delete button arrow and select Delete Sheet Rows.
question
In cell H12, enter a formula using a counting function to count numbers in the Value Received column (cells H2:H11).
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNT from the list. Click OK.
question
Filter the City column so only rows with Arnold are shown.
On the Home tab, in the Editing group, click the Sort & Filter button and select Filter. Click the arrow at the top of the City column. Click the (Select All) check box to remove all the checkmarks. Click the check box in front of Arnold. Click OK.
question
Apply the Comma Style number format to the selected cells.
On the Home tab, in the Number group, click the Comma Style button.
question
In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11).
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.
question
Use the Create from Selection command to create named ranges for the data table B2:E5 using the labels in row 1 as the basis for the names.
On the Formulas tab, in the Defined Names group, click the Create from Selection button. The Top row check box is checked by default. Click OK.
question
Change the chart type to the first line option.
On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. Click Line category at the left side of the Change Chart Type dialog. Click OK.
question
Change the scaling option so all columns will print on one page.
On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.
question
Unhide column D.
Click the column selector for column C. Hold down the Shift key and click the column selector for column E. On the Home tab, in the Cells group, click the Format button. Point to Hide & Unhide, and click Unhide Columns.
question
Set row 1 to print on every page.
On the Page Layout tab, in the Page Setup group, click the Print Titles button. In the Page Setup dialog, on the Sheet tab, click in the Rows to repeat at top box. Click the row selector for row 1. Click OK in the dialog.
question
Insert a PivotChart using the first pie chart type.
On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button. Click Pie in the list of chart types at the left side of the Insert Chart dialog. Click OK.
question
Modify the status bar so it displays the maximum of the selected cells.
Right-click the status bar. Click Maximum to add a checkmark for that option.
question
Apply the Percent Style number format to the selected cell.
On the Home tab, in the Number group, click the Percent Style button.
question
In cell E3, enter a formula using a 3-D reference to calculate the sum of cell E3 from sheets Jan through March.
In cell E3, type =SUM( and then click the Jan sheet tab. Press the Shift key and click the March sheet tab. Click cell E3, and then type the end parenthesis ). Press Enter.
question
Edit the Bonuses named range so it refers to cells B9:B12 on the Bonus worksheet. Close the Name Manager when you are finished.
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click in the Refers to box and edit the cell range reference to =Bonus!\$B\$9:\$B\$12. Click the checkmark to the left of the Refers to box to accept the change. Click the Close button.
question
Insert a recommended PivotTable, using the Sum of Total Spent by State option (the first option in the Recommend PivotTables list).
Click the Insert tab. On the Tables group, click the Recommended PivotTables button. Click OK.
question
Clear the selected Sparklines from the worksheet.
On the Sparkline Tools Design tab, in the Group group, click the Clear button.
question
Complete the one-variable data table in cells G12:H17 to calculate the total investor payout for varying percentage rates. The formula has been entered for you in cell H12. It references the original investor payout percentage in cell B17. The substitute values have been entered for you in cells G13:G17.
On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Data Table... Enter B17 in the Column input cell box. Click OK.
question
Change the photo for the user account to use the FayeShell picture located in the Pictures folder on your computer.
Click the File tab. Click Account. Click the Change photo link. On the live.com site, click the Change picture link under the user picture. On the Picture page, click the Browse... button. Click the picture to select it and click the Open button. Click the Save button.
question
Hide column D.
On the Home tab, in the Cells group, click the Format button. Point to Hide & Unhide, and click Hide Columns.
question
Insert the current date in the selected cell. Do not include the current time.
On the Formulas tab, in the Function Library group, click the Date & Time button. Click TODAY. Click OK.
question
Enter a formula in cell E4 to calculate the average value of cells B4:D4.
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average.
question
Enter a formula in cell B8 to return a value of 15% if the cell B6 is greater than or equal to 20,000 or 3% if it is not.
On the Formulas tab, in the Function Library group, click the Logical button. Select IF from the list. Enter B6>=20000 in the Logical_test box. Enter 15% in the Value_if_ true box, and 3% in the value_if_false box. Click OK.
question
Change the print option to print the entire workbook, and then print the workbook.
Click the File tab to open Backstage. Click Print. Click the Print Active Sheets button. Click Print Entire Workbook. Click the Print button.
question
On the Summary sheet, in cell D3, enter a formula to display the value of cell B34 from the March sheet.
Type = and then click the March sheet tab. Click cell B34. Press Enter.
question
There is an error in cell D6. Accept Excel's suggestion for fixing the error.
Click the Smart Tag. Click Copy Formula from Above.
question
Delete the Old data worksheet.
On the Home tab, in the Cells group, click the Delete button arrow, and select Delete Sheet. Click Delete.
question
This worksheet has been split into four panes. Remove the split.
On the View tab, in the Window group, click the Split button.
question
Display the data labels on this chart above the data markers.
Click the Chart Elements button. Click the Data Labels arrow and select Above.
question
Display the data table, including the legend keys.
Click the Chart Elements button and click the Data Table check box.
question
Change the width of column A to best fit the data.
Double-click the right column boundary for column A.
question
Display the formulas in this worksheet.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
question
Convert the cell range to a table using table style Table Style Light 9. The table should include headers.
On the Home tab, in the Styles group, click the Format as Table button. Click Table Style Light 9. It is the second option in the second row under the Light styles. Click OK.
question
Open the FourNewServices file located in the Head Over Heels folder.
Click the File tab and click Open. Click Computer on the left side of the Open page. Click the Head Over Heels folder. In the Open dialog, click the FourNewServices file and click the Open button.
question
Name cell B8 as follows: BonusRate
Type BonusRate in the Name box to the left of the formula bar. Press Enter.
question
Hide the gridlines on this worksheet only.
On the Page Layout tab, in the Sheet Options group, click the View check box under Gridlines (to remove the checkmark).
question
Group together worksheets Q1-Q2 and Q3-Q4 so you can make changes to both at the same time.
Hold the Shift key and click the sheet tab labeled Q3-Q4.
question
Save the file to the Head Over Heels folder on your computer with the name CustomerLetter.
Click the File tab. Click Save As. Click Computer. Click Head Over Heels under Recent Folders. Type CustomerLetter in the File name box. Click the Save button.
question
Save the file to a new folder inside the Documents folder named Customers. Do not change the name of the file.
Click the Save button on the Quick Access Toolbar. Click Computer. Click My Documents. Click the New Folder button. Type Customers and press Enter. Double-click the Customers folder. Click the Save button.
question
Split the worksheet into four panes at the selected cell.
On the View tab, in the Window group, click the Split button.
question
Change the width of the selected columns to 10.
On the Home tab, in the Cells group, click the Format button. Select Column WidthÂ¿ Type 10, and click OK.
question
Use AutoFill to copy the formula and formatting in cell B9 to cells C9:D9.
Click in cell B9. Click the Fill Handle tool and drag across to cell D9. Release the mouse button.
question
Add slicers to filter the data in this table by City and State.
On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. Click the City and State check boxes. Click OK.
question
Enter a formula in cell B3 to display the text from cell B2 in proper case with only the first letter of each word in upper case.
On the Formulas tab, in the Function Library group, click the Text button. Select PROPER from the list. Type B2 in the Text box, and click OK.
question
Enter a formula in cell B9 to calculate Ken Dishner's bonus for the first quarter. Multiply his sales total (cell E2) times the bonus rate (the cell named BonusRate).
Double-click cell B9 to enter Edit mode. Type: =E2* and then type bo to display the Formula AutoComplete list. Double click BonusRate to enter it into the formula and then press Enter.
question
From Page Break Preview view, insert a page break immediately above the selected cell.
On the Page Layout tab, in the Page Setup group, click the Breaks button. Click Insert Page Break.
question
Save the document to the Documents folder in OneDrive - Personal with the name CustomerLetter.
Click the File tab. Click Save As. Click OneDrive - Personal under Recent Folders. Double-click the Documents folder. Type CustomerLetter in the File name box and click the Save button.
question
Hide the horizontal (x) and vertical (y) axes.
Click the Chart Elements button and click the Axes check box
question
Change the height of row 3 to best fit the data.
Double-click the bottom row boundary for row 4.
question
Filter the chart so the Misc data series is hidden.
Click the Chart Filters button. Click the checkbox in front of Misc to remove the check marks. Click Apply.
question
Hide the formulas in this worksheet and display the values instead.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
question
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. The annual interest rate is stored in cell B5, the number of payments in cell B6, and the loan amount in cell B4. Omit the optional arguments. The result of the formula should be a negative number.
On the Formulas tab, in the Function Library group, click the Financial button, and click PMT. Enter B5/12 in the Rate argument box. Enter B6 in the Nper argument box. Enter B4 in the Pv argument box. Click OK.
question
Convert the table to a normal range.
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.
question
Preview how this worksheet would print with formulas showing instead of calculated values.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button. Click the File tab. Click Print
question
Add a header that displays the sheet name in the center section.
Click the Page Layout button on the status bar. Click in the center section of the header area above the worksheet grid. On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click the Sheet Name button.
question
In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11).
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTA from the list. Type C2:C11 in the Value 1 box. Click OK.
question
Sort this data by the Date of Last Visit column so the oldest dates are listed first.
On the Data tab, in the Sort & Filter group, click the Sort A to Z button.
question
Remove duplicate rows where data in all the columns are identical.
Click the Table Tools Design tab. In the Tools group, click the Remove Duplicates button. Click OK. Click OK.
question
Show the precedent arrows for the selected cell to display arrows from other cells containing the formulas or values the selected cell refers to.
On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.
question
Insert a column to the left of the selected cell.
On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns.
question
Freeze the top row of the worksheet.
On the View tab, in the Window group, click the Freeze Panes button. Click Freeze Top Row.
question
Switch the rows and columns in the chart, so the data points are grouped into data series by PO Month.