How to create an INVOICE in Excel - Spreadsheet Template for 2021

[@tun-tunninc.6492]

Oh this incredible!! Id like to use this personally. Thank u.

[@MrSpreadSheet]

Thanks, glad you found it useful :-)

[@sydneewright6244]

I love the way you set it up! It is fun to watch AND interactive! You got a like from me :)

[@MrSpreadSheet]

Thanks, glad you liked it 😊

[@pabloandres4916]

excellent tutorial!!! thanks

[@MrSpreadSheet]

Glad it was helpful!

[@randulaguruge2801]

Thank you so much..

[@MrSpreadSheet]

You're most welcome

[@ahmedelrowey1570]

Simply amazing.

[@MrSpreadSheet]

Thank you! Cheers! :-)

[@mggabar5127]

Thank you for this great job. Well done!

[@MrSpreadSheet]

Glad you enjoyed it!

[@martinwatson9437]

Excellent tutorial, but noticed Contact and Terms should be switched, N40 for L40 when adding the tax. Really impressed with the way in which you present this in a simple to follow format. Thanks !

[@MrSpreadSheet]

Thanks for for comments Martin :-)

[@matthewzupke5298]

Very nicely done! Is there a way to use the xlookup function for Open Office?

[@MrSpreadSheet]

Thanks you, I'm glad you found it useful. XLOOKUP is not available in OO that I am aware of. You could always arrange the tables such that you can use VLOOKUP instead :-)

[@mikhaelajoisesanglay7634]

May I ask if what is the "2,0)" in the formula bar for the price in 16:29?

[@MrSpreadSheet]

Hi Mikhaela, The 2 is the second column in the Products Table, the zero is the same as the word False [exact match required] :-)

[@amadougitteh2058]

another cool video, keep it up Mr Spreadsheet

[@MrSpreadSheet]

Appreciate it!

[@royplumber]

can I save this into my yearly Exel work books

[@MrSpreadSheet]

Hi Roy, Anything is possible in Excel, but this would involve a lot of VBA programming. :-)

[@bartoszplewa3788]

Świetny przykład i super uproszczona forma

[@MrSpreadSheet]

Thanks :-)

[@asadreet7731]

Thanks

[@MrSpreadSheet]

Welcome

[@dannehansen]

"Terms" is "Harry Both" and "Our Contact" is "45 Days". Else it is a very nice invoice.

[@MrSpreadSheet]

Great, glad you liked it. :-)

[@bryanjosephferry7617]

Hi i follow all the instruction and I made it all correct but I want to know how I will change those information that you give example, I want to know how to change all the dATA. please let me know.. thanks Im new here

[@MrSpreadSheet]

Hi Bryan, well done, just repeat the various processes using your own data. :-)

[@asimsiddiq475]

Respected Mr.SpreadSheet, you're really a very intelligent instructor, Thanks a lot.

[@MrSpreadSheet]

You are very welcome

[@HCSNI]

Loved following this speadsheet - if I am not too late can I get a copy to 'play' with please?

[@MrSpreadSheet]

Hi HCSNI, glad you liked it. Please follow the guide at the end of the video :-)

[@HCSNI]

@@MrSpreadSheet Hello, I did! I liked and followed your Facebook page and left my email address ( Sam.S******gmail**m) - I also liked and subscribed to this channel - what else do i need to do?

[@debbieknight4594]

@@HCSNI I did as well, have not received.

[@lakshmananlakshmanan586]

Thanks very helpful, Question : how to add a second page to invoice and bring all the logo, Invoice# etc to second page.

[@MrSpreadSheet]

Hi lakshmanan, this would entail a lot of programming and goes way beyond the preparation of a simple invoice. :-)

[@lakshmananlakshmanan586]

@@MrSpreadSheet thank you appreciate your help and reply

[@kimberlyheu9483]

Your tutorial is wonderful. The version of excel I am using does not have XLOOKUP. I saw in a reply you advised using VLOOKUP. Could you possibly give an example of how to use VLOOKUP in place of XLOOKUP? I tried changing the X to a V but that did not work. Thank you!

[@MrSpreadSheet]

Hi Kimberly, you would need to combine the various tables to use VLOOKUP. unfortunately this is not an easy task given the existing parameters. Perhaps you should migrate to a more current version of Excel.

[@ellaaldovino5098]

Hi! I dont have the xlookup function in excel 2016. What formula can i use?

[@MrSpreadSheet]

Hi Ella, Use VLOOKUP :-)

[@ellaaldovino5098]

