Cards and UPI

Credit utilisation tracker template, monthly and per card

Build a credit utilisation tracker in Excel or Google Sheets: columns, formulas for each card and combined, and a twelve-month trend line.

Editorial illustration for Credit utilisation tracker template, monthly and per card

A credit utilisation tracker needs only a few columns: month, card, limit and statement balance, with the ratio calculated for you. This template gives you per-card ratios, a combined figure for each month and a twelve-month trend line. It works in Excel and Google Sheets, apart from one function noted below.

What columns do I need?

ColumnHeadingWhat goes in it
AMonthFirst day of the statement month, for example 1-Apr-2026, formatted as MMM-YY
BCardA short name such as Card A
CLimitTotal credit limit in rupees
DStatement balanceTotal on the statement, in rupees
EUtilisationFormula, shown as a percentage

Put the headings in row 1 and your data from row 2 down. One row per card per month. A financial year of three cards is 36 rows.

Which formulas do I use?

  • Per-card ratio in E2: =IF(C2=0,"",D2/C2), then format the column as a percentage. The IF avoids a divide-by-zero error if a limit is blank.
  • Combined ratio for a month: put the month in H2, then in I2 use =SUMIFS($D$2:$D$500,$A$2:$A$500,H2)/SUMIFS($C$2:$C$500,$A$2:$A$500,H2).
  • Highest ratio in a month: =MAXIFS($E$2:$E$500,$A$2:$A$500,H2). MAXIFS needs Excel 2019 or later, or Google Sheets.
  • Leave the ranges long enough to cover future rows. Ranges down to row 500 leave room for years of three-card data.

Check the first combined ratio by hand. If Card A has ₹30,000 on ₹2,00,000 and Card B has ₹45,000 on ₹1,00,000, the combined figure is ₹75,000 ÷ ₹3,00,000 = 25 percent. If the sheet shows something else, check the month cells match exactly.

How do I add a twelve-month trend line?

Make a second block with one row per card and one column per month from April to March, so you can lay out a financial year. Fill each cell with the ratio, for example =IFERROR(SUMIFS($D$2:$D$500,$B$2:$B$500,$K2,$A$2:$A$500,L$1)/SUMIFS($C$2:$C$500,$B$2:$B$500,$K2,$A$2:$A$500,L$1),"") where K holds the card names and row 1 holds the month dates.

  • Google Sheets: in a cell beside the row, type =SPARKLINE(L2:W2). This function exists in Google Sheets only.
  • Excel: select the row of ratios, then use the Insert tab and choose Sparklines, then Line, and pick the cell for it.

Can I flag a high month automatically?

Use conditional formatting on the ratio cells. Choose a rule such as cell value greater than a number you set in a separate cell, say $M$1, so that you can change it without editing the rule. Pick your own number, and do not assume any particular level is right for everyone. The point is to catch months that jump.

When does a sheet stop being enough?

A sheet is fine for the ratio. What it does not do is explain the balance. SpendixAI can import each card's statement into its own account so you can see which purchases made up a month. The limit and the ratio still belong in your sheet, since it does not track limits.

How do I keep the sheet tidy over a year?

  • Use a new row for every card every month, even if the balance is zero, so the trend line has no gaps.
  • Freeze the top row and format the first column as a date so sorting stays correct.
  • Add data validation to the Card column with a dropdown of card names, so spelling cannot split a card into two.
  • Keep a Notes column for limit changes, new cards and closed cards.

At the start of each April, copy the sheet to a new tab for the new financial year, keeping the old one untouched, so the past stays as it was recorded.

Before you start

  1. Copy the limit from each card's latest statement.
  2. Fill in the last three statements to get a short history.
  3. Check each ratio against a calculator once.
  4. Set a reminder on each statement date to add the new row.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports