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 them 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 creating client databases in excel.


Download Practice Workbook

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


What Is a Client Database?

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

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

Step by Step Guideline to Create a Client Database in Excel

Now I will explain the process of creating a client database step by step. 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.

Create Column or Field Headers

  • 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.

Enter Data into Rows/Records

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.

Enter Data into Rows/Records

  • 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.

Format the Data in Records

  • 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.

Format the Data in Records

  • 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.

Create a Table from the Entered Client Data

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

Create a Table from the Entered Client Data

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.

Naming the Table/Database


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.

Add More Records to the Client Database

  • 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.

Appy Excel ‘Sort & Filter’ to Client Database

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

Appy Excel ‘Sort & Filter’ to Client Database

  • 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.

Appy Excel ‘Sort & Filter’ to Client Database

  • 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 requirement.


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo