- Video Tutorial
- Track, Monitor, and Alert: Set Up the Complete Advanced Account Tracker on Google sheets
Track, Monitor, and Alert: Set Up the Complete Advanced Account Tracker on Google sheets
C
ctech
Updated
Enter the title for the tutorial
Track, Monitor, and Alert: Set Up the Complete Advanced Account Tracker on Google sheets
Enter the detailed description of the tutorial
Watch Video
Purchase Tracker
Enhancing the Account Tracker with Advanced Features on Google Sheets
Introduction
Welcome to the second part of the tutorial series on how to enhance the accounts tracker on Google Sheets. By the end of this tutorial, you will be able to incorporate advanced features to make your tracking process more efficient and effective.
Getting Started
To get started, make a copy of the Accounts Tracker 1 template from here: http://bit.ly/3GnoJZr. To do this, open a browser window and log in to your Google account. Paste the provided link into your browser's address bar and hit "Enter." Then click "Make a Copy" to have your own version of the template.
Customize the Tracker
- Renaming the Template: Open the copied template. Rename the copied tracker to something like "Accounts Tracker - Template 2" or "Advanced Accounts Tracker."
- Adding Columns: Switch to “Accounts List” tab. Add few additional columns to your tracker for a more detailed view of your accounts:
Add the following new columns to the tracker.
- Balance Transfer Expiry Date
- Last Checked
- Group Checked
- Days Passed
- Next Pay Due
- Next Pay Set
- Regular Bill Pay
- Go To
Column Descriptions
- Institute Name: This is the name of the financial institution, like a bank, credit card issuer, or insurance company.
- Account Name: Use a user-friendly name to easily recognize the account, especially if multiple users will be accessing the tracker. To display complete information in a specific column, hover your mouse over the right edge of the column header until the cursor changes into a double arrow. Then drag to adjust the width.
- BT Exp Date: This stands for Balance Transfer Expiry Date of credit cards. Use this column to track the due date for the entire balance transfer payment. This will help you avoid incurring a higher APR.
- Account Type: Use this to categorize similar accounts like bank, investment, and retirement accounts.
- Last Checked: Use this column to track the last checked account within a group. It can be used to bookmark and monitor the most recent account that was checked. By referencing the bookmark, you can proceed to check the next account in the group sequentially if you intend to periodically review all the accounts within the group.
- Group Checked: Use this optionally to bookmark the last checked group when you plan to review the accounts by group and wish to proceed to the next group in sequence periodically. This will help you check the accounts by group and avoid missing any accounts for an extended period.
- Last Check Date: This is the date when you last reviewed the specific account. You can update it with the date of your most recent check of the particular account.
- Days Passed: This automatically calculates the number of days since you last checked the account, based on the "Last Check Date" and today’s date.
- Next Pay Due: This represents the next payment due date for the specific account. You can update it when you check the particular account. It will be highlighted when the due date falls within the next few days, a timeframe that you can set.
- Next Pay Set: This denotes the next payment set date for the specific account. You can update it when you check the particular account and schedule the payment through bill pay or plan to send the payment by mail on a specific date.
- Regular Bill Pay: Use this column to indicate whether the account requires regular payments, such as monthly or weekly. This will assist you in managing periodic payments and in reviewing paid accounts to determine if they need to be continued.
- Account Access: Use this column to record how you typically access the account. Most often, this will be "Online," but in some cases, it might be "Offline" if you refer to a physical document or a soft copy of the document stored on your computer as an account.
- Go To: This column is for adding a direct login link to the specific account for easy access. You can click this link to open the login page and enter your credentials to login to the account.
Customize the Additional Fields
As the next step to elaborate on the purpose of additional fields, a few sample values have been added to the newly added columns with additional formatting and calculations as needed for efficient tracking and visibility. If you want to learn more about the already existing columns in the sheet, please refer to the first tutorial on setting up the basic account tracker.
Balance Transfer Date:
Balance Transfer Date:
The balance transfer expiry date column holds dates. To set the data type as date, select the column. Once selected, go to the menu bar at the top and choose Format > Number > Date. Now, the column's data type should be set as a date.
For example, if you have transferred a balance from a credit card using a promotional APR, enter the expiry date of the promotional offer. This will help you to keep track of the expiry date of the balance transfer offer and prepare to pay the remaining balance before the deadline to avoid incurring a much higher APR. This will enable you to better plan your balance payments.
To enhance tracking and gain additional visibility, you can custom format the column to provide notifications by highlighting the date before a certain number of days prior to the balance transfer expiry date.
To do this, follow these steps:
- Select the first cell in the column "Balance Transfer Expiry Date" for the account type "US Credit Card."
- Click Format > Conditional Formatting on the menu bar. A toolbar will open on the right.
- The first rule will be added to maintain the default formatting for empty cells.
- In the toolbar, stay on the single-color tab and leave the default "Apply to Range."
- Under "Format cells if," choose the condition "Is Empty." Under "Formatting style," select Fill Color as "None" at the top.
- Click "Done."
- To add the next rule to highlight the date if the balance transfer expiry date falls within a certain number of days, follow these steps:
- Click "Add Another Rule."
- Under the "Format cells if" drop-down menu, select "Custom formula is" and enter the rule =DAYS(C13, TODAY()) <= 30 to highlight if the balance transfer expiry date falls within the next 30 days.
- Under "Formatting style," select Fill Color as "Red" to highlight the cell in red when the specified condition on number of days to balance transfer expiry date is met.
- Now, select the cell in which the custom format is applied. Click Ctrl+C or right-click and select Copy.
- Select the remaining cells for the Account type "US Credit Card." Right-click and select Paste Special > Format Only. This will apply the formatting to all the relevant cells.
- Enter a few sample balance transfer expiry dates to check if you are promptly notified according to the set condition if the number of days to balance transfer date which is within the set 30 days. You can update this according to your need by following the same steps to apply conditional formatting.
Last Checked:
Use this column to bookmark and track the last checked account within an account type and group. It can be utilized to bookmark and monitor the most recently reviewed account. By referencing this bookmark, you can proceed to check the next account in the group sequentially if you intend to periodically review all the accounts within that group. You can also deviate from the order and inspect any accounts within the group according to your priority, marking the last checked account as you go. Given that there are multiple groups of accounts, this column will be used to track the last checked account within each group separately.
Group Checked:
In addition to the "Last Checked" column for bookmarking individual last checked accounts within a group, you can optionally use this column to bookmark the last checked group. This is useful if you plan to review the accounts by group and wish to proceed to the next group in sequence periodically. Utilizing this feature will help you manage the accounts by group and prevent you from missing any accounts or groups for an extended period.
Days Passed:
Days Passed:
This column displays the number of days that have elapsed since you last checked a particular account. It is an automatically calculated column based on the "Last Check Date" and today's date.
To apply the number format to the column for displaying the number of days as integers, follow these steps:
- Highlight the entire column.
- Go to the top menu bar, click "Format," and then select "Number."
- Click on "Custom number format."
- Choose the "Integer" option.
- Click "Apply."
Next, add the formula to calculate the number of days:
- Select the first data cell (H2) in the column and input the formula, then press "Enter."
- Select the cell again. Click Ctrl+C or right-click and select "Copy."
- Choose cell H3. Click and hold the Shift key. Scroll down and click the last data cell in the column to select the entire range.
- Right-click and select "Paste Special" > "Formula only" from the menu. This will apply the formula to all the selected cells.
- For all the rows with the date on the "Last Checked Date," the number of days will be automatically calculated.
To highlight the days if they exceed a certain number of days since the last check, follow these steps:
- Select the entire "Days Passed" column to format it.
- Go to the menu bar and Click "Format" > "Conditional Formatting". A toolbar will appear on the right.
- In the toolbar, stay on the "Single Color" tab and update "Apply to Range" to start with H2.
- Under "Format cells if," choose the condition "Greater than or equal to" and enter "7" in the value or formula box.
- Under "Formatting style," select "Fill Color" as "None" at the top.
- Apply the "Bold" format. Click on "Text color" and select "Red."
- Click "Done."
Now, the formatting is applied to all the relevant cells. Adjust a few sample dates to recalculate the number of days and verify that the highlighting format is applied correctly. All the cells with a number of days greater than or equal to 7 will be highlighted in red.
Next Pay Due:
The "Next Pay Due" column is used to input the next payment due date for the specific account. You can update this date when you check the particular account.
To display the "Next Pay Due" date correctly, apply the date format to the column:
- Highlight the entire column.
- Go to the top menu bar, click "Format," and then select "Number" > "Date."
For enhanced tracking and visibility, you can custom format the column to notify you by highlighting the date if it falls within a certain number of days. This will allow you to pay immediate attention if the payment due date is approaching for a particular account.
To apply custom formatting, follow these steps:
- Select the entire "Next Pay Due" column.
- Click "Format" > "Conditional Formatting" in the menu bar. A toolbar will appear on the right.
- The first rule will be added to maintain the default formatting for empty cells.
- In the toolbar, stay on the "Single Color" tab and leave the default "Apply to Range."
- Under "Format cells if," choose the condition "Is Empty." Under "Formatting style," select "Fill Color" as "None" at the top.
- Click "Done."
- Click "Add Another Rule."
- In the toolbar, stay on the "Single Color" tab and update "Apply to Range" to start with I2.
- Under "Format cells if," choose the condition "Custom formula is" and enter the formula =DAYS($I2, TODAY()) <= 7. You can modify this formula as needed.
- Under "Formatting style," select "Fill Color". select the color "orange" or a color of your choice.
Now, the formatting will be applied to all the relevant cells. Close the toolbar and proceed to enter a few sample payment due dates to ensure that you are notified by highlighting the date when the payment due date falls within the next 7 days. Be sure to update the payment due date for regularly paid accounts when you check them to effectively monitor and make the payment on time.
Next Pay Set:
Next Pay Set:
The "Next Pay Set" column is used to enter the next payment scheduled date for the specific account. You can update this when you check the particular account and set the payment to be sent before the payment due date.
By looking at both the "Next Payment Due" and "Next Pay Set" dates, it will help you determine when you want to check the particular account next and avoid checking it more frequently than necessary.
Reg Bill Pay:
With the "Reg Bill Pay" column, you can note whether the particular account requires regular payments, such as monthly or weekly, with the options "Yes" or "No". This will help prioritize and check all the accounts that need regular payments on a monthly or other interval. Using this option, you can also review all the paid accounts regularly to determine if any of them are genuinely necessary or if there is an opportunity to reduce the payments.
Account Access:
The "Account Access" column is used to specify how you access a specific account, and in most cases, it will be online. In certain cases, such as when you add a row to represent an account for an offline document, you can specify it as "Offline." In other cases, such as subscribing to a paid app on your mobile, you can add that as an account and indicate the access method as "Mobile."
Go To:
Use the "Go To" column to add a direct login link to the specific account. You can click this link to open the login page and enter your credentials to access the account. To add the login link to a specific account, follow these steps:
- For example, to add the login link to a Chase account, enter the name of the link as "Chase" or another name of your choice.
- Go to the Chase login page and copy the URL.
- Right-click the cell with the name and click "Insert link."
- Paste the copied URL and click apply.
Now, the hyperlink is enabled for the name, and you can click the link to go to the login page directly from the sheet.
Managing Your Accounts Effectively
When you add numerous accounts to the tracker, you can apply data filters to search for or look up a specific account.
- To apply a filter, Select the column headers.
- Go to the "Data" menu and click "Create a Filter."
- Now you can see the filter option added to the right side of the column header.
- Click the filter option and select specific data values to filter the records on the tracker.
Once you apply a filter to a column, a filter icon is displayed to the right of the column header. Click the filter icon again to clear the applied filter and display all the values in that column.
By freezing the column headers, you can always view the data associated with the column headers, avoiding confusion when scrolling down to look at the data rows towards the bottom. To freeze the column header row, follow these steps:
- Select the first data row below the header.
- Go to the “View” Menu.
- Select “Freeze” and click “1 row”.
- Now the column headers are frozen when you scroll down.
You can easily copy and paste an existing account to add a similar new account to the tracker.
- To add a new account above or below an existing account, select the specific account row first.
- Press Ctrl+C, or right click and select “Copy”.
- Right click on the selected account row again. Click “1 row above” or “1 row below” option, depending on where you want to add the new account.
- To add the copied account information, , select the newly inserted empty row and press Ctrl+V, or right-click and select "Paste."
- After duplicating an existing account, you can update it with the new account information.
- Alternatively, you can also manually enter all the information for a new account in a new row.
To delete the accounts from the tracker, follow these steps:
- To delete a specific account, click the row number of the record on the left.
- Press the “Del” key to remove only the account information and leave the row empty.
- To delete the complete row, right click the selected row and click “Delete row”.
- To delete multiple rows, click the row number of the first record on the left.
- Click the “Shift” key and select the last row of the range of records to highlight them.
- Within the selected range, right-click and choose “Delete Selected Rows” from the menu.
Share the Tracker
Once you've created the account tracker with your own list of accounts, you can share it with your family members as read-only or allow them to edit or add more accounts to the tracker.
- To share the tracker, click the “Share” button at the top right.
- Before sharing, restrict access to the tracker so that it can't be accessed by anyone with the link.
- Enter the names or emails of the people or groups in the text box.
- Select the role from the drop down to choose the level of access as Viewer, Commentor or Editor.
- Select Checkbox to “Notify people” that you want to share.
- Optionally You can also enter a message to send with the notification.
- Click “Send” to share the tracker and close the share window.
Share with the link:
- To share the tracker with anyone using the link, click the “Share” button again.
- Under “General access”, click the Down arrow Down.
- Choose Anyone with the link.
- Select the role from the drop down to choose the level of access as Viewer, Commentor or Editor.
- Click Copy Link.
- Click Done to close the share window.
- You can share the copied link with anyone.
Unlock the Tracker's Advantages
This advanced account tracker offers a holistic approach to account management, ensuring full oversight while streamlining and simplifying the process. You can add any type of accounts to the tracker that you want to check, review or monitor on a regular basis. By unlocking the full potential of the tracker with advanced features, you can effectively track and manage all your accounts in one place.
- Saves Time: The tracker saves a significant amount of time in managing your accounts.
- Simplifies Tracking: It eliminates the complexity and confusion involved in monitoring and tracking all your accounts.
- Easy Identification: You can easily identify accounts that are long overdue for checking by applying a filter on "Days Passed." You can also quickly look up for the accounts that need immediate attention with color code.
- Efficient Account Checking: It helps you avoid checking accounts more frequently by using the Last Checked and Group Checked bookmarks with the Last Checked Date.
- Prioritizes Regular Payments: The tracker focuses on accounts needing regular payments with a convenient filter option.
- Centralized Repository: It acts as a centralized accounts repository for you and your family to share information.
- Access Anywhere: You can access it from anywhere using a computer or mobile device, ensuring to keep your account information always up to date.
- Mobile Access: It can be accessed from the mobile devices using the Google Sheets app.
- Automatic Sync: The tracker automatically syncs updated account information to all shared users when connected to the internet. Additionally, all the changes made to the tracker are saved immediately; there is no need to explicitly save the changes.
- Offline Access: If offline access is enabled for the tracker, it becomes available for updates even when offline. Any changes made to the tracker will synchronize with all shared users when the device reconnects to the internet and goes online. You can also enable offline access using the Google Sheets app on your iPhone or Android mobile phone.
- Direct Access: Enabled links allow you to access the account login page directly from the tracker. By using these links in combination with a password manager, such as LastPass, or the one offered by your browser to store login credentials, it enables convenient access to your accounts.
- Backup: Google Sheets keeps a revision history of the tracker that you can access by going to "File" > "Version history" > "See version history." This allows you to see changes made to the sheet over time and revert to previous versions if needed. You can name and save specific versions from the version history.
- Easy look up: Column filters allow you to search for a specific value in a particular column. Use the simple search option by pressing Ctrl + F Keys on Windows or Cmd + F on Mac. Enter the information you're looking for in the search box. Google Sheets will highlight occurrences of the search term in the sheet.
Advanced Account Tracker Version 1.0
Google Sheets is a free online software tool that offers full access to its features without any user restrictions.
This Advanced Account Tracker version 1.0, created as part of this tutorial, is available for purchase. This tracker is fully featured and comes with numerous sample accounts. If you're interested in purchasing, please click here. After the purchase, you can modify the tracker, inputting your personal accounts for precise monitoring and tracking.
In the provided template, generic placeholders have been used for member names such as 'Member 1', 'Member 2', and 'Member 3'. You're encouraged to replace these with the actual names of yourself or family members to facilitate easy account identification. Feel free to delete any unnecessary accounts or add new ones as required.
For any inquiries, feedback, or assistance related to the tracker, kindly drop your comments by clicking the "Add new review" button below this post or submit a ticket.
Stay updated with our latest tutorials aimed at further enhancing the tracker and introducing more features. Don't forget to subscribe to the Cleartalking Youtube channel to receive instant notifications.
With these advanced features, your account tracker will become a comprehensive tool for managing all your financial accounts more efficiently. Thank you for following along, and be sure to check out other tutorials in this series.
Other Tutorials:
Tutorial 1: Set up a Free Basic Account Tracker on Google Sheets
Useful Links:
Purchase Advanced Accounts tracker: Click here.
Enter the relevant keywords for the tutorial.
Provide the complete url of the video starting with http.
User reviews
There are no user reviews for this listing.
Already have an account? Log in now or Create an account