Whats the formula going to be to fill out the customer info? Thanks!

[@mykolaskaliacius7355]

Subscribed! First video that I have watched from you and it is just great!!! Can I please please have a template for this? :)

[@MrSpreadSheet]

Hi Mykolas, Glad you enjoyed it. Please see the guide at the end of the video :-)

[@babamatwinz1642]

I'm stuck... Do we need 2 Tables sheets? Or did you write over the CustomerName table coz Im getting an error when trying to put in th xlookup c8 customerName formular

[@MrSpreadSheet]

Hi Baba, why not check your errors against the original, see the instructions at the end of the video to get a copy :-)

[@ck61118]

Great tutorial! I was setting up a template for my business and I need to figure out how to set this up so that sales tax is not charged on each item. I do not need the discount columns so I left that out, and my thought was to in column M to Choose to tax(T) or not to tax(0). The formula that I tried was =IF(M17="T",+L17*N40,O40). This formula gives me the correct amount for that line, but I can't pull down that formula for the other lines because I need the true and false part of the formula to remain constant. I am not sure how to get the +L_ would change, but the *N40,040 needs to remain those same letters and numbers. Is there a way to do this? I appreciate your help, figuring this out. I have subscribed to your channel and will be watching more of your content. If there is a different video that would explain this senerio if you could point me in that direction.

[@MrSpreadSheet]

Hi Cindy, without going into depth, have you tried an IF Statement nested in the formula.

[@kuhunyawilliam9877]

thanks for the videos. would like a template of these

[@MrSpreadSheet]

Hi Kuhunya, please follow the guide at the end of the viseo :-)

[@badboykitty2]

The formula “=XLOOKUP(C6,CustomerName,Add_1) does not work even though I have made no mistakes. I keep getting #VALUE” error. Any ideas why this is not working?

[@MrSpreadSheet]

Hi, You can check your work against the original. A download is available at www.MrSpreadSheet.com :-)

[@sardcanada2640]

How do I add a credit to the invoice? the program just adds every amount when I want to subtract one line from the total as a credit?

[@MrSpreadSheet]

Hi, Have you tried entering your values as negatives?

[@cosmiccharms]

Why am I getting "Value!" alerts on the customer inputs? I am not very computer savvy. Can you help?

[@MrSpreadSheet]

Hi, You can check your work against the original. See the download guide video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@jennyharris3533]

I’m loving your video, and love the layout of this invoice! I’m not very computer savvy, so I’m trying to figure this out, but at 8 minutes, 15 seconds, when you switch from invoice to tables tab, how do you get those tables in order to fill ranges? I only have the information on my tables page from where we left off at the 4 minutes 33 second mark on my tables page. I feel like I might have missed a previous video?

[@MrSpreadSheet]

Hi Jenny, I have pre-populated the Tables worksheet with the data shown at 8:15. So, just expand on the entries where I left off at 4:33. You have not missed anything. :-)

[@opeyemiadeyemi9816]

O

[@opeyemiadeyemi9816]

O

[@seanahunter3610]

I have a hp chromebook. I use the online version of excel and when i try to do the formula for vlookup it wont work. I have tried everything. I would like my client names be in the dropdown box.

[@MrSpreadSheet]

Hi Seana, Unfortunately the online version does not support many of Excels formulae and functions :-(

[@c9seng241]

Very useful. But how can I get the template?

[@MrSpreadSheet]

Please refer to the guide at the end of the video :-)

[@debbieknight4594]

@@MrSpreadSheet I followed your guide at the end of the video - I followed you on Instagram, left a message with my contact information and signed up for your newsletter. The only way I see of obtaining the template is to purchase it for $49.99 from the website. Please advise.

[@bawatechreview2168]

Any automated invoice please?

[@MrSpreadSheet]

Hi Bawa, not sure what you mean ?

[@ianwatts6246]

Why would you need a fix value in o 40 Im sure it would just be 20% in the UK sorry if that sounds rude!

[@MrSpreadSheet]

Hi Ian, That's fine for the UK, but other countries have different rates and different ways of calculating Sales Tax / VAT. This template allows you to choose your methodology :-)

[@trex6230]

Hello Mr spreadsheet, what version of excel are you using?

[@MrSpreadSheet]

Hi, Excel 365 :-)

[@TheCrossed23]

