Full version with all templates and detailed checks. Back to the main article

Create an Excel customer database: template for small trades businesses

A customer asks about renovating a bathroom. Later, a second project at the same house follows. The phone number stays the same, but the bathroom needs a callback and the WC already needs a quote followed up. A customer list with just one note field makes these tasks hard to distinguish. This is the starting point for the fictional example in this template. ^[a]

The Excel file therefore separates customers and records. Each customer receives a fixed customer ID, or customer number. Each enquiry and each order gets its own row with its own record ID and the linked customer number. Contact details stay in one place, while the next task sits against the relevant project.

The template helps you maintain this overview yourself. It sends no messages and replaces neither invoicing nor bookkeeping. All contacts, names and properties in the file are fictional examples.

Download the Excel template

Open and download the Excel workbook

The workbook comes as an XLSX file, the file format used for this Excel template. Your own copy gives you room to try the sample data before setting up the file for your business. The workbook contains no macros (small built-in programs) and is not connected to your email inbox. Keep invoicing, bookkeeping and detailed job costing separately.

The file has 100 prepared customer rows and 200 record rows. This is the capacity of this file, rather than a recommended business size. If you need more rows, extend the tables, formula ranges and data validation together. Data validation defines which entries are intended for a cell. Simply appending rows is not enough for this template, because its formulas use fixed ranges.

The template's four sheets

In everyday use, start with the contact details and the specific enquiry. The customer card then summarises what belongs to the selected customer. The whole file uses one shared review date for due date checks: the day for which you are reviewing open tasks. German sheet and column names are retained as technical labels, with English explanations below.

Kunden: keep contact details in one place

The ‘Kunden’ sheet contains one row per customer. Its columns are Kunden-ID (customer ID), Kunde / Firma (customer/company), Ansprechperson (contact person), Telefon (phone), E-Mail (email), Straße (street), PLZ (postcode), Ort (town/city), Kunde seit (customer since), Notiz (note) and ID-Prüfung (ID check). The last column is calculated. If an email address changes, update it in this customer list. The records remain linked through the customer number.

You do not have to fill in every field during the first conversation. For an initial callback without commitment, you may not yet need the exact street address. What matters is which details you actually need for the particular purpose. The WKO explains this principle as data minimisation. ^[1] The note field is for short, factual information about the next contact. It should not contain personal speculation or sensitive details collected just in case. ^[b]

The customer ID, phone number and postcode fields are formatted as text in the template. This is intended to retain leading zeros and a leading plus sign. Microsoft describes text formatting as a way to retain numerical codes in their original format. ^[2] ‘Kunde seit’ (customer since), by contrast, is a date cell. The template displays dates as year-month-day, for example 2026-10-12.

Vorgaenge: give each project its own row

The ‘Vorgaenge’ sheet holds the details of each enquiry or order. You enter Vorgangs-ID (record ID), Kunden-ID (customer ID), Objekt / Einsatzort (property/work site), Anliegen (enquiry), Eingang (date received), Status, Nächste Aktion (next action), Fällig am (due date) and Zuständig (assigned to). Excel calculates the ‘Kunde (Formel)’ (customer, calculated), ‘Zuordnung’ (customer link), ‘Termin’ (due date check) and ‘ID-Prüfung’ (ID check) columns.

The customer ID connects the two sheets. The displayed name alone would not be suitable, because different people or companies can share a name. For another project for the same customer, the customer ID therefore stays the same. Only the record ID is new.

In the entirely fictional example, it looks like this:

Both rows belong to ‘Sample Customer Alpha’. The bathroom still needs a site visit, while the WC has already reached the quote stage. The property description helps distinguish these projects during the next phone call. On the review date of 2026-10-12, both records are open and one action is due. ^[a]

Each row holds the current next task. If you also want to retain a complete history of earlier conversations and messages, you need a contact history in a separate activity log or a suitable CRM. A property description alone also cannot fully manage several buildings with their own documents.

Kundenstammblatt: open the customer card

In ‘Kundenstammblatt’, select a customer ID in cell B5. The card brings in contact details from ‘Kunden’ and counts the linked records. It also shows the number of open records, actions due by the review date and open records without a due date. The individual tasks remain in ‘Vorgaenge’.

Selecting M-K001 opens Sample Customer Alpha's card with two open records and one action due on the review date shown. An unknown customer ID or one assigned more than once triggers an alert. When the number is duplicated, the customer link stays unresolved rather than showing an arbitrary customer.

How to use the customer database

The sample is enough to get started. In your own copy, you can select another customer ID or move a due date first and see how the display changes. Add real customer data only once the intended access protection is in place.

