Free Google Sheets Address Book Template + How to Use

This Google Sheets address book template helps you organize contacts in one place. My guide below includes a free workbook, already laid out to collect contacts. I also show step-by-step how to create a Google Sheets Address Book template. Either way, you can customize it according to your requirements. 

• Here’s my Address Book template link.

What Does an Address Book Template Consist of?

An address book template or a Google Sheets Contact List Template consists of a grid with details about contacts, like Name, Phone Number, Address and Email Address. The template should either automatically sort the rows containing contact information or at least give the user the option to sort it by pressing a button.

It is also quite useful to have a search feature in the template, so that the user can easily find the contacts they need without having to scroll through the entire list. This is especially helpful as your contact list starts getting bigger with time.

Understanding the Template Design

The template that we will create in this tutorial is organized into three tabs:

• Contact List
• Sorted Contact List
• Search List

Let us look at what each of these tabs are meant to achieve:

The Contact List Tab

The Contact List tab is meant to simply let you add contacts to the list. You can also use this tab to make edits to existing contacts or delete contacts that you don’t need.

The Sorted Contact List

The Sorted Contact List tab simply organizes your original contact list by sorting the rows in alphabetical order of Names.

This tab is connected to the Contact List tab so that every time you make a change to the original data in the Contact List, the contents in the Sorted Contact List get automatically updated with the changes, while still keeping the rows sorted.

The Search List Tab

The Search List tab is meant to help you quickly find contacts using a search string. You will find a small pink search box that lets you enter your search string in this worksheet.

You can search by Name or part of the Email address. If any of these three fields match the given search string even partially, the row corresponding to that contact will get displayed in this tab.

One of the benefits of using this free digital product is the ability to quickly sort hundreds of entries. Looking for someone with the first name of John? You can find it quickly. Here’s how it looks in my worksheet.

How to Create a Google Sheets Address Book Template / Google Sheets Contact List Template

To create a Google Sheets address book template, we start by creating the basic outline. This requires identification of the fields that you want to include in your address book. For example, in this tutorial we will include the following fields:

• Name
• Phone Number
• Email Address
• Address

We create these fields along with their headers in a worksheet that we name ‘Contact List’.

We also put some basic formatting in place so that alternating rows are displayed in alternating colors. This breaks the monotony when you have many contacts to look through. It also ensures that the user can easily distinguish between rows while skimming through the data.

Once the basic skeleton is ready, you can start filling in the contact details. You can then sort your contacts in alphabetical order of Name. There are two ways to do this. You can either explicitly sort the list every time you add a new contact, or have the list automatically sorted using the SORT function. The automatically sorted list will be saved in a separate tab, the ‘Sorted Contact List’ tab.

In this tab, we also make sure to protect the sheet so that the user does not accidentally write over it.

Note: Any additions/deletions/edits to the contacts should only be done from the ‘Contact List’ tab.

Next, we create the ‘Search List’ tab. We again create the basic outline of this tab, which consists of a specified cell where the user will enter the search string. We also add the main headers below which the search results will get displayed, along with other basic formatting.

Again we will make sure that the user cannot make edits to the search results area.

Creating the Basic Outline of the Contact List

Let us start with the basic outline of our Contact List.  To change the name of the worksheet tab, simply double click over the worksheet tab name and write over it with the new tab name, ‘Contact List’.

         

Now add the field headers for the contact list. You can add the field headers shown in the image below:

 

You can also put your own headers according to your requirement, but you have to make sure that you use these same headers to display your data in the other tabs as well.

Formatting the List to Show Each Row in Alternating Colors

You can format your contact list to display rows in alternating colors. These are the steps you need to follow for this:

• Select all the rows, including the header row up to the last row in your sheet (which is usually row 1000).
• From the Format menu, select ‘Alternating colors’.

• This will open the Alternating Colors sidebar, from where you can select the color formatting that you want to apply. You can select one of the default styles or create your own custom style. Here’s the default style that we selected:

• You should now find your rows in alternating shades of your selected colors.

Once the basic outline of your contact list is created, you can start adding in your contact details. We created a random list of contacts as shown below, just to demonstrate this tutorial:

 

Using Filters to Sort the Google Sheets Contact List Template

At this point, you can choose to add filters to your contact list to sort your list by name. Here are the steps you need to follow:

