Contains paid promotion

Advertisement

How to create in Excel 2016
own invoice templates

Anyone who is very fresh in self-employment, sees himself at the beginning of any initial cost, which does not come in the start phase right back through orders. Therefore you should save where it is possible. At the beginning you might not need a professional billing software, but you can invest it later if it works really well.

But even for employees who are currently still in a job, and the self-employed activity for the time being only incidentally, it is necessary to save costs where possible.

How to create your own invoice template in Excel 2016 and send it as a PDF we want to describe in this article

Create an invoice template in Excel
Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

How to create in Excel 2016 invoice templates

Anyone who is very fresh in self-employment, sees himself at the beginning of any initial cost, which does not come in the start phase right back through orders. Therefore you should save where it is possible. At the beginning you might not need a professional billing software, but you can invest it later if it works really well.

But even for employees who are currently still in a job, and the self-employed activity for the time being only incidentally, it is necessary to save costs where possible.

How to create your own invoice template in Excel 2016 and send it as a PDF we want to describe in this article

Create an invoice template in Excel
Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Set up page and format

1. Set up page and format

Before we begin to create a table in which the actual calculations are performed later, we should first adjust our sheet. Because just like a letter, our bill will indeed include a letterhead with recipient, sender, etc.

And the whole thing is of course also in an envelope with window fit properly when you print it and the usual Zigzag fold used.

As follows:

  • Open a new Excel document
  • Use the “Page layout” tab – “Orientation” to select the portrait format
  • Then select “Format” DIN A4

After these first steps, your previously empty spreadsheet will be displayed with horizontal and vertical dashed lines, and you will know where the A4 sheet starts and where it ends.

It is now 10 columns (A – J) wide, and 50 rows high.

See picture: (click to enlarge)

Seitenformat in Excel einrichten
Advertisement

Before we begin to create a table in which the actual calculations are performed later, we should first adjust our sheet. Because just like a letter, our bill will indeed include a letterhead with recipient, sender, etc.

And the whole thing is of course also in an envelope with window fit properly when you print it and the usual Zigzag fold used.

As follows:

  • Open a new Excel document
  • Use the “Page layout” tab – “Orientation” to select the portrait format
  • Then select “Format” DIN A4

After these first steps, your previously empty spreadsheet will be displayed with horizontal and vertical dashed lines, and you will know where the A4 sheet starts and where it ends.

It is now 10 columns (A – J) wide, and 50 rows high.

See picture:

Seitenformat in Excel einrichten
Advertisement

2. Insert recipient and sender fields

2. Insert recipient and sender fields

Now it goes on with the correct positioning for your logo, the statement INVOICE, the receiver and sender areas.

So that our content has some space to the edge reduce the column A (right mouse button / context menu) to 0.8 cm
Then mark columns B to E and lines 1 to 4 and connect the cells via the “Start” tab – “Connect and center”

By way of example, we have already written in here “your company name / logo”.

Ultimately, you can just write in your company name, or simply use the “Insert” tab – “Images” to create a previously created logo.

See picture: (click to enlarge)

Spaltenbreite in Excel anpassen
Zellen in Excel verbinden
Advertisement

Now we are preparing an area for the lettering “INVOICE”.

For this we mark the columns G to J and the lines 1 to 3, and connect them again. Here you can now from best beautiful large “INVOICE” write in, and center align.

Underneath we leave a line free, and in line 5 / column G continue with the details of the sender (your company) and write all the data together freely.

For clarity, I recommend under the information such as: name, address, etc. to release two lines, and then continue with the information: date of invoice, customer number, invoice number, tax or VAT numbern

Note: Do not worry if your details do not fit into a single cell, just override it. The grid lines that are visible in the standard, in any case, only a help, but will not be printed later.

See picture: (click to enlarge)

Absender in Excel Rechnungsvorlage
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Next comes the area in the letterhead for the recipient address, as well as a small line in which the sender address is inserted again so that (even if undeliverable) the sender is visible without opening the letter.

To do this, insert the sender address in line 6 / column B, and optimally use a smaller font size (for example 9 pt), and optionally underline this.

Then we release one row again, and continue in row 8 / column B and insert the recipient address there.

See picture: (click to enlarge)

Empfängeradresse in Excel Rechnungsvorlage
Advertisement

Now it goes on with the correct positioning for your logo, the statement INVOICE, the receiver and sender areas.