Enter your customers in the prepared rows 8 to 107 in ‘Kunden’. Each gets a fixed identifier such as K0001. This ID stays the same when you sort and is not calculated from the row number. Otherwise, changing the order of the list could change the link to the records.

For a new enquiry, add a row in ‘Vorgaenge’, within the prepared range from row 8 to 207. Select the existing customer ID, assign a separate record ID and describe the enquiry for the relevant property. This row also holds the next task and the person responsible for it. ‘Call back about site visit’ says more clearly what needs doing than ‘Maintain customer contact’. The ‘Fällig am’ (due date) field records when the task should be completed.

The status describes the stage the record has reached. The dropdown list contains New, Callback pending, Open quote, Commissioned, Completed and Cancelled. Only ‘Completed’ and ‘Cancelled’ count as done in this template. The other allowed statuses remain open.

The next task often changes after a conversation. Update the existing record row when it does. An additional project gets a separate record. This makes it clear whether you are continuing an existing enquiry or recording another one.

To review the tasks, enter the required review date in ‘Anleitung’, B5. All due date checks in the file refer to it. The template does not send automatic reminders, so reviewing tasks remains a job for someone in the business. CRM stands for Customer Relationship Management. A CRM system manages customer relationships and, depending on the system selected, can offer more features.

Filter, sort and understand the alerts

The data ranges in ‘Kunden’ and ‘Vorgaenge’ are set up as Excel tables. The arrows in the headers let you show, for example, only the records for customer ID M-K001. A filter temporarily hides the other rows without deleting their data. This is explained in Microsoft's filtering guide. ^[3]

Sorting changes the order instead. The whole table must stay together. If only the name column moved while phone numbers and other details stayed in place, they could end up against the wrong customer. Microsoft therefore warns against sorting just part of a connected range. ^[4]

The calculated alerts help with the review that follows:

• ID-Prüfung (ID check): ‘ID missing’ and ‘Duplicate ID’ concern the customer or record identifier. Correct these errors first.
• Zuordnung (customer link): An unknown or duplicate customer ID prevents the name from being brought into the record automatically.
• Termin (due date check): ‘Overdue’, ‘Today’, ‘Due date missing’ and ‘Action missing’ show where something needs doing or an entry is missing. ‘Scheduled’ means a later due date.
• Check status or Check date: These mean the status entered does not match the allowed list, or the date field contains text instead of a date value.

Dropdowns, or selection lists, make entries more consistent. They do not replace checks when bringing in data from other spreadsheets. According to Microsoft's data validation guidance , the relevant warnings do not appear during copying and filling operations. ^[5] That is why ‘ID-Prüfung’ (ID check), ‘Zuordnung’ (customer link) and ‘Termin’ (due date check) remain part of the review after pasting data. Keep the blue formula columns intact. Do not overwrite them with your own entries.

Export CSV files when switching systems

A new customer management system may require CSV files, simple text files containing tabular data. In that case, export ‘Kunden’ and ‘Vorgaenge’ separately. For CSV, Excel saves only the active sheet, and workbook formatting and features may be lost. Keep the complete XLSX copy. ^[6]

Select the required file type under ‘File > Save As’. The steps are described in Microsoft's guide to text and CSV export. ^[6] Only an import test with sample data will show whether the new customer management system correctly transfers your IDs and the links between customers and records.

Protect customer data

The supplied file is unencrypted and contains only sample data. Before entering real contacts, your business needs suitable protection and clear rules for processing the data. Downloading a template does not, by itself, make its use GDPR-compliant.

Each processing purpose needs an appropriate legal basis, meaning legal permission to process the data. Depending on the purpose, this could be consent, a contract or a legal obligation. The people concerned must also be informed about the processing. Setting up your customer management therefore includes defining the purpose, the necessary fields and deletion rules. The WKO explains these data protection principles, including data minimisation and storage limitation. ^[1]

The storage location is part of this decision too. Only authorised people should be able to access customer data. The technical and organisational measures required depend on the risk of the processing. The Data Protection Authority also explains restoring availability and regularly checking the measures. ^[7]

Worksheet protection can prevent accidental changes to locked cells. According to Microsoft's explanation of protection and security in Excel , it is not a security feature. ^[8] Locking individual cells therefore does not replace suitable protection for the file and its storage location.

Choose a working copy and backup arrangements

Everyone in the business should know which copy is current and who maintains it. Otherwise, after a change it can be hard to tell which file holds the latest version. A designated working copy gives everyone a clear reference point. ^[b]

