Comp25: Excel - Mailing List

Assignment Goals

1. To use Excel to create a basic mailing list and fix data-entry errors that have occured.
2. To learn to use formulas to modify and combine text strings.
3. To have a list of customers which you will import into your Customer Management Database in a future assignment.
4. To learn a variety of other useful Excel features.

Steps to Take

1. Download this file and immediately save it to your U: Drive (in a place you won't lose it). Close your internet browser.
2. Open the worksheet (in Excel) and review its contents.  You will see that it contains a list of approximately 80 of your customers.  You should also notice that whoever entered the information had poor typing skills -- many cities are misspelled and there is inconsistency with the use of capital letters.
3. Spell Check - Fix the spelling errors with the incorrect city names.  Excel's spell check feature will help us clean up the data, however spell-checking an entire workbook full of people's names and streets will waste a lot of time because you'll be clicking "Ignore" on all the correctly-spelled names that were not recognized.  Tell Excel to only spell check the City column.
   1. Highlight just the City column.
   2. Click the Spell Check icon, or press F7.
   3. Fix each of the misspelled cities.
4. Letter Case - Sloppy typing has caused your database to have inconsistent upper/lower case entries for the first and last name, street, city, and state.  We will use formulas to fix this.
   1. Start a new column (H) and label it "First".  Repeat this for Last, Address, City, State, and ZIP, giving yourself six new columns.  These columns will "mirror" the original six.
   2. In H2, enter the formula that uses the text-manipulation function called Proper and refer it to A2.  This will transform the text into Proper Case, perfect for someone's first name.
   3. Repeat this for the last name column.
   4. In J2, enter the formula that uses the text-manipulation function called Upper and refer it to B2.  This will transform the address into UPPER CASE, which is what the post office will want to see on a mailing. Repeat this for City and State.
5. Find and Replace - Someone thinks that the abbreviation for California is "CAL" and not "CA". Use Replace (from the Edit menu) to replace all occurences of "CAL" with "CA". Be careful though! You only want to replace CAL when found in the State column. To do this, highlight the State column before choosing Replace from the Edit menu.
6. Save your work.
7. Moving Columns - Our new columns should now take the place of the original ones.  However, since they are only formulas which refer to the original columns, if we delete the originals the formulas will have nothing left to refer to.  We must use a special type Copy and Paste to paste only the values and not the formulas themselves.
   1. Highlight all six of the new columns.  Choose Edit, Copy.
   2. Select cell A1.
   3. Choose Edit, Paste Special.  Then choose Values.  This pastes only the values (not the formulas) over the original cells.
   4. Since we no longer need them, delete the six new columns.
8. Cell Comment - Use comments to identify your three best customers.
   1. Sort the entire list by customer sales (Column G) in decending order. To do this, select cell G1 and click the "Sort Decending" icon in the toolbar.   Identify your biggest spender, who should appear at the top of the list.
   2. Right click on that customer's last name and choose Insert Comment.
   3. Add a comment stating that this is a great customer, and indicate some of their preferences so your employees will treat them even better.
   4. Repeat this for the next two biggest spenders.
   5. Sort the list by Last Name.
9. Save your work.
10. Landscape - Notice in Print Preview that your list is too wide to fit on a standard page. From Page Setup, change the Page Orientation to Landscape.
11. Fit to Page - Your work likely takes multiple pages still. We want the entire worksheet to fit on just two pages. From Page Setup, under Scaling, specify to "Fit to 1 Page Wide by 2 Tall".
12. Print Headers - Enter Print Preview mode again and you will see that your worksheet spans two pages, however page two does not indicate which column is which.  Let's fix this.
    1. Close Print Preview Mode and choose File, Page Setup.
    2. Click on the Sheet tab in the Page Setup dialog box.
    3. Place your cursor in the field "Rows to Repeat at Top", then click on Row A in your worksheet (behind the dialog box).  Excel will enter 1:1 in the field.
    4. Click Print Preview and confirm that Row 1 appears on your second page.
13. Filter - Out of curiosity, let's look at customers that are only in Stockton.  How much money did we earn from them?
    1. Click on the City column header (D1).  From the Data menu, choose Filter, and Auto Filter.
    2. Pull down the menu that appears next to City.  Choose Stockton.  Excel will filter the list to only show Stockton customers.
    3. Using your mouse, select the cells from column G.  Watch what happens in the lower-right corner of your screen where it says Sum =
    4. What's the total spending of your Stockton customers?  Write this number down and include it with your Sakai entry.
    5. Right-click on the Sum = display and change it to Average.  What's the average spending of your Stockton customers?  Write this number down and include it with your Sakai entry.
    6. Turn the filter off by going to the Data menu, choosing Filter, and then Show All.
14. Combining Text - So that we can easily create a form letter and mailing labels, let's combine the First and Last names into a single field, and the City/State/ZIP into a single field (we'll call this Locale).
    1. Create two new columns (H and I) with headers "FullName" and "Locale".
    2. Enter a formula into that automatically combines the Full Name into H2.  Don't forget a space between the first and last name.  Refer to this lecture for help.
    3. Enter a formula into that automatically combines the City, State, and ZIP Code into I2.  The format should resemble this:  Lodi, CA 95240.  Notice the comma and space between the city and state, and the space between state and ZIP Code.
15. Formatting - Apply some basic, low-frills formatting to your spreadsheet and make the columns wide enough to see your data.  Don't add any logos or anything too wild.  Make sure the Sales (Column G) has proper formatting for money.  Add a thick border between column G and H.
16. Rename Sheet1 to "Mailing List".  Delete the unused Sheet2 and Sheet3.
17. Save your file again
18. Upload the file via Sakai.  When turning the assignment in, don't forget to include the SUM and AVERAGE of your Stockton customers, which you discovered in an earlier step.

Grading Guidelines

This assignment will be graded using the following guidelines:

All Steps Completed	50%
On Time	20%
Style	30%
	100%