So that our content has some space to the edge reduce the column A (right mouse button / context menu) to 0.8 cm
Then mark columns B to E and lines 1 to 4 and connect the cells via the “Start” tab – “Connect and center”

By way of example, we have already written in here “your company name / logo”.

Ultimately, you can just write in your company name, or simply use the “Insert” tab – “Images” to create a previously created logo.

See picture:

Spaltenbreite in Excel anpassen
Zellen in Excel verbinden
Advertisement

Now we are preparing an area for the lettering “INVOICE”.

For this we mark the columns G to J and the lines 1 to 3, and connect them again. Here you can now from best beautiful large “INVOICE” write in, and center align.

Underneath we leave a line free, and in line 5 / column G continue with the details of the sender (your company) and write all the data together freely.

For clarity, I recommend under the information such as: name, address, etc. to release two lines, and then continue with the information: date of invoice, customer number, invoice number, tax or VAT numbern

Note: Do not worry if your details do not fit into a single cell, just override it. The grid lines that are visible in the standard, in any case, only a help, but will not be printed later.

See picture:

Absender in Excel Rechnungsvorlage
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Next comes the area in the letterhead for the recipient address, as well as a small line in which the sender address is inserted again so that (even if undeliverable) the sender is visible without opening the letter.

To do this, insert the sender address in line 6 / column B, and optimally use a smaller font size (for example 9 pt), and optionally underline this.

Then we release one row again, and continue in row 8 / column B and insert the recipient address there.

See picture:

Empfängeradresse in Excel Rechnungsvorlage
Advertisement

3. Excel invoice template without tax

3. Excel invoice template without tax

Now that we have finished our letterhead, we can do the actual billing section.

For this we have now started in our example in line 21 / column B with the table. But this is not as crucial as it was with the letterhead, which will later fit into the window of an envelope. So, later on, you can do that a little bit higher or lower, which suits you best.

  • In line 21, we start in the horizontal with the entries for: date, description / specification, number, etc.
  • Then we mark all entries (left mouse button pressed, and drag over)
  • Then continue via the “Insert” tab – “Table”
  • In the dialog box you will be asked again for the area in which the data for the table are (because you do not need to do anything, if everything is marked correctly)
  • The checkbox “Table has headers” must be selected
  • Confirm with “OK”

See picture: (click to enlarge)

Markierung Dynamische Excel Tabelle
Dynamische Tabelle in Excel einfügen

The advantage of having a dynamic spreadsheet in Excel is that it makes it easier to insert computational operations and, as the name suggests, dynamically expand and update them.

You can now change the layout of your spreadsheet by selecting it, and then using the “Table Tools” – “Design” – “Table Styles” tab either select one of the templates or create your own.

However, you can change the layout at any time without changing the functionality.

See picture: (click to enlarge)

Layout Dynamische Excel Tabelle
Advertisement

Now you can expand the table quite easily, either by pressing the TAB key in the last cell of the last row of the table, or optionally you have a small reinforcement at the bottom right of the table that is easy to hold (left mouse button pressed, and pull) and pull down.

This will always jump you directly to the next line

n the calculations within the table, we simply calculate the “hourly rate” x “number” in our example.
To do this, we go to the first row of the table under the column “Amount” and enter

= SUM (F23 * H23)

and confirm with Enter

Note: This may of course be different in your formula depending on the division of rows and columns.

The function entered in this way is automatically updated for this column by Excel each time you expand the table by one line.

See picture: (click to enlarge)

Excel Tabelle Betrag berechnen
Advertisement

You can either integrate the now missing result row into the dynamic table by clicking in any cell of the table and then activate the check box “Result row” via the tab “Table tools” – “Design”.

The advantage of such a result row in a dynamic table is that it not only allows you to add sums, but also various other options such as: minimum, maximum, mean, and much more. get it done.

The disadvantage (at least with respect to our bill template) is that the result line is, of course, directly below the last line of our entries. It does not look so pretty, which is why we did not do it in our example, and instead just left a line blank, then made it a bit narrower, and then created a separate result line below it.

In this way, the whole thing does not look so bruised, and the resulting row of results automatically slips one line lower with each extension of the table.

