How to Keep Track of Clients in Excel (Download Free Template)

When you deal with different clients, it is a must to keep track of clients. In Microsoft Excel, you can easily track clients. This will give you details about several clients and their service performance. This article will provide you with a template through which you can keep track of clients in Excel in a step-by-step procedure.

Download Practice Workbook

Download this practice workbook.

Step-by-Step Procedure to Keep Track of Clients in Excel

Here, we want to create a client tracker in Excel in a Step-by-step procedure. We want to create a worksheet including client contact details and also their service details. Using these two things, we want to keep track of clients in Excel which gives remarks about that particular client.

Step 1: Make Dataset for Contact Details

When you want to create a client tracker, it is a must to have a client details worksheet. The contact details worksheet includes specific information about that client, such as their contact number, email address, company name, related field, and position.

• First, we need to take a blank worksheet.
• Next, put some client details in your worksheet.

• Then, to change the sheet name, right-click on the sheet name.
• A Context Menu will pop up.
• From there, click on Rename.

• We set our worksheet name as ‘Contact Details’.
• Then, Press Enter.

Step 2: Create Client Service Details

As we want to create a client tracker, it is necessary to create client service details. Without any service details, we don’t have anything to track.

• First of all, we need to take a blank sheet.
• As it is mainly focusing on client service, that’s why we need to include the service name, the amount of money that cost on the particular service, and the scheduled date to give the service.

• Then, to change the sheet name, right-click on the sheet name.
• A Context Menu will pop up.
• Next, From there, click on Rename.

• Now, change the worksheet to ‘Service Details’.

Step 3: Generate Client Tracker

Now, we are going to make a dynamic client tracker. For doing this, we use both VLOOKUP and IFERROR functions which take data from the previous dataset. Most importantly it will reduce the repetitive action to write down previous data.

• First, create the column headers in the client tracker worksheet.

• To choose the client name, we can create Data validation through which we can click on our required client name and their activities.
• To do this first, choose cell B5 to cell B11.

• Next, Click on the Data tab in the ribbon.
• Finally, from the Data Tools group, click on the Data validation command.

• A Data validation dialog box will appear.
• From there, click on the Settings command
• In the Allow section, click on List from the drop-down command.
• Next in the Source section, click on the required source. We take the source from the Contact Details
• Finally, click on OK.

• It will create a drop-down list from there you can choose the client name.

• Click on the drop-down list, all the client names will appear. You can choose any of the clients from there.

• Next, to get the position of that client, click on cell C5.

• Now, write down the following formula in the formula box.

• Press Enter to apply the formula.

• Next, you can choose any client name below then drag the position column fill handle icon, it will provide the position for that client.
• Now, click on cell D5.

• Next, write down the following formula in the formula box.

• Press Enter to apply the formula.

• Now, click on cell E5.

• Now, write down the following formula in the formula box.

• Press Enter to apply the formula.

• Now, click on cell F5.

• Then, write down the following formula in the formula box.

• Press Enter to apply the formula.

• To get the service of the specific client, first, click on cell G5.

• Write down the following formula in the formula box.

• Press Enter to apply the formula.

• Now, click on cell H5.

• Write down the following formula in the formula box.

• Press Enter to apply the formula.

• Here, we need to get the Scheduled Data. To get this, we need to click on cell J5.

• Write down the following formula.

• Press Enter to apply the formula.

• Here, the scheduled date has appeared in the General format.
• To change it, go to the Home tab in the ribbon.
• From the Number group, select the little arrow in the bottom right corner. See the screenshot.

• Format Cells dialog box will pop up.
• Next, click on the Number command at the top.
• From the Category section, click on Date.
• Then, in the Type section click on the following pattern.
• Finally, click on OK.

• It will give us the Scheduled Date as the Date format.

• Next, we have a portion called Service Received. It means the time when you receive the service.
• The Remarks section denotes the final outcome of the client whether he/she provides the service in time or not.
• To make Remarks for a specific client, click on cell L5.

