Video není dostupné.
Omlouváme se.
Accounting Spreadsheet [Excel Template] Create it in 15 minutes
Vložit
- čas přidán 18. 07. 2019
- 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 ...
![CASH FLOW SPREADSHEET - Create it in 7 minutes [Excel Template]](http://i.ytimg.com/vi/vFUp6ean2j8/mqdefault.jpg)
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! :-)
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!
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 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 :-)
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! :-)
You made this so easy. Clear and concise.
Thank you ...
Amazing thank you for your help...I appreciate you taking the time to give us this information.
My pleasure! :-)
Thank you so much for this. I have been experimenting with spreadsheets but this is excellent.
Glad it was helpful!
I can't thank you enough, on the amazing video!!
You're very welcome!
Thank you for this interactive video. Very educative, "Thumbs Up"
Glad you enjoyed it!
Very helpful video. Thank you! Clear and concise
Glad it was helpful!
I can't thank you enough for this video.
Thanks, I'm glad you found it useful 😊
Thank you very much. Interesting and helpful. Added Techniques we didn't know
Glad it was helpful! :-)
Superb thank you! Exactly what I was looking for 👌🏼👏🏻👏🏻
Glad it helped! :-)
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 😊
Thank you! It is much helpful!
You're welcome!
Thanks so much for the tutorial, I found it really useful!
Glad it was helpful!
Simply Terrific! Thank you
Thank you too!
YES I ENJOYED THANKS A LOT
Glad you enjoyed it
Thank you very much! This is perfect
You're welcome! :-)
Thank you so much. You teach very well.
So nice of you
fab thank you so much, so much better than my accounts records
Thanks :-)
Many Thanks! I will be using this!
You're welcome!
You are a damn genius. This works great.
Thanks Donald, appreciate your comments :-)
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 :-)
Thank you! great job
Glad it was helpful!
very easy to follow instructions thank you
Glad it was helpful!
Thank you, great education
Glad you enjoyed it!
thank you for this great video.
Glad you liked it!
Very useful. Thank you so much
Glad it was helpful!
Thank you for this ❤️
Any time!
Nice videos teaching. Thank you
Glad you liked it
So nice Thank you.
Thanks Viki, glad you found it useful.
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 ...
interesting and excellent presentation
Glad you liked it!
Thank you!
Pleased that you liked it
Great Video!
Glad you enjoyed it :-)
You are the best
Thanks :-)
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'. :-)
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.
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. :-)
Keep it up
Thanks again
Awesome video
Glad you enjoyed it :-)
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. :-)
nice presentation.
Thank you! Cheers!
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. :-)
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. :-)
I like this
Thanks :-)
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. :-)
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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Thanks Brother
Any time :-)
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. @CZcams, i found this video very helpful.
Hi Fokrul, thanks for your comments, please message me on FB
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 😊 czcams.com/video/m7Zf8_UWPGQ/video.html
Thank you
You're welcome
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.
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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
Good help
Happy to help
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 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
excellent
Thanks!
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. :-)
Thanks
Welcome
lecture is soft tooooo
Thanks Ibrahim
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.
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?
super
Glad you found it useful :-)
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
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. :-)
Fantastic tutorial, Can you please send me the complete video, I have subscribed top this channel
Hi Walid, please message me on FB :-)
Good video for learning Excel skills. Thank you so much and I have subscribed your CZcams & FB. Can you share your file? Thx
Hi, thank you for your comments. Please message me on FaceBook for the file :-)
Hi, Thanks for this KISS approach of accounting. How could I get the template? Do you have any cost accounting template? Thks.
Hi, We are releasing a cost accounting template in the next 6-9 months. Just contact one of our channels with your email address and we will forward a copy.
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.
Can I use this data to automatic generate a P/L ? also , your samples has date , what if many transaction in one day ?
Hi Shu, Yes, you could extend the file and create a P/L [and Balance Sheet]. Probably require a lot of work to complete it. There should be no issues with the dates as the range is defined.
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 :-)
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 :-)
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 so much!!
Please let me know how can l get a copy of spreedsheet?
Hi, Downloads are available at www.MrSpreadSheet.com :-)
Enjoyed. Would like to have the file with notes
Hi Wally, No problems, just email me and I will get the file to you.
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. :-)
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!
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
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 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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
its really usefull ,can i get template ?
No problems, please follow the guide at the end of the video
Are bank receipts money coming in?
Yes, they are.
Is there any version to apply double entry book keeping?
Hi Lency, although data is entered on one line, this does in fact use double entry. It's Dr or Cr the Transaction Type i.e. Bank and then Dr or Cr the Analysis. I hope this helps ...
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
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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
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
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 do we add each month on separate sheets
Hi Michelle, yes, you can do this, or extend the length of the current month. :-)
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. :-)
So we can work out total profit made
Hi Zak, please see the earlier reply
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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
How can you add purchase rate and sale rate?
Hi Zak, just add extra columns and then follow the routines demonstrated :-)
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. :-)
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 czcams.com/video/m7Zf8_UWPGQ/video.html 😊
When I copied this off the video my Receipts and Payments values were not updated as I entered mock figures
Hi David, have you checked the formula, this is often not entered exactly as shown in the formula bar.
Hi. I type the IF formula exactly like you - then I get a popup note where Excel tells me it doesn't recognised it as a formula 😶
Hi Sandra, sometimes a misplaced comma is the culprit. If you examine your formula using the function analysis tool, this should help you to isolate the problem :-)