CIS practice exam 1

Hide the New Inventory worksheet

1. At the bottom of the workbook, right-click the New Inventory worksheet tab, and select Hide -OR- 1. Click the New Inventory worksheet tab to select it. 2. On the HOME tab, in the Cells group, click the Format drop-down arrow. Select Hide & Unhide, then click Hide Sheet.

Change the worksheet tab color of Sheet1 to Blue, Accent 2, Darker 25%.

1. At the bottom of the workbook, right-click the Sheet1 tab and select Tab Color. 2. Under Theme Colors, in the sixth column fifth row, select Blue, Accent 2, Darker 25%.

Add a new worksheet named Customers to the workbook.

1. At the bottom of the workbook, to the right of the Hardware worksheet tab, click the New Sheet button (+) 2. Right-click the new worksheet tab named Sheet1, and select Rename. 3. Replace the name, Sheet1, by typing the new name, Customers. Press Enter on your keyboard to accept the change.

Simultaneously remove all duplicate records in the Wired Networks table.

1. Click any cell within the Wired Networks table. 2. On the DATA tab, in the Data Tools group, click Remove Duplicates. (Hint: Notice the entire table is automatically selected.) 3. Accept all defaults and click OK. (Hint: A pop-up window should confirm that 2 duplicate records were removed.) 4. Click OK.

Sort the data in the Wired Networks table. Sort by ProductID, smallest to largest

1. Click any cell within the Wired Networks table. 2. On the HOME tab, in the Editing group, click Sort & Filter and select Custom Sort... 3. In the Sort pop-up window, configure the fields as shown below: Column Sort by: ProductID Sort on: Values Order: smallest to largest 6. Click OK.

On the Employee Bonuses worksheet, disable the headings on the Rates table.

1. Click anywhere on the Rates table to select it. 2. On the TABLE TOOLS DESIGN tab, in the Table Styles Options group, click the Header Row box to clear it and disable the option.

Add the Subject Equipment Draft to the document properties.

1. Click the FILE tab. 2. In the bottom right corner of the Info page, click the Show All Properties link. 3. In the Subject field, type Equipment Draft. 4. Click the return arrow in the upper-left corner of the Info window to display the document.

Simultaneously replace all occurrences of the word Choco with Chocolate in the workbook.

1. On the HOME tab, in the Editing group, click Find & Select and choose Replace... 2. In the Find and Replace pop-up window, type the following in the fields: Find what: Choco Replace with: Chocolate 3. Click the Options button and set the Within: field to Workbook. 4. Click Replace All

Beginning at cell A1 on the Hardware worksheet, import the picture file NetworkTopology.png located in the GMetrixTemplates folder.

1. On the Hardware worksheet, click on cell A1. 2. On the INSERT tab, in the Illustrations group, click Pictures. Browse to the GMetrix Templates folder, select the file, NetworkTopology.png. 3. Click the Insert button.

Configure Excel to always print cell range A1:F17 on the Q1 Sales worksheet.

1. On the Q1 Sales worksheet, select cell range A1:F17. 2. Click the PAGE LAYOUT tab. 3. In the Page Setup group, click Print Area and select Set Print Area.

Join cells A1:E1 of the Tackle worksheet. Do not change the alignment of the contents.

1. Select cell range A1:E1 on the Tackle worksheet. 2. On the HOME tab, in the Alignment group, click the Merge & Center drop-down arrow and select Merge Across.

On the Carriers and Coolers worksheet, apply the 3 Flags Icon Set Conditional Formatting to the contents in the Inventory column.

1. Select cell range D4:D19 on the Carries and Coolers worksheet. 2. On the HOME tab, in the Styles group, click Conditional Formatting to open the menu. 3. Select Icon Sets and click 3 Flags.

Copy the contents of the New Inventory worksheet and put it in the table on the Boats worksheet beginning in cell A6.

1. At the bottom of the workbook, click the New Inventory worksheet tab and select cell range A2 through D9. 2. On the ribbon HOME tab, in the Clipboard group, click Copy. (Hint: You can also copy using the keyboard short-cut CTRL-C) 3. Click on the Boats worksheet tab and click the first cell in the table, cell A6. 4. On the ribbon HOME tab, in the Clipboard group, click Paste. (Hint: You can also paste using the keyboard short-cut CTRL-V)

Reorder the worksheets so Boats is first.

1. At the bottom of the workbook, right-click the Boats tab, and select Move or Copy... 2. In the Before sheet: window, select Sheet1. 3. Click the OK button. (Hint: You can also click-drag the tab to move it.)

Copy only the Carriers and Coolers worksheet into a new workbook. Save it in the GMetrixTemplates folder as Inventory Report.xlsx. Close the new workbook before proceeding.

