Excel Core Skill Review 3

Copy the Summary worksheet from the Quarterly_Earnings_Overview.xlsxworkbook, found in your GMetrix Templates folder, and place it as the last worksheet in the Quarterly_Earnings.xlsxworkbook.

1. In the File tab choose Open. 2. Choose the Quarterly_Earnings_Overview.xlsxfrom you GMetrix Templates folder and click the Open button. 3. In the Home tab, Cells group, click the Format button. Under Organize Sheet, select Move or Copy Sheet... 4. In the To book drop down, chooseQuarterly_Earnings.xlsx, in the Before sheet list, select (move to end). 5. Select the Create a copy check box and click OK.

Remove all comments, annotations, personal information and document properties in the workbook.

1. In the File tab, select the Info option. 2. Select Check for Issues. In the drop down menu, choose Inspect Document. 3. Make sure that only Comments and Annotations, and Document Properties and Personal Information are selected. Click Inspect. 4. Click Remove All for Comments and Annotations, and Document Properties and Personal Information. 5. Click Close.

Insert a header that contains the filename on the left side and sheet on the right side. Close the header before grading.

1. In the View tab, Workbook Views group, select the Page Layout button. 2. On the first page of the worksheet, select Click to add header. 3. Select the left section of the header. 4. In the Header & Footer Tools/Design tab, Header & Footer Elements group, select File Name. 5. Select the right section of the header. 6. In the Header & Footer Tools/Design tab, Header & Footer Elements group, select Sheet Name. 7. Click anywhere outside of the header to close.

Set the zoom to 150% and save as a custom view named Page Zoom.

1. In the View tab, Zoom group, click the Zoom button. 2. Click the Custom radio button and type 150 in the text box. 3. In the View tab, Workbook Views group, click the Custom Views button. 4. Click Add... 5. Type Page Zoom in the Name text box and click OK.

Insert a footer at the bottom that contains the author, page number and current date.

1. In the View tab, locate the Workbook Views group, and click Page Layout. 2. Place your cursor anywhere in the header or footer to make the Header & Footer Tools/Design tab show. 3. In the Header & Footer Tools/Design tab, locate the Header & Footer group, and click the Footer drop down. 4. Select any option that includes the author's name, page number, and current date.

Paste only the values from the cell range A1:G26 in the 2009 Sales worksheet to cell range A1:G26 in the 2009 Sales (Corrected) worksheet.

1. Open the 2009 Sales worksheet. 2. Select cells A1:G26. In the Home tab, Clipboard group, select Copy. 3. Open the 2009 Sales (Corrected) worksheet and select cell A1 4. In the Home tab, Clipboard group, select the drop down for Paste. Select Values.

Copy only the formatting from the cell range A1:G26 in the 2009 Sales worksheet to the 2010 Sales worksheet.

1. Open the 2009 Sales worksheet. 2. Select cells A1:G26. In the Home tab, Clipboard group, select Copy. 3. Open the 2010 Sales worksheet.4. In the Home tab, Clipboard group, select the drop down for Paste. Select Formatting.

Using the data in the Combined Sales worksheet, add a Pie chart including labels that presents the combined sales for each representative.

1. Open the Combined Sales worksheet. 2. Select cells E2:E7. 3. In the Insert tab, Charts group, click the drop down for Pie. Choose Pie in the 2D Pie section. 4. In the Chart Tools/Design tab, Data group, click the Select Data button. 5. In the Select Data Source dialog, in the Horizontal (Category) Axis Labels section, click Edit. 6. Select cells A3:A7. 7. Click OK twice.

In the Commission Rates worksheet, for the cell range A2:C6, define the named range Bonus. In the Combined Sales worksheet, add a right-most column named Bonus Pay and calculate the bonus amount for each sales representative using the Bonus named range.

