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


Step 1: Headline Entry for Customer Payments in Excel

  • Open an Excel spreadsheet.
  • Type all the necessary Headline info for the payment data. Look at the picture below for a better understanding.

keep track of customer payments in excel

STEP 2: Input Customer Payments and Apply Data Validation

  • Input the details carefully, one by one. The image below demonstrates how to do so for Invoice Numbers, Payment Dates, and Customer Names.
  • Select the D2:D6 range.

  • Go to the Data tab and select Data Tools.
  • Click on the Data Validation button two times, as pictured below.

keep track of customer payments in excel

  • Choose List from the Allow dropdown menu in the Data Validation dialog box.
  • Enter Pen Drive,Hard Disk,SD Card,SDHC Card,SDXC Card in the Source box.
  • Press OK.

  • Select any cell from the D2:D6 range.
  • Click on the downward arrow in the right corner to select the option for the Product entry instead of typing over and again.

Step 3: Create Dynamic Payment Details

  • Input the Quantity, Unit Price, and Discount data.
  • Copy the following formula for the Bill calculation to cell H2:

Create Dynamic Payment Details

  • Press Enter and use AutoFill to find out the other Bill values.

NOTE: The Bill becomes dynamic after using the formula. You can update the Unit Price and Discounts at any time, but there’s no need to manually calculate the Bills anymore.

  • Apply the Data Validation for Payment Methods. See the image below .

Step 4: Compute Total Bill

  • Select cell H7.
  • Type the following formula:
  • 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

You can also make a summary based on a specific category apart from Keeping Track of Customer Payments. In this example, we’ll form a Dynamic Summary for the list of Discounted Items and the total count for each Payment Method.

  • Select cell C10 and enter the following formula:
  • 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.

  • Choose cell F10 to find the total count for each Payment Method.
  • Enter the following 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

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

You can also 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.


  • Select any header.
  • Go to the Home tab and click on the Editing button.
  • From the dropdown menu, choose the Sort & Filter option followed by Filter.

Sort and Filter Customer Payments Tracker in Excel

  • Select the dropdown icon in the bottom-right corner of the Payment Method header and check Credit Card.
  • The returned list will show 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.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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