Excel file

Project 1 - Pollution Workbook – States sheet

Overview

You work in a government office helping keep the records for pollution nationwide. You need to update several data sheets beginning with States. New data has arrived for eight states and needs to be imported. Also, several additions need to be made to make the sheet more usable and informative.

Tasks

☐ 1

Open the Pollution workbook.

☐ 2

On the States sheet, import from the text file states.txt to the open space between Illinois and Indiana. The delimiter is /. Then fix the width of column B to 28 and column C to 12.

☐ 3

Freeze Panes so that rows 1:2 and columns A:B cannot scroll off the screen.

☐ 4

Search for the cell formatted with Arial font and name the cell ArialFont.

☐ 5

For all of the states’ pollution numbers, apply Gradient Fill Light Blue Data Bars.

Project 2 – Air Pollution sheet

Overview

The main message at the bottom of the sheet needs to be brought to the top and emphasized.

Tasks

☐ 1

On the Air Pollution sheet, insert a Text Box over F3:H10.

☐ 2

Get the text in A40 and put it inside the Text Box. Then make that text 14pt.

☐ 3

Format the Text Box: Shape Styles: Other Theme Fills: Style 6

Border: 3pt Blue Accent 1

Shape Effects: 3-D Rotation: Off Axis 1 Right

Center text: Horizontally and vertically

☐ 4

Wrap the two column headings.

Project 3 – Water Pollution sheet

Overview

You need to make the Water Pollution sheet ready for others to use by applying data validation. For upper level management, you need to highlight the below average values in the Total column and summarize the measurements.

Tasks

☐ 1

Apply to all of the monthly numbers (including the two empty columns) data validation so only Decimals between 0 and 0.2 are allowed. Set the Input message Title to your name and the Input Message to Between 0 and 0.2; Make the Error Alert: Stop, no Title, Out of bounds.

☐ 2

Apply a Conditional Format for Below Average to the Totals only. Use a custom format of a red cell border and black text.

☐ 3

Range name B6:M30: WP

☐ 4

Use the range name WP together with functions to calculate in column Q each result named in column P.

☐ 5

In Q16, use a function to count the zeros in the WP range.

☐ 6

In P19, insert the image DrinkingWater.jpg.

☐ 7

Size: 2” tall.

☐ 8

Picture Styles: Center Shadow Rectangle.

☐ 9

Picture Effects: 3-D Rotation: Perspective Relaxed Moderately.

Project 4 – Data Gathering sheet

Overview

You need to format the data import and draw a chart representing that data.

Tasks

☐ 1

Import beginning with line 4 the tab delimited file GatheringData to A2:N18 on the Data Gathering sheet.

☐ 2

On the Data Gathering sheet, in B4, enter the date January 31 of the current year.

☐ 3

Format it to look like 1/31.

☐ 4

Extend to C4:M4 changing the Auto Fill Option to Fill Months.

☐ 5

AutoFit columns B:M.

☐ 6

Center A2 over A2:N2. Then format the cell as 14pt; Bold; Cambria

☐ 7

Format the Full Year Totals as Comma Style with no decimals.

☐ 8

Insert a 2-D Stacked Bar Chart for the 3rd quarter values only; use A4:A18 for the Vertical Axis Labels.

☐ 9

Chart Title: Third Quarter Counts;

Chart Name: ThirdQuarterCounts

Alt Text Title: Quarterly Count for July, August, and September

Chart Style: Style 8

☐ 10

Size: 4” tall x 5” wide

☐ 11

Format the title: Text Effects: Reflection: Tight Reflection, 4 pt offset

(Hint: Not Shape Effects.)

☐ 12

Show Primary Major Vertical & Minor Vertical Gridlines

☐ 13

Legend position: Bottom

☐ 14

Position the chart so its top-left corner is over K6.

Project 5 – Energy Consumption sheet

Overview

A formula for energy consumption per capita must be created and summary calculations added at the bottom of the table.

Tasks

☐ 1

Turn the list into a table and apply Table Style Dark 3.

☐ 2

Set the column widths of A:E to 15.

☐ 3

Table Name: EnergyConsumed

☐ 4

Remove Banded Rows; apply Banded Columns.

☐ 5

Calculate the “Consumption per capita (million BTUs)” by dividing “Consumption (trillion BTUs)” by the “Population” and then multiplying by 1,000,000.

☐ 6

Add the Total Row.

Change the row label to Average.

Remove the sum from the “Price ($ per million BTUs)” column.

Add averages in the “Population” and “Consumption (trillion BTUs).”

☐ 7

Apply a filter for Population greater than 10,000,000.

☐ 8

As a single sort, sort “Price ($ per million BTUs)” largest to smallest and Population smallest to largest.

☐ 9

Apply Heading 1 style to the main title.

Project 6 – MSW sheet

Overview

You must complete the MSW sheet by calculating for each state its percent of the nation’s total garbage and preparing the sheet for printing.

Tasks

☐ 1

Find & Replace all occurrences of MSW and replace it with Municipal Solid Waste.

(Tip: You must clear the format for Arial that you set earlier by clicking the drop-down arrow on the right edge of the first Format button and picking Clear Find Format.)

☐ 2

AutoFit row 4.

☐ 3

At the bottom of the column for Municipal Solid Waste, calculate the total.

☐ 4

At the top of the column for Percent of National Total, calculate the percent that that state’s Municipal Solid Waste is of the total national waste in C56; extend to D6:D55. Then format as a Percent with 1 decimal place.

☐ 5

In column H, use the CONCATENATE function to combine the name of the state, the words “has a population of”, the Population divided by 1,000,000 and the word “million” so the first record result appears as: Alabama has a population of 4.59024 million.

☐ 6

Extend the concatenation to H6:H55 and AutoFit column H.

☐ 7

Apply Heading 2 style to the main title.

☐ 8

Orientation: Landscape

☐ 9

Print Options: Print Area: A1:H55

Change the top margin to 1.25 inches

Create a page break before row 31

Repeat rows 1:4 to print on the top of each page

Scale to fit on one page wide

☐ 10

Create a left header: your name

Create a right header: the sheet name

Create a center footer: “Page” Page Number “of” Total Number of Pages (e.g. Page 1 of 2)

☐ 11

Save the MSW sheet to a PDF file with your last name and first initial and MSW.pdf (e.g. SmithJ MSW.pdf).

Project 7 – Health Effects sheet

Overview

You need to remove any duplicates from the table, create a more compact code for each Topic, and create macros to hide and unhide the sheet.

Tasks

☐ 1

Delete any duplicate rows based on having the same Type.

☐ 2

In the next available table column, use the MID and LOWER functions to calculate the fourth and fifth characters from the Topic codes as lower case letters. Extend to E19 and name the column New Code.

☐ 3

Convert the table to a range.

☐ 4

Split panes at A12.

☐ 5

Scroll the bottom pane so row 17 is at the top.

☐ 6

Record a macro to hide the Health Effects sheet. Name it HideHealthEffectsSheet. Assign the shortcut key e and record in This Workbook.

☐ 7

Record a macro to unhide the Health Effects sheet. Name it UnhideHealthEffectsSheet. Assign the shortcut key u and record in the current workbook.

☐ 8

Test the two macros to hide and then unhide Health Effects. Leave the sheet showing.

☐ 9

Save the workbook to include the macros.

☐ 10

Add Document Properties: Category: pollution, states

Title: National Pollution

☐ 11

Save the workbook with your last name and first initial and Pollution (e.g. SmithJ Pollution). Submit that workbook and the PDF file via the link on the Assignments page of Blackboard.