Exam 1
Exam 1
Exam 1
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)
This question has different help steps depending on the version of Office 2016 you are using, you can
check which version you have on the File tab, Account page.
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%.
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)
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.)
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.
PROJECT 2
Join cells A1:E1 of the Tackle worksheet. Do not change the alignment of the contents.
On the Carriers and Coolers worksheet, link the contents in cell C10, C11, and C12 to cell A4 on
the Tackle worksheet.
On the Carriers and Coolers worksheet, apply the 3 Flags Icon Set Conditional Formatting to the
contents in the Inventory column.
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.
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.
PROJECT 3
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 once. (Hint: You can verify that the change happened by looking at cells A4 and A18
on the Costs worksheet to see whether it says "Choco" or "Chocolate.")
5. Click Close to exit the pop-up window.
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.)
In cell B28 on the Profits worksheet, insert a formula that displays the number of Sales greater than
250.
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.
PROJECT 4
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 in the Hardware worksheet.
Beginning at cell A1 on the Hardware worksheet, import the picture file NetworkTopology.png located
in the GMetrixTemplates folder.
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.
Sort the data in the Wired Networks table. Sort by ProductID, smallest to largest
PROJECT 5
Configure the Q1 Sales worksheet so rows 1 through 3 remain visible as you scroll vertically.
Configure Excel to always print cell range A1:F17 on the Q1 Sales worksheet.
On the Q1 Sales worksheet, insert a function in cell B19 that calculates all sales from the Total column.
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).
PROJECT 6
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, 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)
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.
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.
PROJECT 7
Use Autofill to copy the formula in cell H4 to calculate the Total Compensation for each employee in
the Bonuses table.
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.
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 Employee Bonuses worksheet, disable the headings on the Rates table.
In cell F4 on the Parts worksheet, insert a line that graphs the trend of sales from Jan through Mar.