Accounting Spreadsheet [Excel Template] Create it in 15 minutes
HTML-код
- Опубликовано: 5 фев 2025
- Create a simple Accounting Spreadsheet that is great for recording your small business bookkeeping transactions.
You probably don't like complicated formulas and you probably don't want to spend hours designing your own.
So this accounting spreadsheet template should be ideal for you.
The simple design allows for both Income and Expenditure and maintains a running balance of your business bank account.
The accounting spreadsheet can be used for weekly, monthly or even yearly accounts.
If after watching the video you would like a copy of our Free Accounting Spreadsheet, then please help us by visiting and subscribing to, one of our social media channels.
We will the send you a copy of the completed file.
Please Like on the MrSpreadsheet Facebook Page, or
Follow on Instagram @Mr.Spreadsheet, or
Follow on Twitter @Mr_MrSpreadsheet
Many thanks for watching and subscribing ...
![Income and Expenditure Spreadsheet [ Step by Step instructions ]](http://i.ytimg.com/vi/buIupSWqmmc/mqdefault.jpg)
Very helpful video. Thank you! Clear and concise
Glad it was helpful!
This video is awesome...I have absolutely NO knowledge of excel and you helped me create an amazing AR/AP spreadsheet..God Bless You
Glad it was helpful! :-)
This has got to be one of if not the best tutorials for a book keeping spreadsheet ever. Thanks a bunch
Glad it was helpful! :-)
Fantastic, really interesting and very functional
Glad you think so! :-)
Best and I mean best video ever. I have viewed so many but this is the easiest one to follow. Thank you
Glad it was helpful! :-)
Realy Great! All these days I was serching one proper accounting excel sheet. This is realy fantastic. Thanks for your work.
Glad it was helpful!
Awesome video
Glad you enjoyed it :-)
This is an amazing tutorial and just what I needed. Very easy to follow and will help me immensely.
Thanks Crystal, glad you found it useful :-)
interesting and excellent presentation
Glad you liked it!
fab thank you so much, so much better than my accounts records
Thanks :-)
Thank you very much. Interesting and helpful. Added Techniques we didn't know
Glad it was helpful! :-)
Many Thanks! I will be using this!
You're welcome!
Nice videos teaching. Thank you
Glad you liked it
wonderful!
Many thanks!
It took me a few attempts to get it right. But it works for me 😊, Thanks so much for the Spreadsheet, I can now balance my budget 😀,
OK, well done :-)
very easy to follow instructions thank you
Glad it was helpful!
Thank you for this interactive video. Very educative, "Thumbs Up"
Glad you enjoyed it!
Superb thank you! Exactly what I was looking for 👌🏼👏🏻👏🏻
Glad it helped! :-)
Thanks so much for the tutorial, I found it really useful!
Glad it was helpful!
Thank you so much for this. I have been experimenting with spreadsheets but this is excellent.
Glad it was helpful!
This is excellent. Thank you!
Glad you enjoyed it!
thank you for this great video.
Glad you liked it!
nice presentation.
Thank you! Cheers!
You are a damn genius. This works great.
Thanks Donald, appreciate your comments :-)
You made this so easy. Clear and concise.
Thank you ...
Simply Terrific! Thank you
Thank you too!
Thank you so much. You teach very well.
So nice of you
You are the best
Thanks :-)
Great Video!
Glad you enjoyed it :-)
Amazing thank you for your help...I appreciate you taking the time to give us this information.
My pleasure! :-)
Hi Mr.Spreadsheet, i hope you are well and keeping safe. thank you for sharing your knowledge. i was wondering if you could do a video with this template spreadsheet data to automatically generate an invoice in excel? pleeasee :)
That's a good idea. I will put this on the 'to do' list ...
Thank you, great education
Glad you enjoyed it!
Very useful. Thank you so much
Glad it was helpful!
Thank you for this ❤️
Any time!
I can't thank you enough, on the amazing video!!
You're very welcome!
Simply Awesome. Thank you for the informative video. I had a question. After preparing the Accounting Spreadsheet, how to use it for the next month?
Hi, Glad you found it useful. For a fresh month, either extend the tables, save a blank copy and then use this each month. :-)
excellent
Thanks!
Thank you! great job
Glad it was helpful!
This is really informative.
You make understanding accounting spreadsheet so simple.
Keep it up!
Thank you. I am pleased that you found the content helpful.
Thanks very mushc Mr.SpreadSheet Your video is very useful
@@saeed8219 Thanks Saeed 😊
I can't thank you enough for this video.
Thanks, I'm glad you found it useful 😊
super
Glad you found it useful :-)
thanks for this video its educative. please can we get a comprehensive video of two hours on the whole accounting software in excel from the start of income statement, balance sheet, cashbook, petty cashbook and so on
Hi Alidu, take a look at our 'How to keep your Accounts in Excel video'. :-)
Thank you very much! This is perfect
You're welcome! :-)
I like this
Thanks :-)
Keep it up
Thanks again
Great video, learned a lot in an hour. How can I add a second or third bank and add the commands so that it deducts and adds transfers and payments between separate banks?
Hi Jordano, take a look at our How to keep your accounts in Excel video. This has 4 Bank Accounts and uses the same methodology. :-)
I have a question. Can I use the same spreadsheet for my checking account and credit card account? Do I need a separate spreadsheet for each? Do I input the beginning balance from my checking account? Should I have an expense that reads credit card payments? I'm confused as you can probably tell from my question.
Hi, The product is designed for one account only and Yes, you would need to enter in your opening balances. :-)
it's amazing i like it..but i'm looking for advanced spreadsheets including bank recon,issue receipt and payment voucher,calculate income and generate financial statements...income statement,balance sheet,cash flow and trial balance..can you provide me....thanking your great efforts
Hi Ghaleb, take a look at our 'How to keep your Accounts in Excel' video. This does all of these things. :-)
This is wonderful, kindly share the link so that I can download the completed file
Thanks Peter, The file you have requested can be obtained by following the guide in this video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
Great tutorial for anyone starting their own business How do I get a copy if I am not on social media?
Hi,, Downloads are available at www.MrSpreadSheet.com :-)
lecture is soft tooooo
Thanks Ibrahim
Thank you so much!!
Please let me know how can l get a copy of spreedsheet?
Hi, Downloads are available at www.MrSpreadSheet.com :-)
Can I have the spreadsheet? I found your video helpful and looking forward to learning more from you! Thank You! I did follow what you want me to do at the end of the video :) Please let me know if I can do anything else to help your channel go more viral. @RUclips, i found this video very helpful.
Hi Fokrul, thanks for your comments, please message me on FB
Interesting, thanks so much. What strategy would you suggest for adding to this for allowing for monthly (period) reports that also tabulate into an annual statement?
Hi, You could watch our 'How to keep your Accounts in Excel' video. This expands on the techniques demonstrated and covers all the points you mentioned.
This video is so much help to me, but I have this problem when I am making template inside onedrive. There are some formulas I can't use like Define name. Is there any online storage app that will function all formulas? I hope you can help me. Thanks in advance.
Hi Blanco, there are lots of limitations in the cloud version of Excel. A work-around might be to simply highlight the relevant ranges, rather than giving them names. I hope this helps.
Thankyou very much, very useful , but one request can this be accomodated with Foreign Currency details along with Local Currency ?
The template is designed for a single currency, however, you could easily design a simple currency converter to wok alongside.
Thank you
You're welcome
Help! I'm a total beginner to excel. I followed your instructions and it works, but I want to add more rows to my account sheet and the new rows are not registering in G3 - G5. What do I need to do?
Hi Melanie, you will need to extend the NAMED Ranges in the SUMIF Command in G3. :-)
Good help
Happy to help
Thanks so much, I really need a copy of the complete spreadsheet
Hi Kenneth, please see the guide at the end of the presentation :-)
@@MrSpreadSheet sure, have liked and subscribed the youtube page hence my writing to you.
In the expense types column, how do you add another expense? I was able to list all the expenses but after I closed it and decided to come back and add another expense, I was unable to add to the drop down list under "ExpenseTypes". It kept saying the name "ExpenseTypes" was already being used. However, the video is awesome but I don't know how to add number 16 to the list of 15 that was given in the video.
Hi, Adding to the Expenses Table should not be a problem, just make sure that the table co-ordinates are correct. Also, you could download a copy of the original to check your work. Visit www.MrSpreadSheet.com :-)
This video has helped me a lot, thank you so much. one question for you, lets say i have duplicated multiple "account" sheets to reflect calender months. the "tables" sheet only reflect the first "account" sheet. how can i make the "tables" sheet reflect all the accounts. when i try to add multiple =SUMIF(AnalysisRange, D5,ValueRange) for each month it says that its too many arguments. any help would be greatly appreciated. keep up the good work!
Hi Marcus, why not have 12 tables and then one table to consolidate the 12.
@@MrSpreadSheet never thought about it like that, i will try it out. i thought there must've been a formula or an excel power move to assist me.
How can I connect different workbook sheets to the tables ? I would like to have each month on a different sheet but attached to the tables.
Hi Debbie, watch our 'How to Keep your accounts in Excel' video for some ideas. :-)
Hi do we add each month on separate sheets
Hi Michelle, yes, you can do this, or extend the length of the current month. :-)
Thank you Mr. Spreadsheet! Is there an Excel spreadsheet option to record all bookeeping and accounting in one spreadsheet? Also have the ability to keep monthly and annual totals?
Hi Matt, see our other accounting video, this performs all these tasks. :-)
Hello Mr spreadsheet , the part that I don't understand is the analysis part where only an expense accounts are there , how about the revenue accounts ? Also why do you credit the expense in a bank parlance yet it is a debit ?
Hi, your analysis can contain both Income and Expense types, and these can be both Dr's and Cr's. [Normally Expenses are Dr's and Income are Cr's, though it is quite possible for the reverse to occur]. I hope this helps :-)
Thank you! It is much helpful!
You're welcome!
Hi
My sales column isn't recording totals on the tables page.
Everything else is working perfectly. How can I correct this?
Hi Tom, You can check your work against the original. See the download guide video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
Hello, really appreciate this i'm using and its amazing. I am however struggling to add additional tables that work? Could you help please?
Hi Paul, You can check your work against the original. See the download guide video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
How can i record for various months, i.e., January till December ? should i create a different sheet & table for each month?
Hi, you will need to create a new sheet for each month. :-)
@MrSpreadSheet Oh means Feb = 1 excel file. March = 1 Excel file?
Sorry, I'm new to accounting because do small business, need to learn where to start, how to record, and what to record.
A bit confused about the accounting terms as well. 😮💨
Hi, would love a copy of the spreadsheet. Thank you for taking the time to create this video.
Hi Aaeon, Please follow the ‘Download’ guide in this link 😊 ruclips.net/video/m7Zf8_UWPGQ/видео.html
So nice Thank you.
Thanks Viki, glad you found it useful.
Thank you for this great tutorial! Question, can we add lines to the Accounts sheet? I am going to do monthly sheets, so let's say I have more than 12 entries, would it throw everything off if I add a lines?
Hi, Yes, just make sure that you extend the NAMED Ranges and copy the underlying formulae. :-)
Hi, For some reason after I got past column 20 my value amounts column 21 and further won't register in the Bank receipts, payments and close balance. I copied and pasted the formula down to further rows in the Value cell but it still will not calculate balances past row 20. I was successful with transactions and analysis copy and paste procedure but not with balances..... Any help would be very helpful I cant seem to find it anywhere else. Thank you
Hi Matt, try adding lines in the middle of the table, rather than the end :-)
Fantastic tutorial, Can you please send me the complete video, I have subscribed top this channel
Hi Walid, please message me on FB :-)
Thanks Brother
Any time :-)
This is interesting. Please how can I get a copy of the spreadsheet?
Hi Dizi, The file you have requested can be obtained by following the guide in this video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
I encountered a problem. Some data calculates the sum correctly on the Tables tab and some don't. Also some data entered doesn't show up at all on the Tables tab.
Also, I used the same formula all the way.
Hi Sharon, best you have a look at my finished version. Follow the guidelines at the end of the video to get a copy. :-)
Are bank receipts money coming in?
Yes, they are.
YES I ENJOYED THANKS A LOT
Glad you enjoyed it
Firstly, awesome tutorial. I did this in the small hours of the night. Everything went well, except for some reason my 'payments' value won't alter... repeated a few times and still no change. I'm guessing it's something in the formulas area, but.. (bangs head on table) ....please help me if you can do? ..All the best!
Hi Sean, Thanks for the comments, if you email me, then I can send you a copy of the file. You can then check the formula.
@@MrSpreadSheet Will do asap. Once again, thank you so much for all.
@@MrSpreadSheet Subscribed!
@@MrSpreadSheet awesome knowledge. Can you plz send me at my email. lucky1rehman@gmail.com.
Thanks
thank you so much but can you add in option for bank payment to be negative red like -76.96 to be in red and deduct from the close Balance. when i add negative number that add, that supposed to subtract
Hi Jeaner, You can use the number formatting tool to achieve this.
@@MrSpreadSheet I did that but not succeed do you have an email so I can share my works please
Hi, may I receive a copy of this spreadsheet? It is so much better than what I am doing.
Hi Barbara, The file you have requested can be obtained by following the guide in this video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
Hi, I ma using Excel for Mac Version 16.16.27 but I do not get the options as outlined for negative numbers. Can you assist please?
Ive sent the file to a windows machine running a different version and solved it
Glad you are sorted ...
See below
Simple question what's the difference in this case between Receipts and Payments?
Hi Rodrigo, Receipts would be monies in whilst payments represent monies out. :-)
its really usefull ,can i get template ?
No problems, please follow the guide at the end of the video
Hi just wanted to know how do i extend the spreadsheet to add more transactions?? Im struggling to figure out the Value section 😩
Hi, You can check your work against the original. See the download guide video ruclips.net/video/m7Zf8_UWPGQ/видео.html 😊
I am looking to do an accounting spreadsheet for an estate that involves only property and expenses. What is the best table to use? Do you have any relevant template that I can possibly customize and use? I am desperate to find one because I can't afford an accountant, but need to correctly track my expenses.
Hi, You can use this one, just change the analysis headings to suit your needs. :-)
I have a question. How can I add multiple banks?
Hi, The process would be exactly the same as for the one already created. :-0
What version of excel is required for this?
Hi, Excel 2007 and above would be fine :-)
So how can we get a copy of the spreadsheet? I'm trying to convert from Quicken.
Hi John, please see the guide at the end of the video :-)
This was perfect, and most likely applicable to my Quicken export files. Thank you!
Thank you for making look so easy. please send me the accounting spreadsheet
Hi Louise, please see the guide at the end of the video :-)
Good video for learning Excel skills. Thank you so much and I have subscribed your RUclips & FB. Can you share your file? Thx
Hi, thank you for your comments. Please message me on FaceBook for the file :-)
When I define name to bank reciept and payment then data validation to c8 it does not come... Only comes transaction type
C8 should contain a drop down box to enable you to select either Bank Receipt or Bank Bank Payment. If it does not, then you will need to check that you have completed the Data Validation routine correctly. I hope this helps.
I had this same problem, you need to put an = before the name when you enter it inside the data validation field. Hope that helps!
Enjoyed. Would like to have the file with notes
Hi Wally, No problems, just email me and I will get the file to you.
Excellent video. Thank you for your sharing. I would like you to share the file to me.
Hi, please follow the instructions at the end of the video :-)
@@MrSpreadSheet I have liked your FB & share the video to my friends
Great video! If you have more than one bank account (e.g. bank, paypal, transferwise, etc.), would you do a separate spreadsheet for each?
Hi Kate, You could set up multiple Bank's, you would need a new Balances Grid for each one and you would need to expand the Data Validation drop box such that it includes new Receipts and Payments for each new Bank. :-)
Mr.SpreadSheet thanks so much! Truly, this video just brought me out of weeks of pain trying to pull all the info together. It’s great!
@@MrSpreadSheet Hi, I tried to add a new balances grid and add the new receipts and payments as 'transactiontypes' but it doesn't work?
The sumif in the tables isnt owkring for me i keep getting a #name error even though i went over the comman multiple times this isnt workihng.
Hi Fable, why not download the final template, then you can compare your version with the original 😊 ruclips.net/video/m7Zf8_UWPGQ/видео.html
Great and very informative Please can we have a copy, thank you
Hi Soebhaas, please send an email to the address on the About link ...
@@MrSpreadSheet 163mangar@gmail.com
@@MrSpreadSheet 163mangar@gmail.com
When I go to set up data validation in analysis column all it comes up with is ExpenseTypes not the actual list of types - sales a ect
Hi Chris, have you selected the wrong range ?
@@MrSpreadSheet don't think so have double checked even won't do it on transaction one now maybe it's just my spread sheet
@@MrSpreadSheet problem solved I wasn't putting the = in when did the data validation