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.
- 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.
- 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.
- 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.
=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.
- 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)
- 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.
=IFERROR(VLOOKUP(B5,'Service Details'!B5:E11,2,FALSE),0)
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)
- 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)
- 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.
=IF(J5>K5,"Outstanding",IF(J5=K5,"Good",IF(J5<K5,"Bad")))
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.
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.
=$J5:$J8>$K5:$K8
- 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.
- 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.
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.