Excel: 3D Workbooks

Updated 2017.02.21

Open the Southern Region Sales workbook. All references below to "all four worksheets" below refer to the Q1, Q2, Q3, and Q4 worksheets. 30 points total.

1.           Save the file with the name Southern Region Sales-FirstName-LastName.xlsx using your first and last name.

2.           On all four worksheets, merge and center the text in A1 over columns A:E.

3.           On all four worksheets, change the cell style of cell A1 to the Title cell style. Make sure that row 1 is tall enough for all of the text to be seen.

4.           On all four worksheets, change the cell style of all column headings (A2:E2) to Heading 3.

5.           On all four worksheets, right-align cells B2:E2 (doesn't include column A).

6.           On all four worksheets, in B8 through D8, put a formula that will sum the sales numbers for each column.

7.           On all four worksheets, in E3:E8, put a formula that will sum the sales numbers for each row.

8.           On all four worksheets, in B3:E8, format all numbers with the Accounting number format and reduce the number of decimal places to 0.

9.           On all four worksheets, in B8 through E8, format the cells with the Total cell style.

10.       Change the color of the sheet tab for each worksheet: Q1: Red, Q2: Green, Q3: Blue, Q4: Purple. Use the named colors on the Standard Colors row.

11.       On all four worksheets, change the font color of cell A1 to match the tab color.

12.       Create a new worksheet called Summary and copy everything from the Q1 worksheet to it.

13.       Move it to the right of the Q4 worksheet.

14.       Delete B3:D7. Put formulas that will add all of the numbers on the other four worksheets and put the sums in the corresponding locations in your Summary worksheet (3 points).

15.       On the Summary worksheet, put Summary in A1. The cell style for A1 should still be Title.

16.       For all five worksheets, display the page number and the total number of pages in the center part of the footer. For example: Page 2 of 5 (2 points).

17.       For all five worksheets, put your name in the right part of the header.

18.       For all five worksheets, set the page orientation to landscape.

19.       On all five worksheets, center the worksheet horizontally on the print preview page.


20.       Create a column chart (clustered 2D) from the data on the Summary worksheet from A2:D7.

21.       Use the Move Chart button to put the chart on a separate worksheet.

22.       Change the chart style to Style 8.

23.       Display a legend on the right side of the chart.

24.       Change the title to Southern Region Sales.

25.       Change the y-axis title to Total Sales. Delete any x-axis title.

26.       Change the font size for the legend, the y-axis title, the numbers on the y-axis, and the words on the x-axis to 12 points.

27.       Change the color palette for the chart to the Color 4 palette (gives green, blue, and yellow bars).