How to Create an Automated Invoice in Excel | Including Formulas and Customer Database
Вставка
- Опубліковано 10 лип 2024
- Download the featured invoice template here
www.bluepecantraining.com/exc...
Full playlist for this video series: • Create an Automated In...
Check out Simple Sheets Invoice Template (all purchases help support this channel)
www.simplesheets.co/a/2147525...
------------------------------------------------------------
Blue Pecan Computer Training Ltd offers on-site or online Excel training for businesses. Training can be delivered at your business premises in the south-east of England or via Teams wherever you are in the world. Visit our training page at www.bluepecan.co.uk
This tutorial is part of a series of tutorials that demonstrate how to create an automated invoice system in Microsoft Excel. This video looks at how to create the invoice itself including formatting, formula and a customer database.
***FIX: There is a school-boy error in the VAT calculation in this video, where the quantity is not included -apologies. I have corrected this in the download.***
Other videos in this series show you how to write VBA macros that
1) Automatically generate the next invoice number
2) Add the invoice details to a record of invoices
3) Save the invoice as an Excel workbook or as a PDF
4) Automatically email a PDF version of the invoice to the customer
Table of Contents:
00:00 - Introduction
01:32 - Starting the invoice
01:40 - Margins and page size
02:42 - Invoice headings
04:30 - Invoice number, date etc
05:48 - Company logo area
06:19 - Customer address area
07:07 - Invoice billed items area
08:51 - Customer message area
09:30 - Subtotals and totals area
10:21 - Payment details and company details area
12:11 - Drop-down list for invoice terms
12:41 - Invoice date
12:53 - Customer address and customer database, including customer drop-down list
18:38 - Billed items area - formulas
20:42 - Subtotals and total formulas
22:08 - Inserting a company logo
22:36 - Exporting the invoice as a pdf
------------------------ - Навчання та стиль
I want to leave a really huge thank you on this video. I know what it’s like to make videos for a small audience and get not very many views. I just wanted to say after getting wrongfully terminated from my job and not having enough money for a lawyer I was looking for a new job. I finally found one a month later and managed to impress my new employer by automating their invoice system to spreadsheets with this series and save them thousands on paper and ink they would typically have to mail out each day. Thank you so much for helping me and so many others.
Have you donated to the fund raiser as a gesture of your appreciation? Its for a good cause :)
I searched the internet high and low for the perfect way to set up invoices and receivables all while maintaining a customer database- and I am so glad that I stumbled upon this video! Fantastic explanation and narrating. Thank you for posting such a miraculous invention! I've been using this file for my small business, with some adaptations, and it works WONDERFULLY.
Finished following all six videos. Going to go through all again to polish off.. Absolutely brilliant. Thank you Chester.
I'm a writer and really only know how to work in Word .... this is the first time I've ever felt I understood excel. What an absolutely marvellous tutorial, many thanks!
This is really awesome. Not only have you put a lot of work into this, but its downloadable. You have saved me time and money and I thank you for that
THANK YOU< Chester! Super helpful. I spent the entire day working on an invoice with customer list and item list dropdowns. This was unfamiliar territory and I could not have accomplished this without this most excellent tutorial. Thank you!
Thank you for this video! I will watch more of your other videos to refresh my skills and knowledge in Excel. I learned all these in College but have forgotten most of them especially the macros, formulas, etc. Super thankful I found this video because this is exactly what I need for my business now. Who knew right? That almost 20 years down the line we'd find ourselves still using Excel for data entry, record keeping, etc. I thought before, in 20 years I'm sure what I'm learning now would be useless because technology would be so advanced by that time but...I find myself here now watching this video. 😄
You've gained a new subscriber! I am new to Excel and am intrigued and overwhelmed with all the possibilities and project ideas. Your calm demeanor and clear instructions make this project easy to understand and follow. Thank you!
Great series sir! I almost purchased an invoicing system for my business, but my girlfriend insisted I do some research and create my own. I found this series and all she's been saying is "I told you so!" LOL. A few tweaks and scripts to get it running on my Mac, but I now have a perfect invoicing system for my business! THANK YOU SO MUCH!
Hi Carl, would you mind to share your tweaks to get this to work on MAC OS? Thanks!
Mr. Chester, with your help I now have a fully built invoicing system from automated invoices to generation of account statements.
Thank you, you are a great teacher!❤
This tutorial video was one of the best. Explaining well, and clearly, all details are said nothing is ignored. Tune of voice and speed etc. Why not everyone around the world is not following this guy? well-educated person
This is probably one of the best tutorials in the world🙌🏾 the guidance in these series is absolutely phenomenal and easy to follow along. Thank you very much for gifting us with this wonderful automated invoicing system ❤ it definitely will make it easy for my business!!
Glad it was helpful!
This video is amazing, very clear and easy to follow. I am starting my new small business and can't afford much to start with. Thank you to your series of videos Chester, I was able to create an invoice to get myself going. I am very thankful I have come across your videos and you certainly have been a life saver. Not only that, you have educated me on how to use excel and the many uses it has. I am sure you have only scratched the surface on what excel can really do. I don't post comments very often but truly thank you for your videos and I also look forward to learning more from you. Truly amazing!!!!
Thanks Matt - I appreciate your comment
Hi Chester, You have made auto invoicing a very, "less stressful" experience. Thank you so much!😃
Thank you so much for taking your time to share this and educate us. I almost bought an invoicing system but my partner told me to Google this for my business and it's exactly what I was looking for. You've saved me monthly subscriptions . Many thanks to you.
I have just used this tutorial and found it excellent. Just the right speed to work along and an excellent level of detail.
This is EXACTLY what I needed for my business. Thank you so much!
This is awesome! My dad has been using a TYPEWRITER to send out invoices! I will be taking over the company next year and with this I can a move us into the 21st century.
The contribution is very detailed and instructive. Great praise for your patience and willingness to share your knowledge with others. I am grateful that there is someone willing to do this. You enabled others to speed up their work and give them a great incentive to achieve their goals
This was superb! Apart from a very basic knowledge I am not at all adept at Excel. However, thanks to your extremely simple and clear instructions, creating my Invoice was a piece of cake. Thank you so much! Now I'm tempted to move on to try and get the macros... biting off more than I can chew maybe?... ah well, nothing ventured nothing gained! LOL!!
Simply wonderful and clear teaching. This video is a game changer for me when it comes to using excel. Looking forward to viewing more of your videos
THANK YOU SO MUCH! This is *exactly* what I need! I cannot tell you how many zillions of hours and $$ you have saved me. I can now ditch the $600/year quickbooks subscription and build my own. Your video descriptions are incredibly clear and educational. I was able to expand your system to include time tracking and create more buttons to generate the invoice from a log of services rendered for each client. I really cannot thank you enough for this wonderful resource and excellently delivered content.
That's great to hear. 👍
I'm only 3 mins in the video and I'm already thanking you! Thank you!
This is so useful! I would definitely need this skill in my next job. It took me a while to grasp, but I am satisfied with my overall result. I have been trying out things whilst you were explaining. You are a star!
Glad it was helpful!
You, Sir, are a star. You have really been a great help. In two videos, I have found the answers to all I needed for automating my invoice.
This is one of the best presentations on how to use Microsoft excel I have seen , I have known for a long time that Exel can do many things but didn't know where to start. Chester explains things brilliantly , I got the invoice and macros working because thanx to Chester I understood what I was doing. Thank you very much for your professional contribution.
Nicholas - thanks for your generous comment.
Huge Thankyou Chester! After 20 yrs of QBooks and a few upgrades Im forced to their subscription service with my appx 100 invoices a year. Went through all 6 videos and was able to modify to get a solution that works for me. As a non Excel user, I was able to build your basic KEEL to get the process started. Now Im back up and running a simple invoicing database. Thankyou for not wasting my time, sticking to the point and really helping!!!! Goodbye QB
Welcome
I am so excited to have found this series. Legend.
Top tier stuff mate! Srsly saved me massive headaches, cheers!
Great demonstration! Thanks Chester!!
Excellent video, many thanks. Fantastic and very easy to follow.
That’s amazing very good and clear to follow thank you,
Hi Chester, thank you for this series of videos. I was looking for this for a couple of weeks, and I found this tutorial of yours today. It was clear and informative. Keep doing more! God Bless!
Glad it was helpful!
THE BEST TUTORIAL EVER!!! Thank you so much for taking your time to do this.
You're very welcome!
i liked this video. It really helped me understand how I can make my tasks more efficient. Thank you for taking the time.
Very informative and useful. Thanks Chester
Excel, Word and Corel Draw are such excellent software!
Simply brilliant!
Hi,
I do not generally add comments online, but this tutorial merit my comments.
My wife and I want to start a rental business, we needed an invoice system, did not know how to do that. Your tutoral has helped significantly. than you. will watch the other videos .
You have made this boring job fun and easy thank you for this fantastic tutorial :)
Absolutely fantastic tutorial !!!
You have done magic there, thank you so much. 😊
Thank you! Just subscribed you because of this helpful video, hope you keep guide us in excel 😊
Thank you Sir.. Well explained, and Great work
Great tutorial! I can’t wait to follow along the rest of the series as I look to replace FileMaker Pro for my invoicing
Have fun!
Thank you for your sharing. That's so easy to learn and great invoice.👍☺
Extremely informative. The quite useful technique for designing Invoice Template
Very good job, great way to explain.
Learned a lot you are an awesome person and teacher
concise and beautiful.....! good job mate
Thank very much for these content 🙏🏽
Extremely easy to understand video.. Thanks for sharing it with us..
This was very helpful! Thank you. I wondered on how to do this. Your video made it so much easier for me to do our business invoices. 😀
Glad it was helpful!
Hi Chester. Question - in the description area, is there a way to merge the invoicing information in from another tab in the workbook? I have a "recording sheet" where I list orders as they come in, so I may have the same customer listed 30 times between other customers. Is there a way to pull in the billing details for just that customer in the description table?
Hi Chester, I just wanted to say a big thank you. You helped me a lot today, not on this subject, but on creating a numbered style list. Your explanation is wonderfully clear. Much appreciated. Angela
My pleasure, especially as you seem to be a cat lover 🐈⬛
@@ChesterTugwell I am a cat lover indeed. Dogs too.. and the odd monkey lol
Espectacular!!! thank you!!
Enjoyed your video thank you
Thank you for these amazing videos! Quick question, what macro do I need to insert in order for me to input a price and have it automatically multiply it by a percentage (all in the same cell)?
it is very simple to understand and follow the guidance. thank
chester i feel so smart thanks to you i could make it from scratch omg thank youuuu I love you you are an angel..
Glad I could help!
Outstanding man!
Can you combine the invoice system with inventory? This way as the parts are listed on the invoice the quantities are taken out of inventory stock. Thank you
This really transformed my understanding of Excel! You are a great teacher!
Many thanks
great content. Thanks for your work
Thank you so much for this❤
thank you so much really helpful
This is a great work mat
Thank you very MUCH!!!
Thank you so much!
Thanks so much, this is great!
Thank You!!
Thank you!!! Excellent.
Hello there! Thank you for the super helpful video! Can I check if I am able to edit the individual invoices once it is kept in the record? Thank u!
Thanks man👍🏽
que barbaridad de tutorial!! gracias!!
Very NICE! good job!
One question, i need to store data base in another excel file example "invoices book"
thank you so must it was helpful
Thanks!
Thank you!
Hi Chester, your videos are just what I needed. However, I have a question. In my scenario I need to create an invoice based on a value which is already calculated in the master spreadsheet which also contains the company name, address, email etc. can I use the same formula for the address to pull that through from the master spreadsheet?
Thank you Sharon
Hello thank you from that video I would really need to use that., I tried to download the file however I have a MacBook Air and it did not copy the formulas
;( is there a way you can do that template for excel for MacBooks. Thank you!!!
Thank you so much, i love your video. It make me to easily know how to set the VBA. Just have a question, if i want to send the PDF using gmail instead of outlook. May i know how to do the setting.
Great tutorial, so clear and easy to follow your instructions... however, I'm getting an error with the data drop down. Doing it exactly as you are showing, but I think there is a cell formatting issue on mine that needs to change? For example the 30,60,90 it gives me "30,60,90" as the only drop down option, its not breaking it up into 3 options and with my customer drop down its still not giving me the newly added customers even after creating the name dropdown which I did like yours. Any advice?
I followed this tutorial step by step until I reached the formula tab to change the name on column J but my version on excel doesn't have a "Name Manager". It only has "Define Name" and Create from Selection". So that is far as I have gotten. Any suggestions as to why there is no option for "Name Manager on my version of Excel. Thanks.
Question. So lets say you have three sheets, one is your invoice, one is customers and one is vehicles. At the top of the invoice I have the customers like you have it listed and I have another section for their vehicles. But I only want to see which vehicles the customer owns in the dropdown, not all the vehicles in the list. How would I go about doing this?
Those who may be having trouble using the F3 key on Mac..you can simply type the name you saved... just place an equal sign first.
Thank you very much
Thanks a lot
Thanks alot!
just one issue when I save it as pdf with marcos it says "cannot open the specified file"
could you please help ?
how can i print the encoded details only without the background if i have an actual physical invoice on the printer. thank you
Do I need to do the invoice tracking part to make the save as a Pdf part work??
Thank u so much
question, with handwritten invoice, we usually have both original and copy of the invoice which the copy will stay with the seller but in computed invoice like one made excel should I print out two of them and save one as a record?
Thank you for producing this series, it's been very insightful. Is it possible to create a drop down list inside of the customer database table that you are using to create the customer drop-down list?
There is, at the 14:95 mark
Hello! I am trying to create this invoice template for my mom's small business but I'm doing it on google sheets not excel. It's going well so far, however I am currently stuck at 13:54. Do you have any helpful advice on how I can complete this part?
Hi Chester thank you so much for this awesome content, I am trying to follow along and create my own automated invoice system...but of course, having issues lol, I am using Excel 2019and I am on the first video and I can seem to make the reference customer list to work...also, when you create the customer table, under the email, can I add multiple emails? if so, how do I do that? I mean, if I use a comma between all the necessary emails, will Excel understand it? Thanks in advance
Is there a way of having a drop-down list that follows the vlookup drop down list. So when a select a different client i get a drop-down list specific to them ?
Thank you for the lesson. am having troubles with the =Vlookout formula. i do the same thing you do and it doesnt pull address down for my customers.
Chester, thank you very much for this series on creating an Invoice, it has been so so helpful, I have followed all the videos and am now using this for my small business, you are a star.
Just one question, is there a way that when i select an item from my popdown box in description, the price can be populated at the same time.
Thank you again
This is good work. I just had a challenge with the camera