1

Start Excel. Download and open the file named Student_Excel_02_SA_Sales_AS.xlsx. Save the file as Last_First_Excel_02_SA_Sales_AS.

0

2

Format the column labels with Wrap Text, Middle Align, and Center alignment.

3

3

In row 16, insert the column totals. Format the range B16:E16 with the Total cell style. AutoFit the width of columns A:F.

10

4

In cell F4, enter a formula that provides the Item Total as a Percent of Store Total. In the formula, use an absolute cell reference for the appropriate cell.

5

5

In cell F4, format the result with the Percent Style and display two decimal places. AutoFill the formula down through cell F15.

8

6

In the range B18:D18, use a function to calculate the average sales amount for each store.

9

7

In the range B19:D19, use a function to calculate the lowest sale amount for each store.

9

8

In the range B20:D20, use a function to calculate the highest sale amount for each store.

9

9

Select the nonadjacent ranges A3:A15 and E3:E15 and insert a 2-D Pie chart. Move the chart to a new sheet named Sales Chart.

10

10

Change the layout of the pie chart to Layout 5. Change the title to Garden Sales. Change the data labels to display both the Category Name and the Percentage. Change the position of the data labels to Outside End. Change the font size of the data labels to 12 and Bold.

Note, Mac users, select the fifth layout (second column, second row) in the gallery.

10

11

In the April worksheet, insert a Line chart based on the range A3:D15. Move and resize the chart so that top left corner is just inside cell A22, and the lower right corner is inside cell F36.

10

12

Change the layout of the line chart to Layout 4. Change the chart style to Style 9.

Note, Mac users, select the last layout in the gallery.

5

13

Insert three rows at the top of the worksheet. Insert the text Tom’s Place Sales as WordArt using the Gradient Fill, Gray. Change the font size to 34. Move the WordArt to the top of the worksheet, and center the text box in the range A1:E3. (ensure that the top right corner is at the edge of the E:F border)

9

14

Scale to fit the Height of the page to 1 page.

2

15

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Chart; April. Save and close Last_First_Excel_01_SA_Visitors_HW.xlsx. Exit Excel. Submit the file as directed.

1