Comp25: Excel - Mailing List

Learning Objectives

1. To use Excel to create a basic mailing list and fix data-entry errors that have occurred.
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 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, and State, giving yourself five new columns.  These columns will "mirror" the original five.
   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 C2.  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 occurrences 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. Copying new column data - 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 the Paste Special option to paste only the values and not the formulas themselves.
   1. Highlight all five of the new columns.  Choose Edit, Copy or CTRL+C
   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. Delete columns H-L and insure that your Proper values and not an #REF error occurs.  If so, UNDO and go back and insure you copied values only. 
8. Sort: Insure your list is sorted by Last Name.  
1. Choose: Data | Sort | Last
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 Titles - Rows to Repeat at the Top  --- 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. 
       1. Choose Stockton.  (You may have to uncheck the Select All option in Vista and check only Stockton)
       2. 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 Blackboard 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 Blackboard entry in the comments section.
    6. Turn the filter off by going to the Data menu, choosing Filter, and then Show All or Select 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 Blackboard.  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, in the comments section.

Grading Guidelines

This assignment will be graded using the following guidelines: