A Excel invoice is generated from three building blocks: the invoice details, the individual services and a comprehensible calculation. The unit can be selected from a list. Quantity and unit price are individual entries. Excel calculates the position amounts, the VAT and the total amount.

This guide uses the own sample calculation of TabellenFaktura. The completed exercise file already contains the unit selection and the described formulas. All cell addresses refer to this file. It can be edited without macros. For the subsequent step to ZUGFeRD or XRechnung, the article leads through the setup in TabellenFaktura.

The example: Two hours of consulting at EUR 95.00, three hours of project work at EUR 65.00 and one journey at EUR 25.00 total EUR 410.00 excluding VAT. At a uniform VAT rate of 19%, EUR 77.90 is added. The total amount is 487,90 EuroNames, prices and services are exercise data.

1. Download the invoice template and completed exercise file

  • Excel-Datei · XLSX · 44 KBExcel practice invoice with unit selection
    A bill sheet with free performance texts, selectable units and prepared calculations.
    Download
  • Excel-Datei · XLSX · 52 KBInitial TabellenFaktura model calculation
    It remains available as an unchanged initial template.
    Download

For direct understanding, the exercise file is the right start. First, save under a separate name, for example Rechnung-2026-001-Entwurf.xlsx. Keep an unchanged template copy. Replace the existing sample addresses, identifiers, bank details and logo completely before a real use.

The formulas use WHEN, SUMME and ROUNDS as well as tests for missing entries. The menu names and tests refer to the Windows-desktop version of Excel. TabellenFaktura requires Excel under Windows.

2. Enter billing information in the right cells

The Leaf Tabelle1 consists of many narrow columns. For the print image several of them are connected. The content is in the upper left cell of the connected area. A click in the name field to the left of the editing bar and the input of H27 therefore leads directly to the first performance designation.

DetailCell or range on table1Processing
Company and addressAD4 to AD7, E11 and foot range from E59Customize all repeated entries. A logo does not replace the company name.
Beneficiaries of the accountsE13 to E16Enter full name, street, postcode, place and country.
E-mail of the recipientAD16Check the address for the intended billing.
Invoice numberL22Replace the exercise number with a unique number.
Invoice dateU22Enter a fixed date.
Service dateAD22Check actual performance date regardless of the invoice date.
Due dateAM22By default, U22 + 30 calendar days. Adjust to the agreed payment destination.
VAT-ID and bank dataAL59, T60 and T61If the tax number is used, adjust the label as well.

For an ordinary full invoice, the parties with address, tax identification of the exhibitor, date of issue, unique invoice number, type and scope of service, date of performance, and remuneration and tax details are included. The special information required depends on turnover. § 14 UStGA short catalogue term such as "Consultancy" is often not enough for a real contract to be able to understand the service description. The concrete activity and the appropriate period of time must be recognizable.

Why not have a TOWARD() on a finished invoice

Used in the original model invoice U22 the formula =HEUTE(). It changes when you open it later. The exercise file therefore contains fixed date values in U22 and AD22. The maturity in AM22 read:

=U22+30

This leaves a training calculation issued at 28.10.2026 with this date. The example is due at 27.11.2026. A date such as 28.10.2026 Enter the date value as true Excel and display it as date via the number format. In a performance period, expand the template accordingly and later check its integration into the e-invoice.

3. Enter the power rating freely

Page 3 Tabelle1 the names are in: H27:H48. These fields are freely descriptable. Each line contains a coherent description of the actual calculated delivery or service. Examples are "Consulting on the invoice template, "Set up and check template, or a concrete article with its execution.

The term must be a comprehensible representation of the order. If necessary, it should be added to the relevant period, project name or concrete work result. A general term such as "service is often too inaccurate. Even if the price is a flat-rate, an understandable description remains necessary.

The fields are spread over the connected columns H to X. Entering the upper left cell, for example H27. For longer texts, activate the line break and increase the line height appropriately. For connected cells, the automatic height adjustment does not always work reliably.

