How To Create An Invoice In Excel [Free Invoice Template Download]
Hey everyone it’s Brian again and in this video I’m going to show you how to create and customize an invoice template using Microsoft excel…
And obviously once you have a template you can save and reuse as many times as you’d like for your own business.
So at the end of this video I’m going to give you a link to download 2 free templates that I’m going to show you in this video so you won’t actually have to do much work to create your own, you can just customize one of these 2 templates that I’m going to go over with you today and make changes as you see fit.
The download link will also give you access to the hosted payment link TOOL called SIMPLYPAY.ME that I’ll show you how to use in the video too because there’s a special payment link function in these templates…
That link function will give your customer the ability to just click on your invoice once you’ve saved it to PDF or they can click on a link in your email copy and pay your invoice through a secure hosted payment page, so again stay tuned to the end to get that access.
Again, I’ll show you how to set it all up.
So here we go!
I have two templates to go over with you and the functionality is similar, but they are a slightly different layout and format so you can choose the one that you like best.
So here’s template one.
As you’ll see this is a template that has more than just basic functionality. There are pre-coded form fields that make it easy for you to fill out text fields and have excel to all the hard work cell references and calculations!
This is how it will come to you when you download the template for free.
So you can start with the basics of changing out your logo, by just right clicking and selecting change picture.
Find the image logo on your desktop and upload it to the document.
You can also just come to the INSERT tab and insert a new image and resize it accordingly.
The Invoice number is listed here and this header is fixed so that when you scroll down the top stays fixed and so does this button which I’ll come back to in just a minute.
The total of the invoice in red, auto populates so you can leave it as is.
You can type in the Date and Due date here. They’re standard date fields
Come over to the select your customers cell and the first line is a dropdown box and these are referenced from page 2.
So if I come back up to this purple button, and click it takes me to this page which is a simple list of customers. You can also get there by clicking down here at the bottom under the excel sheets tabs.
The customer information is self-explanatory so just add and delete customers OR copy and paste them from another spreadsheet.
Once they are listed click this button or return to page 1 with the tabs at the bottom and select the customer using the dropdown menu just like this. You’ll notice that the cell references auto populate everything from the customer info sheet so there’s nothing more you’ll need to enter.
The next section is where you enter your products and services.
First you have Quantity and Number of Units which you can change with a dropdown. For most of you, I think you’ll just leave it as UNITS.
That’s what I use most often.
Put a Product Description in and type the cost per unit… and the TOTALS column auto populates everything for you.
Repeat the process for as many products or services as you see fit.
Then come down to the bottom and you’ll see the totals and payment details. If you want to add sales tax, click the cell and choose the applicable tax from the drop down menu.
The payment details and Other Info section can be customized with your information or your company information and they’re standard text fields so just type in your contact information and make them look the way you want them to.
The final thing that you can do to add your payment link for your client or customer to pay the invoice.
Some accounting software have built in payment options when they are mailed so why not give yourself that option with simple invoicing too.
So this button here that says click to pay invoice… and this is where you can insert a payment link to a hosted payment page that’s linked to your merchant account.
Now the service that I’m using here and that I recommend is called SimplyPay.me which is a hosted payment page and invoicing link tool.
So it’s perfect for this application
You can try the service for free by going to simplypay.me and creating a free account so you can follow along with what I’m going to show you now.
The service allows you to link your stripe and PayPal accounts to Simplypay.me and there’s even an option for premium merchant account with Cardconnect which gives you lower processing rates.
So for now, just know that simplypay.me gives you a link that your customer can click on and it takes them to a page that looks similar to this where they can instantly pay your invoice.
So I’m going to grab the payment link and come back to my invoice and go to page number two.
Paste the link right here in this box.
The green payment button on page one references that cell that you just pasted your link into.
I’m going to save this invoice to PDF in just a minute but a 2nd option to attaching your link to this payment button is to right click on the button and select “EDIT HYPERLINK”
Come down and paste the link right here, the URL, just put it right in this box and then click OKAY to save.
Then, it’s always a good idea to SAVE the main Excel document to make sure all of the changes are picked up.
The last step is to come to file export or File > Save As and choose the PDF option.
Once you do you can open the PDF and test the link by clicking on the payment button
Once it checks out you’re ready to email it to your customer.
In the email it’s also a good idea to include the payment link right in the text of the email so that it is EASILY clickable for your customer.
No one likes outstanding accounts receivable so the easier you make it for your customer to pay and invoice that you’re sending them, the better it is for everyone.
TEMPLATE 2 is laid out in a similar format to the first template but there are some distinct differences that I’ll go over with you.
Start with simple customization of the template itself by AGAIN, adding your logo and your own company info.
Update the Date, Invoice #, Customer info (if applicable) and this is just a unique identifier specific to your company if you want to use it and the Due Date of the invoice.
The BILL TO info for your customers is referenced in the exact same way from Page 2 just like I showed in the first template that we just went through.
If you don’t like that functionality by the way, you can, of course, just delete this cell reference and type in all of the fields for each customer that you use to invoice.
So you can make everything manual if you’d like.
Come down to the description and type in your service or product title.
The AMOUNT column on this template is not a reference, so just type the total dollar amount that needs to be billed in the AMOUNT column.
If it’s a taxable item, you’ll just put an X right here in this column and it will auto populate the tax for that item.
So on a single invoice you can add products that are taxable and also include line items for something like consulting services that you might not want to include tax on.
You can also add a dollar amount to the OTHER field for something like shipping and handling for example which is not included in taxable amount.
Additionally, you could just list shipping and handling as a separate line item at the top if you’d like. Just make sure to leave it unchecked so that that’s not taxed.
Now if you’re going to use this template to invoice for your services (vs. your products), you might not even want the taxable column at all.
In that case let’s modify this template with some different cell references.
You can just change the Taxable column to the QUANTITY column. So I’ll just type in “QTY”
Then if you do that, you might want to add another column for UNIT PRICE so everything totals up correctly.
So I’ll go ahead and do that and in your template Click here and select MERGE and CENTER so that it unlinks the title column like this.
Then I’ll copy this column header so it matches font and color and paste it in here.
SO i now have UNITs, QUANTITY and AMOUNT.
The AMOUNT will be the subtotal for each line item…
The cell reference that you’ll type into the AMOUNT COLUMN is:
EQUALS then arrow over or put your mouse in this cell here and click.
It will populate that cell number and hit the ASTERIX key for multiply, and then use your mouse to click in the same row, under the UNIT PRICE field and that cell number will get added to our formula. Then just click enter to save it.
So for each line item you add, you want to have this cell formula in the AMOUNT totals column so the grand total at the bottom calculates properly.
So hover your mouse over the bottom right corner of the cell that holds the formula we just created and click and hold, and pull down to the next two or three columns like this.
You’ll notice that it puts a DASH in columns that don’t have values in all of the fields required so if there’s a formula in the AMOUNT column you want to make sure to fill out the QUANTIY and UNIT PRICE field for that item ROW
Then just fill out the number of line items you want on this invoice and delete the other references in the AMOUNT column by clicking in the cell and hitting the delete key.
Then if you’re not going to be invoicing for taxable items, you can just deleted these cells from the totals section and leave the rest there.
The template will calculate your total and you still have your “OTHER” field if you like to use it.
Finally you can insert your SimplyPay.me link by using the payment link cell on page 2 of this template or you can just right click, and choose EDIT HYPERLINK and paste your link and then click okay just like I showed before.
Save it to PDF just like the last template and you’re ready to email it to your customer.
So that’s both templates and you can download them for FREE by clicking the link in the description box.
If you have questions about the excel templates, the cell references, or simplypay.me please comment below and I’lll get your questions answered as quickly as I can.
Additionally, if you found this video helpful, please subscribe to the channel.
If you’re new to the channel, I publish tutorial style videos that are on the topics of making the process of collecting payments and getting paid, simple, and efficient.
I talk about payment software tools, payment gateway, CRM software, general merchant accounts topics, how to set up payment on your website, and a bunch of other stuff all related to payment processing for your business.
I’m Brian manning and I’ll see you next time.