1. Open the Commission Rates worksheet. 2. Select cell range A2:C6 3. In the Formulas tab, Defined Names group, click Defined Name. 4. In the New Name window enter Bonus for the name. Click OK. 5. Open the Combined Sales worksheet. 6. Select cell F2. 7. In the Home tab, Cells group, click the Insert button and choose the Insert Sheet Columns. 8.Type Bonus Pay and hit ENTER. 9. Select cell F3 10. In the Formulas tab, Function Library group, select Lookup & Reference. Select VLOOKUP. 11. In the Lookup_value text box, type E3. In the Table_array text box, type Bonus, and in the Col_index_num text box, type 3. 12.Click Ok. 13. Select cell F3. 14. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 5. Click and drag down to cell F7. 16. When you release the mouse button, the cell range should now display bonus pay amount for the other representatives.

In the Top Performers worksheet, determine the highest sales number for 2010 in cell B2.

1. Open the Top Performers worksheet. 2. Select cell B2. 3. In the Formulas tab, Function Library group, click the drop down for AutoSum. Select Max. 4. Ensure that your cursor is blinking between the parentheses in the text=MAX(). 5. Open the Sales 2010. Select cells D3:D7 6. Hit the ENTER key.

Freeze the top four rows so that they are always visible when scrolling through the worksheet.

1. Select cell A5. 2. In the View tab, Window group, click the Freeze Panes button and choose the Freeze Panes option from the drop-down list.

Add the text Sunday to cell A2 and enumerate the days of the week from cell A3 to A8.

1. Click cell A2. 2. Type Sunday and hit the ENTER key. 3. Click on cell A2 again. 4. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 5. Click and drag to cell A8. 6. Release the mouse button. The cell range should now display the days of the week.

Change the worksheet view to Page Layout and hide rows 2 and 3.

1. In the View tab, Workbook Views group, click the Page Layout button. 2. Select Rows 2 and 3. 3. In the Home tab, Cells group, click the drop down for Format. 4. In the Visibility section, choose Hide & Unhide > Hide Rows.

In the Combined Sales worksheet, add a new column with the title % Total between the % Change and Sales columns.

1. Open the Combined Sales worksheet. 2. Select cell E2. 3. In the Home tab, Cells group, click the Insert button. Select Insert Sheet Columns. 4. Click on cell E2 (now an empty cell in the new column) if it is not already selected. 5. Type % Total and hit the ENTER key.

In the Summary worksheet, determine the total units sold by flavor for each quarter based on the values in the 2009 Sales and 2010 Sales worksheets.

1. If the Summary worksheet is not open, click on the Summary tab. 2. Select cell B3. 3. In the Formulas tab, Function Library group, click the AutoSum button. 4. Ensure that your cursor is blinking between the parentheses in the text=SUM(). 5. Open the 2009 Sales worksheet. Click once on cell B3. 6. Hold down the SHIFT key and then open the2010 Sales worksheet. This will create a 3-D reference across both worksheets. 7. Hit the ENTER key and then click once on cell B3. 8. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 9. Click and drag down to cell B25. 10. When you release the mouse button, the cell range should now display the units sold in the first quarter of 2009 and 2010 for each flavor. 11. Move your cursor over the AutoFill handle at the lower-right corner of cell B25. The pointer should change from a thick white cross to a thin black cross. 12. Click and drag down to cell E25. 13. When you release the mouse button, the cell range should now display the units sold in the remaining quarters of 2009 and 2010 for each flavor.

In the Top Performers worksheet, determine the highest sales number for 2011 in cell B3.

1. If the Top Performers worksheet is not open, click on the Top Performers tab. 2. Select cell B3. 3. In the Formulas tab, Function Library group, click the drop down for AutoSum. Select Max. 4. Ensure that your cursor is blinking between the parentheses in the text=MAX(). 5. Open the Sales 2011worksheet. Select cellsD3:D7.6. Hit the ENTER key.

In the 2010 Sales worksheet, format the data range A2:G26 as a table using the Table Style Dark 5 table style.

