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.
Read More: Formula to Create Email Address in Excel
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 Organize Addresses in Excel
- Extracting Email Addresses from Excel
- Format a Column for Email Addresses in Excel
- Create Email Address with First Initial and Last Name Using Excel Formula
<< Go Back to Address Format | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
