(PRACTICE TEST 2)

PROJECT 1
Task 1

Navigate to the named cell range AppHeading2 and delete only the contents, leaving the empty
cells.

1. Above the worksheet to the left of the formula bar, click the Name Box down arrow.
2. Select AppHeading2.
(Hint: Alternatively, on the Home tab, in the Editing group, click Find & Select, and
select Go To... Select AppHeading2, then click OK.)
3. Cell range E1:F3 should be selected.
4. Right-click on the selected cells and select Clear Contents.
5. Click OK.

Task 2

On the Downloads worksheet, adjust the height of row 27 to 78.

1. On the Downloads worksheet, click row 27 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 78.
4. Click OK.

Task 3

Apply the cell style Light Blue, 40% - Accent 2 to cell A27.

1. Select cell A27.

2. On the Home tab, in the Styles group, click the More dropdown.
3. In the Themed Cell Styles section, click Light Blue, 40% - Accent 2.

Task 4

Create a table with headers from cell range A3:B24 by applying the Blue, Table Style Light 10.

1. On the Downloads worksheet, click anywhere within the cell range A3:B24.
2. On the Home tab, in the Styles group, click Format as Table.
3. In the section, select Blue, Table Style Light 10.
4. In the Format As Table pop-up window, do the following:
o Confirm the data field contains =$A$3:$B$24.
o Confirm the My table has headers box is enabled.
5. Click OK
Task 5

Insert a Footer that displays today's date on the right, and then return to Normal view.

1. On the Insert tab, in the Text group, click Header & Footer.
2. In the Header & Footer Design tab, in the Navigation group, click Go to Footer.
3. Click the rightmost cell in the Footer.
4. On the Header & Footer Tools Design tab, in the Header & Footer Elements group,
click Current Date.
5. Click outside of the Footer cells.
6. On the View tab, in the Workbook Views group, click Normal to close the Page Layout
View (header and footer view).

PROJECT 2
Task 1

Import PetFoods.txt located in the GMetrixTemplates folder as a table on a new worksheet.

1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.
(Hint: Comma-Delimited files and Tab-Delimited files are types of Text files.)
2. In the Import Data pop-up window, browse to the GMetrixTemplates folder.
3. Select the PetFoods.txt file and click the Import button.
4. In the PetFoods.txt pop-up window, configure the following:
o File Origin: accept the default - 1252: Western European (Windows)
o Delimiter: Tab
o Data Type Detection: Accept the default - Base on first 200 rows
5. Click the down arrow to the right of the Load button and select Load To...
6. In the Import Data pop-up window, configure the folowing:
o Select how you want to view this data in your workbook: Table
o Where do you want to put the data? New Worksheet
7. Click OK.

Task 2

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

1. On the Feed Inventory worksheet, select cell C27.

2. On the Insert tab, in the Links group, click Hyperlink.
3. In the Insert Hyperlink pop-up window, click Remove Link.

Task 3

Remove the conditional formatting from the Inventory column on on the Feed
Inventory worksheet.

1. On the Feed Inventory worksheet, click anywhere on the table.

2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Select Clear Rules and click Clear Rules from This Table.
Task 4

On the Organic Feed worksheet, format the data range A3:F10 as a table that has headers. Apply
the Dark Red, Table Style Medium 7 format.

1. On the Organic Feed worksheet, select cell range A3:F10.

2. On the Home tab, in the Styles group, click Format as Table to open the gallery.
3. Under the Medium section, click Dark Red, Table Style Medium 7.
4. In the Format As Table pop-up window, do the following:
o Confirm the data field contains =$A$3:$F$10.
o Confirm the My table has headers box is enabled.
o Click OK.

Task 5

On the Feed Inventory worksheet, apply Style 11 to the chart.

1. On the Feed Inventory worksheet, click on the chart to select it.

2. Click the Chart Tools Design contextual tab.
3. In the Styles group, click the More down arrow to open the Chart Styles gallery.
4. Select Style 11.

PROJECT 3

Task 1

Display the Costs worksheet in the Page Layout view. Then insert a page break between row
20 Cracker Jacker and row 21 Raspberry Chocolate.

1. Select the View tab.

2. In the Workbook Views group, click Page Layout.
3. Scroll down the page and select row 21 Raspberry Chocolate.
4. On the Page Layout tab, in the Page Setup group, click Breaks.
5. Select Insert Page Break.
(Hint: The first page should now end with the flavor Cracker Jacker, and the next page
should begin with the flavor Raspberry Chocolate.)
6. On the View tab, in the Workbook Views group, click Normal to return to the normal view.

