Add a total row to this table and display the maximum value for the KD column.
answer
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.
answer
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.
answer
On the Home tab, in the Number group, click the Accounting Number Format button.
question
Change the Office background for the application to Calligraphy.
answer
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.
answer
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.
answer
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
answer
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.
answer
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).
answer
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.
answer
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.
answer
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).
answer
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.
answer
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.
answer
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.
answer
On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.
question
Unhide column D.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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).
answer
Click the Insert tab. On the Tables group, click the Recommended PivotTables button. Click OK.
question
Clear the selected Sparklines from the worksheet.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
Click the Smart Tag. Click Copy Formula from Above.
question
Delete the Old data worksheet.
answer
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.
answer
On the View tab, in the Window group, click the Split button.
question
Display the data labels on this chart above the data markers.
answer
Click the Chart Elements button. Click the Data Labels arrow and select Above.
question
Display the data table, including the legend keys.
answer
Click the Chart Elements button and click the Data Table check box.
question
Change the width of column A to best fit the data.
answer
Double-click the right column boundary for column A.
question
Display the formulas in this worksheet.
answer
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.
answer
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.
answer
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
answer
Type BonusRate in the Name box to the left of the formula bar. Press Enter.
question
Hide the gridlines on this worksheet only.
answer
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.
answer
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.
answer
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.
answer
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.
answer
On the View tab, in the Window group, click the Split button.
question
Change the width of the selected columns to 10.
answer
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.
answer
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.
answer
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.
answer
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).
answer
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.
answer
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.
answer
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.
answer
Click the Chart Elements button and click the Axes check box
question
Change the height of row 3 to best fit the data.
answer
Double-click the bottom row boundary for row 4.
question
Filter the chart so the Misc data series is hidden.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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).
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
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.
answer
On the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.
question
Complete the two-variable data table in cells H12:K17. The formula has been entered for you in cell H12. The substitute values in cells H13:H17 reference the original owner withdrawal value in cell B17, and the substitute values in cells I12:K12 reference the total expenses value in cell E11.
answer
On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Data Table... Enter E11 in the Row input cell box. Enter B17 in the Column input cell box. Click OK
question
Hide the chart gridlines.
answer
Click the Chart Elements button and click the Gridlines check box.
question
Search for help topics using the keywords online pictures and display the topic for inserting clip art.
answer
Click the Microsoft Office Word Help button. Type online pictures in the Search online help box and click the Search button. Click the Insert clip art topic.
Haven't found what you need?
Search for quizzes and test answers now
Quizzes.studymoose.com uses cookies. By continuing you agree to our cookie policy
