Potential client list template

This page explains how to create a Customer Drop-Down List in a spreadsheet and use Lookup formulas to automatically display customer information in other cells based on the value you have selected from the drop-down list. Instead of doing it all from scratch, you can download the Customer List template below which has a customer table and dynamic named ranges already defined to make the creation of the drop-down list and lookup formulas easier. Keep reading below the download area for instructions on how to set everything up.

Advertisement

If you are looking for a template to help you store and organize your general contact information, try our contact list template. This customer list worksheet is designed specifically for creating drop-down lists and lookups, not for general contact management.

Customer List Template

for Excel

⤓ Download

For: Excel 2007 or later & Excel for iPad

License: Private Use (not for distribution or resale)

"No installation, no macros - just a simple spreadsheet" - by Jon Wittwer

Description

This template is mainly for intermediate spreadsheet users who are looking for a way to easily add customer lookup functionality to their spreadsheet(s). It is based on the technique used in our invoice tracking and invoice manager templates.

The Customers worksheet within this template is intended for use with other templates, such as invoices or work orders.

There are three named ranges defined in this spreadsheet that help make it easy to create drop-down boxes and lookup functions.

• CustomerList - This named range includes the entire customer list table (the header as well).
• CustomerName - This is a dynamic named range that is meant for use as the Source for a drop-down selection box.
• CustomerID - Another dynamic named range that could be used for the Source of a drop-down.

Tip: You can press F3 in Excel to view and select a named range in your workbook while entering a formula.

IMPORTANT: When using a customer list in an invoice spreadsheet, it would not be wise to send the actual spreadsheet to the customer (because it contains your list of customers). Instead, you would want to just print the invoice or create a PDF of the invoice worksheet.

Step 1. Copy the Customers Sheet into your Workbook

1. Open both the customer list template AND the invoice or work order template that you are wanting to copy to.
2. Right-click on the Customers tab and select "Move or Copy". Make sure to check the "Create a Copy" checkbox.
3. Select the workbook you want to copy the sheet to, then press OK.

After you have the worksheet copied into your spreadsheet, you can set up a drop-down box and lookup functions.

The image below shows a drop-down list that uses the Customer Name. It is a screenshot from the Invoice Tracking template. The instructions below explain how the drop-down box is created.

1. Select cell C9 and go to Data > Validation to open the Data Validation dialog.
2. In the Settings tab, choose List from the Allow field.
3. In the Source field, enter =CustomerName

After you have selected a value from the customer list cell, you want other cells to display the related customer information. There are many ways to create lookup formulas. We will use the VLOOKUP method because the Customer Name is the first column in the Customers worksheet.

Our Spreadsheet Tips Workbook and article "VLOOKUP and INDEX-MATCH Examples" explain in detail how to create various types of lookup formulas, so if you want to use another method, such as INDEX-MATCH, see the article or the workbook.

Note that cell C14 in the example above shows the Phone number. The Phone number is in column 7 of the Customers sheet. Or more specifically, it is column 7 of the CustomerList range.

The formula for cell C14 could be:

=VLOOKUP(C9,CustomerList,7)

The problem with the above formula is that if the value in the table is blank, the formula will display a zero. There are two ways around that. One approach is to use a custom number format like "#;#;;@" to display blank when the value is zero. The other way, and the way we do it in the invoice tracking template, is to handle the exception with an IF() formula, like this:

=IF( ISBLANK(VLOOKUP(C9,CustomerList,7)), "", VLOOKUP(C9,CustomerList,7) )

• Service Invoice with Customer List - This invoice template was one of the first templates we created that included a built-in customer list with drop-down selection and lookup functions.
• Invoice Tracker - This Customer List template is exactly what was used to create the invoice tracking template.
• Invoice Assistant - Our popular invoice manager spreadsheet uses these same techniques to automate the task of creating new invoices.

Speaker Coach in PowerPoint gives smart, real-time feedback so you become a more confident presenter. Try it now

Years ago, I felt I was ready to start working as an illustrator but I didn’t have any clients. I dreamed of working for clients like The New York Times or Scientific American, but they looked completely unattainable to me.

Nowadays they are two of my regular clients, and I love working with each of them.

Back in the days before I had big clients, I developed a plan to start opening the way. I started a wish list of the clients I wanted to work with, and I collected dozens of email addresses and sent emails to many Art Directors.

The problem was that I found it tedious to keep track of sent and pending emails. I lost hours with temporary lists that led nowhere.

I needed solutions, and I decided to start a spreadsheet. Over time my spreadsheet evolved, and eventually it became very effective.

Today I want to share that spreadsheet with you, so you can use it to help reach your goals.

Why it is useful:

