Excel Associate 2019 Skill Review 3

Add the text Sunday to cell A2 and enumerate the days of the weeks 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. 5. Click and drag to cell A8 6. Release the mouse button

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

1. Click cell B1 2. Type January and hit the ENTER key 3. Click on cell B1 again 4. Move your cursor over the Autofill handle at the lower right corner of the cell. 5. Click and drag to cell M1 6. Release the mouse button

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, clickLine. 3. In the Create Sparklines dialog box, forData 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 Shape Format contextual 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 Design contextual tab, Layouts group, click the Pyramid List layout. 3. In the SmartArt Design contextual tab, SmartArt 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 Format contextual tab, Adjust group, click the drop down for Corrections and choose Sharpen: 50%. 3. In the Picture Format contextual 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 Design contextual 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 F3 and type the formula =E3/$E$8. 3. Hit the ENTER key. 4. Click on cell F3 again. 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 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 the 2010 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.

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 within the next drop-down and type lux in the last box. 8. Click OK.

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

1. In the File tab choose Open. 2. Choose theQuarterly_Earnings_Overview.xlsx from your 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.

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.

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:$4 3. 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.

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 forFormat. 4. In the Visibility section, choose Hide & Unhide > Hide Rows.

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 Add Header 3. Select the left section of the header. 4. In the Header & Footer contextual tab, Header & Footer Elements group, select File Name 5. Select the right section of the header 6. In the Header & Footer contextual tab, Header & Footer Elements group, select SheetName 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 in that order

1. In the View, locate the Workbook Views group and click Page layout 2.Place your cursor anywhere in the header or footer to make the Header & Footer contextual tab show 3. In the Header & Footer contextual tab, locate the Header & Footer group and click the Footer dropdown 4. Select the option that includes the author's name, page, number, and current date in that order

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

In the 2010 Sales worksheet, modify the size of the Title style font to 24 and 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 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, format the data range A2:G26 as a table using the Table Style Dark 5table 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 5 from the drop-down list. 4. Click OK.

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 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 D3 again. 7. 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. 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.

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.

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 Design contextual 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. 15. 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.

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

1. Open the Commission Rates worksheet. 2. Select cells A2: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 theFormat 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:C25 3. In the Home tab, Number group, click the Percent Style button.

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 Format contextual tab, Adjust group, click Remove Background. 4. In the Background Removal contextual tab, Close group, click Keep Changes.

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 Links. 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 Links. 4. In the Link section, select Email Address. 5. For Email Address, type [email protected]. For Subject, type Sales Summary. 6. Click Ok.

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

1. Open the Summary worksheet.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 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 cellsD3:D7 6. Hit the ENTER key.

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

1. Open the Top Performers worksheet. 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 2011. Select cellsD3:D7 6. Hit the ENTER key.

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 Cells C3: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, type 200000 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, type 10000 and choose Red Border in the drop-down list. 7. Click OK.

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.

Split the worksheet at cell A5 into two horizontal panes.

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

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 textMarginal. 3. Hit the ENTER key.

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.

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

1. Select cells A5:D106 2. In the Home tab, Alignment group, click the center button

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.

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

1. Select cells A5: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.

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

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

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 dropdown 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 for Format. 3. In the Visibility section, choose Hide & Unhide > Hide Columns.

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.