V22.0004 Computers in Principle and Practice
Spring, 2007

Assignment # 6 (Excel )

This Excel assignment consists of three parts: data analysis and a related graph; mailmerge; and working with macros. These are among the fundamental and important skills that we want you to have in Excel.

Part A: Data Analysis and a related Graph -- Unique Visits Spreadsheet for the web (to be posted to your i5 website)

One metric used by web sites to gauge their success is unique visits. This refers to the number of unique visitors that visit a web site in a given period. In this assignment, you will be creating a Microsoft Excel Spreadsheet that contains data which shows 26 weeks of "actual" unique daily visitors for your web site, as well as a forecast of unique daily visitors for the next 26 weeks.

First, copy and paste the "actual" data into a new worksheet and use Data / Text to columns to parse the data into the appropriate columns. You are to create a spreadsheet that looks like this one.

Your spreadsheet should match the formatting of the sample spreadsheet as much as possible. This includes merged cells, borders, colors, alignment, number formatting, chart formatting and a hyperlink back to your assignments page below the title.

In addition to the formatting, your spreadsheet must have the following (all shown in the sample):

Now, publish your spreadsheet on the World Wide Web through your i5 account.

Part B: Mailmerge - Send 3 Files to your Grader

Your forecast is now all set up and you want to let your potential investors know where they can review the data.

1) Make a "Contacts List," which is a database of a few friends and people that you know (at least 5 people or imaginary friends) who might be interested in this information.

Harry Potter hpotter@hogwarts.edu Number Four Privet Drive Little Whinging Surrey 110181 998-1101 Student

3) Next, use the Mail Merge tool to make a form letter that you could send to a number of friends.

4) Then Merge the document to create the merged form letter, and print out two (only two) instances of the letter, to your first two friends.

5) Send the following three files as email attachments to grader email account:

  1. The Original Excel Data base which includes the macros and the toolbar
  2. The Word Form Letter
  3. The Word Merged Letters

C. Macros -- Send one file to your grader (Note: Send the actual EXCEL file for this part!)

Attach the macros to a new toolbar along with a button that has a link to your website.

On a PC:

On a Mac:

Attach the toolbar to the worksheet

In the CUSTOMIZE menu for Toolbars, select your toolbar, and then select ATTACH. Use the COPY button so that the name of your toolbar appears in the right-hand column. Remember to save your workbook again after attaching the toolbar.