Credit Card Tracker Help Page
Current Version: 1.0
Compatibility: PC and Mac | Excel 365 | Works on 32-bit or 64-bit | No VBA code utilized
This webpage will serve as an instructional resource for operating the Credit Card Tracker Excel Template. Please use the below table of contents to navigate through the help page or simply scroll through the page if you’d like to read the information in order.
This template utilizes the FILTER() function to dynamically populate the dashboard. Please ensure you have access to this function inside your version of Excel. If you are utilizing a Microsoft 365 subscription, you should have access to this function.
Configuring The Settings Tab
Account Type Table
The Account Type Excel table allows you to group or categorize your cards for different purposes. Typically, this is used to separate Business vs Personal credit cards. If you run multiple businesses, you may want to create an account type for each business to group your credit cards in the tracker (see Credit Impact section).
Card Type Table
The Card Type Excel table allows you to categorize your credit cards by their payment network. The template is pre-populated with the larger US-based networks, but you may need to add more depending on the cards you have.
The Cardholder Excel table gives you the opportunity to classify your credit cards by who opened the card. The values in this table will likely be your and your spouse’s names.
The first entry (row value) in this table serves as the label for summarizing all cardholders in the dashboard calculations. You can keep the value as “Family” or change it to something else such as “All” or “Total”.
These values will be available in the drop-down menu on the Dashboard tab.
The Tracker Tab Introduction
The Tracker Tab will serve as your source tab to help you stay on top of all your credit cards, including progress on enrollment bonuses and your card utilization strategy.
You will be in charge of filling out columns with black font, while columns with blue font and a purple cell fill color indicate the cell values are driven by formulas.
There are 5 sections to the data table:
- Card Overview
- Credit Impact
- Annual Fee Details
- Card Bonus Tracking
- Card Action Plan
Removing/Adding Tracker Table Rows
Removing Table Rows
When you first receive the Credit Card Tracker template, there will be some example data in the tracker table. You can either choose to override the data or delete the table rows (start with row 2, DO NOT delete row 1).
To delete rows in the Excel table, simply:
- Select one or more cells that reside in the rows you wish to reside
- Right-click on one of the cells
- Navigate to the Delete menu and select Table Rows
After you have clicked on Table Rows, the rows within your cell selection should instantly be deleted.
Inserting New Table Rows
You have two options to add additional rows to your Tracker table.
Option 1: Start typing in the cell directly below the last row in the table. After you finish typing your cell value and click off the cell, the table should visually auto-expand down to include an additional row.
Option 2: You can select a cell where you would like to insert a row above. Right-click on that cell and select Insert > Table Rows Above.
When you add additional rows, all table formulas should automatically populate into the new row.
Card Overview Section
Status – This is a formula-base column that flags whether a card is active (green) or not (gray). The formula looks at the Actual Cancel Date Column to determine when a specific card is currently active or not.
Who Opened? – This column indicates the name of the person who opened the card account. The values are restricted by an in-cell drop-down menu that is fed from the Cardholder table in the Settings tab.
Card Name – A free-text field that allows you to input the name of the credit card.
Opened Date – A date field allowing you to record the date the credit card was opened.
Issuing Bank – A free-text field allow you to indicate the bank backing the card. This information can typically be found in fine print on the back of your credit card.
Ending 4 Digits – A numerical field allowing you to input the Last four digits of your credit card number
Credit Impact Section
Type – This column indicates a name for the type or group of cards (typically Personal or Business). The values are restricted by an in-cell drop-down menu that is fed from the Account Type table in the Settings tab.
Card Type – This column indicates the name of the processing company for your credit card. The values are restricted by an in-cell drop-down menu that is fed from the CardType table in the Settings tab.
Credit Limit – Max amount you can charge to the credit card
Credit Checks (formula) – The formula in this column provides an estimate for how many credit checks you’ve had recently from opening credit cards
Years Active (formula) – A formula to calculate how long has the card been active.
Annual Fee Details Section
Annual Fee – Amount of annual fee for card
First Year Waived – Do you pay an annual fee in year 1? Enter “N/A” in the cell if this is not applicable.
Next Fee Date (formula) – A formula that provides a due date for the next annual fee (if applicable)
Days Until Next Fee (formula) – Days until the next annual fee is due (if applicable
Card Bonus Tracking Section
Rewards Type – This field allows you to indicate what kind of awards you receive when you spend money on this credit card. Typical reward types include: cash back, mileage, store credit, etc…
Enrollment Bonus – Potential bonus you can earn for opening
Minimum Spend – Minimum spend required to earn your enrollment bonus
Deadline – The time limit to earn an enrollment bonus
Bonus Value – What is the dollar value of the enrollment bonus
Days Until Bonus Expires (formula) – How many days left to achieve the bonus
Date Bonus Received – A date field to allow you to record the date you actually achieved the enrollment bonus
Card Action Plan Section
Rewards Details – Summarize Reward Benefits for the credit card (see next section for more details)
Use Card After Bonus Achieved? – Should you use this card regularly?
Current Purchase Plan – What types of purchases to utilize the card for
Keep Card Past 1 Year? – Should you cancel this card immediately?
Days Until Cancel (formula) – Let’s you know when you need to cancel
Actual Cancel Date – Date you canceled card
Last Statment Date – Last time you used a card
Yearly Spend Alert – Do you need to utilize a card (try to use it once annually to prevent auto-cancellation)
Adding Reward Summary Cell Note
I recommend summarizing the reward your card provides in a cell note in the Ongoing Rewards Details column. You can browse through examples I’ve created when you first open the template. Essentially I bullet-point out the various % or points bonuses from the credit card’s marketing materials.
If you like the format I’ve used in the cell note, you can copy it by navigating to Cell AH4 and copy/paste it into the cell you are wanting to fill out in the Ongoing Rewards Details column (when you insert new rows, the note will not be there).
After you have added the cell note to the cell (indicated by a visible red triangle in the upper right-hand corner of the cell), you can proceed to edit the text by right-clicking on the cell and selecting Edit Note.
Understanding the Dashboard
The Dashboard is where you see the overall status of your data. You can filter the data on a particular person or view all cardholders together. There are dynamic summaries that display:
- The current sign-up bonuses the cardholder is working on
- Cards that need to be canceled (don’t want to pay those annual fees!)
- The current cards that need to be utilized and what types of purchases should be made with them
Adding More Dashboard Rows
If you are bringing a bunch of data into this dashboard, you may want to expand the amount of rows within the 3 purple tables.
To do this, you will first need to make the row/column heading visible. You can do this by navigating to the View tab in Excel Ribbon and checking the Headings checkbox.
Next, select an entire row within the middle of the purple tables. Right-click on a cell within the selection and select the Insert button from the menu. This will add an additional row with all the appropriate formatting within the dashboard. You can repeat inserting rows until you have enough to suite your data size.
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.