mos exam sg 2

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.

On the Employee Bonuses worksheet, disable the headings on the Rates table.

1. Click anywhere on the Rates table to select it. 2. On the TABLE TOOLS DESIGN tab, in the Table Styles Options group, click the Header Row box to clear it and disable the option.

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.

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.

Apply the Orange, Accent 2 Fill Color and White, Background 1 Font Color and apply the bold format to cell range A2:B2 on the Cookie Sales worksheet.

1. At the bottom of the workbook, click the Cookie Sales worksheet. 2. Select cell range A2:B2. 3. On the HOME tab, in the Font group, click the Fill Color icon and in the first row select Orange, Accent 2. 4. In the Font group, click the Font Color icon and in the first row select White, Background 1. 5. In the Font group, click the Bold icon.

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 Qtr 2 worksheet, create a table from cell range A9:E14 by applying Table Style Medium 18. Use the data in row 9 as headers.

1. On the Qtr 2 worksheet, select cell range A9:E14. 2. On the HOME tab, in the Styles group, click Format as Table to open the gallery. 3. Under the Medium section, click Table Style Medium 18. 4. In the Format As Table pop-up window, do the following: a. Confirm the data field contains =$A$9:$E$14. b. Confirm the My table has headers box is enabled. c. Click OK.

Join cells A1:E1 of the Tackle worksheet. Do not change the alignment of the contents.

1. Select cell range A1:E1 on the Tackle worksheet. 2. On the HOME tab, in the Alignment group, click the Merge & Center drop-down arrow and select Merge Across.

On the Carriers and Coolers worksheet, apply the 3 Flags Icon Set Conditional Formatting to the contents in the Inventory column.

1. Select cell range D4:D19 on the Carries and Coolers worksheet. 2. On the HOME tab, in the Styles group, click Conditional Formatting to open the menu. 3. Select Icon Sets and click 3 Flags.

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.

Modify the chart on the Profits worksheet so the Legend appears at the Top.

1. If the Profits worksheet Is not already displayed, click the Profits worksheet tab to select it. 2. Click the chart to reveal the CHART TOOLS tabs. 3. On the far left of the CHART TOOLS DESIGN tab, in the Chart Layouts group, click Add Chart Element, select Legend and click Top.

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

