How to create shop sale invoice in Excel

1
141

There are so many ways to create invoice system for your business. You can buy a POS software or web application for this purpose. But if you own a small business and cannot afford to buy a software for your business invoicing purpose, you can create your own basic invoice system in Microsoft Excel. You can easily create invoice in Excel. follow this step by step guide to create invoice in Excel.

Create Invoice in Excel

To create your own billing invoice system in Microsoft Excel, follow there step by step instruction.

Create a new excel worksheet and open it. Create 3 sheets and name it as Products, Quantity, and Invoice.

Products:

in the products sheet, enter some products with product name and price on the product sheet.

invoice in excel

Quantity:

in the quantity sheet, simply add a column with numbers from 1 to 20 etc.

invoice in excel

Invoice:

the mainsheet which requires a lot of work is the invoice sheet. You need to do some work here. First of all, create some design for your billing invoice. Add your business name, address, contact number, company logo and some comments. Once you finish the design, its time to do the important piece of this task i.e. to add your invoice data to the invoice sheet. Follow these step by step instruction to complete this task.

Select 4 columns and name it as Item, Quantity, Unit Price and Amount. The Item will show the product name from the product sheet. The quantity cell will have to select the quantity of items from the quantity sheet. Quantity can also be added manually, but I have kept it automated. The unit price entered automatically when the product is entered. The total amount is the result of two cells Unit Price*Quantity.

invoice in excel

Connect product sheet with invoice

Yeah, it’s time to connect these two sheets with respect to generate a billing invoice. Select the first cell under the Item title and go to the DATA tab in the menu bar and click “Data Validation”.

invoice in excel

Click on the “Data validation” button and select “List” from the drop-down list under the settings tab.

invoice in excel

Download Microsoft Excel 2013 Activated

Click on the source box and select the range of rows from the products sheet. And click the OK button to save the changes.

invoice in excel

Now, check the invoice will have a drop down in the item cell where you can select your required product and add it to the invoice system.

invoice in excel

Repeat this process for the quantity cell and select the quantity column from the quantity sheet. This will allow you to select the sale quantity from the drop-down list.

invoice in excel

Set formula for Uint Price

Now select the Unit Price cell and enter the following formula in the formula bar and press Enter.

=IF(ISNA(VLOOKUP(C10,PRODUCTS!A$2:C$6,2,FALSE))=TRUE,””,VLOOKUP(C10,PRODUCTS!A$2:C$6,2,FALSE))

Note. The cell numbers can be different in your case so change it as per your cells number in your sheets.

invoice in excel

The total amount is a calculated output of two cells, Unit price, and quantity. To get the calculated value in the total amount cell, click on the total amount cell and type the following formula in the formula bar and press Enter button to save the changes and get the calculated value.

=IF(E10=””, “”, E10*D10)

Note. The cell numbers can be different in your case so change it as per your cells number in your sheets.

invoice in excel

To apply formula on all rows, select the first cell and drag the formula. Now create a grand total amount cell which will show a sum of all the total amounts. Select a blank cell under the Total amount column and enter the following formula and press Enter to save the changes.

=SUM(F10:F28)

Note. The cell range may be different in your case, so please change the cell numbers as per your sheets and cell numbers.

invoice in excel

Your invoice is ready now. You can create and print invoices using your own invoice system.

Also Read: How to create age calculator in Microsoft Excel