Customer database in Excel: which columns, which sheets and an example to copy

What goes in a customer database in Excel?

A customer database in Excel for a service company has three sheets: Customers (who orders and pays), Sites (where the technician works) and Installations (what they maintain). For each customer, you record the official name, the VAT or company number, the contact person, the billing address, the language and the payment term. Each customer gets one row and a fixed customer code.

Where a customer database goes wrong

A customer database usually starts as a list of names and phone numbers. After a few years, you will recognise these problems:

• The same customer appears several times. “VME Residentie Linde”, “Residentie Linde” and “Linde VME”, each with a different address.
• Customer and site address get mixed up. The property manager (syndic) appears again for every building, as five different customers.
• One cell holds three pieces of data. Two phone numbers in one cell, or the full address in one cell.
• The units are in the comments. “Boiler in basement, replaced in 2019?”, with no brand or serial number.
• Several versions are in circulation. A copy on the desktop, one on the planner’s laptop, one in an email.

The result: the invoice goes to the wrong address and the technician has to hunt for the way in.

Customers, sites and installations: why three sheets

A service company works for a customer, at a place, on a unit. Those three rarely match one to one:

• A property manager is one customer with several buildings.
• One site sometimes has a boiler, a water heater and a water softener.
• The invoice goes to the customer, the technician goes to the site, and the history belongs with the installation.

On a single sheet, you repeat the customer’s details on the row of every building and every unit. If the billing address changes, you edit twenty rows and forget one. With three sheets, each piece of data is in one place, and a code makes the link: the customer code is on every site, the site code on every installation.

Customer database in Excel example: the columns per sheet

A template to copy into a blank Excel file. Column names and values are examples. On each sheet, put the column names in the first row, and nothing above them.

Customers sheet

More than one contact person per customer? Then add a fourth sheet, Contacts, with the customer code, the name, the job title, the email address and the mobile phone number.

Sites sheet

Installations sheet

When each unit is due for maintenance, you track in a separate maintenance schedule in Excel.

Keeping a customer database: rules for clean data

1. One row per customer. Search by name and VAT number before you add a row.
2. One piece of data per cell. Two phone numbers in one cell cannot be sorted, filtered or imported later.
3. Fixed formats. The VAT number always as BE0123.456.789, dates as real dates, phone numbers in one format.
4. Drop-down lists for fixed values. With data validation in Excel, everyone picks the language, the type and the status from a list. That way “French”, “FR” and “french” become one value.
5. Make duplicates visible. Conditional formatting on the VAT number column colours duplicate values. Two rows with the same VAT number are usually one customer with two sites.
6. Archive instead of deleting. A customer who leaves gets the status archived, and their code is never used again.
7. Check the VAT number. For a Belgian company, the VAT number is the company number with BE in front (business.belgium.be, in Dutch). How to check it is explained in checking a VAT number.
8. One owner. One person decides on the columns and cleans up. The file is kept in one place.

Customer data and the GDPR: the basics

The names and contact details of customers and contact persons are personal data. A company number is not, except for a sole proprietorship. That is what the Data Protection Authority (Gegevensbeschermingsautoriteit / Autorité de protection des données) writes in its GDPR guide for SMEs, Wegwijs in de AVG voor KMO’s (in Dutch, January 2018). From that guide, applied to a customer database:

• Only what is needed. Do not write unnecessary information in a Comments column. The guide gives the example of the personal reason for a late payment.
• Accurate and up to date. Process a change the customer reports as quickly as possible, and check incorrect email addresses and the combination of postcode and street.
• No longer than necessary. Decide how long you keep the data of former customers. A legal period applies to your accounting records: see the retention period for invoices.
• Secured. Only people who need the data for their work can access it, and a backup protects against loss.

Have your legal adviser check what that means for your file.

An example: building a customer database at a water treatment company

A company that installs and maintains water softeners keeps its customers on one sheet. It contains private individuals, a few shops and a property manager who manages five apartment buildings, each building on its own row.

The office employee converts the file to three sheets. The property manager becomes one customer with five sites, each with the caretaker as the on-site contact. The water softeners go on the Installations sheet. If a serial number is missing, the technician copies it from the rating plate at their next visit. For the business customers, the employee looks up the VAT number in the Crossroads Bank for Enterprises (KBO/BCE) and copies the official name. She merges duplicate rows after a phone call with the customer. From now on, only she adds new customers.

When Excel is no longer enough

A customer database in Excel is a list. It does not tell you what happened at the customer’s. Signs that the list is no longer enough:

• Two colleagues each work in their own copy, and a change gets lost.
• The technician on the road cannot see the installation, the access or the previous intervention.
• Reports, quotes and invoices sit in other folders, separate from the customer.
• A customer asks when the technician last came, and the answer is on a paper work order.

Why the history belongs with the unit is explained in the service history per installation.

How it works in Digitalnatie Service Suite

Service Suite follows the same three levels: customer, site and installation. Together they form the customer file.

• Customers: in the list of customers, new customer opens the creation form. The name, street and number, postcode and municipality are required; add, where possible, the VAT number, the email address, the phone number and the language (Dutch, French or English). Service Suite assigns the customer number (KL-…) itself.
• Duplicates: if the VAT number or the name already exists, Service Suite refuses to create the customer and the message names the existing customer.
• Contacts: when you add a contact, you choose its type: contact person, billing address, delivery address or other address.
• Payment terms and official details: when you edit the customer details. The payment terms field only appears for people who have both the invoicing permissions and the customer management permissions. The company details lookup (VIES / KBO/BCE) works on a VAT number that has already been saved.
• Sites: new site asks for the customer, the site name and the address, and where possible the coordinates, the on-site contact and directions.
• Installations: when you register one, you choose the customer, the site and a name, then where possible the brand and model, the serial number, the installation date and the end of warranty.
• Taking over your Excel file: in the configuration’s data import, the administrator downloads an Excel or CSV template for each type, with the required columns. The method is described in importing customer data.

Good to know:

• Merging two customers is not possible in the application. So clean up the duplicates in Excel, before the import.
• A site without coordinates does not appear on the map, and navigation is then not offered.
• An installation belongs to a site: if the customer does not have one yet, Service Suite asks you to create it first.
• The application and the guide are available in Dutch, French and English.

Frequently asked questions

How do I create a customer database in Excel?

Create three sheets: Customers, Sites and Installations. Give each customer one row and a unique code, with the name, VAT number, contact person, billing address, language and payment term. Refer to that code on the other sheets, and use drop-down lists and fixed formats.

Can I download a customer database in Excel for free?

This page has no file to download. It does contain a template in three tables: copy each table onto its own sheet in a blank Excel file, with the column names in the first row.

How do I avoid duplicate customers in Excel?

Search by name and VAT number before you add a customer, and let one person create new customers. Conditional formatting on the VAT number column highlights duplicate values.

Which customer data may I keep under the GDPR?

The data you need to carry out the work and invoice it, accurate and up to date, and for no longer than necessary. The names and contact details of customers and contact persons are personal data, the Data Protection Authority writes in its guide for SMEs. Have your legal adviser check what that means for your file.

Can I import my customer database from Excel into Service Suite?

Yes. In the configuration’s data import, the administrator downloads the template for each type, in Excel or CSV, and fills it in with your data. Before the import, a check shows which lines are correct, which are duplicates and which contain an error. A file is at most 10 MB.