How to Create a Customer Database in Microsoft Excel: Step-by-Step Tutorial and Template

Download Customer Database Spreadsheet Template — Microsoft Excel

This template is prefilled with some common customer database fields that you can change easily to suit your needs. It is also pre-formatted to simplify sorting and filtering your results. Check out our collection of Excel customer database templates for more options, and for more examples of personal data list templates, see our list of contact list templates and client list templates.

Maintaining an updated customer database can benefit customer relationship management (CRM) by ensuring that client contact and purchasing details are current. For more examples of how to use templates to assist in CRM, check out our list of free CRM templates.

First, let’s define some terminology. A database is a set of data organized into rows and columns. This formatting makes it easier to read and reference the data. 

Database entries are divided into records (or rows), and fields (or columns). A record is one row of a database that shows information about a single subject. In this example, a record is a  single customer and their corresponding information.

A field is one column of a database that contains all of the information in a single category; for example, you would enter phone numbers or names in a field.

Follow the steps below to easily complete a database template, with customizable records and fields.

1. When you download the customer database template above, it will automatically open in Microsoft Excel.
   
   Pro Tip: Click Enable Editing at the top of the spreadsheet. This step will allow you to make changes to the downloaded template.
   
2. Decide the type of  customer information you want to include in the database. In this example, we are using first and last names, phone numbers, customer identification numbers, city, country, and invoice numbers. You can name every field and enter any type of data in this fully customizable template.
3. Ensure the predetermined fields along the top row of the database template match the details you would like to include. To change a field, click the desired cell and type your new entry.
   

1. To enter a customer record, click cell 4B and type the customer number. Click the Tab key on the keyboard and type the last name in cell 4C.
   
2. Click the Tab key on the keyboard to navigate the spreadsheet and enter data in each record’s field.
3. Go record by record and enter the customer information you have on file. If you do not have all of the information you need, check your client info forms.
   
   Pro Tip: Keep the format for each field consistent. For example, if one phone number uses parentheses, all of them should use parentheses. Enter countries as full names or acronyms consistently.

1. You can sort records alphabetically. Click the drop-down arrow in the Last Name cell (C3). 
2. Click Sort A to Z. Click the OK button. Note: Your version of Excel might have options for Ascending and Descending.
   
   If it is a numerical field, you can sort your records numerically by clicking the drop-down arrow and Sort Smallest to Largest or Sort Largest to Smallest. Note: Your version of Excel might have options for Ascending and Descending.
   
   Pro Tip: If there is no drop-down arrow, click the Data heading, then Filter, and the arrows will appear on the main title fields in the template.
   

Download Customer Database Example Template — Microsoft Excel

This template is an example of a customer database for use in Microsoft Excel. The template has been prefilled with sample customer data, but each cell and field is fully customizable. Change the field headings and fill in customer data to create a database that is organized and easily searchable.

1. To format a single cell, right-click the desired cell. Click Format Cells… Cycle through the menu options Number, Alignment, Font, Border, Fill, and Protection to change number styles, fonts and colors, add or change borders, change the alignment, and protect these cells. 
2. To format a group of cells, hold the Shift key on your keyboard and click the top and the bottom cells of the desired range. Release the Shift key. Right-click anywhere inside the highlighted selection. Click Format Cells in the drop-down menu.
   

1. Click the cell you want to change. Right-click and click Format Cells... 
2. Click the Fill tab. 
3. Click the drop-down menu for Background color. Choose a color in the background color palette. Click the OK button. Note: You can also add or change the background pattern.
   

1. Click the cell you want to change. Right-click and click Format Cells... 
2. Click the Font tab. 
3. Click and scroll through the list of fonts, and select the one you want. Do the same to change the font style, font size, and color, as well as to underline. You can also add strikethrough, superscript, and subscript effects from this window. Make your selections and click the OK button.
   

1. Click the cell you want to change. Right-click and click Format Cells... 
2. Click the Border tab. 
3. Click a line Style, Color, and Border type. After you make your selections, click the OK button.
   

1. Click the cell you want to change. Right-click and click Format Cells... 
2. Click the Alignment tab. 
3. Select the horizontal and vertical alignment of text in cells from the corresponding drop-down menus. Change the number of degrees to adjust the orientation of the text in the cell. After you make your selections, click the OK button.
   
   Pro Tip: Orientation is a fun tool to stylize the title fields in your database. Select your title fields and change the orientation to 45 degrees for an interesting visual effect.
   

1. Click the cell you want to change. Right-click and click Format Cells... 
2. Click the Number tab. 
3. Click through each option to change how the spreadsheet is displaying numbers. You can adjust how it shows currency, with decimals and dollar signs, dates, with a consistent date format, percentages, and more. Choose the category that best describes how you would like numbers to be displayed and click the OK button.
   

1. Click the area where you would like to add a logo or picture. 
2. Click the Insert tab on the ribbon. 
3. Click Pictures and choose the location of the image you want to insert into the cell.
   
   Pro Tip: Excel has a large catalog of photos that you may find useful for personalizing your database in the Stock Images selection.
   
4. If the image you are looking for is on the computer you are currently working on, click This Device. Navigate to the folder containing the image you would like to insert. Select the image and click the Insert button.
   
5. Once you insert your image, press and hold the Shift key on your keyboard, then click and drag one of the corner circles of the image to resize it. Release the Shift key, then click and drag the center of the image and place it where you like.
   
   Note: You may need to resize a row or column to make room for your image. Click the row you need to change, click on the green square at the corner of the row, and drag it to expand or shrink.
   

1. When you have completed customizing the customer database, you should protect it from accidental changes. Click anywhere on the spreadsheet. (Note: You can also highlight and select specific cells.)
2. Click the Review tab on the ribbon. 
3. Click Protect Sheet.
   
4. You can set a password for your document. Users without the password can only perform the actions that you select in this window. Users with the password will have full editing capabilities. 
5. Make your selections, and click the OK button.
   
   Pro Tip: It is a good idea to protect your database when you share it with people who need to view but do not need to make changes to it.

1. To find the records that match certain criteria (in this example, customer records from the same city), format your data as a table by highlighting all of the data you have entered, including your field titles.
2. Click the Home tab, then click Format as Table and choose a style.
   
3. The Create Table window will open. If your data is already highlighted, the Create Table window will automatically populate with the location of your data.
4. Make sure you select the My table has headers check box and then click the OK button.
   
5. Next, right-click on the cell you would like to filter your results by. In this example, we only want to see the records of customers that come from Tracy.
6. Click on Filter in the drop-down menu, then click Filter by Selected Cell’s Value.
   
7. The records that do not contain the word “Tracy” in the City field will be hidden from view. To reveal the hidden records, click the arrow button in the City field.
8. Click Clear Filter From “CITY” in the drop-down menu, and then click the OK button.
   
   Pro Tip: The drop-down menu will automatically populate the title of your field in the Clear Filter From selection, so make sure that it matches the filter you would like to clear.