Keep backups in the designated protected location. They also contain customer information and need appropriate access restrictions. The WKO lists backups and access controls as examples of suitable protective measures. ^[9] You can use sample data to test whether a backup can actually be restored. ^[b]

When a CRM is a better fit

The Excel template suits a workflow where one shared working copy is enough to hold current tasks and who is responsible for them. It reaches its limits when, for example, you need a complete contact history or different access rights. Many file versions being edited at the same time are also a reason to reconsider the workflow. The number of contacts alone does not determine when to switch. ^[b]

The guide to choosing a CRM for a trades business helps you describe these requirements before comparing systems. For the agreed scope of work and other order details, there is a separate order record. The customer list remains the shared place for your contact details.

Keep the IDs when you switch later. After exporting customers and records separately, use sample data to test whether the new system connects them correctly. Only inspecting the imported data will show whether the required details and links have arrived.

Your next step

Sample customer M-K001 offers a manageable starting point. In your copy, open the customer card, filter the records by the same ID and compare the two tasks. Then change a sample due date and the review date to see what the due date check refers to.

You can then decide whether this way of working suits your business. Set up your own workbook and access protection before adding real customer data.

Further guides

• CRM for trades businesses: describe and compare what you need from customer management.
• Create an order form: record the agreed scope of work separately.
• Handle customer enquiries with AI: organise website enquiries you have already received.

Sources and publication date

Sources on Excel features and data protection appear beside the relevant notes. The illustrations show the workbook created for this guide with fictional sample data. Statements about capacity, fields and calculations refer to this specific template. The sample and suggested workflow are intended for trying out in your own business.

Editorial version dated 16 October 2026. Numbers link to sources, while letters explain editorial examples or recommendations. Each source note states what the source supports.

[a] Editorial examples. Content labelled as sample or fictional illustrates possible situations. These are not real customer cases or evidence of a measured effect.

[b] Practical recommendations. The suggested workflows and decision aids are editorial applications of the principles cited. Their specific effect on enquiries or orders has not been measured here.

Sources and context (9 sources)

1. Data protection principles at a glance — Wirtschaftskammer Österreich

   Supports the general principles covering legal basis, transparency, purpose limitation, necessary data and storage limitation. Does not determine the applicable legal permission or a business's specific deletion or retention period. The callback and note-field examples are editorial suggestions for applying the principles. Checked: 2026-10-16.

2. Keeping leading zeros and large numbers — Microsoft Support

   Explains the automatic conversion of numerical codes and how text formatting retains their original format. The text formatting of these specific fields was checked separately in the XLSX file. This does not guarantee that external imports or pasted entries will be error-free. Checked: 2026-10-16.

3. Filter data in a range or table in Excel — Microsoft Support

   Supports the explanation that filters temporarily hide rows that do not match, and that filtering is controlled through column headers. Does not establish correct customer links in this specific template or any effect on the business. Checked: 2026-10-16.

4. Sort data in a range or table in Excel — Microsoft Support

   Supports the warning about sorting part of a range separately and the use of sorting for a whole connected table. Microsoft explicitly states that the partial-range sort described cannot be performed in this way within an Excel table. The article warns against manually separating related details, rather than against the normal table feature. Checked: 2026-10-16.

5. More on data validation — Microsoft Support

   Specifically supports the statement that data validation warnings do not appear when data is entered by copying or filling cells. This does not mean all entries or all Excel versions produce the same errors. Checked: 2026-10-16.

6. Save a workbook to text format (.txt or .csv) — Microsoft Support

   Supports the statements that CSV saves only the active sheet, text formats remove formatting, and changing format can lose data or features. Describes Save As and Save a Copy. Makes no claim about a specific CRM import or guaranteed transfer of the relationships. Checked: 2026-10-16.

7. Your obligations as a controller — Austrian Data Protection Authority

   Supports choosing technical and organisational measures to suit the risk, restoring availability and regularly checking effectiveness. The authority has not assessed this specific file or the reader's organisation. Downloading it provides no guarantee of compliance. Checked: 2026-10-16.

8. Protection and security in Excel — Microsoft Support

   Supports the distinction between worksheet protection and file protection. Worksheet protection restricts changes to locked cells and is not a security feature. Does not establish GDPR compliance or that a password alone provides sufficient security. Checked: 2026-10-16.

9. GDPR: Data security through technology and data protection-friendly defaults — Wirtschaftskammer Österreich

   Lists backups and access controls as examples of technical and organisational measures. Does not establish a particular secure Excel configuration or a successful restore test. The suggested test with sample data is a practical aid. Checked: 2026-10-16.