11/17/23, 12:17 PM MOS Excel 2016 - CORE Practice EXAM 2
PROJECT 1
Game Store
You operate a store that sells and rents video games. You are updating your Excel workbook used to track your
business.
TASK 1
On the New Releases worksheet, navigate to the cell range NewestTitles and delete the
contents. Retain all other cells on the worksheet.
1. Click on the New Releases worksheet to select it.
2. Above the worksheet to the left of the formula bar, click the Name Box down-arrow.
3. Select NewestTitles. (Hint: cell range A17:E19 should now be selected).
4. Right-click and select Delete.
5. Click OK.
TASK 2
On the New Releases worksheet, adjust the height of row 2 to 34.5.
1. On the New Releases worksheet, click row 2 to select it.
2. On the HOME tab, in the Cells group, click Format and select Row Height.
3. In the Row Height pop-up box, type 34.5
4. Click OK.
TASK 3
In cell I7 of the New Releases worksheet, use a function to calculate the average of
the Review Score column where the Systemtype is YCube 720.
1. On the New Releases worksheet, click cell I7.
2. In the Formula Bar, type =AVERAGEIF, 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:
Range: B7:B19
(Note: This will be B7:B16 if you have completed Task 1)
Criteria: YCube 720
Average_Range: E7:E19
(Note: This will be E7:E16 if you have completed Task 1)
5. Click OK.
TASK 4
Remove the table functionality from PreOrders. Retain the font and cell formatting.
1. Above the worksheet to the left of the formula bar, click the Name Box down-arrow.
3. Select PreOrders.
4. On the TABLE TOOLS DESIGN tab, in the Tools group, click Convert to Range..
5. Click Yes.
TASK 5
Add a new worksheet named Specials with a tab color Blue.
1. At the bottom of the workbook, to the right of the Pre-Orders 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, Specials. Press Enter on your keyboard to accept
the change.
4. Right-Click the Specials worksheet and select Tab Color. Under standard Colors, select Blue.
about:blank 1/9
, 11/17/23, 12:17 PM MOS Excel 2016 - CORE Practice EXAM 2
PROJECT 2
Feed Store
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, import New Flavors.txt located in the
GMetrixTemplates folder as a tab-delimited file without headers.
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.
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.
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.
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.
about:blank 2/9
PROJECT 1
Game Store
You operate a store that sells and rents video games. You are updating your Excel workbook used to track your
business.
TASK 1
On the New Releases worksheet, navigate to the cell range NewestTitles and delete the
contents. Retain all other cells on the worksheet.
1. Click on the New Releases worksheet to select it.
2. Above the worksheet to the left of the formula bar, click the Name Box down-arrow.
3. Select NewestTitles. (Hint: cell range A17:E19 should now be selected).
4. Right-click and select Delete.
5. Click OK.
TASK 2
On the New Releases worksheet, adjust the height of row 2 to 34.5.
1. On the New Releases worksheet, click row 2 to select it.
2. On the HOME tab, in the Cells group, click Format and select Row Height.
3. In the Row Height pop-up box, type 34.5
4. Click OK.
TASK 3
In cell I7 of the New Releases worksheet, use a function to calculate the average of
the Review Score column where the Systemtype is YCube 720.
1. On the New Releases worksheet, click cell I7.
2. In the Formula Bar, type =AVERAGEIF, 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:
Range: B7:B19
(Note: This will be B7:B16 if you have completed Task 1)
Criteria: YCube 720
Average_Range: E7:E19
(Note: This will be E7:E16 if you have completed Task 1)
5. Click OK.
TASK 4
Remove the table functionality from PreOrders. Retain the font and cell formatting.
1. Above the worksheet to the left of the formula bar, click the Name Box down-arrow.
3. Select PreOrders.
4. On the TABLE TOOLS DESIGN tab, in the Tools group, click Convert to Range..
5. Click Yes.
TASK 5
Add a new worksheet named Specials with a tab color Blue.
1. At the bottom of the workbook, to the right of the Pre-Orders 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, Specials. Press Enter on your keyboard to accept
the change.
4. Right-Click the Specials worksheet and select Tab Color. Under standard Colors, select Blue.
about:blank 1/9
, 11/17/23, 12:17 PM MOS Excel 2016 - CORE Practice EXAM 2
PROJECT 2
Feed Store
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, import New Flavors.txt located in the
GMetrixTemplates folder as a tab-delimited file without headers.
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.
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.
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.
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.
about:blank 2/9