Three Excel invoice positions with free designation, quantity, unit, unit price and calculated total amount
The position of the exercise file. The name is free, the unit is selected in the column "Unit.

The combo box belongs in the column UnitIn the model calculation, the related areas are: AC27:AH27 Until AC48:AH48 The cell addresses are AC27 to AC48. The prepared list contains:

  • h for hours, for example, amount 2 for two hours of work.
  • pcs for pieces, for example, quantity 3 for three delivered copies.
  • flat rate for a flat rate, in a simple example with quantity 1.

To select in AC27 click and open the list arrow. Alternatively, open Old + Arrow Down select the appropriate unit and repeat for the other positions. Each position receives its own unit.

Create the unit list yourself

  1. Page 3 Tabelle1 the area AC27:AH48 These are the 22 connected fields of the unit column.
  2. The Data > Data verification in the case of ‘Allow to select the type ‘List'.
  3. As source in a German Excel installation with semicolon as list separator Std.;Stk.;pausch. If the installation uses another list separator, the entries will be separated accordingly.
  4. "Cells dropdown, activate. Under "Error message, select the type "stop, and for example "Select a unit from the list, deposit.
  5. Confirm with OK and open the list in the first and last row.

The list is already available in the units in both of the sample files offered. The completed exercise version also contains the test and rounding formulas explained in the article. Microsoft instruction on the dropdown list describes the data check for the different Excel versions.

Add additional units

Mark the entire unit area again and edit the source under data check. Add another unit and then check the first and last cell. For a later e-invoice TabellenFaktura the selected unit must be able to assign a suitable standardized unit code. The technically correct unit depends on the calculated performance.

Copying and pasting can bypass or overwrite a data check. After such changes, check the selection list and the entered values. One unit is deliberately selected per position. Changes to the free name do not automatically change the selected unit.

5. Enter quantity and net unit price separately

The amount is in Y27 and the net single price in AI27. In the next line follow Y28 and AI28. Both fields contain direct number entries. For example 2 hours at each 95,00 Euro result in a net amount of 190,00 Euro.

Into the price field 95 or 95,50 The Eurosign is displayed in the number format. An input as text such as "95 Euro, cannot be reliably multiplied and leads to an error message in the calculation in the exercise file.

Unit and price must match. In hours, an hourly price is entered, in hours a unit price. For a one-time package, the quantity is at 1 and the agreed net price is in the price field. Also a consciously free position is possible: For this, the price is explicitly entered as a number 0. An empty price is a missing indication.

6. Calculate quantities, line-item amounts, the net total and VAT

Enter quantities

The quantities are in Y27:Y48. For the example 2 are entered at the consultation, 3 at the setup of the template and 1 at the approach. 2,5 The exercise file displays quantities with two decimal places. A described position requires a positive numerical quantity.

Round the amount of each position to cents

The basic calculation for AO27 is:

=RUNDEN(Y27*AI27;2)

The two decimal digits number format only changes the display. RUNDEN(...;2) The result changes to cents. Three units to 0,335 Euro thus result in a position amount of 1,01 Euro. This rounding must remain consistent with the later generation of the structured invoice.

The actual deposited formula also checks the quantity, a completed unit and a numerical price:

=WENN(H27="";"";WENN(ODER(NICHT(ISTZAHL(Y27));Y27<=0;AC27="";NICHT(ISTZAHL(AI27));AI27<0);NV();RUNDEN(Y27*AI27;2)))

Without a performance description, the result cell remains empty. If a performance described with missing or invalid quantity, missing unit or missing or invalid price appears #NV. This also leaves the total amount incorrect until the input is corrected. Leave unused position lines completely blank; the prepared formulas in E and AO remain unchanged. E27 is formed by: =WENN(H27="";"";ZEILE()-26); The positions should be recorded without empty intermediate lines.

The sums of the unchanged exercise file

CellMeaningFormula
AO49Net sum of rounded items=RUNDEN(SUMME(AO27:AO48);2)
AO50VAT with 19 percent=RUNDEN(AO49*19%;2)
AO51Total net and tax=RUNDEN(SUMME(AO49:AO50);2)

