Hosne Ara
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.
- 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.
Similar Readings
- How to Create an Employee Database in Excel (with Easy Steps)
- How to Create a Database with Form in Excel
- How to Create Student Database in Excel (With Easy Steps)
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 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.
