Simple Excel Bank Account Tracker

Here’s my guide to creating a super simple Excel spreadsheet to help you keep track of your bank account.  For this I used Excel from Office 365 which, at the time of writing, was on Version 2102

First get a new Excel spreadsheet file and save it as something meaningful.  I called mine “bank_account_logger.xlsx”.  Also double click on the worksheet tab and call it something meaningful too (mine is “Bank spreadsheet”).

Next add some column headings and set the column widths.  I added 4 headings:

Date, Description, Amount, Total

On the image below I show how to set the column width of the date column.  I selected the whole column by clicking on the “A”, right clicked, selected “Column Width…” typed 10 in the box that appears and clicked “OK”.

With the following column widths set the spreadsheet now looks like the image below.  Date = 10, Description = 100, Amount = 15, Total = 15.

You now need to do some formatting to make the spreadsheet look good and work nicely.  First freeze the top row so that when you scroll down further than the first page you still see the headings.

Do this by selecting the whole row by clicking on the “1” and selecting the “View” menu, then “Freeze Panes” and “Freeze Top Row”.

Just type today’s date in cell A2 and clever old Excel will then recognise this as a date column forever more.  I typed “20/3/21” in cell A2 and Excel replaced it with my preferred, UK-centric, date format.  As a extra hack, you can hold down the control (ctrl) button and press the ; key (semi-colon) and todays date will be entered in the cell.

The spreadsheet now looks like this:

The next column is description which will just contain text so there’s no extra work to be done here.

The amount and Total columns will contain monetary values so the Number format needs to be set to your local currency (British Pounds for me). 

To do this, first select columns C and D.  Do this by first clicking on the “C” at the top of the 3^rd column, then hold down the ctrl (control) key and click on the “D”.  Next in the “Home” menu go to the “Number” area and click on the little slanting downward pointing arrow to open the Format Cells menu.

Under category select “Accounting”, set decimal places to 2 and the symbol to the currency used in your country (so £ for me).  

Finally, as an optional advanced task, use Conditional formatting to vary the colour of the Amount and Total columns depending on whether they’re positive or negative.

In the “Home” menu select “Conditional Formatting”, “Highlight Cells Rules” and “Greater Than…”

Type 0 in “Format cells that are GREATER THAN:” and select “Custom Format…”.

Simply set the Color to Green and select “OK” and “OK” again.

Repeat the steps above but with a “Less Than…” rule, with “Format cells that are LESS THAN:” set to 0 and a custom format to set the font colour to Red.

If you like you can play with the cell formats, I just like to select something that says “this is definitely positive” and “this is definitely negative”.

Now you can create your first entry, a starting balance.  In cell A2 make sure you have today’s date.  In cell B2 write an informative description, like that below.  In cell C2 write your current bank balance.  In cell D2 type the formula “=c2”.  That just makes sure your Total column also has your starting balance.

You can now add your first transaction to the spreadsheet in row 3.  As before, enter the date, a good description and the amount.  Type the Amount as a positive number for credits to your account and a negative number for debits.  In cell D3, type the formula “=D2+C3”.  What this means in practise is “Take the Total from the row above and add the amount from this row”.

You now get a running total in the Total column.

When you want to put a debit on the sheet, just add as a negative figure in the Amount column and use the same formula in the Total column.  I’ve typed this manually by way of explanation but an easy way to do it is to select a cell with the formula (cell D3 in this example), right click, select “Copy”, select the cell you want the formula in (cell D4 in this example), right click select “Paste”.

So now the spreadsheet looks like this.

So now you every time you spend something or a bill goes out you simply add a new row with the date, a description, the credit or debit (written as a positive or negative number) and then update the Total by copying the formula from the cell above.  So you end up with a spreadsheet that looks like this.

Happy spreadsheeting!