In this article, I will show you how to create automatic invoices in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Below is a photo of the automatic invoice I will introduce to you. If you choose Client nr from the E6 cell list, Excel will automatically fill in the customer information. If you select a product from the list of cells A13 to A31, Excel will automatically fill in the product information.
How to create invoices automatically in Excel
1. On the Products page, enter product information.
2. On the Invoice page, select cells A13 through A31.
3. On the Data tab, in the Data Tools group, click Data Validation.
4. Under Allow, click List.
5. Click Source and select the area A2:A5 on the Products page.
6. Manually change 5 to 1048576 (or any other large number) to include more cells. Now you can add as many new products as you want.
7. Click OK.
8. Select cell B13 and enter the following formula.
Explanation: If cell A13 is empty, the formula returns an empty string. Otherwise, the VLOOKUP function will look for Product nr (1001) in the leftmost column of the range $A:$C (columns A to C) on the Products page and return the value in the same row from the second column (col_index_num is specified). set to 2).
9. Select cell C13 and enter the following formula.
Explanation: This formula is almost the same as the one above. This time it returns the value in the same row from the third column (col_index_num is set to 3).
10. Select cell E13 and enter the following formula.
Explanation: If cell A13 is empty, the formula returns an empty string. Otherwise, it will return Price and Quantity.
11. To copy the formula to other cells, select the range B13:E13 and drag it down to row 31. Use Format Painter to restore the formatting.
In addition, you can also view many other excel articles here.