Task 2

Use a built-in Excel feature to copy all of the formatting of cell range A2:G2 located on
the Profits worksheet and apply that formatting to cell A2 on theCosts worksheet.

1. On the Profits worksheet, select cell range A2:G2.

2. On the Home tab, in the Clipboard group, click the Format Painter.
(Hint: The cursor will change to a paintbrush.)
3. At the bottom of the worksheet, click the tab of the Costs worksheet.
4. On the Costs worksheet, place the paintbrush cursor on cell A2 and click to apply the
formatting.
Task 3

On the Profits worksheet, enter a formula in cell A29 that uses an Excel function to return the
average costs of the cookie dough flavors based on values in the Cost column.

1. On the Profits worksheet, select cell A29.

(Hint: Always put your formula in the cell where you want the result to be displayed.)
2. In the Formula Bar, type =AVERAGE, 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:
o Number1: B4:B26
5. Click OK.
(Hint: The formula in cell A29 should be =AVERAGE(B4:B26). The result displayed should be
1.73.)

Task 4

On the Profits worksheet, include the Total Profit data on the Flavor Expenses, Income,
and Profit chart.

1. On the Profits 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 left window pane, click the box Total
Profit to enable it.
5. Click OK.
(Hint: Total Profit should now be added to the legend representing the third column on the
chart.)

Task 5

Filter the Profits worksheet to display only flavors with a Cost that is Above Average.

1. On the Profits worksheet, click the autofilter down arrow on the Cost heading.
2. Hover over Number Filters and click Above Average.

PROJECT 4
Task 1

Add Open to the Quick Access Toolbar.

1. Above the ribbon, click the Customize Quick Access Toolbar down arrow.
2. Click Open.
Task 2

Add the value New Rates to the Title property of the document.

1. Click the File tab.

2. Locate the list of Properties on the right side of the Info page.
3. In the Title property field, type New Rates.
4. Click the return arrow in the upper-left corner of the Info window to return to the worksheet.

Task 3

Set cells A1:F10 so that they will be the only cells that print.

1. Select cell range A1:F10.

2. On the Page Layout tab, in the Page Setup group, click Print Area and select Set Print
Area.
(Hint: You can check your print area setting under the File tab by selecting Print and viewing
it in the Preview pane.)

Task 4

Save the Rooms worksheet as a webpage named Rent_Prices.htm to the Gmetrix

Templates folder.

1. Select the File tab.

2. In the left pane, select Save As, and browse to the GMetrixTemplates folder.
3. In the Save As pop-up window, configure the following:
File name: Rent_Prices
Save as type: Web Page (*.htm, *.html)
4. Under Save: select Publish.
5. In the Publish as Web Page dialog box, select All Contents of Rooms.
6. Click Publish.
(Hint: If prompted to keep using the format, click Yes.)

Task 5

On the Rooms worksheet, configure the heading row in the table (row 2) so that entries wider than
the column wrap to multiple lines.

1. On the Rooms worksheet, select row 2.

2. On the Home tab, in the Alignment group, click Wrap Text.
 PROJECT 5
Task 1

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

1. On the Sold worksheet, select column A by clicking the A at the top of the column.
2. Right-click on the column and select Insert.

Task 2

Split the view of the Sold worksheet so only rows 1 through 6 appear in the top pane and the
remaining rows appear in the bottom pane.

1. On the Sold worksheet, select row 7.

(Hint: When splitting views, select the row below the last row you want to appear in the top pane.)
2. On the View tab, in the Window group, click Split.

Task 3

Configure the Vehicles worksheet so rows 1 through 3 remain visible as you scroll vertically.

1. On the Vehicles worksheet, select row four (Hint: When freezing frames vertically, select the
row below the last row you want to remain visible.)
2. On the View tab, in the Window group, click the Freeze Panes down arrow and
select Freeze Panes.

Task 4

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

1. On the Sold worksheet, click anywhere within the table to select it.
2. On the Table Tools Design contextual tab, in the Table Styles Options group, click the Banded
Rows box to enable it.

Task 5

On the Vehicles worksheet, insert a function into cell B4 of the Code column that extracts the two
leftmost letters of the vehicle style displayed in cell D4.
1. On the Vehicles worksheet, select cell B4.
(Hint: Always put your formula in the cell where you want the result displayed.)
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.)

