I do a lot of banking by mail—mainly depositing checks that I receive in the mail. I have run out of deposit tickets from the back of my check books, and I resent the idea of having to pay for more to be printed. I took a look online and didn’t find anything particularly satisfying, so I made my own.
When I did a search online, I came to
a post in a forum that looked pretty good. It seemed to have all the right bits in place. I downloaded it and discovered that the template doesn’t work on its own. I haven’t investigated enough to figure out what it does, but as far as I can tell it is either meant to be filled out by QuickBooks (which its name implies) or it’s just meant to be printed and filled out by hand. I find this funny because it’s a spreadsheet. We could just type in the values and let it add them up and all that. That wasn’t useful enough for me.
So here is my Excel template for a bank deposit ticket. You will also need to download the MICR font (which is free) and install it.
- You can type in the blanks next to the amounts. I.e., you can enter check numbers (like where I typed “check #1234”
- You can type in the amount of the check in the blanks (e.g., 123.45)
- The “Total Items” field automatically totals up how many items there are. This can be 1, 2, or 3, depending on how many checks are listed.
- The subtotal line calculates automatically.
- The total line calculates automatically based on the subtotal minus the cash received line
- The date is automatically filled in with today’s date, but you could easily just override that and type in any date you want.
- It’s roughly the same shape and size as the reference ticket I used (from Wells Fargo née Wachovia)
Prepare the Template for First Use
If you make it a custom XLTX (Excel template) with your own values, then you can save yourself a lot of changes each time you use it.
Fill in your account holder details. Name, address, city, state, zip (Cells A4, A6, A8, A10)
Fill in your bank’s name and address. It seems to be sufficient to put the name, city, state, and zip. (Cells B15, B16, B17)
Fill in your deposit routing number (Cell A20) and account number (D20). Note that this is very often NOT the same as the routing number at the bottom of your checks. For one possible reason why, see here.
Notice that, in the formula bar, you will see the letter A, some numbers, and the letter a. Those two letters create symbols at the start and end of your routing number. Keep them!
Notice, also in the formula bar, after the account number (Cell D20) there is the letter c. Keep it!
If you don’t see the numbers in a strange looking typeface (font), then you haven’t installed the MICR font properly. If you see actual letters A, a, and c, you have done it wrong.
Make sure ALL the digits and all the symbols are visible.
Remove the example data (checks, amounts, etc.).
Do File → Save As.. and save it as an Excel Template (xltx). Make sure it’s in the My Templates area. Now, when you choose to create a new Excel Spreadsheet (via File → New from Template..) this template will be available each time.
Using It Normally
It’s just a normal deposit ticket now. Just fill it out as necessary. You can either use the New from Template feature of Excel, or you can double-click the XLTX file. It will create a copy (so you’re not changing the template permanently). Once you have it open, you just fill in the values.
- Check the date. If Today is ok, leave it alone.
- Enter the cash amount (Cell H2)
- Enter the check numbers or identifiers (cells F4, F6, F8, as necessary)
- Enter the corresponding amounts for the checks (cells H4, H6, H8 as necessary)
- Check that the subtotal is calculating correctly
- Enter any cash you plan to receive
- Double-check the total
- File → Print