Aung Shine
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.
Download Template
Download the following template to practice by yourself.
Step by Step Procedures to Keep Track of Customer Payments in Excel
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.
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.
- 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.
- 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.
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.
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.
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.
Read More: How to Keep Track of Customer Orders in Excel (With Easy Steps)
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.
- 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 (3 Ideal Examples)
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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Create Leave Tracker in Excel (Download Free Template)
- Maintain Store Inventory in Excel (Step by Step Guide)
- How to Track Stocks in Excel (Download Free Template)
- Make To Do List in Excel with Checkbox (With Quick Steps)
- How to Create a Task Tracker in Excel (Download Free Template)
- How to Track Project Progress in Excel (Download Free Template)
