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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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.

Keep Track of Clients in Excel

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

Read More: How to Keep Track of Customer Orders in Excel


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.

Keep Track of Clients in Excel

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

Read More: How to Keep Track of Customer Payments in Excel


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.

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

  • Now, write down the following formula in the formula box.
=IFERROR(VLOOKUP(B5,'Contact Details'!$B$5:$G$11,2,FALSE),0)

 

Breakdown of the Formula

  • VLOOKUP(B5,’Contact Details’!$B$5:$G$11,2,FALSE): Here, the VLOOKUP function searches the value in cell B5 in the range of B5 to G11 from the worksheet called Contact Details. It will return the second column of that range where B5 is matched. Here, false means you have to have an exact match otherwise it won’t give any result.
  • IFERROR(VLOOKUP(B5,’Contact Details’!$B$5:$G$11,2,FALSE),0): The IFERROR function will return zero if the previous function has some error.
  • Press Enter to apply the formula.

Keep Track of Clients in Excel

  • 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.
=IFERROR(VLOOKUP(B5,'Contact Details'!$B$5:$G$11,3,FALSE),0)

  • Press Enter to apply the formula.

  • Now, click on cell E5.

  • Now, write down the following formula in the formula box.
=IFERROR(VLOOKUP(B5,'Contact Details'!$B$5:$G$11,5,FALSE),0)

  • Press Enter to apply the formula.

  • Now, click on cell F5.

  • Then, write down the following formula in the formula box.
=IFERROR(VLOOKUP(B5,'Contact Details'!$B$5:$G$11,6,FALSE),0)

Keep Track of Clients in Excel

  • Press Enter to apply the formula.

Keep Track of Clients in Excel

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

  • Write down the following formula in the formula box.
=IFERROR(VLOOKUP(B5,'Service Details'!B5:E11,2,FALSE),0)

Keep Track of Client in Excel

Breakdown of the Formula

  • VLOOKUP(B5,’Service Details’!$B$5:$G$11,2,FALSE): Here, the VLOOKUP function searches the value in cell B5 in the range of B5 to G11 from the worksheet called Service Details. It will return the second column of that range where B5 is matched. Here, false means you have to have an exact match otherwise it won’t give any result.
  • IFERROR(VLOOKUP(B5,’Service Details’!$B$5:$G$11,2,FALSE),0): The IFERROR function will return zero if the previous function has some error.
  • Press Enter to apply the formula.

  • Now, click on cell H5.

  • Write down the following formula in the formula box.
=IFERROR(VLOOKUP(B5,'Service Details'!B5:E11,3,FALSE),0)

Keep Track of Clients in Excel

  • 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.
=IFERROR(VLOOKUP(B5,'Service Details'!$B$5:$F$11,5,FALSE),0)

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

  • Write down the following formula.
=IF(J5>K5,"Outstanding",IF(J5=K5,"Good",IF(J5<K5,"Bad")))

Keep Track of Clients in Excel 

Breakdown of the Formula

  • IF(J5>K5,”Outstanding”,IF(J5=K5,”Good”,IF(J5<K5,”Bad”))): This denotes if your client gives the service prior to the scheduled date, he/she will get Outstanding remarks. Then If the client gives the service on time then she/he will get Good remarks. Finally, if the client gives the service after passing the scheduled date, he/she will get Bad remarks.
  • 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.

Keep Track of Clients in Excel


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.

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

  • 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.
=$J5:$J8>$K5:$K8

Keep Track of Clients in Excel

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

Breakdown of the Formula

  • $J5:$J8>$K5:$K8: Here, column J denotes the scheduled time, and column K denotes the service received. This condition demonstrates the situation when a client gives their service before the deadline. Another important to remember, if you use the formula in the Conditional Formatting, you don’t need to use the IF function.
  • 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.
=$J5:$J8=$K5:$K8

  • 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.
=$J5:$J8<$K5:$K8
  • Then, click on Format to set the preferred color.

Keep Track of Clients in Excel

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

Keep Track of Clients in Excel

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

Read More: How to Make a Sales Tracker in Excel


Download Practice Workbook

Download this practice workbook.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Durjoy Paul
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo