cis

Project 1 - Initial Tasks

Overview

You just started working at a car dealership. You need to produce an Excel workbook containing automobile information.

Tasks

☐ 1

Open a new blank workbook.

☐ 2

Import autosales.csv, Comma Delimited, in cell A1.

☐ 3

Change the Theme to: Ion.

☐ 4

Rename Sheet1 to American; tab color: Orange, Accent 2, Lighter 40%.

☐ 5

Add & Rename the following:

Sheet2: British; tab color: Orange, Accent 2, Lighter 60%

Sheet3: Japanese; tab color: Orange, Accent 2, lighter 80%

Sheet4: Summary; tab color: Green, Accent 4

☐ 6

Move Summary so that it is to the left of American.

☐ 7

Move the following data:

On American: G3:I21 to British A1:C19

A24:E39 to Japanese A1:E16

A47:B53 to Summary D1:E7

☐ 8

On the British sheet add two rows above row one.

☐ 9

Copy A1:A2 from the American sheet to A1:A2 on the British sheet.

☐ 10

Add a column between column A and column B on the American, British, and Japanese worksheets and set its width to 14.

☐ 11

On the American, British, and Japanese sheets, in B4, type Region; in each cell B5 to B9, enter South; in B10:B13, Mid-Atlantic; in B14:B16, New England

(NOTE: Do NOT merge cells.)

Project 2 - American Worksheet

Overview

You want to total the data and add formatting to the worksheet so it will be eye catching.

Tasks

☐ 1

In the American sheet apply the Title style to cell A1, and the Heading 2 style to the column headings. Apply a Total style for the National Total row.

☐ 2

Insert a Hyperlink in cell A3 that links to E7 on the Summary sheet.

☐ 3

In E4, enter State Total; in F4, % National Total. Make sure they are formatted the same as adjacent cell D4.

☐ 4

Make the width of column A: 20;

E and F: AutoFit

☐ 5

In E5, use a function to total Ford and GM sales for Florida and extend for all the states.

☐ 6

In the National Total row, calculate the sum of the State Totals in column E.

☐ 7

In F5 calculate the percent the Florida Total is of the National Total and extend for all the states.

☐ 8

Format: % National Totals as Percent, 1 decimal place;

Format the Ford, GM, State Total, and National Total as Currency with no decimal places.

☐ 9

Center A1 over A1:F1 and center A2 over A2:F2.

☐ 10

Format the Ford and GM sales with the Red-Yellow-Green Color Scale.

☐ 11

Name the range E5:E16, AmericanTotals

Project 3 - British Worksheet

Overview

Add formatting and totals to the British worksheet to make it more robust and useful.

Task

☐ 1

Insert a table for the range A4:D16; Keep the names of the existing columns.

Format: Table Style Medium 6;

Add a First Column style option.

☐ 2

Add the heading Totals in E4.

☐ 3

Add a Total Row and change its label to Average; change the calculation for Rolls Royce to Average; for Jaguar to Average; add to the Region field a Count.

☐ 4

Calculate in the Totals column the sum of Jaguar and Rolls Royce sales for Florida.

☐ 5

Use the total row to add an Average to Totals.

☐ 6

Increase the width of columns A:E to 20 each.

☐ 7

Sort the table alphabetically by State (A-Z).

☐ 8

Move C19:D19 to B19:C19.

☐ 9

In B20, use a function to calculate the Average of the Totals for the South Region only;

In B21 the average of the Totals for the Mid-Atlantic Region ; and in B22 the average of the Totals for New England.

☐ 10

In C20, calculate the difference between the National Average (E17) and the Southern Average (B20) so that it’s positive if the Southern average is higher; copy to C21:C22.

☐ 11

Format C5:E17 and B20:C22 for Comma style, no decimal places.

☐ 12

Wrap the text in C19.

☐ 13

Type the text: Max. Regional Avg. in cell D18 and find the largest of the regional averages using a function in E18.

Project 4: Japanese Worksheet

Overview

Add automatic subtotals to the Japanese worksheet to make it more informative.

Tasks

☐ 1

Sort A4:F16 alphabetically by Region.

☐ 2

Subtotal A4:F16 to display the sum for each of the four manufacturers for each region.

☐ 3

Add another subtotal that shows the average of each of the four manufacturers for each region. (Do not replace.)

☐ 4

Display only Regional Averages and Regional Totals and the Grand Average and Grand Total.

☐ 5

Set a range name for C24:F24 of JapaneseTotals

☐ 6

Make sure all the data in column B can be seen.

Project 5: Summary Sheet

Overview

Add totals, a picture, and a chart to the Summary worksheet to add value and interest.

Tasks

☐ 1

In E5, calculate the sum of the JapaneseTotals named range.

☐ 2

In E6, use a function to calculate the sum of E5:E16 from the British sheet.

☐ 3

In E7, create a Reference to cell E17 on the American sheet.

☐ 4

Insert the picture file car and trailer and make it 2 inches in height. Crop it so only the car is visible.

☐ 5

Re-color the picture so that it is Green, Accent color 4 Dark; add the Photocopy artistic effect.

☐ 6

Position the picture in the upper left corner of the worksheet. (NOTE: Exact position does not matter.)

☐ 7

Type your first and last name in E15.

☐ 8

Use the Total Sales data to insert a 2-D Pie chart.

Title the chart: Auto Sales by Country

Name the chart: PercentAutoSales

Provide Alt Text Title: Pie chart comparing American, British, Japanese auto sales

☐ 9

Apply Chart Style 6.

Add Data Labels at the Center position that show the Value; choose the Number format with commas and no decimal places.

☐ 10

Position the chart below the data so it completely covers your name in E15. (NOTE: Exact position does not matter.)

Project 6: Entire Workbook

Overview

Prepare the workbook to be distributed to the Executive Team.

Tasks

☐ 1

Replace S. Carolina with South Carolina throughout the workbook. Also replace N. Carolina with North Carolina throughout the workbook.

☐ 2

Add a footer to every sheet:

Left side: Created by: your name

Make the footer font bold with color Dark Red, Accent 1

Right side footer: file name on the top line; sheet name on the second line.

☐ 3

Change the orientation of all sheets to landscape and center them all horizontally.

☐ 4

Leave all worksheets in Normal view.

☐ 5

You want to add the Document Properties of a title: June Sales and the company name:

Auto Sales Research, Inc.

☐ 6

Save the workbook with your last name and first initial along with Autosales (e.g. SmithJ Autosales).