Spreadsheets are an ideal way of keeping track of contact information. Instead of entering addresses in a traditional address book, create an address book in Microsoft Excel to store this information. Having an address book in Excel lets you easily add and delete information and keep everything extremely organised. Using an Excel address book is also the first step in setting up mail merge to make address labels, letters and envelopes. Creating an address book in Excel requires two basic procedures: setting up the appropriate columns and entering information.
Open Microsoft Excel. The new worksheet that appears is a series of blank cells.
Select the worksheet, including the cells that will contain column headers. In the Defined Names group, on the Formulas tab, select "Define Name" and enter the name for the list. For example, set up one address book for personal use, labelled "Personal," and one for business use, labelled "Business;" the title helps you distinguish them from one another. Use the tools on the formatting palette to bold the title and make the font larger.
Set up columns for the address book. Use the following column headings: Name, Address, City, State, postcode, Phone Number, Fax Number and Email Address. Enter these headings into columns A through H, respectively. Your information is much easier to read when everything is in a separate column.
Highlight the row of column headings by clicking on the row number found on the left. Center and bold the column headings using the formatting palette.
Enter information into each column. This might take time, but you only have to enter everything in Excel once; after that, you merely have to add new people and update information. If you are missing certain bits of information, simply leave the cell blank. Be sure to use the two-letter postal abbreviations for the states.
Format the text in the postcode column. Highlight the column (but not the column heading). Click on what you’ve highlighted and select "Format Cells." In the Number tab, click on "Special" in the "Category" box. Click on "Zip Code" or "Zip Code+4" in the "Type" box. Click "OK." This will make all of the postcodes look the same.
Format the text in the Phone Number column. Highlight the text in the column, except for the column heading. Click on it and select "Format Cells." In the Number tab, select "Special" from the "Category" box. Click on "Phone Number" in the "Type" box. Click "OK." All of the phone numbers will be formatted in the same way.
Storing first and last names in separate columns enables mail merge of letters that use the recipient's first name. Worksheet names must start with a letter, not a number or character. Use and underscore ("_"), not a space for multiple words in the name.
Tips and warnings
- Storing first and last names in separate columns enables mail merge of letters that use the recipient's first name.
- Worksheet names must start with a letter, not a number or character. Use and underscore ("_"), not a space for multiple words in the name.