The label in E50 The following is the following: "VAT 19%,. It must match the tax rate used. In the example, the position amounts of 190,00 Euro, 195,00 Euro and 25,00 Euro are generated. This is followed by 410,00 Euro net, 77,90 Euro tax and 487,90 Euro total.

Further examples of cent deviations explain the contribution Rounding errors in Excel invoices.

What is different with 7 percent, multiple tax rates and small business owners

The exercise file is prepared for exactly one tax rate per invoice. The choice of the rate depends on the specific performance and its tax treatment. It is not a freely selectable pricing. For the example, a fully taxed invoice is accepted with 19 percent.

In the case of an invoice actually taxed uniformly with 7 percent, the formula must be: AO50 on =RUNDEN(AO49*7%;2) The text of the text is in the following form: E50 to ‘Umsatzsteuer 7%’. If the net amount remains EUR 410.00, the calculation gives EUR 28.70 in VAT and a total of EUR 438.70. This calculation does not establish whether the example services legally qualify for the reduced VAT rate.

If an invoice contains both 7 and 19 percent, a common tax cell is not enough. Then each position needs its applicable tax rate. The net amounts must be grouped according to the rate and the tax amounts calculated per group. To multiply the total net sum only with one of these rates would be wrong. For this purpose, the already published article explains 7 and 19 percent on an e-invoice the separate structure.

If the small business VAT exemption is used according to § 19 UStG, no VAT is reported separately. This includes a reference to the tax exemption for small businessmen. A normal invoice only to "0 %, to change, does not completely represent this case. § 34a VATV regulates the minimum details and allows for this turnover a different invoice. The matching separate template and explanation are in the contribution Invoice for small businessmen from ExcelOther tax-free transactions and reverse charge also require their own appropriate treatment.

8. Add more bill lines and check typical errors

The prepared lines 27 to 48 provide space for 22 invoice items. Each line receives a free designation, quantity, unit and a net unit price.

For another position line first create a working copy. A complete new line over the line header before line 48 insert within the current position range. Then copy the existing line 47 into the new line 48. Delete possibly withcopied name, quantity, unit and unit price. Keep the formulas in E and AO. The previous line 48 is placed on line 49. The sum lines move accordingly to 50 to 52.

Then check the formulas: The net sum must now be AO49 The new line also needs unit dropdown, quantity and price check, and the position formula. A test position in the last line must change the final amount. Also adjust the print range, page break and if necessary the stored position assignment in TabellenFaktura. For extensive calculations, the guide to multipage Excel invoices.

ObservationTest
Unit list missingCheck data check in AC27:AH48 Allow: List. Activate cells dropdown.
A new unit is missing in single linesUpdate the source for the entire unit range uniformly.
#NV at the position amountCheck quantity, unit and unit price. Quantity must be positive, the price must be a number from 0.
Formula is displayed as textCell format to "Default, set. Remove leading apostrophe and confirm the formula again.
Description of performance cutActivate line break, increase line height and control print preview.
Total does not fit after new lineCheck sum range and formula of the last position. Distinguishing display format and actual rounding.

9. Check the print image and reuse the bill safely

  1. The invoice sheet Tabelle1 The prepared print area of the unchanged exercise file is D3:AU63.
  2. The File > Print control each page. Use A4, matching edges and one page in width. Allow multiple pages in height for longer bills instead of making the font unreadable.
  3. Replace sample logo and sample data. Match performance description, date, number, unit, price and sums with the actual order.
  4. About File > Export > Create PDF/XPS document issue the active invoice sheet. Only the intended invoice sheet.
  5. Open and check the generated file. Then keep the final invoice issued together with the corresponding work status in a comprehensible way.

A separate working copy for each invoice

The individual prices are directly entered in this template. For each new invoice, create a copy of the original and check the valid prices according to the agreement. If a completed copy is used, remove the old position entries completely. The calculation formulas in E and AO as well as the sum formulae are retained.

Keep the actual PDF or e-invoice file as a final document unchanged. Changes to a later working copy do not replace this document. A Excel file alone or a simple PDF export is also not a complete archiving solution. Storage, access and comprehensible changes must fit the operational procedure.

10. Create a ZUGFeRD invoice or XRechnung from the Excel template

A Excel file and a common PDF are not structured e-invoice yet. The Federal Ministry of Finance has distinguished structured e-invoices from other invoices since 2025. There are transitional regulations for 2026; a common PDF requires the consent of the recipient. What deadlines or exceptions apply depends on the case. B-Mf-fx e-invoice explain the details. Separate requirements may also apply to public-sector customers.

TabellenFaktura takes the associated data from Excel and creates it from ZUGFeRD or XRechnung. In the current public version 1.6.13, Stand 28.10.2026, the setup starts in the Excel menu band below e-invoice > Help > Setup.

  1. Excel template: Open your own working copy and Tabelle1 The sheet contains the invoice data to be taken over.
  2. Invoicing fields: For this example, the term H, the quantity in Y, the unit in AC and the unit price in AI. The position amount is in AO. Connected cells are addressed over their left upper cell. The sums are in AO49 to AO51.
  3. Numbers & Archive: Set the number circle to match the numbers already assigned and select a accessible archive folder. Do not use the exercise number as a real invoice number.
  4. Customers and e-mail: Set up these optional steps only if necessary. Recipient data can still be maintained in Excel.
  5. Invoice & Audit: Read the invoice, edit the notes and check the details. Before final creation, compare in particular recipients, performance description, quantity, unit, tax rate and final amount.
  6. Conclusion: After the creation of the visible invoice and structured data, the set up workbook is stored. A technical examination does not replace the technical control of the concrete turnover.

The exercise file has its own unit column. If a template is not in this column, it must be added and assigned to the status described here. A missing unit is not automatically replaced by a assumed value. After inserted rows or moved fields, check the assignments again.

The complete operation steps are in the Excel template documentationThe contribution Creating ZUGFeRD invoices with Excel then leads through the way to the finished structured invoice.

Status and scope verified

Published on 28.10.2026. The provided exercise file was opened, recalculated and reopened in the Windows-desktop version of Excel. Tested include unit drop-downs, free designations, price and quantity changes, missing information, cent-rounding, the last prepared position area and print image. The compact position cut-off comes from this file and does not show any customer invoice. The calculation example treats an ordinary invoice with a uniform VATset and without discount, discount, partial payment or special deductions.

Related practical guides

FAQ

Frequently asked questions

How can an invoice be created in Excel?

Create a template with invoice details and position lines, record quantity and net price per position and sum up the rounded position amounts. Then calculate the appropriate VAT and the total amount. The exercise file contains free label fields, unit drop-downs and the appropriate formulas.

Which column will the dropdown in the sample bill get?

The dropdown is only in the unit column AC27 to AC48. The list contains hours, hours and p-squeeze. The service designation in H27 to H48 and the quantity and unit price are entered free of charge.

What formula calculates the invoice amount of the quantity and the unit price?

For the first position, the basic formula is = ROUND(Y27*AI27;2).The exercise file adds a check: Empty positions remain empty and a described position with missing quantity, unit or valid price creates a visible error.

Can the exercise file 7 and 19 calculate percent VAT simultaneously?

The exercise file provided uses a uniform tax rate per invoice. For mixed rates each position needs its own tax assignment and the sums must be separated according to the rate. The contribution links a separate guide for this.

Why should invoice data not be calculated with HEUTE()?

HEUTE() updates when reopening. An invoice already issued could show a different date. The completed exercise file contains fixed invoice and performance data. The due date is derived from the fixed invoice date.

Is a PDF file exported from Excel already an e-invoice?

A common PDF does not contain structured invoice data and is therefore not a structured e-invoice. TabellenFaktura can generate ZUGFeRD or XRechnung from the associated Excel data. Transitional rules, exceptions and requirements of the recipient must be observed separately.