Thank you for this. Could somebody answer a question? Our invoice is very similar but we have a master tab that has an extra 20 columns. One for each product, each cell in the customers row will show the product name if they have purchased it or be blank if they haven't. Is there a way to auto fill what they have purchased, on the invoice, from this master tab? I have managed to do it but not without leaving lots of gaps in the invoice from when an item hasn't been purchased. Im trying to find a way to automate the products purchased as the master tab is huge and isnt something we can edit so flicking back and forth to find what product each customer has bought can be very difficult. With this I would be able to choose just the customers name and the entire invoice would auto fill including what they have purchased. Any advice or examples of auto filling products without leaving gaps would help so much. I know this may not be possible as our master tab is very specific and large.

[@MrSpreadSheet]

Hi Crossed, sounds like a job for the VLOOKUP command coupled with an IFERROR statement to ignore the blanks.

[@abdulrahimsheriff4405]

I am following up your instruction on this topic but i did not understand which key is the today command key to click to please help me

[@MrSpreadSheet]

Hi Abdul, the Excel command is TODAY() and the quick key is Control + ;

[@ppam4461]

Hi MrSpreadSheet, I keep getting an error when I went into Name Manager and hight customer Name, when I added $B$8. What is the $8 represent?

[@MrSpreadSheet]

Hi P Pam, please message me on FB :-)

[@chipmunk1088]

Good morning how do you link an invoice to your income and expenses sheet in excel please

[@MrSpreadSheet]

Hi Derek, we have a 'Paid For' product to do this, please see our website for ndetails. :-)

[@kimly1766]

Thank you for such a great tutorial! However on the very last step after VIEW>PAGEBREAKVIEW>NORMAL - when I got to print or save as pdf - it has the 15% tax workout on the second page. How do I get rid of that ?

[@MrSpreadSheet]

Hi Kim, sounds like your print co-ordinates are misplaced. You need to make sure that the Tax Boxes are NOT included in the printing grid. :-)

[@pierresobers8634]

I would like this invoice

[@MrSpreadSheet]

Hi Pierre, please see your FB messages :-)

[@TheWellnessHubNYC]

Following your video but keep getting #NAME? when it's time to input address...

[@MrSpreadSheet]

You can check your work against the original. See the download guide video kzbin.info/www/bejne/o2i9l2uViryDfbM 😊

[@SayaliSashte]

hello sir i have a error in term cell of invoice what is the reason #N/A

[@MrSpreadSheet]

Hi, You can download a copy of the completed template and then check your work against this. Go to www.MrSpreadSheet.com :-)

[@umakargoranthala7312]

If 1 item having 2 different prices, how will u do...?

[@MrSpreadSheet]

Hi Umakar, that would depend of your selection criteria, but I would probably use an IF statement.

[@ehanusman9462]

How to use multiple VLOOK multiple time in a cell, Like City,provine, postal code

[@MrSpreadSheet]

Hi Ehan, use a fresh VLOOKUP for each component. :-)

[@Adewaleoluyede]

i would like the template

[@MrSpreadSheet]

Please see my message on FB

[@loriejaramillo5627]

I'm not sure if you realize but your terms and contact are mixed up

[@MrSpreadSheet]

Thanks Lorie :-)

[@SA-sb7rt]

👍🏻👍🏻👍🏻

[@MrSpreadSheet]

?

[@SayaliSashte]

please need your help

[@MrSpreadSheet]

Hi, please see my reply to your other query :-)

[@AbdullahKazimyar]

send me a copy of invoice thank

[@MrSpreadSheet]

Hi Abdullah, please see the guide at the end of the video :-)

[@AbdiShibis]

wouldn't be nicer to combine the formula like this instead of cell reference =ROUND(IF($B16="",0,(VLOOKUP($B16,ProductTable,2,0)*(1-(XLOOKUP($C$8,Customer,Discount))))),2)

[@MrSpreadSheet]

Hi Abdi, Yes, there are many ways to achieve results in Excel. I think it is a matter of what you understand and what you are comfortable with :-)

[@AbdiShibis]

@@MrSpreadSheet Absolutely 💯% you are right... I am confident I will learn a lot from you and I am glad I am taking advantage 🙏 from your knowledge, Time you spent creating those videos, and hardworking 🙏 Thanks in a Million.

[@Steve-oh9tu]

Complicated

[@MrSpreadSheet]

Hi Steve, sorry to hear that...

[@gildaferreira7977]

My first time using excel , I’m trying to great am invoice for a client and I’m getting error VALUE while using XLOOKUP. Formular What can I do

[@MrSpreadSheet]

Hi Gilda, try examining your formula using the 'Insert Function' tool next to the formula bar. This should help you to isolate the incorrect component in your formula :-)