• Organize your client wish list
• Avoid contacting potential clients too often
• Eliminate unproductive or hard to use lists
• Keep track of your potential clients in a single place
• Have additional valuable information about your desired clients.
• Easily find the last contact with a client
• Quickly identify the great clients (those you love to work with) and the bad ones (those you should avoid working with).

What it includes:

• Client’s name
• Position in the company
• Company name
• Field
• Email address
• Last time you contacted them
• Whether client responded
• Color coding to identify client interactions

Use the spreadsheet

You can create a copy of my Google spreadsheet and use if for yourself.

Just follow the link and go to File > Create a copy.

Some notes

• The spreadsheet is designed to work as a wish list and to keep track of email inquiries you have sent.
• It also works to keep track of correspondence with current clients.
• I use email tools to obtain information on clients I would love to work with. As soon as I find a potential art director, creative, media, studio or agency I wish to work with, I add them to the spreadsheet.
• I contact potential clients personally, and I never send generic emails. (You can learn about how to properly email potential clients in this post).
• For me, deciding if a client is a good fit depends on several factors: how the process went when you worked together, budgets, how long it took to get paid, respect for your work, etc.

When you and your work are mature and ready to start working for clients, you should take action. You need to be active and proactive to get assignments. Nowadays, it means sending a lot of emails.

Otherwise, be prepared to wait for years.

Having a place to track and follow up on your wish list of clients is very helpful. It avoids wasting lots of time and burning yourself out.

The process of finding clients is going to be long and tedious, but with this spreadsheet, it can be a bit more bearable.

Building a client list for your business is a continuous process. You need to review the list often to understand the behavior and business interactivity patterns of your audience. Because there is a lot on the line in business relationships, it can take weeks or even months to put up your list together. The first thing you need, though, is a reliable tool that you can use to create a list of your clients, and a project templates is, of course, the cheapest reliable option. The following is the list of useful client list templates.

59+ FREE LIST Templates - Download Now Adobe PDF, Microsoft Word (DOC), Microsoft Excel (XLS), Google Docs, Apple (MAC) Pages, Microsoft Publisher, Apple (MAC) Numbers

Activity Client List Template

mypmllc.com

Details

File Format

Size: 19 kB

Download

Creating a list of active, repeat clients for your freelancing project can help you to optimize your workflow. To do this in the simplest way possible, consider downloading and using this template.

Client Call List Template

ilcadv.org

Details

File Format

Size: 4 kB

Download

Sometimes clients call for help when they need something; you need to create a call list to respond to their issues when you have the best solutions to their problems. For the best call list, use this template.

Potential Client List Template

thebuchanangroup.net

Details

File Format

Size: 58 kB

Download

A potential client is one who requests your service or buys your goods from time to time. By creating a potential client list, you are able to determine a customer’s behavior pattern in relation to your business.

Inspection Company Client List

absa.ca

Details

File Format

Size: 13 kB

Download

If you are busy with inspection work, you need to arrange your clients in the order of project urgency. This will help you to not only provide the best services, but also provide timely services to all your clients.

Word Documentation Checklist Template

fda.gov

Details

File Format

Size: 13 kB

Download

Use the checklist to highlight the names of the clients that buy your goods or services. The beauty of this template is that it is easy to read and you can also edit it if you want. You may also visit Project Status Templates.

Capital Telecom Client List

capitaltelecom.com

Details

File Format

Size: 198 kB

Download

> Uses of the Client List Template

If you are a freelancer who aspires to work online, you can use the client list template to create a list of the names of the clients that you would like to work with on a long-term contract. The terms of the contract will depend on the relationship between your freelance business and the customers on your list. You can also see Project Estimate Templates.

A client call list is one of the most important tools for business, for it helps you to provide your buyers the best customer support whenever they need it. Note that each client will have different needs, which means you will have to schedule enough time to offer help to each client.

> Who Should use the Client List Template?

Sole traders who provide online services can use the template as a paper database to record the details of their customers. The template is useful if yours is a business with a small number of repeat customers. You can also see  Project Tracking Templates.

Freelancers can use this template to keep an up to date record of loyal clients who order writing, SEO, and other website related services. You can include the names, e-mail addresses, and the phone numbers of those clients and get in touch with them whenever the need arises.

> What are the Advantages of a Client List Template?

The template is free, that means you don’t have to create the model yourself. Being able to customize the outline of the document gives you the full rights to change the design if you want. Overall, the goal of creating this template is to save you time by eliminating the need to create the model from scratch. Even if template design is your forte, it is just easy to download the template as opposed to creating the model from the ground up.

As you continue to provide services to your clients, your customer base is going to expand. However, don’t get too excited to a point that you expand the list beyond limits. Keep the number to a minimum so that you continue doing great business without failing to meet the expectations of your business.