1. At the bottom of the workbook, right-click the Carriers and Coolers tab, and select Move or Copy... 2. Click the Create a copy box to enable it. 3. In the To book: field, click the down-arrow and select (new book). 4. Click OK. The new workbook containing only the worksheet Carriers and Coolers should open. 5. Click the FILE tab and select Save As. 6. Browse to the GMetrixTemplates folder and save the workbook with the File name: Inventory Report.xlsx 7. Click Save. 8. Close the new Workbook you just saved.

In cell B28 on the Profits worksheet, insert a formula that displays the number of Sales greater than 250.

1. Click the Profits worksheet tab to select that worksheet. 2. Click cell B28 to select it. 3. On the FORMULAS tab, on the far left click Insert Function. 4. In the Insert Function pop-up window, in the Search for a function: field, type COUNTIF. Click Go. 5. In the Select a function: window, select COUNTIF and click OK. 6. In the Function Arguments pop-up window for COUNTIF, type the following in the fields: Range: D4:D26 Criteria: ">250" 7. Click OK. (Hint: The completed entry in the Formula Bar should read =COUNTIF(D4:D26,">250") )

On the Hardware worksheet, rotate the text Wired Networks and Wireless Networks to Angle Clockwise.

1. Click the cell containing the text Wired Networks (Cell C3) then hold down the CTRL button on the keyboard and select Wireless Networks (Cell C18) to simultaneously select both. 2. On the in the HOME tab, in the Alignment group, click the Orientation icon and select Angle Clockwise.

Modify the chart on the Profits worksheet so the Flavors are displayed as Horizontal Axis Labels and Expense and Income are the Legend Series.

1. If the Profits worksheet Is not already displayed, click the Profits worksheet tab to select it. 2. Click the chart to reveal the CHART TOOLS tabs. 3. On the CHART TOOLS DESIGN tab, in the Data group, click Switch Row / Column.

Modify the chart on the Profits worksheet so the Legend appears at the Top.

1. If the Profits worksheet Is not already displayed, click the Profits worksheet tab to select it. 2. Click the chart to reveal the CHART TOOLS tabs. 3. On the far left of the CHART TOOLS DESIGN tab, in the Chart Layouts group, click Add Chart Element, select Legend and click Top.

On the Carriers and Coolers worksheet, expand the chart data range to include the rest of the rows in the table

1. On the Carriers Coolers worksheet, click in the center of the chart to select it. 2. Click the Chart Tools Design contextual tab. 3. In the Data group, click Select Data. 4. In the Select Data Source pop-up window, in the Chart data range field, change the last value to $D$19. (Hint: the entire data range should be ='Carriers & Coolers'!$C$4:$D$19 ) 5. Click OK.

Use Autofill to copy the formula in cell H4 to calculate the Total Compensation for each employee in the Bonuses table.

1. On the Employee Bonuses worksheet, confirm that cell H4 is selected. 2. In the lower-right corner of cell H4, click-drag the fill handle down through the Total Compensation column, beginning with cell H4 through H11.

Insert a formula into cell G4 on the Employee Bonuses worksheet that evaluates whether the amount in Parts, Accessories, or Services exceed the Quarterly Goal. For each column that exceeds the goal, apply the Quarterly Bonus Rate.

1. On the Employee Bonuses worksheet, select cell G4. (Hint: Always put your formula in the cell where you want the result to be displayed) 2. On the FORMULAS tab, in the Function Library, click Insert Function. 3. In the Search for a function: field, type Sumif and click GO. 4. In the Select a function field, click SUMIF and click OK. 5. In the SUMIF pop-up window, configure the following: Range: B4:D4 Criteria: >100000 Sum_Range: B4:D4 (Hint: You can leave this field blank and the cells in the Range field will be used by default.) 6. Click OK. 7. In the Formula Bar, to the right of the SUMIF formula, multiply it by the ""Quarterly Bonus Rate"" so it reads as follows: =SUMIF(B4:D4,">100000")*$B$17 8. Press the Enter key to accept the formula and calculate the result.

On the Costs worksheet, repeat the rows containing the company logo and column headings so they appear on all printed pages.

1. On the PAGE LAYOUT tab, in the Page Setup group, click Print Titles. 2. In the Page Setup pop-up window, on the Sheet tab, type the following in the fields: Rows to repeat at top: $1:$3 (Hint: You can also autopopulate this field by selecting the rows with your mouse.) 3. Click OK (Hint: You can see the result by clicking Print Titles again, then clicking Print Preview. The first page will display in the Print Preview window. At the bottom of the window, advance to the second page by clicking the Next Page arrow located right of 1 of 2. You should see the column headings displayed at the top of the table on both pages. Click the return arrow in the upper left corner of the window to go back to the spreadsheet.)

On the Parts worksheet, remove the row containing the salesperson named Allen.

1. On the Parts worksheet, right-click row 11 and select Delete, and then click Delete Row.

On the Q1 Sales worksheet, insert a function in cell B19 that calculates all sales from the Total column.

1. On the Q1 Sales worksheet, select cell B19. 2. In the formula field, type =SUM(F4:F17) 3. Click Enter on the keyboard to display the result. (Hint: The result displayed should be $251,422.00)

