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

In this short video I will show you how to create an Invoice using Excel.

The spreadsheet template will use Excel's features and functions available in 2021.

The final Invoice Template is very easy to use and can be expanded and adapted to suit your business needs.

As usual on the Mr.SpreadSheet channel I will show you how to obtain a copy of the final template towards the end of the tutorial.

The Invoice spreadsheet uses the following Excel commands and formulae:

Importing and re-sizing your company logo
NAME various ranges
Using the new XLOOKUP and the popular VLOOKUP commands
Use CONDITIONAL FORMATING
Create simple IF Statements
Use Excel's ROUND and TODAY functions
Create drop down boxes using the DATA VALIDATION tool
Prepare the final product for PDF's and Printing

We do hope that you enjoy watching and that there is lots of content that will be both useful and informative.

Here are the links to the various Mr.SpreadSheet resources available to you.
Please help support our channel by subscribing.
Many thanks

You can also visit our websites at:

Or our Social channels:

#MrSpreadSheet #Excel #spreadsheet #exceltutorial #msexcel #Accounting

[bartoszplewa]

Świetny przykład i super uproszczona forma

[mggabar]

Thank you for this great job. Well done!

[amadougitteh]

another cool video, keep it up Mr Spreadsheet

[sydneewright]

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

[martinwatson]

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 !

[tun-tunninc.]

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

[kimberlyheu]

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!

[jennyharris]

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?

[dannehansen]

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

[kimly]

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 ?

[TheCrossed]

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.

[bryanjosephferry]

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

[asimsiddiq]

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

[mikhaelajoisesanglay]

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

[babamatwinz]

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

[kuhunyawilliam]

thanks for the videos. would like a template of these

[lakshmananlakshmanan]

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

[ppam]

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?

[matthewzupke]

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

[ellaaldovino]

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