Practice Exam 1 - Project 5

Police Auction

You are the administrative assistant for a police precinct. Each year the precinct auctions unclaimed stolen vehicles to the public. You've been tasked to prepare a workbook for the sales.

Task 1

On the Sold worksheet, insert a new column before column A.

Hint

1. On the Sold worksheet, select column A by clicking the A at the top of the column.

2. Right-click and select Insert.

Task 2

On the Sold worksheet, configure rows 14 through 17 so they are present but not visible.

Hint

1. On the Sold worksheet, select rows 14 through 17.

2. Right-click and select Hide.

Task 3

On the Vehicles worksheet, add subtotals to the Price column that display the combined price for each Type with a page break between each type.

Hint

1. On the Vehicles worksheet, click anywhere within the table cell range.

2. On the Data tab, in the Outline group, click Subtotal.

3. In the Left pop-up window, configure the following:

   At each change in: Type

   Use function: Sum

   Add subtotal to: Price

4. Click the box Page break between groups to enable it. (Accept all other defaults)

5. Click OK.

Task 4

On the Sold worksheet, format the table so that every other row is shaded. Use a technique that automatically updates the formatting if you insert a new row.

Hint

1. On the Sold worksheet, click anywhere in the table to select it.

2. On the Table Tools Design contextual tab, in the Table Styles Options group, click the box Banded Rows to enable it.

Task 5

On the Vehicles worksheet, in the Code column cell B4, create a formula that returns the two leftmost letters of the vehicle style displayed in cell D4.

Hint

1. On the Vehicles worksheet, select cell B4.

2. In the Formula Bar, type =LEFT, then press the tab key on your keyboard.

3. To the left of the Formula Bar, click fx to open the Function Arguments wizard.

4. In the Function Arguments wizard, configure the following:

   Text: D4

   Num_chars: 2

5. Click OK.

(Hint: The result should be Se)