Any healthy business will have a customer list. Some companies will use an old fashioned rolodex for this or maybe paper files. Still others will buy off the shelf software all the way up to the mammoth Customer Relationship Management systems.

These systems might be ideal but in many cases they will be overkill if your needs or scale are quite humble. A full system might be more functionality than you need right now, especially if you can count your customers on two hands.

If you are most comfortable in a Microsoft Excel environment you can make a pretty decent little system with your favorite spreadsheet package. A huge advantage of starting out in Excel is you can be pretty sure you can move your data to a new system as and when you are ready to move up to a full industrial strength suite.

Example Customer Database

To demonstrate, let’s put together an example customer database.

Customer Database

In this example we store some pretty simple details. You could add things like what products or services they have bought or when they were last contacted quite simply, but for now we will use it as a simple contact list.

Right now we can use the built-in Excel search feature to find the customer we want, but let’s get fancy.

Creating a Lookup Form

What we want to do is create an enquiry form so we can enter details and have Excel bring up the matching result.

We create a new tab and write out our form to make it look nice and usable.

Nice and Usable

As you can see in the screen grab above, when we enter a zip code we want all the other fields to populate. This is achievable by using the VLOOKUP function anywhere we want to draw in values from the customer list.

Customer List

Here is an example:

VLOOKUP($B$3,Customer!$A$2:$F$6,6,FALSE)

This is based on the first value being what we want to find, the range of the source values, and if we want it to be an exact or approximate match (which in Excel means the next largest value that is less than lookup_value is returned, providing the original values are sorted in order).

Extending the Example

Obviously there are limitations to this but it wouldn’t take a lot of effort to enhance the solution. First we could make the search able to switch between which fields we are searching on, for example a logical search field would be surname or company name.

A quick solution to allowing a greater search flexibility would be to add an IF statement to the VLOOKUP. Obviously this would get cumbersome very quickly, but as a quick hack it works.

Hack It Works

=IF(ISNA(IF(A3="Enter phone to search",VLOOKUP($B$3,Customer!$A$2:$F$6,6,FALSE),VLOOKUP($B$3,Customer!$B$2:$F$6,5,FALSE))),"",IF(A3="Enter phone to search",VLOOKUP($B$3,Customer!$A$2:$F$6,6,FALSE),VLOOKUP($B$3,Customer!$B$2:$F$6,5,FALSE)))

Basically, this IF function does 2 things, it checks whether to search using the Phone number or the Zip codes and adjusts the VLOOKUP range accordingly (the VLOOKUP function always searches for the value in the first column of the range). It also checks whether the entire VLOOKUP function returns a value at all using the ISNA() function so that a blank would be displayed instead of an error.

Instead of an Error

The second upgrade we could add is we could store more data. We could create a contact log, any relevant details such as if they have had any feedback or problems, and make notes on sales activity and actual purchases. This would allow us to track the impact of our sales and marketing, plus see who our best customers are. I will leave that an exercise for you!

Summary

I hope, while being only a start, this has given you some ideas for how you can create your own customer relationship database. Do you keep track of customers or any other similar data in Excel? Please let us know…

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter