How To Make a Basic Checkbook Spreadsheet With Numbers On a Mac
HTML-код
- Опубликовано: 15 июл 2024
- macmost.com/e-2161 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.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ RUclips - macmost.com/j-youtube (And hit the 🔔)
▶︎ Weekly Email Newsletter - macmost.com/newsletter ✉️
▶︎ Twitter - / rosenz and / macmost 🐦
▶︎ Facebook - / macmost 👍
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
#macmost #numbers 
Наука
![Veeze - F.A.F (ft. Rylo Rodriguez) [Official Music Video]](http://i.ytimg.com/vi/7hlz2S2GmIY/mqdefault.jpg)
THANK YOU SO VERY MUCH for this lesson. I am 70 and recently switched from Microsoft to Apple iOS. I was completely at a loss as to how to set up a simple checkbook. And I had no idea of how to input formulas into cells. My husband always did this for me before he passed away. (And he always used Microsoft.) Now I'm having to learn all this tech stuff--and iOS--without him. Your videos are so well done and easy to follow. I can't thank you enough for this help.
I am 82 old This is very good, It only took over two & a half hours to get it finished I hope I can do it again next time Thank you
Excellent tutorial. Thank you for taking the time to put this together. Saved me some brain cells trying to figure it out on my own.
I am 65, and didn't realize when I purchased my shiny new Mac Mini Sonoma that I wouldn't be able to port from my older Mac. I haven't lost so much computing capability in my life. Last time I set up a computer from scratch was 1994. They used to have manuals back then. You saved me from going back to printing out a ledger and using a calculator, which, come to think of it, isn't so bad.
THIS VIDEO SAVED ME FROM INSANITY !!! I can't say how grateful I'm for your help !!! Thank you so much. I wasted 8 hours trying to figure this out by myself and looking for this answer in other videos.... God bless you
Your videos are so easy to follow. You are a real TEACHER and an awesome one. You are direct. straight to the point. no waste of time or unnecessary commentary. THANK YOU.
Me too! THANK YOU SO VERY MUCH for this lesson. I am 74. I've had a MacBook Pro since 2009, which I used with Excel for Mac. Even when I got a New MacBook in 2017, I kept on using Microsoft Excel on the old machine until the curser developed a mind of it's own, forcing me to think about Numbers. I did not know where to begin until I happened across your BRILLIANT tutorials. I was up and running confidently inside a couple of hours! As Mavanya says, I can't thank you enough for this invaluable help.
Every time I have an issue with my mac, Gary has the answers. I especially appreciate how he gets right to the point and keeps it simple! (I have never once had to fast forward to get to the content!
OMG! Thank you. Why is something so simple not already a template.
Thank you! Thank you! Thank you!
YOU ARE AWESOME! Thank you for the help, I've been trying to figure this out on my own....you saved my sanity!
Thank you so much for this!! Just what I wanted
Very simple, very useful, just what I was looking for! Thank you!
This is a great introduction to Numbers. I I work with Excel so I was reluctant to give it a try, but this tutorial was very helpful. Having a classic checkbook to track spending will be nice. Never could find an app I like.
The best tutorial for this type of spreadsheet!!! You made it so easy.
OMGAWDDDDD I can't thank you enough for this!! I am NOT the most technically inclined individual when it comes to cells/numbers/balance....pretty much anything with numbers, lol. I am amazed that I was able to figure this out. Thank you so very much!
Thank you for this!! I've been searching for a video about this for quite some time!!
Thank You. Your explanation and formulas were just what I was looking for.
I needed this 20 years ago.... thanks and as always great video.
THANK YOU SO SO MUCH FOR THIS VID. EXACTLY WHAT I WAS LOOKING FOR!!!
Thank you this was very helpful.
Thank you for your excellent videos!
Thank you for showing how EASY it is!!! I love this way better than Excel!!!
Awesome.....So simple, yet my brain simply couldn't do it alone. Thank you for taking the time to post this.
Thank you! Everything I've needed for the past 5 years, using other apps, in 6 minutes. Wish I would've found this sooner.
Great video! This really helped. Thank you!
Thank you so much! I didn’t think I could make a checkbook ledger myself. You made it so easy.
Great Video. Simple, basic instruction. Very easy to follow. I created a simple "Cash Flow" spreed sheet in a minimum amount of time. TY
you made formulas much easier! :) Thank you!
Neat! I'm trying that out right now!
Fantastic and easy to follow tutorial. Thank you so much!
Thank you so much for this video.
Great explanation. I now have my checking/debit register under way. Thank you.
thank you great tutorial
Thank you for this! It was tremendously helpful and really easy to set up! I am absolutely shocked that Apple does not include this as a template within their Numbers app for Mac.
Thank you! I enjoy your videos. Glad to know that RUclips can be educational.
Very Helpful! Thank you!
This is a great help. Thank you very much. How could anyone give it a thumbs down?
Thank you so much, kind Sir!
A slight variation could be to add a column for W or D for Withdrawal or Deposit. Then in the Balance calculation, make it the previous rows balance + =IF the types column ="W" then amount = amount*-1, amount. i.e. F2 + (IF D3 ="W", E3*-1, E3) or in column names: Balance + (IF Type="W",Amount * -1, Amount). But as my spouse points out, there are more than one right way to do something! Thanks for the great video tips!
Thank you!
Thank you Thank you!!
Simple. Well done
Thanks so much! Really useful! Now I can do my accounts!
This is basically like an excel spreadsheet. I'd like this to be able to be reconciled when my bank statement arrives. But I am going to play around in numbers which I had no idea what it was for. Very simple informative video.
A very usable spreadsheet. Much better than trying to calculate everything manually..
Great job.
Thank you!!!! I spent almost all day trying to do this since I haven't had to mess with numbers or spreadsheets in 8 months and I lost all of my notes on the subject when I moved.
Thanks man.
This is how I do mine, and from experience it is worth warning that sorting this table can cause the formula in the balance column to become inconsistent, although it is easily fixed.
Let’s say you made a bunch of entries in wrong date order then sorted the table by date, this would result in the formula in some cells of the balance column now referencing the wrong cell. The easy way to fix this is to select the first correct cell in the balance column and drag it down to replace the formulas in the rest of the column. Use the handle in the bottom centre of the cell to drag, not the corner handle (dragging this will just select cells without replacing the contents).
Took me a minute, but it's worth it! Thank you for this. Now then... Is there a way to balance using this spreadsheet?
Thanks ... if you think it's like excel ... the user interface is a LOT different. Thanks for the help ...
That’s a great tutorial thank you, now a request, can you make a tutorial on how to email a large file through the icloud? Thanks
GREAT VIDEO!! I’ve been trying to figure this out on my own, and going nuts! Is there a way to add a dividend column, and if so, how would that be accomplished?
One other point, some might not know, that Numbers runs on your Mac and IOS devices (iPad/iPhone). I run a spreadsheet like this and when I have down time sitting in an airport or restaurant. I look at my bank statement and update the info. You can also pull the bank info into the spreadsheet. What’s interesting is when I started this type of spreadsheet I notice several monthly discrepancies with my account. A pie chart is also a great way to see things and trends at a glance which can be added. Maybe a lesson on adding graphs to such a spreadsheet. Thanks again.
This is a fantastic video! May I suggest a slower transition across each component of your training? From one task to the next-it would be great for new learners if you could perform each component a bit slower. Thank you!
Remember in RUclips you have speed control and you can pause. You can also go back and rewatch. So everyone can watch at their own pace. Thanks.
Awesome tutorial Gary, really appreciate the time you give us.
Can this spreadsheet be exported in any way so that we can access it easier on our mobile device while we are on the move?
Is your mobile device an iPhone or iPad? If so, just save the document to iCloud Drive and then open it up in Numbers on that device. No need to "export" or anything.
Great Video! Thank you! If you enter in date that is not sequential by date, and then sort by date, will the running balance automatically adjust so that all balances are correct? I had issues with this in Excel and it took me a while to find the formula to correct this. Thank you!
Great video. Is there a way to make a row to enter transactions that would then be entered into the table separately?
I created a spreadsheet very similar to this but created a Category column for budgeting. I’m having trouble figuring out how to create a Dashboard table the shows the Unique List of Categories and their summed values based on a date range. Do you have a video that may pulls unique values from a column? Thanks for all your videos!
Would it be possible when you get your bank statement to do reconciliation? Like enter Balance on statement and mark cleared checks.
Your name may not be a Harry, but you're definitely a wizard. Thanks for the help!
Isn't is better that the first row shows balance only, then putting the formula in the next rows, instead of adding a new table of starting balance?
I wouldn't say it is better. Then you have a row that is different than your other rows. It isn't a transaction at all. Doing something like counting the rows (to count the number of transactions) would be off by 1. And it doesn't look as nice.
That’s the way I’ve been doing it for years, in Excel. I was hoping to find a better way because I keep a rolling balance and remove the rows after they’ve cleared my bank account. I don’t care about keeping the history. Instead, I just wanted to make sure I wasn’t forgetting a payment was already being made, thus causing me to have an overdraft. I no longer really *need* that but I still like to keep up with it in a spreadsheet. I guess I’ll keep on doing it the old way, but now I’m using Numbers on my iPad.
cool
Appreciate if you could go a bit slowly. Thank you. You are awesome 😃
Thank you very much for this incredible presentation but i would like to know if you can please make a presentation on how to do the same for multiple account such as credit card or if you have 3 or more different accounts thanks.
You could do each as a separate table on a separate sheet. Or, you could put it all on the same sheet with a column for "account" and then put the account name in that cell. Then you can filter by account to only see one account at a time.
Thank you for the clear instruction. I went ahead and created multiple accounts on different sheets. Is it possible to create a category summary using a pivot table that would summarize data from multiple sheets? Thank you!
No. Create a regular table that summarizes data from multiple sheets. Pivot tables aren't the right tool there.
Good afternoon Gary, this was a great video on setting up a basic checkbook register. In numbers is there a way to be able to Reconcile the checkbook using the formulas. Do you have a video on this or could you point me in a better direction. Don't want to use Quick Books or any other expensive software for my small business teaching CPR..... Dave
I think most of the work of "reconciling" is what I am showing here, right? Not sure what else you would need. If you want to see if the totals in your spreadsheet match the totals in a statement, I suppose you could select those cells and see the sum of them to check.
Thank you. I have tried to move away from Google Sheets and on to Numbers so many times. I just can't figure number out, apart from the sum function, how it works.
I think it's important to say that you need to add the row of information with tab and treat it like a record. When I first created my prototype I entered numbers down each column. When I copy and pasted the formula in F3:F9 it broke I got formula error. Maybe you did that at the beginning but I missed it.
what if you already created a separate expenses list in a different table and different numbers file for each month? how to combine them quickly without adding row 1 by 1 and copy + paste the data 1 by 1?
Would be nice if you added a "Current Balance" next to starting balance on the top - for easy viewing/reading
Hi Gary! I assume in the first column if you wanted to you could make that a drop down menu so you could enter in a eft or a Mac card or a transfer?
Of course. Or, just leave it as text and use the Numbers autocomplete feature.
macmostvideo thanks Gary! I suppose if one desired you could also make individual columns for each too but that I think might lead to a wider spreadsheet lol!!! Not really ideal
In this spreadsheet, is there a way to use a filter to see what you're spending on a particular category or type? For budgeting purposes?
Yes, use Smart Categories for that. ruclips.net/video/Xv5kUn4o1ss/видео.html
how to make the monthly recurring expense automatically added every month?
so that we don't have to type it manually every month
(such as software/app subscriptions, insurance premium, bank admin fee, etc)
This was very helpful. How do I clear the cells without erasing the formulas
Do you mean erase the cells with values and keep the cells with formulas? You just have to select the cells that you want to erase and clear them.
@@macmost yes. I think I was highlighting the entire table including the cells that auto calculated below the heading instead of the tables where I actually entered data. Thanks!
I'm looking for a way to project an estimate of future account balance by entering recurring withdrawals and deposits (monthly, annually etc). Can this be done with Numbers. Or is there maybe an app for that?
Seems like simple math to me. Not sure of exactly what you want, but Numbers should be able to handle it.
@@macmost I'd like to estimate the future balance of a bank account, ideally as a graph, given the current balance and the recurring expenses. Like this app does: ruclips.net/video/dQsynp4XJ24/видео.html just not for the slightly overheated price of $28. Maybe stuff for a future episode?
Hi thank you for this lesson. I’ve been using it for over a year and it works really well. But I’ve run into a problem I can’t solve. Randomly, the sheet has hidden some rows. The rows still show in the formula above and below but when I try to ‘unhide’ the row, that option isn’t available, only hide. I tried hide anyway of the row above and then ‘unhide all rows’ but it still wouldn’t reveal the hidden row. It is noted in the row number column with a tiny thin double line and the number skips from row 385 to 387. Can you help me solve this? Thanks so much
I wrote to you and then I figured it out - I had a filter of a bunch of numbers on. Hmm I’ve been trying to figure it out for quite awhile. You even channel good info 🤣 I’ll keep watching for other Mac tips. Thanks much
Can I set up recurring payments to automatically fill in on a specific date?
Copy and paste? There's no way to have new rows "automatically" be created or anything like that.
I really appreciate the effort however I was unable to use it for my checking account, I feel really stupid because I deleted the numbers to put my account information in and it doesn't work. I saved it as a template but it had all the numbers from the video, I suppose that it's just for educational purposes. I wonder if I should try another tutorial. I used linux os and libre office before but I've had no success with a macbook opening it. Thanks anyway.
Anyone know how to mix between number and text, where the number is varible..., or how convert number to text with format “#,###,###” (like format in excel)
Example: the house with price “2,000,000,-“ USD
How can I save a custom made template with all my heading and formulas installed so that I can start fresh each time and just plug in numbers? Thanks
Create your template, then choose File, Save As Template.
Why does my account in numbers open to the first entry making it necessary to scroll down to the cell I want to use? This was not always true, but seems to be happening since the last update. Is there a setting to open the spreadsheet to the last entry?
I haven't noticed a change. There is a spreadsheet I open every day and it always starts at the bottom with my last selection.
macmostvideo I’m stymied by this change for me. It opens the same way on both my iPad and my Mac Pro laptop. It’s irritating and I’d like to change it back if it is somehow a setting in Numbers.
Do you have a video with correlating the check book with the business expenses?
You'll need to define exactly what you mean by that. Correlating in what way? What is it you are trying to DO, exactly?
@@macmost the video you created for the business expenses, can you create a model with the business expenses, the summary, and how each expense was withdrawn from the checking. I guess possibly a third sheet. So when you put in a category, rent let’s say, and it calculates that expense on the business portion, can it also coordinate the expense when entered in as a check?
@@AquaSourceLLC Just add a column for "method" or something like that and put "check" in there. Add as much information as you need to the table by including more columns.
@@macmost I’ll see if I can figure it out. I want the checkbook, expense sheet, and summary sheet all on one document
Please explain how this will be created on an iPad
Cyndi Feeney, the process would be very similar, with the additional advantage that you can add a form to make data entry easier.
How can I set up recurring payments or deposits to automatically fill in on a specified date?
Can you reconcile with this spread sheet?
You can build it any way you want and perform calculations any way you want. Continue to learn and experiment in Numbers and you can create a custom system that works just how you want it.
When I hit enter on the balance everything keeps disappearing. What do I do?
Hello. I'm new to spread sheets, so I need it broken down. Instead of identifying a specific cell, can a formula use the "cell above" instead of a named cell. In my case, I built a spreadsheet containing all reoccurring expenses and deposits for the month. But, now that I'm adding new rows in between, the math is wrong. Instead of identifying a cell name (like C2), is there a way to say "Cell Above"?
Cell references in spreadsheets are either absolute or relative. By default they are relative. So lets say C2 holds the value 42. If you put a formula is D2 that is =C2+1 then D2 shows 43. If you put the value 100 in C3 and then copy and paste the formula in D2 into D3, that pasted formula now shows =C3+1 instead of =C2+1. It changes relative to where it was put. So if you make a formula in cell G7 and want to refer to the cell above, then just use =G6. That IS a reference to the cell above. If you were to move or copy that formula to H45, it would become =H44. See ruclips.net/video/KZJ8KqV0rPg/видео.html
Instead of adding a second table, simply make your first row the starting balance.
Then you have a row that is NOT a data record. That throws off some calculations. A simple example would be calculating how many transactions. It would be 1 off. Or if you wanted to sort, you'd be sorting that non-entry row with the rest. The rule is to make every standard row a record of the same type of data as the rest.
I want to add a column with pop up items to categorize my withdrawals so I can pie chart them
You can. You just have to use formulas or a pivot table to do the calculations, then produce a pie chart from that.
If I add row in above and entry receivd or payment , sum not auto update . what to do ?
Make sure your SUM formula is correct. It should refer to the column, not a range of cells.
@@macmost My function is like that : =sum(e3+deposit-withdraw) , when I insert row above and enter my amount in deposit / withdraw , the balance amount not autoupdate, insert rows amount and next below rows amount shows same balance amount , not update.
@@satyajitdas9509 That formula doesn't look right. Inside the SUM function should be a column name, not anything with a +.
When I hit return after setting up my one time formula in the Balance Row I get an error message, "The operator “-” expects a number, date, or duration, but cell D1 contains a string.
Why is this? Thank you! I should add I'm new to Macs.
What do you have in D1 then? If it is a number, like 123, then make sure the cell format is set to Automatic or a number format, not text.
Thank you, that solved the problem. Much appreciated!
This formula breaks for me if I insert a row or delete a row (sometimes due to a duplicate entry because I didn't realize I already entered something or the date from receipt didn't match an entry from the bank). If I insert a row the rows below do not properly update and if I delete a row, I get a #REF error and have to manually update the balance entry to point to the correct cell and then the rest update. Weird.
Update: Solved it by using the OFFSET function. It only took me 5 hours to discover this and get it to work.
Isn’t there a temple for this?
A template? No, but there are some similar things. Still, the idea here is to learn how to create one.
Okay I am following this step by step but the formula doesn't go forward when I hit return
What do you mean by "doesn't go forward?"
@@macmost The "total" formula wasn't going to the next line when I hit return. But that was something I messed up. I started from scratch, again, using your tutorial and it worked perfectly! Thank you!
hellow
Where do I find numbers in my Mac though 🤷🏻♀️
If it isn't in your Applications already, then get it from the Mac App Store.
My comment wouldn't post, trying again, sorry if it duplicates it. In this example you gave your F10 shows your running balance at the end. How do I copy that result to another sheet but for it to keep my other sheet updated after I add another row? I got it to copy F10 but when I add another row for another expense it keeps showing F10 results. I want my running balance to stay updated on my budget sheet, if it makes sense.
Put a simple formula. Type = to start a formula. Then, click on that cell in the other sheet to add a reference to that cell. It may look something like Sheet 1::Table 1::F10
I did that, and it works and shows that cell but when I go to the original one and add a row for a new charge then the second sheet still shows F10 and doesn’t update to what would now be the balance on F11
@@melidalatigo7130 Make sure that is shows "F10" and not "F$10" as the cell reference. Or, is it that you have 10 rows and you always want it to point to the last row? So F10 is still F10, but now you want it to point to F11?
Yes, I want it to always show the “last row” and the checkbook will go on for a long time then I want the newest balance to always be reflected
@@melidalatigo7130 You need to use INDEX to get the value of a cell references like that. Use ROWS inside that to get the number of rows in the table.
Gary - I'm sorry. but you talk so fast. I just can't grab the formula part. Have everything else up but just can't finish. Thanks😀
Not sure exactly which formula you mean, but you should be able to pause the video on that frame and see it.
Hi Gary - thanks for your response. I did slow down the video. I have the Starting balance field entered, but it's getting it working with the the main spreadsheet that I'm just not grabbing. I entered on Row 1 =Starting Balance but can't get past that for the rest of it.
@@LloydHolter So do you mean at 3:30? I press the = key, then click on the 2000 cell. Then I type -. Then I click D2. Then I type +. Then I click E2.Then I press Return. Try using the CC along with slowing down and pausing the video. I show each step.
Gary - your printed help worked!! Thank you so much.