Excel

In the Qtr 1 worksheet change the configuration of the Q1 Taco 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.

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.

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

1. Select cell A29 on the Profits worksheet. 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: Number1: B4:B26 5. Click OK. (Hint: The result should be 1.73 )

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.

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

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

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.

Add a function to the Overtime column 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.

On the Employee Hours worksheet, add a row to the table that automatically calculates 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.

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.

Without using the New Sheet button, 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.

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

1. On the Sold worksheet, select rows 14 through 17. 2. Right-click and select Hide.

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 through G16.

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

1. On the Computer Rooms worksheet, select row 4. 2. On the HOME tab, in the Alignment group, click Wrap Text.

In the Flavors column of the Costs worksheet, replace all instances of the misspelled word Purge with Splurge.

. Click the A at the top of the column on the Costs worksheet to select the entire Flavors column. 2. On the HOME tab, in the Editing group, click Find & Select and click 3. Click Replace... 4. In the Find & Replace pop-up, on the Replace tab, configure the following: Find what: Purge Replace with: Splurge 5. Click Replace All (Hint: You should see a message that 3 replacements were made.) 6. Click Close.

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.

Locate the table that has the name Table1 and change the name to Costs.

1. Above the worksheet to the left of the formula bar, click the Name Box down-arrow. 3. Select Table1. (Hint: the entire table on the Costs worksheet should now be selected). 4. Select the TABLE TOOLS DESIGN contextual tab. 5. In the Properties group, in the Table Name field, replace Table1 by typinging Costs. 6. Press Enter on your keyboard to accept the name change.

Make a copy of the Qtr 2 worksheet to the right of the Qtr 2 worksheet.

1. At the bottom of the workbook, right-click the Qtr 2 tab, and select Move or Copy... 2. In the Move or Copy pop-up window, configure the following: Before sheet: (move to end) Click the box Create a copy box to enable it. 3. Click OK. (Hint: The copy will automatically be named, Qtr 2 (2) and will appear to the right of the original worksheet. )

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.

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

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.

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 Chart data range as: =Parts[Fri] and click 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 Palete 4. 7. Move the chart to the right side of the table.

Set cells A1:F10 in the Computer Rooms worksheet so that they will be the only cells that print.

1. On the Computer Rooms worksheet, select cell range A1:F10. 2. Click the PAGE LAYOUT tab. 3. 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.)

In cell I4 of the Employee Hours sheet, use a function to copy the name from cell A4 and format the name so the first letter is upper case and the remaining letters are lower case.

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 =PROPER, 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 should be Emilio)

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.)

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. (Note: If prompted to remove external connections, answer "Yes". This prompt is caused by the imported data in task #1)

In cell I7 of the New Releases worksheet, use a function to calculate the average of the Review Score column where the System type 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

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.

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 a third column on the chart).

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.

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.

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.

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.

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.

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 )

Save the Computer 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 Gmetrix Templates 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, select "Sheet All contents of Computer Rooms" 6. Click Publish. (Hint: If prompted to keep using the format, click Yes.)

Display the formulas that are in the cells of the Qtr 2 worksheet.

1. Select the Qtr 2 worksheet. 2. On the FORMULAS tab, in the Formula Auditing group, click Show Formulas.

Display the Costs worksheet in 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)

Add a new worksheet named Specials with a tab color Blue.

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.