Need Help in Optimizing Payment Tracker with Trend Visualization

[vk7]

I've attached an Excel sheet titled 'PMT.xlsx,' which tracks my credit cards and their outstanding balances for each month. The issue I'm facing is that I update the sheet manually after each payment I do monthly, but in doing so, I overwrite the previous month's data. As a result, I lose the history of past payments and can't easily track whether my outstanding balance is increasing or decreasing over time. Any suggestions on how I can improve the structure to better track my payment progress? I'd also appreciate tips on incorporating a simple graph to visualize the upward or downward trend of my outstanding balance over time. Thanks in advance for the help.

Note: I mostly update the columns C3:M7 every month manually.

 

[vletm]

vk7​

Why do You want to do something ... monthly?
... You could add there more and more months.
... Could You use a table?
If You need to see something monthly, then eg filter it ... have a pivot table.

 

[vk7]

vk7​

Why do You want to do something ... monthly?
... You could add there more and more months.
... Could You use a table?
If You need to see something monthly, then eg filter it ... have a pivot table.

Yeah I never thought this way, will try this out and see how it works.

 

[vk7]

Hello @vletm,

I've implemented the recommendations and created a pivot chart, which I believe is correct. However, I’ve encountered a few challenges and would appreciate some help or suggestions in addressing them.

- First, the filter for selecting the month appears in a random cell, how can I ensure it always stays in a fixed cell? (i.e Currently it is Row 13)
- Second, with this current setup, it seems like I might have to manually update the data range whenever new data is added. Is there a way to automatically adjust the data range as new data comes in?
- Lastly, could you assist me in creating a basic trend chart for utilization based on the data for September and October 2024?

 

[vletm]

vk7​

Few more questions ... with some samples

 

[vk7]

Thank you so much, @vletm for your assistance. Your help gave me some useful insights. To answer your question:

#2 What is connection between Year | Month and Due Date?

It appears there was a typo in the Excel sheet I attached, and I apologize for that the correct value is 2024 as all the data in the excel sheet belongs to the year 2024. The "Year" column represents the year in which the statement was generated, the "Month" column indicates the month of the statement, and the "Due Date" reflects the grace period by which the payment must be made. Therefore, the "Months" column should refer to the statement generation month, not the due date.


Currently, the Grand Total reflects the combined total for both September and October. However, is it possible to see the Grand Total for each month separately. The reason is that the credit limit for a given card typically remains fixed and doesn’t change monthly. Summing the credit limit for both months together doesn't provide any useful insights.

 

[vletm]

vk7​

I would recommend to use dates instead of Year and TEXT-Months.
If Your sample data has that kind of ... then those will always makes more wondering.
>> You could modify Your data and after that modify my made pivot-table and timeline.
is it possible to see the Grand Total for each month separately.
Yes ... have You used Pivot-tables before?
... select Pivot-table > From Excel-menu > Design > Subtotals

 

[vk7]

Hello @vletm,

I worked on the pivot and grouped it by both month and year to calculate the totals for each month, and it’s functioning smoothly. I also replaced the manually added YEAR and MONTH columns by using the YEAR and MONTH from the Statement Generation Date, based on your suggestion. Thank you for that!

I would now like to highlight the total for each month. Currently, I’m manually highlighting the specific row, but as new data is added each month, I anticipate having to do this manually again. While I’m able to handle that, I wanted to check if there’s an automated way to highlight the totals for each month, avoiding the need for manual updates every time.

Additionally, I’m noticing some unusual behavior with the slicer. When I select only November 2024, data is still being displayed, even though there is no statements for the Period November. I only have the data for September and October. Could you help me understand what might be causing this issue?

 

[vk7]

Please ignore the above post, I have fixed the Timeline by recreating it again. One last thing, I would like to check here is,

The below 2 cards mentioned in the Excel Sheet, belongs to two different banks earlier and recently has got merged as 1. Therefore the Credit Limit of both the cards are now combined into 1 with the Cumulative Credit Limit of Rs. 70000

Citi Bank / Now - Axis Bank - Cashback Credit Card (Earlier 10000)
Axis Bank - Neo Credit Card (Earlier 60000)

How can I showcase this information clearly in Excel so that my Pivot has the correct Credit Limit Displayed. As of now it is showing 70000 for Each (summing up to 140000) but it will be the cumulative credit limit of both the cards.

 

[vletm]

vk7​

Is this possible way for You?

 

[vk7]

Thanks a lot @vletm for all the help and guidance. This helps!