Excel and Powerpoint test

Emerge excel 2010

Cumulative Project 2

Training Solutions

Skills

2 SAM PROJECTS 2010 – CENGAGE LEARNING

SAM PROJECTS 2010 – CENGAGE LEARNING 3

Save a workbook with a new name

Change fill color

Add borders

Change the font

Change the font size

Change the font color

Apply bold

Enter formulas

Create a formula using the SUM function

Fill adjacent cells with formulas

Copy cell contents

Merge cells and center their content

Apply styles

Modify vertical text alignment

Rotate text

Use the Format Painter

Center cell contents

Apply Comma Style number format

Modify the number of decimal places

Format a range as a table

Adjust table style options

Cancel a filter

Modify worksheet orientation

Control page breaks

Add print titles

Add footers to worksheets

Project overview

Ethel Robinson is the Curriculum Director for Training Solutions Learning Centers, a growing franchise that provides tutoring, seminars, training, and classes to prepare workers for today’s jobs. Based in El Paso, Texas she hopes to expand the centers across the country by analyzing which divisions are most successful. She’s been tracking the enrollment for the three main learning divisions: Office Technology, Auto Repair, and Plumbing and Electric in the five centers. She created a workbook that contains the enrollment figures from the past year for the five existing centers and wants you to help format the report.

STUDENT start FILE

Emerge_Excel2010_ CP2a_FirstLastName_1.xlsx (Note: Download your personalized start file from www.cengage.com/sam2010)

Instructions

Open the file Emerge_Excel2010_ CP2a_FirstLastName_1.xlsx and save the file as Emerge_Excel2010_CP2a_FirstLastName_2.xlsx before you move to the next step. Verify that your name appears in cell B4 of the Documentation sheet. (Note: Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site.)

In the Information sheet, set the background color for all the cells in the worksheet to Purple, Accent 4, Lighter 40% and then set the background color for the range B3:B5 to standard Yellow. Add a border line around the range B3:B5.

Change the font of cell A1 to the (Headings) font of the current theme, change the font size to 28 points, change the font color to standard Yellow, and then bold the text. Change the font size of the range A3:A5 to 12 points, and then bold and italicize the text.

Go to the Enrollment Report worksheet. For all three learning divisions, add formulas to calculate the total enrollment for each month, the total enrollment for each learning center (as identified by city), and the total enrollment for each learning division overall for the year.

Merge and center the range A1:H1, apply the Heading 1 cell style, and then increase the font size to 26 points. Merge and center the range A2:H2, apply the Title cell style, and then increase the font size to 20 points. Change the font color to standard Purple for both cells.

Merge and center the range A3:A16, set the alignment to Align Text Right, rotate the text vertically by using the Rotate Text Up command, apply the Accent4 cell style, increase the font size to 20 points, and then italicize the text. Use the Format Painter to apply the same formatting to the two ranges A18:A31 and A33:A46.

Center the text in the range C3:H3. Format the range C4:H16 using the Number group on the Home tab to include thousands separators (,) and no decimal places. Use the Format Painter to copy the formatting in C3:H16 to the two ranges C18:H31 and C33:H46.

Format the range B3:H16 as a table by applying the Table Style Medium 5 table style. Format the ranges B18:H31 and B33:H46 as tables using the same table style.

For all three tables, in the table style options, leave the Header Row and Banded Rows check boxes selected, and also select the First Column and Last Column check boxes for emphasis. Remove the filter arrows for all three tables.

Format the ranges B16:H16, B31:H31 and B46:H46 by setting the fill color for these Total rows to standard Yellow.

In the Enrollment Report worksheet, set the page orientation to Landscape, insert manual page breaks at cells A18 and A33. Modify the page setup so that the first two rows of the worksheet are repeated at the top of every printed page.

Using the appropriate buttons in the Header & Footer Elements group on the Header & Footer Tools Design tab, display the filename in the left footer, display Page page number of number of pages in the center footer, and display the current date in the right footer.

Your worksheet should look like the Final Figure below. Save your changes, close the workbook and exit Excel. Follow the directions on the SAM Web site to submit your completed project.

FINAL FIGURE