Paco Hope My Random Musings and Rants

15Jan/12Off

Print a Bank Deposit Ticket with Excel

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.

My Solution

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.

Excel template for a bank deposit ticketFeatures

  • 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.

  1. Fill in your account holder details. Name, address, city, state, zip (Cells A4, A6, A8, A10)
  2. 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)
  3. 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.
    1. 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!
    2. Notice, also in the formula bar, after the account number (Cell D20) there is the letter c. Keep it!
    3. 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.
    4. Make sure ALL the digits and all the symbols are visible.
  4. Remove the example data (checks, amounts, etc.).
  5. 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.

  1. Check the date. If Today is ok, leave it alone.
  2. Enter the cash amount (Cell H2)
  3. Enter the check numbers or identifiers (cells F4, F6, F8, as necessary)
  4. Enter the corresponding amounts for the checks (cells H4, H6, H8 as necessary)
  5. Check that the subtotal is calculating correctly
  6. Enter any cash you plan to receive
  7. Double-check the total
  8. File → Print

Done.

Did I Save You Some Time?

You might think about donating a dollar. I spent a few hours tinkering with this to get it right. Folks like Harland want to charge you $5-$7 plus shipping every time you run out for packages of deposit tickets. Unlike checks, which show off style and might be a personal statement, deposit tickets are just between you and the bank. They don't even allow customizing. Why pay for them? I've given you the ability to do it forever, just for the cost of your own ink and paper. Click that "donate" button to send me a buck or two if you think two hours of my time were worth it for you. If not, no big deal.

Comments (3) Trackbacks (0)
  1. the link is not working :( (load a page full of code in any browser i use)

    • (to clarify, sorry: i meant the link to the actual excel template itself … the font link worked great :)

    • My web server wasn’t sending the right codes to tell your browser that this was an Excel file. If you had right-clicked and chosen “Save As…” it would have worked. Anyways, I looked into this and it’s fixed now. It probably works a lot better if you click on it.


Trackbacks are disabled.