For anyone who needs to create an invoice to request the money for a job as a freelancer or consultant or for a company, we see here how to create a personalized invoice in Excel, simple, starting from a white sheet, without then using a template already ready.
Creating an invoice in Excel is a relatively simple job, you just need to prepare a table, set some rules, add the required information and then, if you want to do a more technical job, also add some calculation automation. Obviously it is possible to use an invoice template already prepared by experts, download one and customize it. Some models are already available in Excel, opening a new sheet and then going to the File tab and looking for invoices in the search field. Other models are available on the Free Invoice Templates specialized site or on other sites with Excel models to download for free .
In this case, however, we go to see the steps to create a simple invoice on Excel from scratch (the guide also works with LibreOffice Calc ).
READ ALSO: 10 free invoicing and accounting programs for self-employed and small businesses
To make a simple invoice in Excel, the required information is:
Now we need to resize some of the columns and rows, to have more space to write longer information such as descriptions of the sales object. To resize a row or column, click on it and drag it with the mouse. By default, the rows are set to a height of 20 pixels and the columns are set to a width of 64 pixels. For an optimized configuration you can resize the columns and rows in this way:
Line 1 must contain the name of the seller and the word "Invoice". The extra space allows you to increase the font size of this title to have a better graphic rendering that captures the attention of the recipient. Column A contains most of the important (and longest) information in the invoice: buyer and seller data, item description and payment method. Column B contains the dates. In column C we will write the invoice number, the invoice date, the individual price of each item listed and the total amount due.
In column A lines 4, 5, 6 and 7, we enter the address (divided into two lines), telephone number, VAT number and tax code
In column B lines 4 and 5, write " DATA: " and " INVOICE NO .: " with bold text and align the text to the right. Columns 4 and 5 of column C are the spaces where to insert the date and the invoice number.
Finally, for the last part of the basic information, we will insert the text " Invoice A: " (in bold) in column A, row 9 . Rows 10, 11, 12 and 13 can be used to enter recipient data.
Then join the columns A and B in row 15 (select the two cells and then press the Merge button at the top) so as to write a title for the listed elements that can be listed in column A from row 16 to 30), with the escape dates (column B, lines 16-30). After joining columns A and B in row 15, give the cell a border on all sides (using the border button in the Font section of the Home tab), perhaps also using a light gray fill color or other color. Do the same also for cell C15.
In the first cell of row 15, write DESCRIPTION and align the text in the center. In C15, write PRICE, putting both in bold for a table header. We select the space from row 16 to row 30 of columns A and B to add a lower border. The cells of column C from 16 to 29 can instead be surrounded by a left and right border.
Highlight cell C30, give it borders around the entire cell and a gradient of color so that it stands out. On the left (then the B30), write TOTAL and align on the right.
This completes the invoice structure, with the possibility of adding another row depending on the number of products. which should look like this.
Similarly, highlight cells C16-30, and select the " Currency " option to format prices in Euros.
To automatically add all the individual amounts and make them reflect in the " Total " cell that we have created, select the cell (C31 in this example) and enter the following formula:
= SUM (C16: C29)
Now filling the price fields, the total cell will be automatically filled with the sum .
Continuing, insert the text " Payment method: " in A34 or other information depending on what you need, for example by entering the forms of payment accepted for the payment of the invoice or VAT exclusions or any other important notes.
To the right of the price column you could add the column, for the calculation of the total price with VAT included, putting in the first column the price without VAT or Taxable . To add 22% VAT, the total price column must have the following formula: = C16 * 1.22 . From the lower right corner, pull down to the total to extend the formula to the other cells. You can also add an E column with the VAT (22%) written on it and then use the formula on the D column of the type: = C16 * E16 + C16 .
For the finishing touch, don't forget to add a thank you to the bottom line.
READ ALSO: Free and complete electronic invoice programs
Creating an invoice in Excel is a relatively simple job, you just need to prepare a table, set some rules, add the required information and then, if you want to do a more technical job, also add some calculation automation. Obviously it is possible to use an invoice template already prepared by experts, download one and customize it. Some models are already available in Excel, opening a new sheet and then going to the File tab and looking for invoices in the search field. Other models are available on the Free Invoice Templates specialized site or on other sites with Excel models to download for free .
In this case, however, we go to see the steps to create a simple invoice on Excel from scratch (the guide also works with LibreOffice Calc ).
READ ALSO: 10 free invoicing and accounting programs for self-employed and small businesses
To make a simple invoice in Excel, the required information is:
- Seller details
- First name
- Address and postcode
- Telephone number
- VAT number P
- Fiscal Code
- Buyer data
- First name
- Address and postcode
- VAT
- Fiscal Code
- Invoice date
- Invoice number
- Description of service or product sold
- Item price (of a single product or service)
- Total amount due
- Payment method
Prepare the invoice sheet with Excel
Then open an empty Excel sheet and immediately delete the grids, to have a clean sheet to work on. To do this, go to the "Page Layout " tab and uncheck "Grid " in the "View" selection.Now we need to resize some of the columns and rows, to have more space to write longer information such as descriptions of the sales object. To resize a row or column, click on it and drag it with the mouse. By default, the rows are set to a height of 20 pixels and the columns are set to a width of 64 pixels. For an optimized configuration you can resize the columns and rows in this way:
- Row 1: 45 pixels
- Column A: 385 pixels
- Column B: 175 pixels
- Column C: 125 pixels
Line 1 must contain the name of the seller and the word "Invoice". The extra space allows you to increase the font size of this title to have a better graphic rendering that captures the attention of the recipient. Column A contains most of the important (and longest) information in the invoice: buyer and seller data, item description and payment method. Column B contains the dates. In column C we will write the invoice number, the invoice date, the individual price of each item listed and the total amount due.
Invoice preliminary information
In column A row 1, enter the name, with a larger font size (18 pt can go) and bolding the text so that it is clearer. In Column B Row 1, write "Invoice" to immediately make what this document is. as font size you can put 28pt and as writing style we can do it all in upper case and, perhaps, changing the color with a gray.In column A lines 4, 5, 6 and 7, we enter the address (divided into two lines), telephone number, VAT number and tax code
In column B lines 4 and 5, write " DATA: " and " INVOICE NO .: " with bold text and align the text to the right. Columns 4 and 5 of column C are the spaces where to insert the date and the invoice number.
Finally, for the last part of the basic information, we will insert the text " Invoice A: " (in bold) in column A, row 9 . Rows 10, 11, 12 and 13 can be used to enter recipient data.
Product or service details
To add the invoiced products and services, we need to add a few more lines to the sheet.Then join the columns A and B in row 15 (select the two cells and then press the Merge button at the top) so as to write a title for the listed elements that can be listed in column A from row 16 to 30), with the escape dates (column B, lines 16-30). After joining columns A and B in row 15, give the cell a border on all sides (using the border button in the Font section of the Home tab), perhaps also using a light gray fill color or other color. Do the same also for cell C15.
In the first cell of row 15, write DESCRIPTION and align the text in the center. In C15, write PRICE, putting both in bold for a table header. We select the space from row 16 to row 30 of columns A and B to add a lower border. The cells of column C from 16 to 29 can instead be surrounded by a left and right border.
Highlight cell C30, give it borders around the entire cell and a gradient of color so that it stands out. On the left (then the B30), write TOTAL and align on the right.
This completes the invoice structure, with the possibility of adding another row depending on the number of products. which should look like this.
Rules, formulas and automatisms
If the dates are written in column B, lines 16-29, select these cells and press on the " Number format " box in the " Number " section of the " Home " tab. Then choose the date format in numbers so that that field is always a correctly written date.Similarly, highlight cells C16-30, and select the " Currency " option to format prices in Euros.
To automatically add all the individual amounts and make them reflect in the " Total " cell that we have created, select the cell (C31 in this example) and enter the following formula:
= SUM (C16: C29)
Now filling the price fields, the total cell will be automatically filled with the sum .
Continuing, insert the text " Payment method: " in A34 or other information depending on what you need, for example by entering the forms of payment accepted for the payment of the invoice or VAT exclusions or any other important notes.
To the right of the price column you could add the column, for the calculation of the total price with VAT included, putting in the first column the price without VAT or Taxable . To add 22% VAT, the total price column must have the following formula: = C16 * 1.22 . From the lower right corner, pull down to the total to extend the formula to the other cells. You can also add an E column with the VAT (22%) written on it and then use the formula on the D column of the type: = C16 * E16 + C16 .
For the finishing touch, don't forget to add a thank you to the bottom line.
READ ALSO: Free and complete electronic invoice programs
