In the YouTube video below, you’ll see the steps to copy your bank transactions from an old version of my Excel Checkbook spreadsheet over to the newest version. A summary of those steps include:
- Download the latest version of the Excel Checkbook spreadsheet
- Open your current checkbook spreadsheet and also open the new version
- Review your old checkbook to ensure you have the same number of columns as the new version, e.g., Date, Trx Type, Check #, Description, Withdrawal and Deposit.
- If your old checkbook doesn’t have the same number of columns (e.g., your old version doesn’t have a “Trx Type” column), insert any additional columns in your old spreadsheet to match the new version. They can be empty.
- Use your mouse to select your bank transactions in your current working copy. Select cells starting in the date column and going over to the deposit column, but ensuring you do not include the balance column. Copy that data to your clipboard.
- Switch to the new version of the checkbook file and click your mouse in the first date cell. Use ‘Paste as Values‘ under the paste button to paste your data.
- Switch back to the old version (your working copy) and use your mouse to select your sub-category values that coincide with the data you just copied. Do not select the ‘category’ or ‘type’ columns as those are formula-based. Copy your selected data to your clipboard.
- Switch to the new version of the checkbook file and click your mouse in the first sub-category cell. Use ‘Paste as Values’ under the paste button to paste your data.
- Repeat the steps above for any extra register worksheets you have.
- Save your new file with a filename that makes sense to you.
The New Formula for Calculating Balance
Performance Update: I switched the formula for balance to use an INDEX function without any explicit cell references. The old formula used OFFSET which caused performance issues as your spreadsheet got larger. The new formula looks like this:
=INDEX([Balance],1)+SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Withdrawal],1):[@Withdrawal])
This revised formula will improve the speed of the spreadsheet and it also makes it possible for you to safely delete or insert rows in the spreadsheet without an error.