In cell B4 on the Q1 Sales worksheet, insert a function that joins Description and Style from the Catalog worksheet, separated by a hyphen. Include a space on both sides of the hyphen. (Example: Cross Country - Hardtail).

1. On the Q1 Sales worksheet, select cell B4. 2. Click the FORMULAS tab and click Insert Function. 3. In the Search for a function field, type the keyword, Join and then click GO. 4. In the Select a function field, select CONCATENATE. Click OK. 5. In the Function Arguments pop-up window for CONCATENATE, do the following: a. Click in the Text1 field. b. Click the Catalog worksheet tab. In the Description column, select cell D4. c. Click in the Text2 field d. Type a set of quotes containing a space, a hypen, and a space. Example: " - " e. Click in the Text3 field. f. Click the Catalog worksheet tab. In the Style column, select cell C4. 6. Click OK.

Configure the Q1 Sales worksheet so rows 1 through 3 remain visible as you scroll vertically.

1. On the Q1 Sales worksheet, select row 4. (Hint: When freezing frames vertically, select the row below the last row you want to remain visible.) 2. On the View tab, in the Window group, click the Freeze Panes down-arrow and select Freeze Panes.

Enable the Total Row for the table located on the Qtr 1 worksheet.

1. On the Qtr 1 worksheet, click on any cell in the table to select the table. 2. On the TABLE TOOLS DESIGN tab, in the Table Style Options group, click the Total Row to enable it.

On the Qtr 1 worksheet, use the data contained only in the Trail, Jan, Feb and Mar columns to create a 3-D Clustered Column chart. Do not include Total data. Position the new chart to the right of the table.

1. On the Qtr 1 worksheet, select cell range A9:D14. 2. Click the INSERT tab. 3. In the Charts group, click the Insert Column Chart icon and select 3-D Clustered Column. 4. Click-drag the new chart and position it to the right of the table.

On the Qtr 1 worksheet, in the Maximum row, insert a formula in column B that returns the greatest number of successful attempts for the month of January.

1. On the Qtr 1 worksheet, select the cell in the Maximum row, column B. (This will be B16 if you have not done Task 1 and B17 if you have.) 2. Click the FORMULAS tab. 3. In the Function Library group, click the AutoSum down-arrow and select MAX. 4. Above the worksheet, in the Formula Bar, adjust the selected cell range to include only cells B10:B14. 5. Press the Enter key to accept the formula and calculate the results. (Hint: the result displayed should be 632.00)

Apply Style 3 to the pie chart on the Qtr 2 worksheet.

1. On the Qtr 2 worksheet, click anywhere on the chart to select it. 2. On the CHART TOOLS DESIGN tab, in the Chart Styles group, click the More drop-down arrow to open the Chart Styles gallery. 3. Select Style 3.

On the Qtr 2 worksheet, create a table from cell range A9:E14 by applying Table Style Medium 18. Use the data in row 9 as headers.

1. On the Qtr 2 worksheet, select cell range A9:E14. 2. On the HOME tab, in the Styles group, click Format as Table to open the gallery. 3. Under the Medium section, click Table Style Medium 18. 4. In the Format As Table pop-up window, do the following: a. Confirm the data field contains =$A$9:$E$14. b. Confirm the My table has headers box is enabled. c. Click OK.

Beginning at cell A16 on Sheet1, import the list from the comma delimited source file MoreToys.csv located in the GMetrixTemplates folder. (Accept all other defaults)

1. On the Sheet1 worksheet, select cell A16. 2. Click the Data tab. 3. In the Get External Data group, click From Text. 4. Browse to the GMetrixTemplates folder. 5. Select the MoreToys.csv file and click the Import button. 6. In the Text Import Wizard - Step 1 of 3 window, ensure that Delimited is selected and click Next >. 7. In the Text Import Wizard - Step 2 of 3 window, under Delimiters, deselect Tab and select Comma. Click Next > then Finish. 8. In the Import Data pop-up window, ensure the Existing Worksheet bubble under the "Where do you want to put the data?" section is checked, and make sure it reads =$A$16 9. Click OK.

On the Carriers and Coolers worksheet, link the contents in cell C10, C11, and C12 to cell A4 on the Tackle worksheet.

1. Select cell C10 on the Carriers and Coolers worksheet 2. On the INSERT tab, in the Links group, click Hyperlink. 3. In the Link to: section, select Place in This Document. 4. In the "Type the cell reference" field, type "A4" 5. In the "Or select a place in this document" field, under Cell Reference, highlight Tackle 6. Click OK.

In cell F4 on the Parts worksheet, insert a line that graphs the trend of sales from Jan through Mar.

1. Select cell F4 on the Parts worksheet. 2. Select the INSERT tab. 3. In the Sparklines group, click Line. 4. In the Create Sparklines pop-up window, configure the fields as follows: Data range: B4:D4 Location range: $F$4 5. Click OK.