Need a way to quickly manage, filter, sort, and update your customer list?
As a small business owner, you know that your customer list is one of the most important assets you have. This list includes people who have done business with you or have shown interest in your products in the past.
Excel is a great tool for managing your list because it provides:
- Easy data entry methods
- Sorting capabilities
- Filtering tools to pinpoint an exact subgroup of customers
- Graphing capabilities to visualize your customer demographics
- And many more…
So let’s start setting up our customer list in Excel.
This tutorial applies to Excel 2016, 2013, and 2010.
Creating a Customer List
You can create a customer list in two ways – either by entering data or by importing it from another source.
Let’s set up a customer list with column headers in the top row and where individual customers are being entered line by line down the spreadsheet. Start by typing in the column header names. These headers will vary based on your specific needs but typically include things such as:
- Telephone number
You could also add a column header describing the relationship you have with this customer ( i.e. prospect, lead, paying customer, repeat buyer, etc.). And lastly, you might want to add a column for each of your products so that you can match customers with the products they have shown interest in or purchased.
With the column headers done, start populating your list by adding the information of existing customers. These should be easy to find by looking through your past invoices. Here is an example of what your list might look like:
Take care not enter too much information in a single cell. For instance, instead of fitting an entire address in one cell, break it up into columns such as Address, City, Zip Code. This will simplify filtering of the data later on.
If you have an existing customer list in another program, chances are you might be able to export the list as a CSV file and then import it into Excel. Import the data by doing the following:
- Choosing File -> Open.
- In the bottom right, choose to display All Files.
- Select the CSV file from the browser and click Open.
- If your data does not contain headers yet, insert a row at the top and enter header names.
To learn more about CSV files, check out this article.
Formatting Your Customer List as a Table
Spreadsheets that contain many rows of data are easily managed with the use of an Excel Table. Not only does a table make your data look neat, it also provides amazing sorting and filtering capabilities and makes it easy to enter functions and formulas.
To convert your customer list into a table, do the following:
- Click anywhere inside your list of data and use the keyboard shortcut Ctrl + T. Alternatively, click on Insert -> Table.
- Check that Excel’s guess of the table’s cell references are correct.
- Tick the box that says “My table has headers” to let Excel know we’ve already added column header names in Row 1.
- Click OK.
Excel then converts your data into a table with alternating shades of dark blue and light blue (the default look). It looks like this:
A single contextual tab called Design is also displayed in the Ribbon with further options to customize your table. If you wish, you can change the appearance of your table by clicking on one of the styles in the Table Styles group on the right hand side of the tab.
To add more rows to your table, simply start typing in the first row underneath the table. As soon as you navigate away from the cell, Excel will include the new row in the rest of the table and format it accordingly.
You can find more information regarding Excel tables here.
Using Number-Formatting for Better Data Entry
By default, cells in Excel are formatted to contain text (right aligned) or numerical values (left aligned).
A problem arises when we want to enter numbers that ought to be interpreted as text. For instance, when you enter a phone number with a leading zero, Excel will format that input as a numerical value and thus remove any leading zeros.
For example, “011 982 5000” becomes “119825000”
The best way to fix this is to change the number formatting of the entire column of telephone numbers. To change the number formatting of a column:
- Select the column you would like to format.
- On the Home tab, in the Number Formatting group, select Text from the Number Format drop down list.
Chances are you will need to correct values that you’ve entered before this time (since they have already been formatted as numbers), but any values entered into this column going forward will display correctly.
Sorting Your Customer List
Now that your data is formatted as a table, sorting becomes a breeze.
To sort data by a particular column, do the following:
- Click on the dropdown next to the particular column.
- In the Sort section of the popup box, select either Ascending or Descending.
Not only will this column now be sorted, but the other columns in the table will also reorder so that original rows stay intact.
Filtering Your Customer List
Filtering becomes very simple when your data is formatted as a table.
To filter your data:
- Click on the dropdown next to column you would like to filter.
- In the list at the bottom of the popup box, untick the box that reads “Select all” to deselect all items.
- Now, select the values of the entries you would like to appear in the filtered data.
- The table will update as you tick/untick boxes.
Removing a Filter
To remove a filter, click on the dropdown next to the filtered column and select Clear Filter from [Column Name].
The table will return to its previous state.
More Complex Filtering
For more complex filtering, you can create filtering rules in the same popup box. To do this:
- Click on Text Filters and select Custom Filter.
- In the first row, select a filter type in the first dropdown box and enter/choose a value in the next box.
- If needed, complete the second filter as well.
- Click on OK
Select And between the two filters to show result that meets both conditions, or select Or to show results either one or the other condition.
At times one might want to combine two columns into one, such a combining first name and last name into a full name.
The easiest way to combine text columns is with the ampersand (&) symbol in a formula. Follow these steps:
- Create a column for the new value (do this by right-clicking on any column header and choose Insert Column).
- Now create a formula and add an ampersand (&) between all values to be joined.
For example, if the first name is in cell A2 and the last name in cell B2, the full name (cell C2) will contain the formula:
=A2 & ” ” & B2
Note the ” ” in between A1 and B2, which inserts a space between the name and surname, preventing them from being displayed as one word.
As you complete the formula, Excel will automatically copy the formula down to all rows in the table. This is standard procedure for all columns in a table.
It is often useful to split a column into two or more separate columns based on some criteria. For instance, you might want to split a full name into a first name and last name.
There are two ways to split a column:
- With the Text to Columns feature
Copy an existing column and have Excel split it based on certain delimiters or according to a specified column width. Useful when the values in your column have a similar format, such as account numbers or product numbers.
- With a formula
Use a formula to find the space in between words, and then extract the part before and after the space. This would work better if the values in your column are not similar, such as customers with a first, middle, and last name.
See this article for a complete tutorial on both these methods.
Creating a Total Row to Sum, Count, or Find Averages
Another amazing feature of Excel tables is the Total Row, a row added to the bottom of the table and used to provide a summary of your data.
The total row can perform the following summary calculation on each column:
- Average – find the average of all cells
- Count – Count the cells that contain a value
- Count numbers – count the cells that contain numbers
- Max – Find the maximum value in the column
- Min – Fin the minimum value in the column
- Sum – add up the values in all cells
- StdDev – calculate the standard deviation of all values in the column
- Var – calculate the variance of all values in the column
To insert a total row, do the following:
- Click inside the table to display the contextual Table tab.
- In the Table tab, tick the “Total Row” box. A total row is inserted at the bottom of the table.
- Click inside each cell of the total row, then click on the dropdown next to the cell and select the summary you would like to see.
- Repeat step 3 for each column in the table.
As you can see, managing a customer list becomes a breeze with all the available tools in Excel. Tables are a great way managing any data sheet, not just customer lists.
Have any specific questions around managing your customer list? Please post it in the comments. I’ll do my best to answer it.