How to Set Up an Excel Spreadsheet for a Joint Bank Account

by Erica Sweeney ; Updated July 27, 2017

It doesn’t take much for a joint bank account to get disorganized with both you and your spouse purchasing, depositing and writing checks out of it. It is so important to get organized and find a way to keep track of everything so that you know your bank balance and how much you’re spending. Using a Microsoft Excel spreadsheet for a joint bank account is an excellent way of doing this.

Step 1

Open a blank Excel spreadsheet on your computer. Title the spreadsheet “Joint Account” or something similar and put the month and year underneath it.

Step 2

Set up four columns with these titles: "Date," "Check Number," "Payee" and "Amount." Center and bold the titles.

Step 3

Skip a line and then type “Previous Balance” in the Payee column. To the right, in the Amount column, enter the account’s balance from the previous month.

Step 4

Leave about 50 or so blank lines to list transactions. This probably will not be enough for a whole month, so if you need to add lines, just select the cell where you want to add lines, and then go to "Insert" and "Rows."

Step 5

Type “Total" in the Payee column about 50 lines from the title.

Step 6

Use the sigma function (it looks sort of like an uppercase E) to create an ongoing total, or your bank balance. Click on the cell immediately to the right of the Total cell, in the Amount column, and then click on the sigma, which is usually on the toolbar. Click the first cell that you want to include in the total (the Previous Balance number) and drag your mouse to the last cell that you want to include (a box with dotted lines will form around the cells); press "Enter." Totaling the spreadsheet this way will create an accurate bank balance that changes as you add numbers in the Amount column.

Step 7

Highlight the cells in the Amount column that will have numbers in them—do not highlight the title cell. Go to "Format" and "Cells." A box will appear; click on the "Number" tab. In the "Category" box, click "Currency." The “Decimal Places” box should have 2, and the symbol should be $. Click in the box next to “Use 1000 Separator (,)” to select it. In the “Negative Numbers” section, select the number that is red and enclosed in parentheses. Click "OK."

Step 8

Highlight the cells in the Date column but not the title. Go to "Format" and "Cells." In the "Number" tab, this time choose "Date" in the "Category" box. Choose a date style that you like in the "Type" box and click "OK."

Step 9

Enter information into the columns as you make purchases and deposits. If using a debit card, you can leave the Check Number column blank. Use the minus symbol (-) in front of any number that should be subtracted from the bank balance when entering into the Amount column—after pressing "Enter," it will automatically turn red and be enclosed in parentheses since you formatted it to do so. Also, enter the Date and Payee name in the appropriate columns.

Step 10

Create new worksheets for each month. Change the name of the worksheet by right-clicking on the tab at the bottom of the sheet—it will usually say “Sheet 1”—select "Rename" and name it for that month. To add worksheets, go to "Insert" and "Worksheet." Sometimes they are added in odd places, but you can move it by right-clicking on the tab and selecting "Move" or "Copy."

Step 11

Create a system with your spouse to make sure that all transactions are placed on the spreadsheet. Try using a little box or folder where each of you places receipts at the end of the day. Entering information on the spreadsheet daily is the most effective way to keep everything organized and up to date.

Step 12

Compare the balance on the spreadsheet to the actual bank account regularly. Using online banking is a great way to do this. Scroll through the transactions online to make sure that you have listed all of them on your spreadsheet.

Tips

  • This method can also be used for a single-person or business account.

About the Author

Erica Sweeney is a freelance writer and editor based in Little Rock, Ark. She has a master's in journalism from the University of Arkansas at Little Rock. Her work has been published at SaidIt.org, Arkansas Times, Aging Arkansas and Arkansas Business.