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

Step 1 – Make Dataset for Contact Details

The contact details include specific information about that client, such as their contact number, email address, company name, related field, and position.

Make a new sheet.
Put some client details in your worksheet.

To change the sheet name, right-click on the sheet name. A Context Menu will pop up.
Click on Rename.

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

Step 2 – Create Client Service Details

Press + in the toolbar with the sheet names to create a new sheet.
Put information that includes the service name, the amount of money that cost on the particular service, and the scheduled date to give the service.

Right-click on the sheet name.
Click on Rename.

Change the worksheet to ‘Service Details’.

Step 3 – Generate Client Tracker

Make a new sheet for the tracker.
Create the column headers in the client tracker worksheet.

We can create a Data validation through which we can click on our required client name and their activities.
Choose cell B5 to cell B11.

Click on the Data tab in the ribbon.
From the Data Tools group, click on Data validation.

A Data validation dialog box will appear. From there, click on Settings.
In the Allow section, click on List.
In the Source section, click and select the array that contains the information. We take the source from the Contact Details sheet.
Click on OK.

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

Click on the drop-down list and choose any of the clients from there.

Click on cell C5.

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.

Changing the client name automatically updates the cell.
Click on cell D5.

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.

Click on cell E5.

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

Click on cell H5.

Write down the following formula:

=IFERROR(VLOOKUP(B5,'Service Details'!B5:E11,3,FALSE),0)

Press Enter to apply the formula.

We need to get the Scheduled Data. So, 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.

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.
Click on the Number command at the top.
From the Category section, click on Date.
In the Type section, click on the following pattern: two-digit number and three-letter month.
Click on OK.

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

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.

Press Enter to apply the formula.

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

Step 4 – Make Client Tracker Dynamic

Choose cells H5:H8.

Click on Conditional Formatting from the Style group.

Click on New Rule.

A New Formatting Rule dialog box will appear.
Click on Format only cells that contain.
Set Greater than 5000.
Click on Format to change the format color.

We put a green color for greater than $5000.
Click on the Fill command.
Set Green as your preferred color.
Click on OK.

That will set the amount greater than $5,000 as green.

Go to Conditional Formatting again.

In Conditional Formatting, click on New Rule.

Click on Format only cells that contain.
Set it to less than or equal to 5000.
Click on Format to change the format color.

We put Yellow for less than or equal to 5000.
Click on the Fill tab.
Set Yellow as your preferred color.
Click on OK.

That will set all the values less than or equal to $5,000 as yellow.

In terms of remarks, we want to set outstanding remarks as green, good as yellow, and bad as red.
Select cell range L5:L8.

Go to Conditional Formatting.

Choose New Rule.

Select Use a formula to determine which cells to format.

Write down the following formula in the box:

=$J5:$J8>$K5:$K8

Click on Format to set the preferred color. Click on the Fill tab.
Set Green as your preferred color for this condition.
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.
Make a new Conditional Formatting rule.
Click on Use a formula to determine which cells to format.

Write down the following formula in the box:

=$J5:$J8=$K5:$K8

Set the Format color to Yellow.

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.
Make a new Conditional Formatting rule as shown above.
Click on Use a formula to determine which cells to format.