How to Create a Client Database in Excel (With Easy Steps)

In this article, I will discuss how you can create a client database in Microsoft Excel. Usually, a traditional database has tables, fields, records, primary keys, foreign keys, etc. Luckily in Excel, we can create a database and convert it to Excel tables. Besides, Excel has some very useful tools like Sort & Filter which we can apply to navigate data in databases. So, let’s go through the article to learn how to create client databases in Excel.

Watch Video – Create a Client Database in Excel

What Is a Client Database?

Basically, a client database is a collection of information that might include the client information:

Name
Company Name
Position of an individual client in a company
Address
City
State
Contact No.
Email etc.

How to Create a Client Database in Excel: With Easy Steps

Now, I will explain the step-by-step process of creating a database of clients in Excel. While creating the database, I will call columns as fields and rows as records. This reason is, that in traditional databases we use the terms; fields and records.

Step 1: Create Column or Field Headers

First of all, enter the name of the fields that you want to include in the client database. For example, I have included the Client’s Name, Company, City, State, Contact No., Email, etc. fields in my database.

Next, make sure you have highlighted the field names so that they become easily noticeable.

Step 2: Enter Data into Rows/Records

Now we will enter the client’s data into rows. For instance, I have added 7 records to my dataset.

⏩ Note:

While entering records in the database, you cannot leave any row empty between two rows containing data. To illustrate, you cannot leave row 7 empty where rows 6 and 7 have data in them.

Similarly, you cannot keep an entire column empty in your dataset as shown in the below screenshot.

This is because, when Excel encounters an entirely blank row or column, it is unable to include that row or column in the database. As a result, the database becomes divided into two parts, an entirely new and disconnected set of information.

Step 3: Format the Data in Records

As the records are added to the database now, we can format them as per our requirements. For example, we will format the ‘Contact No.’ field.

To format the above field, select it and press Ctrl + 1 to bring the Format Cells dialog. You can bring the Format Cells dialog by right-clicking the selection too.
When the Format Cells dialog appears, go to the Number tab, and click on the Special category.
Then select Phone Number from the Type section and press OK.

As a result, we will see that data in the Contact No. field is formatted as we wanted. You also can format your data by using the Format Cells option.

Step 4: Create a Table from the Entered Client Data

Now, I will create a table from the above range of data. To do that, select any cell from the data range and press Ctrl + T.
Consequently, Excel will display the Create Table dialog. Check the table range, put a checkmark on the My table has headers option, and press OK.

As a consequence, you will see that the below table is created.

⏪ Note:

You can convert the data range into a table from Excel Ribbon too by following the path: Insert > Table.

Step 5: Naming the Table/Database

If you want you can name the client database. In this case, as the table represents the database, I will name it. To do that, select the table, go to Name Box, and rename the tale as Client_Data. You can name your dataset as you want.

Step 6: Add More Records to the Client Database

As our database is ready now, we can add more records to it. While entering a new record, type data in any cells of the new row, and press Enter/Tab.

As a result, the row will automatically be included in the table/database. Enter the rest of the client data in the new record. You can add more records or fields to the dataset by following the above method.

Appy Excel Sort & Filter Options in Client Database

As I have mentioned earlier, we can Sort & Filter data from databases in Excel. In typical databases, we need to write queries to get the task done. Follow the below steps to sort and filter data from our client database.

Steps:

First, I will filter client data for the state of TX. To do that, click on the filtering drop-down menu of the field: State.
Then put a checkmark only on TX and press OK.

Consequently, all the client data of Texas is filtered as below:

Similarly, you can sort the database too. For instance, I will sort the Client’s Name field alphabetically. To do that, click on the drop-down menu of the field: Client’s Name and click on Sort A to Z icon.

As a result, you will see the below result. The Client’s Name field is sorted alphabetically as we wanted.

You can apply the Sort & Filter options depending on your requirements.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Create a Client Database.xlsx

Conclusion

In the above article, I have tried to discuss the steps to create a client database in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

<< Go Back To Database in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!