• Select all the column headers of your contact list
• From the Data menu, select ‘Create a Filter’. Alternatively you can click on the ‘Create a Filter’ button from the main toolbar:

• This will add filters to your dataset. The filters appear in the form of triangles next to the each column header, as shown below:

• Now you can sort your rows by Name. All you need to do is click on the filter (or arrow next to) the Name column header.
• Select ‘Sort A-Z’ to sort the contact list in alphabetical order of names.

Here’s how our sorted contact list looks:

Note: Sorting the contact list in this way is not very efficient, because you will need to re-sort the list every time you add, remove or edit an entry. A more effective way would be to have a separate tab that displays the sorted contact list dynamically.

Creating the Sorted Contact List Worksheet

Once the Contact list is created, you can create a new worksheet that will display the sorted contact list. Simply press the ‘+’ button near the worksheet tab as shown below:

This will create a new worksheet with the name Sheet2.

Change the name of the sheet to ‘Sorted Contact List’:

Again create the same column headers as you did in the Contact List worksheet and format the sheet with alternating colors for readability.

Once done, you can add the formula to display the sorted list in this worksheet.

Using the SORT Function to Create the Sorted Contact List

To create the sorted contact list, you only need to know one formula:

=SORT('Contact List'!A2:D,1,TRUE)

The above formula uses the SORT function, which simply displays the sorted data into a range of cells that start from the cell containing the formula. The syntax for the SORT function is:

SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])

Here,

• range is the group of cells that you want to sort.
• sort_column is the column by which you want to sort the data in the range. Usually this is specified as an index number of the column in the given range.
• is_ascending can be a TRUE or FALSE value. It specifies if cells in the range should be sorted in ascending or descending order of sort_column. A TRUE value indicates ascending order, while a FALSE value indicates descending order.

The above three parameters are compulsory in a SORT function. Additional parameters can be added to sort the range by more criteria. Since we don’t need these parameters in this example, we will not go over them, but if you are interested, you can go through this article to understand the SORT function better.

In our formula, we specified that we want to sort all the rows starting from cell A2 up to the last row of column D (which are in the worksheet named ‘Contact List’). We want to sort the range by column 1 (which in our selected range is the 1st column).  We specified the is_ascending parameter as TRUE because we want the rows sorted in ascending order.

Insert this formula in cell A2 of your ‘Sorted Contact List’ worksheet, and press the return key. You should now see all your contact list entries sorted by Name in this sheet.

The SORT function is dynamic, so any changes you make to the original contact list get automatically updated in the result of the SORT function.

Protecting the Sorted Contact List Tab from Accidental Changes

If you type anything in a cell of the ‘Sorted Contact List’ sheet, it might result in an #REF! error. To ensure that this doesn’t happen, you can protect the sheet by the following steps:

• Right-click on the worksheet’s tab
• Select ‘Protect Sheet’ from the context menu that appears.

• This opens the Protected sheets & ranges sidebar.
• Press the ‘Set Permissions’ button.

• Select ‘Show a warning when editing this range’ and press Done.

Now, every time a user wants to edit the sheet, they will be given a warning message. If they select Cancel then the changes will be undone, and if they select OK, then the changes will remain.

Creating the Search List Worksheet

To allow the user to search for specific contacts from the contact list, you can create a third worksheet and name it ‘Search List’.

Design the outline of this sheet as in the image below:

 

As you can see, this sheet has a specific cell where users can enter their search string (cell B1). Try to make this cell stand out from the other cells by changing the background color.

Below this cell, add the same headers that you had used in the other two sheets.

Now you can start typing the formula to display the rows that match the search string.

Using the QUERY Function to Display Required Rows

To display the contacts that match the search string entered by the user (in cell B1), you can type the following formula in cell A4:

=IFERROR(QUERY('Sorted Contact List'!A2:D, "select * where A contains '"&B1&"' or C contains '"&B1&"'"))

The above formula uses the QUERY function. This function lets you apply SQL-like queries on a given range of cells.

The syntax for the QUERY function is:

QUERY(data, query, [headers])

Here,

• data is the group of cells that you want to perform the query on
• query is the SQL-like query that you want to apply to the data.

The above two parameters are compulsory in a QUERY function. For more information about the QUERY function, you can go through our other article dedicated to how to use the QUERY function.

