How to Create an Address Book in Excel
Organize your address details for quick access with a sortable table created in Microsoft Excel. As an alternative, copy a Microsoft template and style a pre-designed address book to make the data meaningful. These time-saving layouts include table columns with a clearly defined header row for categories such as names, addresses and phone numbers. Each table column features a sorting button to sort the rows of data by alphabet or size to help you search and analyze your database of contacts.
Click and drag to select the cells for your table. For example, select cells "A2:E50" to select a range with 5 columns and 50 rows.
Click the "Insert" tab on the command ribbon and click "Table" in the Tables group to open the dialog box. Select "My Table Has Headers" and then click "OK" to create sortable columns with arrow buttons for sorting.
Enter the category headers, such as First Name, Last Name, Address and Email.
Click the table to bring up the Table Tools ribbon with the Design tab. Click the "Design" tab to show the style tools, such as the gallery of thumbnails in the Table Styles group. For example, click the "More" button in Table Styles to open the chart that includes the Custom, Light and Medium sections, then point over a color sample to preview the effect. Select the preferred thumbnail to update the table's color and design.
Click a cell, type the data and then press "Tab" to move the cursor to the next cell.
Press "Ctrl-S" to save this workbook.
Adapt a Template
Click the "File" tab on the command ribbon and then select "New" to open the templates list.
Enter "address book" (without quotations) in the "Search for Online Templates" box and press "Enter" to bring up the thumbnails in the search results. Examples include "Address Book," "Personal Address Book" and "Customer Contact List."
Click a preferred template thumbnail to preview an enlarged version with its description, download size and rating. Click "Create" to copy this template to a new workbook.
Click the table and update the format, layout or style with the ribbon tools and Table Tools options. For example, make the data more readable on the rows and columns by selecting or deselecting options, such as "Banded Rows" and "Banded Columns," in the Table Style Options group on the Design tab of the Table Tools ribbon.
Click a cell, enter the contact's data and then press "Tab" to move the cursor to the cell in the next table column.
Press "Ctrl-S" to save this workbook.
Tips & Warnings
- Your Excel worksheet provides the data source for Microsoft Word's mail merge option to create mailing labels. As you plan your worksheet categories, ensure these categories will match the fields required in the mail merge. For templates that show Name as one category header, insert a new column to keep first names and last names in separate columns for form letters. The Word ribbon includes the Mailings tab with the Start Mail Merge group options.
- To check for duplicate data, such as entries sharing the same last name, click the table to bring up the Table Tools ribbon, click the "Design" tab and then select "Remove Duplicates" in the Tools group to open the dialog box. Select the column filters and then click "OK" to filter and remove the duplicate data.
- Save a version of the worksheet as a template to create future address books with a consistent style. Select "Excel Template (*.xltx)" in the Save As Type field in the Save As dialog box. To find this template, press "Windows-F" to bring up the Search box and type "*.xltx" (without quotations). Press "Enter" to bring up the search list and then select the template.
- Information in this article applies to Microsoft Excel 2013, Small Office Premium. It may vary slightly or significantly with other versions or products.
References & Resources
- Microsoft Office: Introducing the New Templates Site
- Microsoft Office: “Quick Styles” Is Under “Table Tools/Table Styles”
- Microsoft Office: “Banded Columns” Is Under “Table Tools/Table Style Options”
- Microsoft Office: Save a Workbook as a Template
- Microsoft Office: Create and Print Mailing Labels for an Address List in Excel
- Microsoft Office: Create an Excel Table in a Worksheet
- Microsoft Office: Welcome to the New Office
- Microsoft Office: What’s New in Excel 2013
- Microsoft Office: Where Are My Custom Templates in Office 2013?
- Microsoft Office: Keyboard Shortcuts in Excel