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 ofExcel customer database templatesfor more options, and for more examples of personal data list templates, see our list ofcontact list templatesandclient list templates.

Maintaining an updated customer database can benefitcustomer 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 offree CRM templates.

First, let’s define some terminology. Adatabaseis 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). Arecordis 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.

Afieldis 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:ClickEnable Editingat 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 cell4Band type the customer number. Click theTabkey on the keyboard and type the last name in cell4C.
   
2. Click theTabkey 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 yourclient 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 theLast Namecell (C3).
2. ClickSort A to Z. Click theOKbutton. Note: Your version of Excel might have options forAscendingandDescending.
   
   If it is a numerical field, you can sort your records numerically by clicking the drop-down arrow andSort Smallest to LargestorSort Largest to Smallest. Note: Your version of Excel might have options forAscendingandDescending.
   
   Pro Tip:If there is no drop-down arrow, click theDataheading, thenFilter, 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. ClickFormat Cells…Cycle through the menu optionsNumber, Alignment, Font, Border, Fill, andProtectionto 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 theShiftkey on your keyboard and click the top and the bottom cells of the desired range. Release theShiftkey. Right-click anywhere inside the highlighted selection. ClickFormat Cellsin the drop-down menu.
   

1. Click the cell you want to change. Right-click and clickFormat Cells...
2. Click theFilltab.
3. Click the drop-down menu forBackground color.Choose a color in the background color palette. Click theOKbutton. Note: You can also add or change the background pattern.
   

1. Click the cell you want to change. Right-click and clickFormat Cells...
2. Click theFonttab.
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 theOKbutton.
   

1. Click the cell you want to change. Right-click and clickFormat Cells...
2. Click theBordertab.
3. Click a line风格,颜色,andBordertype. After you make your selections, click theOKbutton.
   

1. Click the cell you want to change. Right-click and clickFormat Cells...
2. Click theAlignmenttab.
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 theOKbutton.
   
   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 clickFormat Cells...
2. Click theNumbertab.
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 theOKbutton.
   

1. Click the area where you would like to add a logo or picture.
2. Click theInserttab on the ribbon.
3. ClickPicturesand 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 theStock Imagesselection.
   
4. If the image you are looking for is on the computer you are currently working on, clickThis Device. Navigate to the folder containing the image you would like to insert. Select the image and click theInsertbutton.
   
5. Once you insert your image, press and hold theShiftkey 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 theReviewtab on the ribbon.
3. ClickProtect 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 theOKbutton.
   
   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 theHometab, then clickFormat as Tableand choose a style.
   
3. TheCreate Tablewindow will open. If your data is already highlighted, theCreate Tablewindow will automatically populate with the location of your data.
4. Make sure you select theMy table has headerscheck box and then click theOKbutton.
   
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 onFilterin the drop-down menu, then clickFilter by Selected Cell’s Value.
   
7. The records that do not contain the word “Tracy” in theCityfield will be hidden from view. To reveal the hidden records, click the arrow button in theCityfield.
8. ClickClear Filter From “CITY” in the drop-down menu, and then click theOKbutton.
   
   Pro Tip:The drop-down menu will automatically populate the title of your field in theClear Filter Fromselection, so make sure that it matches the filter you would like to clear.