How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac

A good way to learn some basic Numbers skills is to build a simple checkbook. You can use this to keep track of your bank account balance.

Video Transcript: Hi this is Gary with MacMost.com. Today let me show you how to do a basic checkbook in Mac Numbers.
MacMost is brought to you thanks to a great group of more than 600 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So I've been asked a few times recently about doing a basic checkbook in Mac Numbers. So a checkbook is basically a running record of deposits and withdrawals from you checking account. But the tricky part is calculating the current balance based on each row of the checkbook. This is actually a good example to use to start learning how to use formulas in Numbers. 
So let's start off with a standard blank template here in Numbers. We're going to label the columns first. Now you have the Header column here and we can keep that because there is a unique identifier for each item here. That's the check number. Now we're not always going to use a check number but it's going to be common enough that we can use that as the header column. So I'll call that Check Number. Next we have the Date. Then we can have the Recipient. Now here we can put the amount of the check.
But a checkbook should also record any deposits. So instead of calling it an amount we'll call it Withdrawal and then the next column we'll call the Deposit. The idea would be that if this row represents a withdrawal, a check written out, the number would go here. If it's a deposit, money going into the account, the number would go here. You would never have a row where there's something in both of these cells. Only one or the other. 
The next column is going to be the running balance which is the whole point of the spreadsheet. To be able to keep track of how much money is in the account at any time according to the amounts deposited or withdrawn. Then we'll have a column here for Miscellaneous Notes which is always important. So let's enter in some sample data here. 
I'll just create a pretend number there. Put in a date. I won't worry about filling out Recipients here. But let's say that this is a $100 check. Let's say there's another check and that's on the next day and that's for $45. Then let's say on the next day there's a deposit. Now there may be a deposit number or something you have and you could enter that here or you could leave that blank. Let's say here there's no withdrawal but there is a deposit of $400. Then let's add a few more samples. You could see here I've added a few more checks and another deposit as well.
Now it's time to calculate the balance based on that. To do that we're going to need a starting balance. Let's create another table. First we'll name this one Bank Account and I'll move it down somewhat to make room for another table up here. We're going to insert another table and just use a blank table like that. Move it up here above Bank Account and let's shrink it down so it's one by one because there's only going to be one number in this table. We're going to call that table Starting Balance. We'll move that over here, kind of above the balance column and we'll enter in the starting balance. Let's say we're starting with $2000 in this account at the time that we're starting to keep track of this. You can adjust these tables and move them around as you want.
So now I've got a starting balance of $2000. To do the first balance calculation I'll hit the equals key here to start entering in a formula. I'm going to start with the Starting Balance and we're going to then subtract any withdrawal in this row and add any deposits. Then I hit Return and I'll get the balance after this check has been written out. Now this first row is the only time we're going to refer to the Starting Balance. For the second row here I'm going to do a similar formula but I'm going to start with the balance in the row above. Then I'm going to subtract the withdrawal. Add the deposit.
Now that I've done that I can Copy this cell and paste it in the remaining rows. Now it will pull the balance from the row above and subtract the withdrawal of the current row and add the deposit of the current row. So here we can see it added $400 and subtracted $67. Subtracted $154. Subtracted $300. Added $256 and subtracted $50.
Let's go and shrink this table down. I'm going to grab the bottom handle here and shrink it all the way up there so there are no extra rows. This allows me to enter in new data by simply going over here to the check number, hitting Return, and then entering in the check number and the other data. Notice the formula is automatically filled in. So as I put this in here, if I do a withdrawal of $12, you can see it automatically update the balance.
Let's make this a little bit nicer. I'm going to select this column here and I'm also going to Control Click this column and this column. Select all three columns. Go to Cell, change the Data Format to Currency, and now I can check here that it's two decimal places. I can. I can add a Thousands Separator as well. I can do the same thing here for the Starting Balance. Change that to Currency and add the Thousands Separator. So now I've got a nicer looking set of data.
That's basically what it takes. It's just a matter of understanding the main formula here which is the one that appears in all of these cells. Which is just to simply take the balance from the row above, subtract the withdrawal, add the deposit of the same row. Then realize that the first one is going to be different. Instead of taking from the row above it's going to take it from a separate table here that contains just the starting balance. 
Note sometimes people like to have things like withdrawal as negative numbers and deposits as positive numbers. That's fine. All you need to do then is adjust this formula. So instead of subtracting the withdrawal it adds it because  if the withdrawal is a negative number. You want to add the negative number and not subtract the negative number.  But other than that it all remains the same.

Comments: 15 Responses to “How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac”

Ian MacGregor

3 years ago

I wrote one of these spreadsheets last year, but I like yours better. Thank you!

Gary, Great video. I have been using a numbers spreadsheet for years. The biggest difference being the first row has the beginning balance in the deposit column with the note stating Starting balance. Works the same. Thanks for all you do.

martin kelinsky

Gary,

Why go to the effort of creating a starting balance table when it is only used once.

I feel it is simpler to put a starting balance on the very first empty row before any transactions begin to be recored.

Gary Rosenzweig

Martin: It really isn't much effort. There are some good reasons to place this unique value into a separate table. For instance, say you want to use COUNT to count the number of transactions. You don't want that special row counted. Saw you want to double-click on the column heading to select all of the cells in the column to re-paste the formula or do something else -- you would get that special unique row also if you did that.

Cathy McLaren

Great one for this Numbers newbie. Thanks Gary.

Arthur Dixon

I am getting an error when I try to copy and paste the formula. It tells me this: The operator “-” expects a number, date, or duration, but cell D4 contains a string. What is my problem?

Arthur: What do you have in cell D4? Is it a number? When you select D4, what does it show as the cell type in the bottom left corner of the window?

Randy

In this spreadsheet/checkbook, is there a way to use filters to see what you are spending on a particular type/category? For budgeting purposes?

Randy: Check out Smart Categories https://macmost.com/using-smart-categories-pivot-tables-in-mac-numbers.html

Debbie Throne

Great video. Helpful to a Numbers newbie. I want to delete rows but get error message in balance column below if I do. How do I create a formula that holds even if a row above was deleted? Do you have a video for it? Thanks in advance

Debbie: Make sure instead of a range, you just use the column. For instance, if the table is 44 rows, and the last row is a footer with the balance, and the first row is the header with the label, you may have a formula with B2:43 in it. Instead just have B. That will include only the cells that are not header or footer cells. If the column has a label it will look different. You can just click on the letter B above the column to get either "B" or the name of the column.

Dick Millard

Thanks, Gary. This Numbers beginner has learned a lot. Works well. Would I be able to add a table containing a dynamic Recipients list that could then be referenced in the payee/recipient cell in each row of the Account table? Not sure how to formulate that. Thanks, Dick

Dick: check out https://macmost.com/a-simple-numbers-lookup-example.html

jason Lintner

This is great. My older mom is using this and we are looking for help. She is adding transactions, sometimes future dating, and we know how to sort by date, however when we do so the formula is affected and always needs to be corrected. Is there any way to have transaction automatically enter by date and still have your formula work?

Jason: You can't really do automatic dates, at least not very easily.

Comments Closed.

How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac

Comments: 15 Responses to “How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information