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 book 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.
In this tutorial, we will show you step by step how to create a Google Sheets Address Book template that you can further customize according to your requirements. For your convenience, we have also shared a ready-made template that you can simply download and start using immediately.
Click here to view and download our Address Book template.
To use this template, click File->Make a copy. This will let you edit and save the sheet as your own.
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.
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 a large number of 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 get saved in a separate tab, which is 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.
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 the cell B1, where the user can enter the search string. Â
Conclusion
We tried to provide you with a detailed tutorial to help you create your own Google Sheets Address Book template, so that you can customize it to your own requirements.
However, if you prefer to simply download a ready-made template rather than create one from scratch, you can go ahead and download our template from this link.
We hope our address book template makes it convenient for you to organize, store, find, and share your contacts more easily.
Related Posts:Â