1. Open the 2010 Sales worksheet. 2. Select cells A2:G26. 3. In the Home tab, Styles group, click the Format as Table button and choose Table Style Dark 5from the drop-down list. 4. Click OK.

Change the page orientation to landscape.

1. In the Page Layout tab, Page Setup group, click the Orientation button and choose the Landscape option from the drop-down list.

Filter the table to display data from only the Oncology and Gastrointestinal categories and those pharmaceuticals that end with the suffix ex or lux.

1. In the Data tab, Sort and Filter group, select Filter. 2. Click the drop-down arrow in cell A2. 3. Click to clear the (Select All) check box, and then click to select the Gastrointestinal and Oncology check boxes. 4. Click OK. 5. Click the drop-down arrow in cell B2. 6. Click the Text Filters > Ends With... option. 7. In the Custom AutoFilter dialog box, type ex in the first box. Click Or. Choose ends with in the next drop-down and type lux in the last box. 8. Click OK.

Set the labels in the fourth row to repeat on each page.

1. In the Page Layout tab, Page Setup group, click the Print Titles control. 2. In Rows to repeat at top, type $4:$43. Click OK.

Ensure that row and column headings and gridlines display when printing the worksheet.

1. In the Page Layout tab, Sheet Options group, select the Print check boxes for Gridlines and Headings.

In the 2010 Sales worksheet, modify the size of the Title style font to 24and apply the Title style to cell A1.

1. Open the 2010 Sales worksheet. 2. In the Home tab, Styles group, click the drop down for Cell Styles. 3. Under Titles and Headings, right click Title and select Modify... 4. In the Style dialog box, click Format... 5. In the Format Cells dialogue box, Font tab, select 24 from the Size list. 6. Click OK twice. 7. Click cell A1. 8. In the Home tab, Styles group, click the drop down for Cell Styles. Under the Titles and Heading section, select Title.

In the Combined Sales worksheet, correct the formula in the cell range D3:D7 so that it calculates the percentage of change from Sales 2010 to Sales 2011.

1. Open the Combined Sales worksheet. 2. Select cell D3. 3. In the Formula Bar, add an opening parenthesis after the text = and a closing parenthesis before the text /. 4. Change 'Combined Sales'!E3 to 'Sales 2010'!D3 5. Hit the ENTER key. 6. Click on cell D3again. 7. Move your cursor over the Auto Fill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 8. Click and drag down to cell D7. 9. When you release the mouse button, the cell range should now display change percentage for the other representatives.

Define the name Bonus for the cell range A2:C6 in the Commission Rates worksheet.

1. Open the Commission Rates worksheet. 2. Select cellsA2:C6. 3. In the Formulas tab, Defined Names group, select Define Name. 4. For Name: type Bonus. Click Ok.

Save the entire workbook as a PDF file in your GMetrixTemplates folder, omitting document properties and tags.

1. Open the File tab and choose Export. 2. In File Types, choose Create PDF/XPS Document and click Create PDF/XPS. Make sure you navigate to the GMetrixTemplates folder. 3. In the Publish as PDF or XPS dialog box, click Options. 4. In the Options dialog box, under Publish what, click Entire Workbook. Clear the check boxes Document properties and Document structure tags for accessibility. 5. Click OK. 6. Click Publish.

In the Flavor Cost worksheet, apply the formatting from cell A27 to A1 and from A28 to the cell range A2:E2 and A3:A25.

1. Open the Flavor Cost worksheet. 2. Select cell A27. 3. In the Home tab, Clipboard group, select Format Painter. 4. Select cell A1 to apply the formatting. 5. Select cell A28. 6. In the Home tab, Clipboard group, double-click the Format Painter button. 7. Select A2:E2.8. Select A3:A25.9. Hit the ESC key to exit the Format Painter.

In the Flavor Cost worksheet, format data in the Markup column to display as percentages.

1. Open the Flavor Cost worksheet. 2. Select cells C3:C253. In the Home tab, Number group, click the Percent Style button.

Split the worksheet at cell A5into two horizontal panes.

