How to Make an Address Book in Excel (An Ultimate Guide)

We can use Excel templates for our daily necessary usage. Instead of using Notes or MS Word, making an address book in excel is quite easy and very user-friendly. Because in Excel, we can apply a lot of customizations. I hope, this article will be a useful guide for you to make an address book in Excel.

What Is an Address Book in Excel?

Like the phone book in our phone, we can create an address book in Excel. Where we can store a lot of data in different categories for persons. The advantage is- we can sort and filter them, and modify them in various ways easily.

How to Make an Address Book in Excel

Now by following some easy steps, we’ll learn the procedure to make an address book in Excel.

Steps:

• First, insert the necessary headers along a row. I inserted along row 4.
• Then to highlight the headers, bold them, fill them with color, and use the center and middle alignment.

The headers are now highlighted with our selected formats.

• Next, insert the corresponding data under the headers.

• In the same way, insert more data under the headers. You can add more headers if you want.

How to Manage an Address Book

In this section, we’ll learn some Excel features to manage the address book.

Sort & Filter in Address Book

One of the most useful features of the Excel address book is- we can easily sort & filter data in it which will help us to find any particular data fastly.

Sorting

First, we’ll learn how to sort in a column in the address book.

Steps:

• Click on any data in the column where you want to sort. I’ll sort the first column in A to Z alphabet order.
• Then click as follows: Home > Editing > Sort & Filter > Sort A to Z.

Now have a look, the First Name column is sorted in that order.

Filtering

By applying a filter in a column in the address book, we can easily get any kind of particular data.

Steps:

• Select any data from the address book.
• Later, again click as follows: Home > Editing > Sort & Filter > Filter.

Soon after, you will get the filter icon in every header. Let’s filter in the Last Name column.

• Click on the filter icon of the Last Name column.
• Next, just mark the data you want to filter for and then press OK. I marked Smith.

Now it is showing the data which has the last name- Smith.

Search in Address Book

To search any data in the address book, we can use the Find and Replace tool.

Steps:

• Press CTRL + F to open the Find section of the Find and Replace tool.
• Type the data you want to search for in the Find what box. I searched for Robert.
• After that, just press Find Next.

• It will highlight the result with a green cell border.
• If your data has more results then press Find Next again to see the next result. If you wanna see them all ta a time, then you will have to just press Find All.

Similar Readings

• Formula to Create Email Address in Excel 
• Separate Address Number from Street Name in Excel 
• How to Split Inconsistent Address in Excel
• Create Email Address with First Initial and Last Name Using Excel Formula
• How to Separate City State and Zip from Address Using Excel Formula

How to Format an Address Book in Excel

By formatting the address book, we can make its outlook far better.

Format As Table

If we format the address book as a table then it’s easier to manage the address book.

Steps:

• Click on any data from your address book.
• Then click as follows: Home > Format As Table.

• Then from the appeared menu, you can select any table style. I selected the table style- Light Orange.

• Soon after, a dialog box will open up to ensure the table range. Just press OK at this moment.

The address book is now converted to a table. So now, we’ll be able to apply all the table features in our address book like, Sort & Filter, table color, etc.

Here you see- the same Sort & Filter feature that we applied before.

Change Cell Format

We can set specific cell formats. Like, zip code or phone number has different formats. Here, I’ll show how to set the Phone Number format.

Steps:

• Select all the cells from the Phone column.
• Then click on the Number Format shortcut icon from the Number section of the Home ribbon.

• Later, click Special from the Category section.
• Next, select Phone Number from the Type section.
• Finally, just press OK.

Now see, the numbers are now formatted to the default phone number format of the USA.

Format Fonts

If we change the font of the address book excluding the headers, then it may look better and different.

Steps:

• Select all the data from the address book excluding headers.
• Then from the font selection box, click the drop-down icon and select your desired font. I set Calibri Light.

Now have a look, it looks pretty cool, right?

Read More: How to Format Addresses in Excel 

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to make an address book in excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• How to Separate Address in Excel
• How to Separate City and State in Excel 
• How to Separate Address in Excel with Comma 
• How to Separate City and State without Commas in Excel
• How to Separate Address in Excel Using Formula 
• Format a Column for Email Addresses in Excel
• How to Organize Addresses in Excel