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.

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

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

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

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:

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

• Press Enter to apply the formula.

• Click on cell E5.

• 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, click on cell G5.

• Write down the following formula in the formula box:

• Press Enter to apply the formula.

• Click on cell H5.

• Write down the following formula:

• Press Enter to apply the formula.

• We need to get the Scheduled Data. So, click on cell J5.

• Write down the following formula:

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

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

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

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

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

• Write down the following formula in the box:

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

• Choose Red and select OK.

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

Read More: How to Make a Sales Tracker in Excel

Download Practice Workbook

Download this practice workbook that can also be used as a template.

Related Articles

• How to Create a Task Tracker in Excel
• How to Create a Daily Task Sheet in Excel
• How to Create a Progress Tracker in Excel
• How to Keep Track of Invoices and Payments in Excel
• How to Create Real Time Tracker in Excel

<< Go Back to Create a Tracker in Excel | Tracker in Excel | Excel Templates