PROJECT 6
Task 1

On the Qtr 2 worksheet, in cell F10, insert a column sparkline showing the sales trend
from Apr through Jun. Apply Gold, Sparkline Style Accent 2, Darker 25% to the sparkline. Copy
the sparkline to the remaining cells in the column (F11:F14).

1. Select cell F10 on the Qtr 2 worksheet.

2. Select the Insert tab.
3. In the Sparklines group, click Column.
4. In the Create Sparklines pop-up window, configure the fields as follows:
Data range: B10:E10
Location range: $F$10
5. Click OK.
6. On the Sparkline Tools Design tab, in the Style group, click the More down arrow to open
the Style gallery. Select Gold, Sparkline Style Accent 2, Darker 25%.
7. In the lower-right corner of the cell, click-drag the Fill Handle from cell F11 through cell F14 to
create a sparkline for each location.

Task 2

Using the Move Chart feature, move the pie chart on the Qtr 2 worksheet to its own chart sheet
named Qtr 2 Chart.

1. On the Qtr 2 worksheet, click anywhere on the pie chart to select it.
2. On the Chart Tools Design contextual tab, in the Location group, click Move Chart.
3. In the Move Chart pop-up window, select New sheet: and type the name Qtr 2 Chart.
4. Click OK.
Task 3

On the Qtr 1 worksheet, apply a number format to display the numbers in columns B through E to
two decimal places with the US Dollar symbol ($) left-aligned and the decimal points aligned.

1. On the Qtr 1 worksheet, select columns B through E.

2. On the Home tab, in the Number group, click the $ symbol (this will align the symbols).
(Hint: Alternatively, click the dialog-box launcher in the lower-right corner of the Number group. On
the Number tab, in the Category pane, select Accounting and configure Decimal
places: 2 and Symbol: $. Then click OK.)

Task 4

In the Qtr 1 worksheet, change the configuration of the Qtr 1 Location Revenue chart so that it
displays the months on the x-axis and the revenue on the y-axis.

1. Click the Qtr 1 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.

Task 5

Display all of the formulas on the Qtr 2 worksheet.

1. On the Qtr 2 worksheet, select the Formulas tab.

2. On the Formulas tab, in the Formula Auditing group, click Show Formulas.

PROJECT 7

Task 1

On the Average Call Time worksheet, use an Excel feature to copy the sparkline into all the vacant
cells of the Trend column.

1. On the Average Call Time worksheet, select cell G4.

2. In the lower-right corner of cell G4, click-drag the fill handle down the Trend column beginning
with cell G4 and ending with cell G16.
Task 2

On the Employee Hours worksheet, add a row to the table that automatically calculates the total
hours worked by all employees.

1. On the Employee Hours 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.

Task 3

Add a function to the Overtime column in cell H4 on the Employee Hours worksheet that will
display the word Yes if the value in cell J4 is higher than 40. Otherwise, display the word No.

1. On the Employee Hours worksheet, click cell H4.

2. In the Formula Bar, type =IF, 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:
Logical_test: J4>40
Value_if_true: Yes
Value_if_false: No
5. Click OK.

Task 4

In cell I4 of the Employee Hours sheet, use a function to copy the name from cell A4, and format the
name so that all letters are uppercase.

1. On the Employee Hours worksheet, select cell I4.

(Hint: Always put your formula in the cell where you want the result displayed.)
2. In the Formula Bar, type =UPPER, 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: A4
5. Click OK.
(Hint: The result displayed should be EMILIO.)
Task 5

On the Average Call Time worksheet, create a 3-D Clustered Column chart that shows only
the Call Time for Friday by each Salesperson. Position the new chart to the right of the table, and
change the colors of the chart to Colorful Palette 4.

1. On the Average Call Time worksheet, click the Insert tab.

2. In the Charts group, click the Insert Column Chart icon and select 3-D Clustered Column.
3. In the Chart Tools Design tab, in the Data group, click Select Data.
4. Enter the Chart Data Range: =Parts[Fri] and click the arrow at end of field twice.
5. Under Horizontal (Category) Axis Labels, click Edit. Then select cells A4 through A16 and
click OK twice.
6. On the Chart Tools Design tab, in the Chart Styles group, click Change Colors and
select Colorful Palette 4.
7. Move the chart to the right side of the table.