A Customer List Template that you can download on this page could help you maintain an organized list of customers of your business in a single and convenient place. In a nutshell, this is a simple contact list with your customer details that could be used as-is or as part of your existing worksheets, such as invoices, work orders, receipts, etc. Below on this page, you will find detailed instructions on how to add a customer list to your existing worksheets. The information below will also help you to learn how to create a Customer Drop-Down List in a spreadsheet and how to create a Dynamic Lookup Formulas to display customer information based on the value selected in the drop-down list.

To make the creation of the customer drop-down list a little easier, we have included some dynamic named ranges that will get copied along with the customer table. We have used a similar technique for creating our service invoice and invoice manager templates, although the customer list used in these templates is slightly different from the one on this page.

Download the Customer List

Excel 2003 (xls) - 2003 or newer (PC or Mac)

Excel 2007 (xlsx) - 2007 or newer (PC or Mac), Excel for iPad/iPhone/Android, Excel Online

Google Sheets - Google Sheets

OpenOffice.org Calc (ots) - OpenOffice.org Calc

DOWNLOAD

Description

Although the Customer List worksheet in this template can work as a standalone template that lets you create and store your customer credentials in one place. Besides, the template is designed in a way to ensure a broad spectrum for the use of a customer list in many other templates like invoices, rent or sales receipt templates, work orders, and even in some purchase order templates (with minor modifications).

The template contains four named ranges defined to ensure comfort and make it easy to create lookup formulas and drop-down boxes. By pressing the F3 key on your keyboard, you will be able to view these named ranges or use them while entering a formula. You can also view these named ranges via Formulas > Name Manager in the Excel ribbon.

The following named ranges are divided into two categories. The first three are meant for use as the source for the drop-down selection box, while the last includes the entire customer table for use in the lookup formulas.

• _customer_id - This dynamic named range is meant for use as a source for the drop-down selection box that lets you select your customers by the customer ID.
• _customer_name - This is named range is another source for the drop-down box that lets you select customers by their name as opposed to their ID.
• _customer_company - Yet another source for the drop-down box that lets you select the company name instead of customer name or customer ID.
• _customer_list - This dynamic named range includes the entire customer table referenced in the lookup formulas explained below on this page.

Copy the Customer List Worksheet into your Workbook

There are several methods that you could follow to copy the customer list worksheet into your workbook. For example, you could use a simple copy and paste method, but this means that you'd need to define the named ranges from scratch as they won't get copied. The most convenient way to copy the entire worksheet along with predefined named ranges explained in the following steps:

1. Open the workbook to which you'd want to add a customer list and then open the customer list template.
2. Right-click on the Customer List tab then select "Move or Copy...".
3. In the small dialogue window, select the workbook to which you want to copy from the drop-down, then check the "Create a Copy" checkbox and click "OK".

Create a Drop-Down List for Selecting the Customer Name

Once the customer list worksheet is copied successfully into your workbook, you can create an in-cell drop-down list optionally using one of the named ranges as a source.

Following steps explain how to add a drop-down list for selecting customers by customer name or company name:

1. Select the cell in which you'd want to create a drop-down list (a.e. cell J6)
2. In the Excel Ribbon click on the Data tab then select Data Validation or use the following shortcut Alt + A + V + V
3. Select "List" under the "Allow:" field in the Settings tab.
4. In the "Source:" field enter =_customer_name or press F3 to select the named range from the "Paste Name" dialogue box

Create Lookup Formulas

When you select a customer from the drop-down list, you'd want other cells to display the related customer information on your invoice or work order template automatically. To do this, you will need to set up some lookup formulas to obtain relevant information from the customer table. There are several ways in which you could achieve the same results, for example, you could use the Excel LOOKUP function, but it will require you to make a small modification in the customer table as this function can only lookup data from left to right. In our example, we have used a combination of INDEX and MATCH formulas, which allows for more freedom when searching.

In the example above, we have used the cell J6 to set up the customer drop-down box. We will use this cell reference as a lookup argument in the INDEX-MATCH formula.

The formula below will display the customer ID, which is column 1 of the _customer_list range.

The column reference for a customer phone would be 6, which is the 6th column on the Customer table, therefore, the formula would look as follows:

The formula in both examples will display zero if the value on the customer table is blank. To overcome this exception, you can use the custom number format to hide zero values using the following "#;#;;@" or to display dash use the following number format instead "#;#;"-";@".

Another approach to combat this problem is to use the IF() function in the formula to return blank if a condition is True like so:

Related Content