Excel Unit 3 Exam [Hands-On] (Chapter 7, 8, 9), Excel 2, ACIS PRACTICE
Add the Visits field to the PivotTable. Font Size
In the PivotTable Fields pane, click the Visits check box to add the field to the PivotTable.
This workbook includes a named range that results in a #REF! error. Delete that named range. Font Size
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click the Total_Q1 name and then click the Delete button. Click OK. Click Close.
Apply the Long Date format similar to Monday, July 1, 2016 to the selected cells. Font Size
On the Home tab, in the Number group, click the Number Format arrow. Click Long Date.
Freeze the top row of the worksheet. Font Size
On the View tab, in the Window group, click the Freeze Panes button. Click Freeze Top Row.
Remove the hyperlink from cell B11. Font Size
Right-click cell B11 and select Remove Hyperlink.
Click the Name Box. Font Size
The Name Box appears at the left side of the formula bar and displays the address of the selected cell.
Create a new workbook based on the Breakeven Analysis (orange) template.
You clicked the Create button.
Ungroup the grouped sheets. Font Size
Click any sheet tab that is not part of the group.
Move the Car Loan worksheet so it is positioned before the Personal Loan worksheet. Font Size
Click the Car Loan worksheet tab and hold down the left mouse button. Drag the mouse pointer to a point just in front of the Personal Loan worksheet tab, and release the mouse button.
Filter the chart so the Misc data series is hidden. Font Size
Click the Chart Filters button. Click the checkbox in front of Misc to remove the check mark. Click Apply.
Switch the rows and columns in the chart, so the data points are grouped into data series by PO Month. Font Size
Click the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.
Reset just the Quick Access Toolbar to its original state. Font Size
Click the Customize Quick Access Toolbar button and select More Commands¿ Click the Reset button and select Reset only Quick Access Toolbar. Click Yes.
Add the macro FormatTable to the Quick Access Toolbar. Font Size
Click the Customize Quick Access Toolbar button and select More Commands¿ The Excel Options dialog opens to the Customize the Quick Access Toolbar page. Expand the Choose commands from list and select Macros. Click FormatTable and click the Add button. Click OK. Click the Customize Quick Access Toolbar button and select More Commands¿ The Excel Options dialog opens to the Customize the Quick Access Toolbar page. Expand the Choose commands from list and select Macros. Click FormatTable and click the Add button. Click OK.
Enable the active content in this workbook for just this session. Font Size
Click the Enable Content button in the Message Bar.
Save this worksheet as a CSV, comma-delimited text file. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select CSV (Comma delimited) (*.csv). Click Save. Click OK.
Save this worksheet as a tab-delimited text file. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Text (Tab delimited) (*.txt). Click Save. Click OK.
Always enable this workbook¿s active content by making it a trusted document. You do not need to enable the active content first. Font Size
Click the File tab. Click the Enable Content button and select Enable All Content.
Mark the workbook as final so it opens as read-only to discourage editing. Font Size
Click the File tab. Click the Protect Workbook button, and select Mark as Final. Click OK. Click OK.
Use AutoFill to copy the formula and formatting in cell B9 to cells C9:D9. Font Size
Click the Fill Handle tool in the selected cell and drag across to cell D9. Release the mouse button.
Insert a 3-D Clustered Bar chart (the first chart type in the 3-D Bar section of the Insert Column or Bar Chart menu). Font Size
Click the Insert tab. In the Charts group, click the Insert Column or Bar Chart button. Click the 3-D Clustered Bar chart type.
Insert a Line with Markers chart based on the selected cells. Font Size
Click the Insert tab. In the Charts group, click the Insert Line Chart button. Select the Line with Markers chart type.
Insert a 3-D pie chart based on the selected data. Font Size
Click the Insert tab. In the Charts group, click the Insert Pie Chart button. Select the 3-D Pie chart type .
Insert a Organization Chart SmartArt diagram. It is the first option in the first row of the Hierarchy category. Enter Ken Dishner as the text for the top shape. When you are finished, Click outside the diagram. Font Size
Click the Insert tab. In the Illustrations group, click the Insert a SmartArt Graphic button. Click the Hierarchy category. Click the Organization Chart option and click OK. Click on the word [Text] in the top shape and type Ken Dishner. Click outside the diagram.
Add line Sparklines to cells E3:E12 to represent the values in B3:D12. Font Size
Click the Quick Analysis Tool button and click the Sparklines tab. Click Line.
Add slicers to filter the data in this table by City. Font Size
Click the Table Tools Design tab. In the Tools group, click the Insert Slicer button. Click the City check box. Click OK.
Select column D. Font Size
Click the column selector at the top of column D.
Hide column E (Date Expected). Font Size
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 Hide Columns.
Unhide column E. Font Size
Click the column selector for column D. Hold the Shift key and click the column selector for column F. On the Home tab, in the Cells group, click the Format button. Point to Hide & Unhide, and click Unhide Columns.
Autofit column F to best fit the data. Font Size
Double-click the right column boundary for column F.
Click any of the data markers to select the entire Items Ordered data series. Font Size
In this chart, the data markers are columns. The Items Ordered data series is identified by the green color. Click any green column to select the entire data series.
Import data from the Employees table in the Staff database into a new worksheet. Font Size
On the Data tab, click the Get External Data button to expand the Get External Data group. Click the From Access button. Select the Staff database, and then click the Open button. Click the Employees table. Click OK. Click the New worksheet radio button. Click OK.
Import data from the text file WageIncreases into the selected cell in the current worksheet. Data in the file are delimited by a tab. Do not modify any of the column data formats. Font Size
On the Data tab, click the Get External Data button to expand the Get External Data group. Click the From Text button. In the Import Text File dialog, click WageIncreases, and click the Import button. Click the My data has headers check box. Click Next. Click Next. Click Finish. Click OK.
This workbook is linked to an Access database. Refresh the worksheet data. Font Size
On the Data tab, in the Connections group, click the Refresh All button.
Use the Flash Fill command to autofill the remaining cells in this column with the pattern from cell B3. Font Size
On the Data tab, in the Data Tools group, click the Flash Fill button.
Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through. Font Size
On the Data tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. Click New Bonus in the Scenarios box. Click Show. Click Close.
Create a new scenario to reflect a change in cell B8 to a value of 0.09 Name the scenario New Bonus. Font Size
On the Data tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. Click the Add button. Type New Bonus in the Scenario name box. Click OK. Type 0.09 in the text box. Click OK.
Clear the filter from the State column. Font Size
On the Data tab, in the Sort & Filter group, click the Clear button.
Edit the code for the FormatTable macro in the Visual Basic Editor. Font Size
On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button.
Display the formulas in this worksheet. Font Size
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
Hide the formulas in this worksheet and display the values instead. Font Size
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
Preview how this worksheet would print with formulas showing instead of calculated values. Font Size
On the Formulas tab in the Formula Auditing group, click the Show Formulas button. Click the File tab. Click Print.
Use the Create from Selection command to create named ranges for the data table B8:E11 using the labels in row 1 as the basis for the names. Font Size
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.
This workbook has two named ranges with the same name: Bonus Rename the one that is limited in scope to the Bonus worksheet to: BonusRange Font Size
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click the first Bonus name in the list, and then click the Edit... button. Type BonusRange in the Name box. Click OK. Click Close.
Show the tracer arrows from cell B5 to the cell(s) that are dependent on it (cells containing formulas that reference the value or formula in cell B5). Font Size
On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.
Show the tracer arrows from the precedent cells to cell B5. Font Size
On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.
In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11). Font Size
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.
Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select VLOOKUP. Type B1 in the Lookup_value argument box. Type DailySales in the Table_array argument box. Type 5 in the Col_num argument box. Type False in the Lookup_value box. Click OK.
Enter a nested function in cell B2 using INDEX and MATCH to find the expected delivery date for the item listed in cell B1. Use the named range JunePOs to reference the cell range INDEX Array argument. The expected due date is in column 5. In the INDEX Row_num function argument, use MATCH to look up the row number for the item listed in B1. Use the named range POitems as the MATCH Lookup_array argument. Require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter JunePOs. In the Column_num box, enter 5. Click in the Row_num box, and then click the arrow in the Name box to the left of the formula bar and select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click OK.
In cell B3, enter a formula using INDEX to look up the expected delivery date for the purchase order at the row position in cell B2. Use the named range JunePOs as the Array argument. The price is found in the fifth column of the array. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter JunePOs. In the Row_num box, enter B2. In the Column_num box, enter 5. Click OK.
In cell B2, enter a formula using MATCH to look up the row position of the item listed in cell B1 in the array named POitems. Require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click OK.
In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11). Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTA from the list. Click and drag to select cells C2:C11. Click OK.
In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11). Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Click and drag to select cells G2:G11. Click OK.
Enter a formula in cell B8 to display the text from cell A8 in proper case with only the first letter of each word in upper case. Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select PROPER from the list. Type A8 in the Text box, and click OK.
Enter a formula in cell B8 to display the text from cell A8 in all upper case letters. Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select UPPER from the list. Type A8 in the Text box, and click OK.
The selected cells have been merged and centered. Unmerge them. Font Size
On the Home tab, in the Alignment group, click the Merge & Center button.
Delete this row. Font Size
On the Home tab, in the Cells group, click the Delete button arrow and select Delete Sheet Rows.
Delete the selected cells, shifting cells up. Font Size
On the Home tab, in the Cells group, click the Delete button.
Change the width of the selected columns to 15. Font Size
On the Home tab, in the Cells group, click the Format button. Select Column Width... Type 15, and click OK.
Insert a column to the left of the selected cell. Font Size
On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns.
Add a new worksheet to the left of the PO Q4 sheet Font Size
On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Sheet.
Insert cells above the selected cells, shifting cells down. Apply the insert option to use the same format as the cells below. Font Size
On the Home tab, in the Cells group, click the Insert button. Click the Insert Options button that appears at the lower right corner of the insertion. Click the Format Same As Below radio button.
Enable filtering. Font Size
On the Home tab, in the Editing group, click the Sort & Filter button and select Filter.
Apply the date number format to the selected cells to display dates in the format similar to 14-Mar. Font Size
On the Home tab, in the Number group, click the Number Format arrow and select More Number Formats... Click Date in the Category list. Click 14-Mar in the Type box. Click OK.
Insert the spa logo picture into this worksheet. It is stored on the local hard drive. Font Size
On the Insert tab, in the Illustrations group, click the Pictures button. Click the spa logo file once to select it, and then click the Insert button.
Insert a recommended PivotTable, using the Sum of Total Spent by State option. Font Size
On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Bonus by Employee option. Click OK.
From Page Break Preview view, insert a page break immediately above the selected cell. Font Size
On the Page Layout tab, in the Page Setup group, click the Breaks button. Click Insert Page Break.
Modify the Visits field to use the AVERAGE function instead of the SUM function. Font Size
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Average . Click OK.
Add a timeline to the PivotTable to filter the data by values in the Date of Last Visit field. Use the timeline to filter the PivotTable to show only dates in September.
On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Timeline button. In the Insert Timeline dialog, click the check box for the Date field. Click OK. Click the September segment on the timeline.
Clear the selected Sparklines from the worksheet. Font Size
On the Sparkline Tools Design tab, in the Group group, click the Clear button.
Use a slicer to filter the data in this table to show only rows where the State value is MD or DE. Font Size
On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. Click the State check box. Click OK. In the State slicer, click the Multi-Select button. Click the VA button to turn it off.
Copy the Car Loan worksheet to a new workbook. Font Size
Right-click the Car Loan sheet tab, and select Move or Copy... Expand the To book list and select (new book). Click the Create a copy check box. Click OK.
Click the chart legend. Font Size
The chart legend tells you which data point is represented by each color in the chart. It is located to the right of the chart.
Click the formula bar. Font Size
The formula bar is the data entry area directly below the Ribbon and above the worksheet grid.
Click the part of the workbook that may display statistical information about the selected data. Font Size
The status bar appears at the bottom of the worksheet grid and displays information about the selected data.
Click the x axis. Font Size
The x axis goes from left to right. It is the horizontal axis. Typically, but not always, categories are listed along the x axis.
On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet. Font Size
Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.
Enter a formula in the selected cell to calculate the value of cell E9 times 3. Font Size
Type =E9*3. Press Enter.
Name cell B1 as follows: BonusRate Font Size
Type BonusRate in the Name box to the left of the formula bar. Press Enter.
Type Stone Rivers in cell E2 and press Enter. Begin typing Ken Dishner in cell E3, and when Flash Fill suggests a pattern for the remaining cells, accept it. Font Size
Type Stone Rivers and press Enter. Type K and press Enter.
Create a scenario summary report. Accept the recommended results cells. Font Size
You answered the question correctly. Click "OK" to continue.
Merge the cells so the text appears centered across the merged cells. Font Size
You answered the question correctly. Click "OK" to continue.
Add a slicer to the PivotTable for the Employee field and use the slicer to show only data where the value is Sue Chur.
You clicked Sue Chur.
This workbook has track changes enabled. Accept all the changes that have not yet been reviewed.
You clicked the Accept All button in the Accept or Reject Changes dialog.
Check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes.
You clicked the OK button in the Microsoft Excel - Compatibility Checker dialog.
Mark the workbook as final.
You clicked the OK button in the Microsoft Excel - Confirm Mark As Final dialog.
Modify the workbook so all changes made since you last saved are highlighted on screen.
You clicked the OK button in the Microsoft Excel - Confirm Save dialog.
Enable sharing so more than one person at a time can work on this workbook.
You clicked the OK button in the Microsoft Excel - Confirm Share dialog.
Modify this workbook so users cannot change worksheet names.
You clicked the OK button in the Protect Structure and Windows dialog.
Modify the Bonus field to use the AVERAGE function instead of the SUM function.
You selected Average menu item from the Summarize value field by list, typed average of bonus in the Custom Name text box, then clicked the OK button in the Value Field Settings dialog.
This worksheet has validation rules applied. Find and circle cells that violate those rules.
You selected the Circle Invalid data menu item in the Excel ribbon.
Data that violate the validation rules in this worksheet have been circled. Remove the validation circles.
You selected the Clear validation Circles menu item in the Excel ribbon.
Delete the comment from the selected cell.
You selected the Delete Comment menu item.
Refresh the PivotTable data.
You selected the Refresh menu item in the Excel ribbon.
Create a new scenario to reflect a change in cell B9 to a value of 0.01 Name the scenario Low COLA.
You typed 001 in the Tax text box, then clicked the OK button in the Scenario Values dialog.
Create a scenario summary report. Accept the recommended results cells.
You typed d7 in the Cell Value text box, then clicked the OK button in the Scenario Summary dialog.
This workbook includes macros. Save it in the Data Files folder. Use the file type that allows macros.
You typed marketingbudgetxlsm in the File name: text box, selected the Excel Macro-Enabled Workbook menu item from the Save as type drop-down list, then clicked the Save button in the Save As dialog.
Save this file as a template that allows macros.
You typed marketingbudgetxltm in the File name: text box, selected the Excel Macro-Enabled Template menu item from the Save as type drop-down list, then clicked the Save button in the Save As dialog.
Save this workbook as a template.
You typed registration in the File name: text box, selected the Excel Template menu item from the Save as type drop-down list, then clicked the Save button in the Save As dialog.
Add Use the 3 letter code as an error alert to the validation rules for the selected cells. Do not include a title.
You typed use the 3 letter code in the Error Message text box, selected the List menu item from the Allow drop-down list, typed =deptcodes in the txt_ex13_dv_settings_allow_options text box, then clicked the OK button in the Data Validation dialog.
Modify the workbook to require the password xyz789 to open it.
You typed xyz789 in the Reenter password text box, then clicked the OK button in the Confirm Password dialog.