• Write down the following formula.

• Then press Enter to apply the formula.

• Now, if we take some other client’s details, we will get the following outcome. See the screenshot.

Step 4: Make Client Tracker Dynamic

We can use Conditional Formatting through which you can set different conditions and express them in a different color. To make the clients tracker dynamic, follow the following steps carefully.

• Here, we use conditional formatting for the amount and remarks section.
• First, we choose the amount from cell H5 to cell H8.

• Now, Click on the Conditional Formatting from the Style group.

• Then, click on the New Rule.

• A New Formatting Rule dialog box will appear.
• Click on Format only cells that contain.
• Now, set Greater than 5000.
• Then, click on Format to change the format color.

• Here, we take a green color for greater than $5000.
• Click on the Fill command.
• Then, set Green as your preferred color.
• Finally, click on OK

• That will set the amount greater than $5000 as green.

• Now, for less than and equal to $5000, we need to set another conditional formatting.
• Again, click on the Home tab.
• Next, click on the Conditional Formatting from Styles group.

• Then, in the Conditional Formatting, click on New Rule.

• A New Formatting Rule dialog box will appear.
• Click on Format only cells that contain.
• Now, set less than or equal to 5000.
• Then, click on Format to change the format color.

• Here, we take Yellow for less than or equal to 5000.
• Click on the Fill tab.
• Then, set Yellow as your preferred color.
• Finally, click on OK.

• That will set all the values less than or equal to $5000 as yellow.

• In terms of remarks, we want to set outstanding remarks as green, good as yellow, and bad as red.
• To do this, we have to set Conditional Formatting with the formula for each case.
• First, Click on cell range L5 To L8.

• Next, click on the Home tab in the ribbon.
• In the Styles group, click on Conditional Formatting.

• Then, in the Conditional Formatting, click on New Rule.

• Next, a New Formatting Rule dialog box will appear.
• Click on Use a formula to determine which cells to format.
• It will open up a box where you can write down the formula.

• Write down the following formula in the box.

• Then, click on Format to set the preferred color.
• Click on the Fill tab.
• Then, set Green as your preferred color for this condition.
• Finally, click on OK.

• Finally, It will make Remarks green when some clients give their service before the deadline.

• Next, when clients give their service on time, we want to express their remarks as yellow.
• To do this again click on the Conditional Formatting command and select New Rule.
• Then, A Conditional Formatting dialog box will appear.
• Click on Use a formula to determine which cells to format.
• It will open up a box where you can write down the formula.

• Write down the following formula in the box.

• Then, click on Format to set the preferred color.
• Click on the Fill tab.
• Then, set Yellow as your preferred color.
• Finally, click on OK

• That will set the Remarks which are valid for the formula as yellow.

• Lastly, for those clients who don’t meet the deadline, we want to express their Remarks as Red.
• To do this again click on the Conditional Formatting command and select New Rule.
• Then, A Conditional Formatting dialog box will appear.
• Click on Use a formula to determine which cells to format.
• It will open up a box where you can write down the formula.

• Write down the following formula in the box.

• Then, click on Format to set the preferred color.

• Click on the Fill command.
• Then, set Red as your preferred color.
• Finally, click on OK.

• That will set the Remarks which are valid for the formula as Red.

That is our client tracker in Excel where you can easily keep track of your clients better for future deals.

Conclusion

We have shown how to keep track of clients in Excel with a template. Here, we focus on how to do with excel function and provide an effective solution. If you have further questions, feel free to ask in the comment box. Finally,  Don’t forget to visit our Exceldemy page.

Related Articles

• How to Create Leave Tracker in Excel (Download Free Template)
• Maintain Store Inventory in Excel (Step by Step Guide)
• How to Track Stocks in Excel (Download Free Template)
• Make To Do List in Excel with Checkbox (With Quick Steps)
• How to Create a Task Tracker in Excel (Download Free Template)
• How to Track Project Progress in Excel (Download Free Template)