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.


Download Practice Workbook

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


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.

How to Make an Address Book in Excel

  • Next, insert the corresponding data under the headers.

How to Make an Address Book in Excel

  • 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.

How to Manage an Address Book

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.

How to Manage an Address Book

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

How to Manage an Address Book

  • 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.

How to Manage an Address Book

  • 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


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.

How to Format an Address Book in Excel

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

How to Format an Address Book in Excel

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

How to Format an Address Book in Excel

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.

How to Format an Address Book in Excel

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.

How to Format an Address Book in Excel

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

How to Format an Address Book in Excel

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.

How to Format an Address Book in Excel

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

Read More: How to Format Addresses in Excel (4 Easy Methods)


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. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo