Skip to content

Practice Exam 1 - Project 2

Sports

A Feed and Fencing supply store is creating an inventory report. They need help with charts, hyperlinks, and adding new inventory to the Excel workbook. You've been hired to assist.

Task 1

On the Home-Made Pet Food worksheet, in cell A9, importNew Flavors.txt located in the GMetrixTemplates folder as a tab-delimited file without headers.

Hint

  1. On the Home-Made Pet Food worksheet, click cell A9 to select it.

  2. On the Data tab, in the Get External Data group, click From Text.

  3. Browse to the GMetrixTemplates folder.

  4. Select the New Flavors.txt file and click the Import button.

  5. In the Text Import Wizard - Step 1 of 3 pop-up window, accept the default data type as Delimited, clear the box My data has headers, and click the Next button.

  6. In the Text Import Wizard - Step 2 of 3 pop-up, accept the default Delimiter as Tab.

    (Hint: Notice how you can preview how the data will be separated in the window below).

  7. Accept all other defaults and click the Finish button.

  8. In the Import Data pop-up accept the defaults and click OK.

(Hint: If you do not see these options listed in the Data Tab you may need to enable the Legacy Import Wizards. To do this, go to File > Options > Data > Show legacy data import wizards and check all that apply. Once this is complete you can use the Get Data drop-down to select the Legacy Wizards and select the Wizard that applies.)

Task 2

On the Feed Inventory worksheet, remove the hyperlink functionality, but leave the text in cell C33.

Hint

  1. Select cell C33 on the Feed Inventory worksheet.

  2. On the Insert tab, in the Links group, click Hyperlink.

  3. In the Insert Hyperlink pop-up window, click Remove Link.

Task 3

Show the existing Fencing worksheet located after the Home-Made Pet Food worksheet.

Hint

  1. On the Home tab, in the Cells group, click Format.

  2. Beneath the Visibility section, select Hide & Unhide and click Unhide Sheet...

  3. In the Unhide Sheet window, select Fencing.

  4. Click OK.

Task 4

On the Home-Made Pet Food worksheet, format the data range A3:E11 as a table that has headers. Apply any table style format.

Hint

  1. On the Home-Made Pet Food worksheet, select cell range A3:E11.

  2. On the Home tab, in the Styles group, click Format as Table to open the gallery.

  3. Select any table style format.

  4. In the Format As Table pop-up window, do the following:

    a. Confirm the data field contains =$A$3:$E$11.

    b. Confirm the My table has headers box is enabled.

    c. Click OK. (Note: If prompted to remove external connections, answer "Yes". This prompt is caused by the imported data in task #1)

Task 5

On the Feed Inventory worksheet, resize the Total Inventory chart so that it covers only cells H3 through O19.

Hint

  1. Click on the chart located on the Feed Inventory worksheet.

  2. Click-drag the resize handle located in the bottom right corner of the chart until the chart covers cell O19.