1. On the Q1 Sales worksheet, select row 4. (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.

On the Carriers and Coolers worksheet, link the contents in cell C10, C11, and C12 to cell A4 on the Tackle worksheet.

1. Select cell C10 on the Carriers and Coolers worksheet 2. On the INSERT tab, in the Links group, click Hyperlink. 3. In the Link to: section, select Place in This Document. 4. In the "Type the cell reference" field, type "A4" 5. In the "Or select a place in this document" field, under Cell Reference, highlight Tackle 6. Click OK. 7. Repeat steps 1 through 6 for cells C11 and C12.

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.

Enter a formula in cell E4 that calculates the total production of Barley and updates the results even if the row order is changed.

1. Select cell E4. (Hint: Always put your formula in the cell where you want the result to be displayed) 2. On the FORMULAS tab, in the Function Library, click Insert Function. 3. In the Search for a function: field, type Sumif and click Go. 4. In the Select a function field, click SUMIF and click OK. 5. In the Function Arguments pop-up window for SUMIF, configure the following: Range: B4:B31 (Hint: If you drag to select the cells instead of typing, the structured reference will be Table1[Product]) Criteria: Barley Sum_Range: C4:C31 (Hint: If you drag to select the cells instead of typing, the structured reference will be Table1[Metric Tons]) 6. Click OK. (Hint: The formula in cell E3 should be =SUMIF(B4:B31,"Barley",C4:C31. The result displayed should be 30,270,882).

In the Sales Commissions worksheet, use an automatic formatting method to apply Orange Gradient Fill Data Bars to the Total Sales column that changes the cell formatting whenever the cell values change.

1. Select cell range F4:F10 on the Sales Commissions worksheet, 2. On the HOME tab, in the Styles group, click Conditional Formatting to open the menu. 3. Select Data Bars and beneath the Gradient Fill section, click Orange Data Bar.

Add the Alt Text New Sports to the pie chart containing Soccer, Tennis, and Volleyball data

Option 1: 1. Right-click the pie chart containing the Soccer, Tennis, and Volleyball data. 2. Select Edit Alt Text... 3. In the Alt Text pane type New Sports. 4. Close the Alt Text pane by clicking the X in the upper-right corner. Optoin 2 (If Edit Alt Text isn't available when you right click): 1. Right-click the pie chart containing the Soccer, Tennis, and Volleyball data. 2. Select Format Chart Area. 3. Select Size & Properties 4. Select Alt Text and type New Sports in the Description box.

Sort the Farmers Market worksheet alphabetically (from A to Z), first by Location, then by Product, and then by Season (Spring, Summer, Fall).

1. At the bottom of the workbook, click the Farmers Market tab. 2. Click any cell within the table to select it. 3. On the HOME tab, in the Editing group, click Sort & Filter and select Custom Sort... 4. In the Sort pop-up window, configure the fields as shown below: Column Sort by: Location Sort on: Values Order: A to Z 5. Click Add Level 6. Then by: Product Sort on: Values Order: A to Z 7. Click Add Level 8. Then by: Season Sort on: Values 9. For Order click Custom List, in the Add List box type: Spring, Summer, Fall 10. Click Add. 11.Click OK. 12.Click OK.

Remove all duplicate records from the Food Inventory worksheet. Do not remove any other records.

1. At the bottom of the workbook, click the Food Inventory tab. 2. Click any cell within the table to select it. 3. On the DATA tab, in the Data Tools group, click Remove Duplicates. (Hint: Notice the entire table is automatically selected.) 4. Accept all defaults in the Remove Duplicates pop-up window and click OK. (Hint: A pop-up window should confirm that 4 duplicate records were removed.) 5. Click OK.

Configure the Fundraiser sheet to display rows 1-3 on every page that is printed.

1. At the bottom of the workbook, click the Fundraiser worksheet. 2. On the PAGE LAYOUT tab, in the Page Setup group, click Print Titles. 3. In the Page Setup pop-up window, on the Sheet tab, type the following in the fields: Rows to repeat at top: $1:$3 (Hint: You can also autopopulate this field by selecting the rows with your mouse.) 4. Click OK (Hint: You can see the result by clicking Print Titles again, then clicking Print Preview. The first page will display in the Print Preview window. At the bottom of the window, advance to the second page by clicking the Next Page arrow located right of 1 of 2. You should see the column headings displayed at the top of the table on both pages. Click the return arrow in the upper left corner of the window to go back to the spreadsheet.)

On the Fundraiser worksheet, configure the table to display only records of the Blazing Broncs club members.

1. At the bottom of the workbook, click the Fundraiser worksheet. 2. On the table, click the autofilter down-arrow of either of the color filled cells A3 or B3. 3. Select Filter by Color and click the blue icon. (Hint: Blue is the fill color that identifies records of the Blazing Broncs club members.)

Insert Page 1 of ? page numbering in the header of the Q1 Sales worksheet.

1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it. 2. On the INSERT tab, in the Text group, click Header & Footer. 3. Click on the text, Click to add header, to reveal the HEADER & FOOTER TOOLS DESIGN tab. 4. In the Header & Footer group, click Header and select Page 1 of ? 5. On the VIEW tab, in the Workbook Views group, click Normal to close the Page Layout View (header and footer view).

Copy the data from cell range A8:E13 on the Q1 Sales worksheet and transpose it to the cell range beginning at A8 on the Seedling Sales worksheet.

1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it. 2. Select all cell range A8:E13. (Hint: Do not include the Total column). 3. Right-click and select Copy. (Note: You can also copy the range either by clicking Copy in the Clipboard group on the Home page, OR by using the short-cut key combination CTRL + C. ) 4. At the bottom of the workbook, click the Seedling Sales worksheet tab to select it. 5. Click on cell A8. 6. Right-click and below the Paste Option section, click Transpose (4th icon). (Note: There are several alternative solutions: You can right-click and select Paste Special... and Paste Special.. again. At the bottom of the Paste Special pop-up window, click Transpose to enable the option and click OK. -OR- On the HOME tab, in the Clipboard group, click Paste and select the Transpose icon (7th icon), or at the bottom of the Paste drop-down menu select Paste Special... and in the Paste Special pop-up window click Transpose to enable the option and click OK.)

Remove the table formatting from the Small Tree Sales worksheet. Keep the cell formatting.

1. At the bottom of the workbook, click the Small Tree Sales worksheet tab. 2. Click anywhere within the table to select it (Hint: You must click within cell range A8:F18.) 3. Click on the TABLE TOOLS DESIGN tab. 4. In the Tools group, click Convert to Range. 5. When prompted, Do you want to convert the table to a normal range? Click, Yes. (Hint: When the table converts to a normal range, the autofilters will disappear and so will the TABLE TOOLS tab.

Outline both the columns and rows of the table on the Summary worksheet.

1. At the bottom of the workbook, click the Summary tab. 2. Click any cell within the table to select it. 3. On the DATA tab, in the Outline group, click Group and select Auto Outline.

Copy only the Carriers and Coolers worksheet into a new workbook. Save it in the GMetrixTemplates folder as Inventory Report.xlsx. Close the new workbook before proceeding.

1. At the bottom of the workbook, right-click the Carriers and Coolers tab, and select Move or Copy... 2. Click the Create a copy box to enable it. 3. In the To book: field, click the down-arrow and select (new book). 4. Click OK. The new workbook containing only the worksheet Carriers and Coolers should open. 5. Click the FILE tab and select Save As. 6. Browse to the GMetrixTemplates folder and save the workbook with the File name: Inventory Report.xlsx 7. Click Save. 8. Close the new Workbook you just saved.

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

Rename the Renters worksheet to Customers.

1. At the bottom of the workbook, right-click the Renters tab and select Rename. 2. Type Customers and press Enter on your keyboard to accept the change

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.

Simultaneously remove all duplicate records in the Wired Networks table.

1. Click any cell within the Wired Networks table. 2. On the DATA tab, in the Data Tools group, click Remove Duplicates. (Hint: Notice the entire table is automatically selected.) 3. Accept all defaults and click OK. (Hint: A pop-up window should confirm that 2 duplicate records were removed.) 4. 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.

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

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

Add the Subject Equipment Draft to the document properties.

1. Click the FILE tab. 2. In the bottom right corner of the Info page, click the Show All Properties link. 3. In the Subject field, type Equipment Draft. 4. Click the return arrow in the upper-left corner of the Info window to display the document.

Modify the scaling of the Sales Commissions worksheet so it fits on a single page when printed.

1. Click the FILE tab. 2. In the category column, click Print. 3. At the botton of the Settings section, click the down-arrow to change No Scaling to Fit Sheet on One Page. 4. Click the circled arrow located in the upper-left of the FILE tab to return to the Sales Commissions worksheet.

Remove all hidden properties and personal information from the workbook.

1. Click the FILE tab. 2. On the default Info page, click the Check for Issues button. 3. Select Inspect Document. (Hint: If prompted to save your changes, click Yes) 4. In the Document Inspector pop-up window, accept all of the default selections and click Inspect. 5. On the results page, next to Document Properies and Personal information, click Remove All. 6. Click Close. 7. Click the circled arrow located in the upper-left of the FILE tab to return to the Grain Production worksheet.

In cell B28 on the Profits worksheet, insert a formula that displays the number of Sales greater than 250.

1. Click the Profits worksheet tab to select that worksheet. 2. Click cell B28 to select it. 3. On the FORMULAS tab, on the far left click Insert Function. 4. In the Insert Function pop-up window, in the Search for a function: field, type COUNTIF. Click Go. 5. In the Select a function: window, select COUNTIF and click OK. 6. In the Function Arguments pop-up window for COUNTIF, type the following in the fields: Range: D4:D26 Criteria: ">250" 7. Click OK. (Hint: The completed entry in the Formula Bar should read =COUNTIF(D4:D26,">250") )

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

Modify the chart on the Profits worksheet so the Flavors are displayed as Horizontal Axis Labels and Expense and Income are the Legend Series.

1. If the Profits worksheet Is not already displayed, click the Profits 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.

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.

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 Carriers and Coolers worksheet, expand the chart data range to include the rest of the rows in the table

1. On the Carriers Coolers 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 Chart data range field, change the last value to $D$19. (Hint: the entire data range should be ='Carriers & Coolers'!$C$4:$D$19 ) 5. Click OK.

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

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.

Insert a formula into cell G4 on the Employee Bonuses worksheet that evaluates whether the amount in Parts, Accessories, or Services exceed the Quarterly Goal. For each column that exceeds the goal, apply the Quarterly Bonus Rate.

1. On the Employee Bonuses worksheet, select cell G4. (Hint: Always put your formula in the cell where you want the result to be displayed) 2. On the FORMULAS tab, in the Function Library, click Insert Function. 3. In the Search for a function: field, type Sumif and click GO. 4. In the Select a function field, click SUMIF and click OK. 5. In the SUMIF pop-up window, configure the following: Range: B4:D4 Criteria: >100000 Sum_Range: B4:D4 (Hint: You can leave this field blank and the cells in the Range field will be used by default.) 6. Click OK. 7. In the Formula Bar, to the right of the SUMIF formula, multiply it by the ""Quarterly Bonus Rate"" so it reads as follows: =SUMIF(B4:D4,">100000")*$B$17 8. Press the Enter key to accept the formula and calculate the result.

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.

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 Fundraiser worksheet, delete row 4 from the first table only.

1. On the Fundraiser worksheet, select cell range A4:I4. 2. Right-click and select Delete, and then click Table Rows

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.

Simultaneously replace all occurrences of the word Choco with Chocolate in the workbook.

1. On the HOME tab, in the Editing group, click Find & Select and choose Replace... 2. In the Find and Replace pop-up window, type the following in the fields: Find what: Choco Replace with: Chocolate 3. Click the Options button and set the Within: field to Workbook. 4. Click Replace All 5. Click Close to exit the pop-up window.

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) 5. Click OK.

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.

in the Costs worksheet, repeat the rows containing the company logo and column headings so they appear on all printed pages.

1. On the PAGE LAYOUT tab, in the Page Setup group, click Print Titles. 2. In the Page Setup pop-up window, on the Sheet tab, type the following in the fields: Rows to repeat at top: $1:$3 (Hint: You can also autopopulate this field by selecting the rows with your mouse.) 3. Click OK (Hint: You can see the result by clicking Print Titles again, then clicking Print Preview. The first page will display in the Print Preview window. At the bottom of the window, advance to the second page by clicking the Next Page arrow located right of 1 of 2. You should see the column headings displayed at the top of the table on both pages. Click the return arrow in the upper left corner of the window to go back to the spreadsheet.)

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

In cell B4 on the Q1 Sales worksheet, insert a function that joins Description and Style from the Catalog worksheet, separated by a hyphen. Include a space on both sides of the hyphen. (Example: Cross Country - Hardtail).

1. On the Q1 Sales worksheet, select cell B4. 2. Click the FORMULAS tab and click Insert Function. 3. In the Search for a function field, type the keyword, Join and then click GO. 4. In the Select a function field, select CONCATENATE. Click OK. 5. In the Function Arguments pop-up window for CONCATENATE, do the following: a. Click in the Text1 field. b. Click the Catalog worksheet tab. In the Description column, select cell D4. c. Click in the Text2 field d. Type a set of quotes containing a space, a hypen, and a space. Example: " - " e. Click in the Text3 field. f. Click the Catalog worksheet tab. In the Style column, select cell C4. 6. Click OK.

Apply the Quick Analysis tool to the data on the Q1 Sales worksheet to create a Clustered Column chart displaying sales of only Douglas Fir seedling, Giant Sequoia seedling and KMX seedling trees for each month. Apply Chart Style 14, Monochromatic Palette 7, and Quick Layout 2. Place the chart in the upper-left corner of the Seedling Sales Chart worksheet.

1. On the Q1 Sales worksheet, select cell range A8:E11. 2. In the lower-right corner of cell E11, click the Quick Analysis tool icon to open the toolbox. 3. In the Quick Analysis tool box, click the CHARTS tab. 4. Select the Clustered chart that shows each month as a column and the tree names in the legend. 5. On the CHART TOOLS DESIGN tab, in the Chart Styles group, click the More drop-down arrow to open the gallery. Select Style 14. 6. In the Chart Styles group, click Change Colors. Beneath the Monochromatic section, select Palette 7. 7. In the Chart Layouts group, click Quick Layout and select Layout 2. 8. On the CHART TOOLS DESIGN tab, in the Location group, click Move Chart. 6. In the Move Chart pop-up window, select Object in: then click the drop-down arrow and choose Seedling Sales Chart. 7. Click OK. 8. Drag the chart into the upper-left corner of the worksheet.

Enable the Total Row for the table located on the Qtr 1 worksheet.

1. On the Qtr 1 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, in the Maximum row, insert a formula in column B that returns the greatest number of successful attempts for the month of January.

1. On the Qtr 1 worksheet, select cell B17. 2. Click the FORMULAS tab. 3. In the Function Library group, click the AutoSum down-arrow and select MAX. 4. Above the worksheet, in the Formula Bar, adjust the selected cell range to include only cells B10:B14. 5. Press the Enter key to accept the formula and calculate the results. (Hint: the result displayed in cell B17 should be 632.00)

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.

Apply Style 3 to the pie chart on the Qtr 2 worksheet.

1. On the Qtr 2 worksheet, click anywhere on the chart to select it. 2. On the CHART TOOLS DESIGN tab, in the Chart Styles group, click the More drop-down arrow to open the Chart Styles gallery. 3. Select Style 3.

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.

Beginning at cell A16 on Sheet1, import the list from the comma delimited source file MoreToys.csv located in the GMetrixTemplates folder. (Accept all other defaults)

1. On the Sheet1 worksheet, click cell A16 to select it. 2. On the DATA tab, in the Get External Data group, click From Text. (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 From Text. Once this is complete you can use the Get Data drop-down to select the Legacy Wizards and select From Text.) 3. Browse to the GMetrixTemplates folder. 4. Select the MoreToys.csv 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 and click the Next button. 6. In the Text Import Wizard - Step 2 of 3 pop-up, change the Delimiter from Tab to Comma by clicking on the box next to Tab to clear it, then clicking on the box next to Comma to select it. (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 Small Tree Sales worksheet, insert a formula in cell F20 that uses a built-in Excel function to return the value of the smallest value in the Total column.

1. On the Small Tree Sales worksheet, select cell F20. 2. Click the FORMULA tab. 3. In the Function Library group, click the AutoSum down-arrow and select MIN. 4. In the Formula Bar adjust the selected cell range to include only cells F9:F18. 5. Press the Enter key to accept the formula and calculate the results. (Hint: The formula inserted into cell F20 should be =MIN(F9:F18) and the result displayed in cell F20 should be 550.)

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 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 and select Insert.

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

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 )

In cell F4 on the Parts worksheet, insert a line that graphs the trend of sales from Jan through Mar.

1. Select cell F4 on the Parts worksheet. 2. Select the INSERT tab. 3. In the Sparklines group, click Line. 4. In the Create Sparklines pop-up window, configure the fields as follows: Data range: B4:D4 Location range: $F$4 5. Click OK.

In the Sales Commissions worksheet, insert a formula in cell G4 that references the data in the Total Sales column and the Commission Rate in cell J3 to calculate the values in the Commission column. Copy the formula into all the cells in the Commission column.

1. Select cell G4. 2. In the Formula Bar, type the following: =F4*J$3 (Hint: The result should be $ 4,599) 3. In the lower-right corner of the G4 cell, click the small square (a.k.a, the Fill Handle) and drag down the Commission column to copy the formula into cells G5 through G10. (Hint: If the copied formula fails to display a value, check your absolute referencing to cell J3. You must include the $ symbol in front of the row reference to prevent relative referencing which changes the row value.)

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 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) 6. On the VIEW tab, in the Workbook Views group, click Normal to return to normal view.