How to Keep Track of Customer Payments in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Having a payment tracker template is fundamental in all sorts of business that contains Customer Payments. The invoice number, date of payment, payment methods, etc. are dissimilar for each customer. But still, they are limited to some specific variants. The input of data for every customer can be exhausting and time-consuming. Also, we can take off a huge load using a dynamic tracker. Keeping all of these in mind, this article will show you the step-by-step procedures to Keep Track of Customer Payments in Excel.


How to Keep Track of Customer Payments in Excel: Step-by-Step Procedures

It can get overwhelming to keep the records of all the payment details of customers. But, we can simply create a tracker in Excel with all the necessary information to keep a distinct track of the payments. A company usually has a list of products, their specific price, and/or discounts, and some particular payment system. So, it’ll be efficient if we can build a template where we don’t have to fulfill all the details and can just input them with a few clicks. Therefore, follow the steps for creating a Template to Keep Track of Customer Payments in Excel.


STEP 1: Headline Entry for Customer Payments in Excel

  • Firstly, open an Excel worksheet.
  • Then, type all of your necessary Headline info for the payment data. Look at the following picture for a better understanding.

keep track of customer payments in excel


STEP 2: Input Customer Payments and Apply Data Validation

  • One by one, input the details carefully.
  • In the below image, we place the respective Invoice Numbers, Payment Dates, and Customer Names.
  • After that, under the Product header, select the range D2:D6 for applying Data Validation.

NOTE: Data Validation alleviates the trouble of the data input procedure. We don’t have to type the entries for each payment. We can just click an option with this feature.

  • Now, go to Data ➤ Data Tools ➤ Data Validation.
  • Next, select Data Validation.

keep track of customer payments in excel

  • As a result, the Data Validation dialog box will pop out.
  • Afterward, choose List in the Allow field.
  • Subsequently, type Pen Drive,Hard Disk,SD Card,SDHC Card,SDXC Card in the Source box.

  • Press OK.
  • Lastly, select any cell in the range D2:D6. It’ll return a drop-down icon.
  • Thus, you’ll get to click an option for the Product entry instead of typing over and again.


STEP 3: Create Dynamic Payment Details

A Dynamic Excel Tracker can take off huge loads as we don’t have to do manual updates on every calculation. For instance, we have to calculate the Bill of each customer. But, it can alter at any time when the price gets updated or the discount value changes. So, follow the process below to perform the task.

  • Input the Quantity, Unit Price, and Discount first.
  • After that, we’ll apply a simple formula for the Bill calculation.

Create Dynamic Payment Details

  • For that purpose, in cell H2, type the formula:
=(E2*F2)-G2
  • Then, press Enter and use the AutoFill tool to find out the other Bills.

NOTE: Here, the Bill becomes dynamic by the use of the formula. We can update the Unit Price at any time and also the Discounts. Yet, we don’t have to manually calculate the Bills anymore.

  • At last, apply the Data Validation for Payment Methods. See the below image.


STEP 4: Compute Total Bill

  • Select cell H7 at first.
  • Then, type the formula:
=SUM(H2:H6)
  • Finally, press Enter to return the summation.

Compute Total Bill

NOTE: The SUM function calculates the total of H2:H6.


STEP 5: Generate Dynamic Payments Summary

Moreover, we can also make a summary based on a specific category apart from Keeping Track of Customer Payments in Excel. In our example, we’ll form a Dynamic Summary for the list of Discounted Items, and the total count for each Payment Method. Hence, learn the below process.

  • First of all, select cell C10 and type the formula:
=IF(G2<>0,D2,"")
  • Next, press Enter and use AutoFill to return the list of Discounted Items only.

Generate Dynamic Payments Summary

NOTE: The IF function looks for the values in the Discount column and returns that Product name if found. Otherwise, it returns blank.

  • Again, choose F10 to find the total count for each Payment Method.
  • Type the formula:
=COUNTIF(I2:I6,"Credit Card")
  • Press Enter to return the result.

keep track of customer payments in excel

NOTE: Replace Credit Card with Debit Card and Cash in the COUNTIF function argument to find the count for Debit Card and Cash payments respectively.


Final Output

Lastly, the following dataset demonstrates the final output of the Customer Payments tracker in Excel.

keep track of customer payments in excel

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


Sort and Filter Customer Payments Tracker in Excel

Additionally, you can perform the Sort operation on the payment entries or even Filter them. To illustrate, we’ll apply the Filter to view the information details of Credit Card payments. Therefore, follow the steps below to carry out the operation.

STEPS:

  • First, select any header.
  • Then, select Home ➤ Editing ➤ Sort & Filter ➤ Filter.

Sort and Filter Customer Payments Tracker in Excel

  • After that, select the drop-down icon beside the Payment Method header and check for Credit Card.
  • As a result, it’ll return the list with Credit Card payment details only.

Read More: How to Keep Track of Invoices and Payments in Excel


Download Template

Download the following template to practice by yourself.


Conclusion

Henceforth, you will be able to Keep Track of Customer Payments in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo