Skip to content

Practice Exam 1 - Project 7

Forest Nursery

You operate a small nursery that supplies trees for reforestation. You are creating an Excel 2016 workbook to support your business.

Task 1

Color the tab of the Q1 Sales worksheet to Green.

Hint

  1. At the bottom of the workbook, right-click the Q1 Sales worksheet tab and select Tab Color.

  2. Under Standard Colors select Green.

Task 2

Rename the Sheet2 worksheet to Suppliers.

Hint

  1. At the bottom of the workbook, right-click the Sheet2 tab and select Rename.

    (Hint: You can also double-click the tab to edit the name).

  2. Type Suppliers and press Enter on your keyboard to accept the change.

Task 3

Insert Page 1 of ? page numbering in the header of the Q1 Sales worksheet.

Hint

  1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it.

  2. On the Insert tab, in the Text group, click Header & Footer.

  3. Click on the text, Click to add header, to reveal the Header & Footer Tools Design tab.

  4. In the Header & Footer group, click Header and select Page 1 of ?

  5. On the View tab, in the Workbook Views group, click Normal to close the Page Layout View (header and footer view).

Task 4

Copy the data from cell range A8:E13 on the Q1 Sales worksheet and transpose it to the cell range beginning at A8 on the Seedling Sales worksheet.

Hint

  1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it.

  2. Select all cell range A8:E1

    (Hint: Do not include the Total column).

  3. Right-click and select Copy.

    (Note: You can also copy the range either by clicking Copy in the Clipboard group on the Home page, OR by using the short-cut key combination CTRL + C. )

  4. At the bottom of the workbook, click the Seedling Sales worksheet tab to select it.

  5. Click on cell A8.

  6. Right-click and below the Paste Option section, click Transpose (4th icon).

(Note: There are several alternative solutions: You can right-click and select Paste Special... and Paste Special.. again. At the bottom of the Paste Special pop-up window, click Transpose to enable the option and click OK. -OR- On the Home tab, in the Clipboard group, click Paste and select the Transpose icon (7th icon), or at the bottom of the Paste drop-down menu select Paste Special... and in the Paste Special pop-up window click Transpose to enable the option and click OK.)

Task 5

Remove the table formatting from the Small Tree Sales worksheet. Keep the cell formatting.

Hint

  1. At the bottom of the workbook, click the Small Tree Sales worksheet tab.

  2. Click anywhere within the table to select it (Hint: You must click within cell range A8:F18.)

  3. Click on the Table Tools Design tab.

  4. In the Tools group, click Convert to Range.

  5. When prompted, Do you want to convert the table to a normal range? Click, Yes.

(Hint: When the table converts to a normal range, the autofilters will disappear and so will the Table Tools tab.)

Task 6

On the Small Tree Sales worksheet, insert a formula in cell F20 that uses a built-in Excel function to return the value of the smallest value in the Total column.

Hint

  1. On the Small Tree Sales worksheet, select cell F20.

  2. Click the Formula tab.

  3. In the Function Library group, click the AutoSum down-arrow and select MIN.

  4. In the Formula Bar adjust the selected cell range to include only cells F9:F18.

  5. Press the Enter key to accept the formula and calculate the results.

(Hint: The formula inserted into cell F20 should be =MIN(F9:F18) and the result displayed in cell F20 should be 550.)

Task 7

Apply the Quick Analysis tool to the data on the Q1 Sales worksheet to create a Clustered Column chart displaying sales of only Douglas Fir seedling, Giant Sequoia seedling and KMX seedling trees for each month. Apply Chart Style 14, Monochromatic Palette 7, and Quick Layout 2. Place the chart in the upper-left corner of the Seedling Sales Chart worksheet.

Hint

  1. On the Q1 Sales worksheet, select cell range A8:E11.

  2. In the lower-right corner of cell E11, click the Quick Analysis tool icon to open the toolbox.

  3. In the Quick Analysis tool box, click the Charts tab.

  4. Select the Clustered chart that shows each month as a column and the tree names in the legend.

  5. On the Chart Tools Design tab, in the Chart Styles group, click the More drop-down arrow to open the gallery. Select Style 14.

  6. In the Chart Styles group, click Change Colors. Beneath the Monochromatic section, select Palette 7.

  7. In the Chart Layouts group, click Quick Layout and select Layout 2.

  8. On the Chart Tools Design tab, in the Location group, click Move Chart.

  9. In the Move Chart pop-up window, select Object in: then click the drop-down arrow and choose Seedling Sales Chart.

  10. Click OK.

  11. Drag the chart into the upper-left corner of the worksheet.