In our formula, we specified that we want the query “select * where A contains ‘”&B1&”‘ or C contains ‘”&B1&”‘” to be applied to the range starting from cell A2 up to the last row of column D (from the worksheet named ‘Sorted Contact List’).

The query basically says that we want to select all the rows from the given range where the name or the email address contains the search string in B1 either at the start, middle or end.

If the QUERY function does not find any matching rows, it might return an error code. To ensure that this error code is not displayed, we wrap an IFERROR function around the QUERY formula.

Insert the above formula in cell A4 of your ‘Search List’ worksheet, and press the return key.

Now try entering a search string in cell B1 and press the return key (It can be a part of a Name or Email Address). You should see all matching entries from your contact list sorted and displayed in this sheet.

This formula uses the FILTER function to display rows from your ‘Sorted Contact List’ where the value in cell B1 is found in either column A or column C. The SEARCH function is used to find the position of the text in B1 within each cell of columns A and C. The ISNUMBER function then checks if SEARCH returns a number (which means the text was found).

Protecting the Search List Tab from Accidental Changes

If you type anything in the results area of the QUERY function, it might result in a #REF! error. To ensure that this doesn’t happen, you can protect the all the cells of the ‘Search List’ sheet (except for cell B1) by the following steps:

•  Right-click on the worksheet’s tab
• Select ‘Protect Sheet’ from the context menu that appears.
• This opens the Protected sheets & ranges sidebar.
• Check the box next to ‘Except certain cells’.

• This will display an input box for entering the range of cells you want to exclude from protection. Type B1 in this input box, as shown below:

• Press the ‘Set Permissions’ button
• Select ‘Show a warning when editing this range’ and press Done.

Now, the entire sheet is protected, except for cell B1, where the user can enter the search string. 

Frequently Asked Questions

How do you create a mailing list in Google Sheets?

While it’s not an email host, you can still use Google Sheets for business address books. A spreadsheet collects all the pertinent info, and you can use a script to help you quickly print labels or create email lists.

What’s the best address book spreadsheet template?

I made my own address book spreadsheet template, shared for free below. It collects names, phone numbers, emails, and physical addresses. If you’re looking for more free Google Sheets templates and other helpful tools, you’re in the right place. Check out my template gallery.

Can I use this with a labelmaker?

You can use my Google Sheets address book to collect information for your label maker. A quick printing script then allows you to print all your labels at once, so you don’t have to manually label each piece of mail. Alternatively, you can use the emails in your address book to create an email list.

Is there an address workbook for small businesses?

Small businesses may like my Google Sheets address workbook because it’s built for the cloud. You can invite coworkers and employees to add or access the information, and the template doesn’t cost anything. You could also use it as a substitute database, which would allow you to build a list for newsletters.

Conclusion

A few years back almost everyone had an address book where they stored all their contacts along with phone numbers and addresses. These address books were organized with alphabet tabs so that you could easily find the contact you need by skipping to the appropriate tab.

With spreadsheet software like Google Sheets, maintaining and sharing address books has become easier and more efficient. You can share an address booklet with family members, colleagues or team members and work on it remotely in real-time.

You can use spreadsheet software to keep your contact list organized and quickly find contacts using a simple search string. I made this detailed tutorial to help you create your own Google Sheets Address Book template so you can customize it to your requirements.

However, if you prefer to simply download a ready-made template rather than create one from scratch, you can go ahead with my premade address book.

• Use template.

I hope my address book template makes it convenient for you to organize, store, find, and share your contacts more easily.

Related Posts: 

• Free Content Calendar Template in Google Sheets
• Free Google Sheets Ledger Template [and How to Use]
• Google Sheets Expense Tracker: Free Template & How to Use
• Mail Merge in Google Sheets
• Google Sheets ADDRESS Function
• Schedule Template Google Sheets

References

1. Google Support. Add formulas and functions [Internet]. Google; [cited 2024 Jan 9]. Available from: https://support.google.com/docs/answer/3093197
2. United States Postal Service. Business Mail 101 – Get An Address List [Internet]. USPS; [cited 2024 Jan 9]. Available from: https://pe.usps.com/BusinessMail101/Index
3. Spreadsheetpoint. Google Sheets Query Function: The Ultimate Beginner’s Guide [Internet]. Spreadsheetpoint; [cited 2024 Jan 9]. Available from: https://spreadsheetpoint.com/formulas/google-sheets-query-function/