The calculation for our invoice amount we then simply make as follows:

  • mark the cell for the final invoice amount
  • enter = SUM (
  • mark the column area of ​​the table in which the added hourly rates stand
  • close the function again with a clip

See picture: (click to enlarge)

Excel Dynamische Tabelle Ergebniszeile
Rechnungsbetrag einfügen
Advertisement
#1
WD Elements Portable, Externe Festplatte - 2 TB - USB 3.0 - WDBU6Y0020BBK-WESN
WD Elements Portable, Externe Festplatte - 2 TB - ...*
by Western Digital

Festplattenkapazität: 2000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 17. August 2018 um 12:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Now that we have finished our letterhead, we can do the actual billing section.

For this we have now started in our example in line 21 / column B with the table. But this is not as crucial as it was with the letterhead, which will later fit into the window of an envelope. So, later on, you can do that a little bit higher or lower, which suits you best.

  • In line 21, we start in the horizontal with the entries for: date, description / specification, number, etc.
  • Then we mark all entries (left mouse button pressed, and drag over)
  • Then continue via the “Insert” tab – “Table”
  • In the dialog box you will be asked again for the area in which the data for the table are (because you do not need to do anything, if everything is marked correctly)
  • The checkbox “Table has headers” must be selected
  • Confirm with “OK”

See picture:

Markierung Dynamische Excel Tabelle
Dynamische Tabelle in Excel einfügen

The advantage of having a dynamic spreadsheet in Excel is that it makes it easier to insert computational operations and, as the name suggests, dynamically expand and update them.

You can now change the layout of your spreadsheet by selecting it, and then using the “Table Tools” – “Design” – “Table Styles” tab either select one of the templates or create your own.

However, you can change the layout at any time without changing the functionality.

See picture:

Layout Dynamische Excel Tabelle
Advertisement

Now you can expand the table quite easily, either by pressing the TAB key in the last cell of the last row of the table, or optionally you have a small reinforcement at the bottom right of the table that is easy to hold (left mouse button pressed, and pull) and pull down.

This will always jump you directly to the next line

n the calculations within the table, we simply calculate the “hourly rate” x “number” in our example.
To do this, we go to the first row of the table under the column “Amount” and enter

= SUM (F23 * H23)

and confirm with Enter

Note: This may of course be different in your formula depending on the division of rows and columns.

The function entered in this way is automatically updated for this column by Excel each time you expand the table by one line.

See picture:

Excel Tabelle Betrag berechnen
Advertisement

You can either integrate the now missing result row into the dynamic table by clicking in any cell of the table and then activate the check box “Result row” via the tab “Table tools” – “Design”.

The advantage of such a result row in a dynamic table is that it not only allows you to add sums, but also various other options such as: minimum, maximum, mean, and much more. get it done.

The disadvantage (at least with respect to our bill template) is that the result line is, of course, directly below the last line of our entries. It does not look so pretty, which is why we did not do it in our example, and instead just left a line blank, then made it a bit narrower, and then created a separate result line below it.

In this way, the whole thing does not look so bruised, and the resulting row of results automatically slips one line lower with each extension of the table.

The calculation for our invoice amount we then simply make as follows:

  • mark the cell for the final invoice amount
  • enter = SUM (
  • mark the column area of ​​the table in which the added hourly rates stand
  • close the function again with a clip

See picture:

Excel Dynamische Tabelle Ergebniszeile
Rechnungsbetrag einfügen
Advertisement
#1
WD Elements Portable, Externe Festplatte - 2 TB - USB 3.0 - WDBU6Y0020BBK-WESN
WD Elements Portable, Externe Festplatte - 2 TB - ...*
by Western Digital

Festplattenkapazität: 2000 GB, Festplatten-Formfaktor: 2.5 ...

*Zuletzt aktualisiert am 17. August 2018 um 12:51 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

4. Insert header and footer in Excel

4. Insert header and footer in Excel

All that is missing now is the specification of our bank details, which we could just write anywhere under the billing area, but better looks like something always in the footer of a document.

To do this we go to the “Insert” tab on “Header and Footer”

See picture: (click to enlarge)

Kopf- und Fußzeile in Excel einfügen

In the next window we have to add different options of our footer the current daily date and time, the respective page number, graphics, or own data (such as the bank details).

In this view, it is also easy to jump between the header and footer using the button

If you have entered your details there as you wish, and want to return to normal view, just click into any of the bill templates outside the header and footer, and then from the View – Workbook Views – Normal tab.

See picture: (click to enlarge)

Elemente in Kopfzeile in Excel hinzufügen
Ansicht Kopf-und Fußzeile in Excel beenden
Advertisement

Note: After you return to the normal view, the header and footer are no longer displayed. These are then only visible in the “Page Layout” and “Print Preview” views.

All that is missing now is the specification of our bank details, which we could just write anywhere under the billing area, but better looks like something always in the footer of a document.

To do this we go to the “Insert” tab on “Header and Footer”

See picture:

Kopf- und Fußzeile in Excel einfügen

In the next window we have to add different options of our footer the current daily date and time, the respective page number, graphics, or own data (such as the bank details).

In this view, it is also easy to jump between the header and footer using the button

If you have entered your details there as you wish, and want to return to normal view, just click into any of the bill templates outside the header and footer, and then from the View – Workbook Views – Normal tab.

See picture:

Elemente in Kopfzeile in Excel hinzufügen
Ansicht Kopf-und Fußzeile in Excel beenden
Advertisement

Note: After you return to the normal view, the header and footer are no longer displayed. These are then only visible in the “Page Layout” and “Print Preview” views.

5. Create invoice template with sales tax

5. Create invoice template with sales tax

If you have a sales tax identification number, then you are basically obliged to show this in invoices.

Thus, the 100, – € in our example calculation would be our net amounts, and we have to declare the 19% sales tax separately, in order to present them with the total net sum in our invoice amount as a gross amount.

This little extension is done relatively easily.

  • Just add two lines above the invoice amount
  • In the upper you can e.g. Enter total or net amount, and in the cell next to it just add the totals of the dynamic table (as before with the invoice amount)
  • And in the bottom line you wear e.g. plus 19% UsT. a, and in the cell next to it type the formula = SUM (J27 * 0.19)
  • In the cell Invoice amount you simply add both cells (net amount + UsT.)

See picture: (click to enlarge)

Ust. in Excel Rechnungsvorlage
Advertisement

And that was it.

You simply save the template as such in Excel, and then you can always use it again for new invoices.

Of course you will eventually resort to a professional billing and customer management, and this probably also to be able to better meet the legal documentation requirements, but for the beginning that is perfectly adequate and above all free.

If you have a sales tax identification number, then you are basically obliged to show this in invoices.

Thus, the 100, – € in our example calculation would be our net amounts, and we have to declare the 19% sales tax separately, in order to present them with the total net sum in our invoice amount as a gross amount.

This little extension is done relatively easily.

  • Just add two lines above the invoice amount
  • In the upper you can e.g. Enter total or net amount, and in the cell next to it just add the totals of the dynamic table (as before with the invoice amount)
  • And in the bottom line you wear e.g. plus 19% UsT. a, and in the cell next to it type the formula = SUM (J27 * 0.19)
  • In the cell Invoice amount you simply add both cells (net amount + UsT.)

See picture:

Ust. in Excel Rechnungsvorlage
Advertisement

And that was it.

You simply save the template as such in Excel, and then you can always use it again for new invoices.

Of course you will eventually resort to a professional billing and customer management, and this probably also to be able to better meet the legal documentation requirements, but for the beginning that is perfectly adequate and above all free.

6. Save Excel spreadsheet as PDF

6. Save Excel spreadsheet as PDF

Finally, we should bring our invoices so created in a common format, which is also suitable to be sent by e-mail. Of course, the format of an Excel spreadsheet is not suitable at all.

So we just save it as a PDF, or you access a professional solution.

We can recommend here the program: Nuance Power PDF 2.0

This can be easily integrated into all Office programs during installation, and offers a variety of options, such as: also the conversion of PDF into Word, emails as PDF archive, encryption, and much more.

After the installation, you will always have an additional tab in every Office application, which you can use to carry out a wide range of tasks.

After saving as a PDF, we can then easily send the invoice as an attachment by e-mail to customers.

See picture: (click to enlarge)

Excel als PDF speichern
Nuance Power PDF
Print Friendly, PDF & Email

Finally, we should bring our invoices so created in a common format, which is also suitable to be sent by e-mail. Of course, the format of an Excel spreadsheet is not suitable at all.

So we just save it as a PDF, or you access a professional solution.

We can recommend here the program: Nuance Power PDF 2.0

This can be easily integrated into all Office programs during installation, and offers a variety of options, such as: also the conversion of PDF into Word, emails as PDF archive, encryption, and much more.

After the installation, you will always have an additional tab in every Office application, which you can use to carry out a wide range of tasks.

After saving as a PDF, we can then easily send the invoice as an attachment by e-mail to customers.

See picture:

Excel als PDF speichern
Nuance Power PDF
Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-14T11:46:06+00:00By |Categories: Excel 2016, Office 2016|Tags: , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok