How to make a Customer Database in Excel

Keeping track of your business’s customers isn’t as easy as it sounds, as I’m sure you’ll agree. The more customers you get, the busier you’re likely to be, which results in less time to keep on top of those seemingly less important jobs - such as keeping your business’s customers in one, easy-to-locate database.

In this blog post we’ll explain how to make a simple customer database in Excel, and in a quick and easy way to get you started.

Just want the Sample Customer Database Excel Sheets?

The Excel customer database templates are available to download at the bottom of this post.

Using a simple Excel Spreadsheet

We’re going to use Microsoft Excel because most people have it, whether it’s the desktop version or the cloud-based one. And, you can create the database in few minutes with a process you’re familiar with. You could also use something like Google Sheets, if you use that instead, as the process is virtually identical.

Create the Database Headings

It’s best to spend a little time thinking about what headings you would use now to record the data and how you would query the database fields in the future. If you’re not sure, you can skip to the next section Gathering Customer Data to get an idea of what you’re already using and then come back.

So, the basic contact information such as Contact Name, Company Name, Email Address, Telephone number are all fairly standard and a good starting point. Here’s a visual example of a basic sheet using those fields:

You can see I’ve not used Contact name but separated this out into Firstname and Lastname. The reason for this is that I know I’m going to send informal emails to my customers, along the lines of “Hi firstname, “, so with a little thought at the input stage, you can make things a bit easier further down the line.

Here’s a list of customer fields that might be good to start with:

• Firstname
• Lastname
• Company
• Telephone
• Email
• Address Street
• Address 2
• Town/City
• County
• Post code

And here’s an example of how that looks in spreadsheet form:

Thinking of more headings

Once you have the basics, now’s the time to add in those industry-specific fields or business-related customer fields. Here are a few examples:

• Customer Code - If you have an internal reference for the customer (either through an accounting system or in document management system).
• Number of Employees - If you market certain products or services to different sized companies.
• Website - Useful for checking out your customer’s current products and services and how you might help them.
• Social Media - Again, like the website address you could gain extra insight into your customers by using this further down the line.
• Industry - What industry or sector your customer is in. Useful for marketing and measurement.
• Source - If you use different sales & marketing channels, keeping track of this identifies where the original lead came from.
• Referral Source - Like, the source above, if you have referral partners you might want to keep track of this to find out who your best partners are.

Gathering Customer Data

Once you have your list of headings in your customer database set up, you’re ready to start populating it. How you do this depends on where your customer data is currently located, but if you’re like a lot of small businesses it’s probably in lots of different places. From different email accounts, old access databases, accounting systems, marketing email lists, paper records, notepads, or even rolodex cards(!) Don’t worry, it happens to the best of us!

But now’s the perfect time to start pulling it all together. And you don’t have to do the whole lot, you can do it bit by bit - every little helps! And as long as you and your staff use your new Excel database you’ll eventually be on top of the full list without exerting too much effort.

Maybe start with the place where most of your customer records are (such as email or accounting system) and start pulling that data out, either by exporting it if the system supports it, or by getting it manually exported. Now you have your new customer database format you could get an intern or outsource it to a virtual admin assistant to do most of the work for you.

So, armed with more fields and a couple of sample records, here’s how the spreadsheet’s looking now.

Making the Excel Database a bit easier on the eye

If you’re using this as a way to collate all your customer data then you might as well make it a little bit nicer to look at and use. We’ll make the headings column stand out a little more and fix the headings row (or freeze it) to make sure the headings are ‘sticky’ when you’re scrolling through the list of records.

Styling the heading row

To make the top row bold, we just click the first row (the bit that says ‘1’) and the whole row should be selected. Then select the Bold button (the one with the big ‘B’) to embolden the row. Feel free to add a nice background colour if you prefer something a little more vibrant too.

Freezing the heading row

To access the Freeze option, you need to click on the View menu bar item on the right, then click the Freeze Top Row button on the revealed button bar - that’s it! You now have the header fixed so when you scroll through your records you can see what fields they refer to.

Where to go from here?

A small business might need something simple to begin with but at a certain point there will be a need to automate the process of adding contact information to a database from your company website. Incoporating both customers and prospects, referral partners and more.

And if you grow your salesforce you might need extra capabilities to manage your sales pipeline and track performance of your sales team.

Reldesk is simple crm software for small business, but with powerful features that grow as your business grows. Taking building a customer database much further to save you time and enable your growth.

Get The Sample Customer Database Excel Sheets

Don’t have time to create the Excel sheet yourself? Get the sample sheet shown as well as another which includes lookups for the Industry and Source fields so you can select from a pre-populated dropdown. There’s also room to add or change these lookups, allowing you to personalise it easily.

Just click the download links below to get the templates