1. Select cell A5. 2. In the View tab, Window group, click the Split button.

Hide the label and data in the San Diego Satellite Office rows.

1. Select cells A48:G65 2. In the Home tab, Cells group, click the drop down for Format. 3. In the Visibility section, choose the Hide & Unhide > Hide Rows option.

Remove all duplicates from the table based on the values in the Year and Value columns.

1. Select cells A5:D106. 2. In the Data tab, Data Tools group, click Remove Duplicates. 3. In the Columns list, clear the Variable and Variant check boxes. 4. Click OK twice.

With the cell range C3:J26, apply conditional formatting using blue gradient data bars.

1. Select cells C3:J26. 2. In the Home tab, tyles group, click the drop down for Conditional Formatting and choose Data Bars > Gradient Fill > Blue Data Bar.

Sort the table data by the Variant column and then by the Year column in ascending order.

1. Select cellsA5:D106. 2. In the Data tab, Sort & Filter group, click Sort. 3. In the Sort dialog box, from the Sort by drop down, choose Variant. Click Add Level. From the Then by drop down, choose Year. 4. Click Ok.

Insert a new worksheet named 2009 Sales (Corrected) between the 2009 Sales and 2010 Sales worksheets.

1. Select the 2010 Sales worksheet. 2. In the Home tab, Cells group, select the drop down for Insert. Select Insert Sheet. 3. Double-click on the new worksheet's tab named Sheet1. 4. Type 2009 Sales (Corrected)and hit the ENTER key.

Hide the label and data in the Fax, Ext and Mobile columns.

1. Select the column D heading, then select F and G while holding the CTRL key. 2. In the Home tab, Cells group, click the drop down forFormat. 3. In the Visibility section, choose Hide & Unhide > Hide Columns.

Add the text January to cell B1 and enumerate the months of year from cell C1 to M1.

1. Click cell B1. 2. Type January and hit the ENTER key. 3. Click cell B1 again. 4. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 5. Click and drag to cellM1. 6. Release the mouse button. The cell range should now display the months of the year.

Remove the comment on cell A2 and replace it with the comment Redundant variants removed.

1. Click on cell A2. 2. In the Review tab, Comments group, click the Delete button. 3. In the Review tab, Comments group, click the New Comment button. 4. Type Redundant variants removed in the comment.

Using Autofill, add a Line sparkline to column L using the data in columns C through J.

1. Click on cell L3. 2. In the Insert tab, Sparklines group, click Line. 3. In the Create Sparklines dialog box, for Data Range type C3:J3. 4. Click OK. 5. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 6. Click and drag to cell L27.

Rotate the direction of the arrow shape 90* Right.

1. Click on the arrow shape to select it. 2. In the Drawing Tools/Format tab, Arrange group, click the drop down for Rotate and choose Rotate Right 90*.

Modify the pyramid SmartArt to use the Pyramid List layout and Sunset Scene style.

1. Click on the pyramid SmartArt to select it. 2. In the SmartArt Tools/Design tab, Layouts group, click the Pyramid List layout 3. In the SmartArt Tools/Design tab, Styles group, click More at the bottom-right. 4. In the 3-D section, choose the Sunset Scene style.

In the Summary worksheet, sharpen by 50% and apply the Watercolor Sponge artistic effect to the image.

1. Ensure that the image is selected so that the Picture Tools display in the ribbon. 2. In the Picture Tools/Format tab, Adjust group, click the drop down for Corrections and choose Sharpen: 50%. 3. In the Picture Tools/Format tab, Adjust group, click the drop down for Artistic Effects and choose Watercolor Sponge.

Move the chart to a new worksheet named Sales Chart in the workbook.

1. Ensure the chart is selected in the Combined Sales worksheet. In the Chart Tools/Design tab, Location group, click Move Chart. 2. In the Move Chart dialog box, select New sheet and type Sales Chart in the associated text box. 3. Click OK.

In the Combined Sales worksheet, calculate the sales percentage for each representative in the % Total column and change the cell formatting to Percentage.

1. If the Combined Sales worksheet is not open, click on the Combined Sales tab. 2. Click on cell F3and type the formula =E3/$E$8. 3. Hit the ENTER key. 4. Click on cell F3again. 5. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 6. Click and drag down to cell F7. 7. When you release the mouse button, the cell range should now display sales percentage for the other representatives. 8. Select the cells F3:F8. 9. On the Home tab, Number group click % symbol.

In the 2010 Sales worksheet, apply the Heading 1 cell style to cell A1.

1. Open the 2010 Sales worksheet. 2. Select cell A1. 3. In the Home tab, Styles group, click the drop down for Cell Styles. 4. In the Cell Styles drop-down list, under Titles and Headings, select Heading 1.

In the 2010 Sales worksheet, merge and center the content in the top row from column A to G.

1. Open the 2010 Sales. 2. Select cells A1:G1. 3. In the Home tab, Alignment group, select Merge & Center.

In the Summary worksheet, link the text 2009 Sales to the 2009 Sales worksheet and 2010 Sales to the 2010 Sales worksheet.

1. Open the Summary worksheet. 2. Select cell A28.3. In the Insert tab, Links group, select Hyperlink. 4. Under Link to, choose Place in This Document. 5. Under the Cell Reference category, in the Or select a place in this document list box, click on the text'2009 Sales'. 6. Click Ok. 7. Select cell A29. 8. Repeat steps 3 and 4. 9. Under the Cell Reference category, in the Or select a place in this document list box, click on the text'2010 Sales'. 10. Click OK.

In the Summary worksheet, apply a hyperlink to If you have any questions, please email the sales department, with the e-mail address [email protected] and subject Sales Summary.

1. Open the Summary worksheet. 2. Select cell A30. 3. In the Insert tab, Links group, select Hyperlink. 4. In the Link section, select Email Address. 5. For Email Address, [email protected]. For Subject, type Sales Summary. 6. Click Ok.

In the Summary worksheet, remove the background of the ice cream image.

1. Open the Summary worksheet. 2. Click on the ice cream image to select it. 3. In the Picture Tools/Format tab, Adjust group, click Remove Background. 4. In the Picture Tools/Removal tab, Close group, click Keep Changes.

Set the tab color of the Summary worksheet to Red, Accent 2, Lighter 40%.

1. Open the Summaryworksheet.2. In the Home tab, Cells group, click the Format button. Under Organize Sheets, select Tab Color > Theme Colors > Red, Accent 2, Lighter 40%.

In the cell range C3:J26, apply conditional formatting so that cells that contain values over 200,000 display with green fill and text and values under 10,000 display with a red border.

1. Select CellsC3:J26. 2. In the Home tab, Styles group, click the drop down for Conditional Formatting and choose Highlight Cells Rules > Greater Than... 3. In the Greater Than dialog, type200000 and choose Green Fill with Dark Green Text in the drop-down list. 4. Click OK. 5. In the Home tab, Styles group, click the drop down for Conditional Formatting and choose Highlight Cells Rules > Less Than... 6. In the Less Than dialog box, type10000 and choose Red Border in the drop-down list.7. Click OK.

Correct the function in the Overall column. The values Profitable or Marginal should display, depending on whether the quarterly sales exceed research costs.

1. Select cell K3. 2. In the Formula Bar, replace the comma after SUM(D3,F3,H3,J3) with a greater than sign >. Add a quote " after the text Profitable and before the text Marginal. 3. Hit the ENTER key.

Format the cell range A5:D106 to use center horizontal alignment.

1. Select cellsA5:D106. 2. In the Home tab, Alignment group, click the Center button.

Set the print area to include only cells in the Verigon Pharmaceuticals Quarterly Earnings table.

1.Select cells A1:K27 2. In the Page Layout tab, Page Setup group, click Print Area and choose the Set Print Area option from the drop-down list.