How to Create Non-GST Invoice Format in Excel (5 Steps)

Step 1 – Make Outline of Non-GST Invoice Format in Excel

  • Begin by setting up the basic inputs. You’ll need a Product List in one sheet and a Customer List in another. Here’s what each list should contain:
    • Product List (Columns B, C, and D):
      • HSN Code of the products
      • Product Name
      • Unit Price
    • Customer List:
      • Customer ID
      • Company Name
      • Address
      • Contact No.

Non GST Invoice Format in Excel

Non GST Invoice Format in Excel

  • Create the necessary outline for the non-GST invoice. enter the company’s address and contact number in cells D6:D8. Additionally, put the TIN number in cell E9.

Non GST Invoice Format in Excel

  • In cell C4, enter 1 as the Invoice No since it’s our first invoice.
  • Select cell F4 and enter the following formula in the Formula Bar:
=TODAY()

The Today function returns the current date.

  • Press ENTER.

Non GST Invoice Format in Excel

Read More: How to Create a Tally GST Invoice Format in Excel


Step 2 – Create a Drop-down List

  • To easily select values for the invoice, create a dropdown list of the ID numbers of the companies you’ll be supplying products to.
  • Select cell C9 where you want the dropdown list.
  • Go to the Data tab and choose Data Validation in the Data Tools group.

Non GST Invoice Format in Excel

  • In the Data Validation dialog box, move to the Settings tab.
  • Select List from the dropdown list in the Allow section.
  • Click the upside arrow next to the Source box to open the Data Validation input box.

Non GST Invoice Format in Excel

  • Select cells B5:B9 in the Customer worksheet.
  • Click the down-side arrow to return to the Data Validation dialog box.

Non GST Invoice Format in Excel

  • Finally, click OK to create the dropdown list.

Non GST Invoice Format in Excel

  • Test it by selecting cell C9 and clicking the dropdown arrow icon.
  • Choose R.C.1.3 from the list to see the Customer ID in the cell.

Non GST Invoice Format in Excel

  • We can see the Customer ID in our cell.

Non GST Invoice Format in Excel


Step 3 – Acquire Shipping Details

Company Name Lookup:

  • Select cell B6.
  • Enter the formula below:
=IFERROR(VLOOKUP($C$9,Customer!$B$4:$E$9,2,FALSE),"")

Here:

    • $C$9 is the lookup value (the specific ID).
    • Customer!$B$4:$E$9 is the table array (Customer sheet).
    • represents the column number for Company Name.
    • FALSE ensures an exact match.
  • If VLOOKUP returns an error, the IFERROR function converts it to a blank cell.
  • Press ENTER. You’ll see the Company Name (e.g., Company A) for the specific ID R.C.1.3.

Non GST Invoice Format in Excel

Address and Contact No Lookup:

  • Similarly, enter these formulas for Address and Contact No:
=IFERROR(VLOOKUP($C$9,Customer!$B$4:$E$9,3,FALSE),"")

Non GST Invoice Format in Excel

=IFERROR(VLOOKUP($C$9,Customer!$B$4:$E$9,4,FALSE),"")

Non GST Invoice Format in Excel

  • Now you have the corresponding Company Name, Address, and Contact No for the ID R.C.1.3.

Non GST Invoice Format in Excel

  • Changing the ID via the dropdown list will update these details.

Non GST Invoice Format in Excel

Read More: How to Make GST Export Invoice Format in Excel


Step 4 – Modify Non-GST Invoice Format

  • Dropdown Lists for Product Codes:
    • Create dropdown lists for cells C13:C17 (similar to Step 2).

Non GST Invoice Format in Excel

    • Use cells B5:B9 in the Product sheet as the source.

Non GST Invoice Format in Excel

  • We successfully created the dropdown list in these cells.
  • For testing purposes, select cell C13 and click on the dropdown arrow icon.
  • Select 501 from the list.

Non GST Invoice Format in Excel

  • The selected HSN Code will appear in cell C13.

Non GST Invoice Format in Excel

Non GST Invoice Format in Excel

  • Product Name Lookup:
    • In cell D13, enter:
=IFERROR(VLOOKUP(C13,Product!$B$4:$D$9,2,FALSE),"")
      • This retrieves the Product Name based on the selected HSN Code.
    • Press ENTER.

Non GST Invoice Format in Excel

  • Drag down the Fill Handle to fill D13:D17 with Product Names.

Non GST Invoice Format in Excel

  • We’ll get the Product Names in the Product column.

Non GST Invoice Format in Excel

  • Unit Price Calculation:
    • In cell F13, enter:
=IFERROR(VLOOKUP(C13,Product!$B$4:$D$9,3,FALSE),"")
      • This gets the Unit Price.
    • Press ENTER.

Non GST Invoice Format in Excel

    • Enter the respective quantities in the Qty column (column E).

Non GST Invoice Format in Excel

    • In cell G13, calculate the amount:
=E13*F13
    • Press ENTER.

Non GST Invoice Format in Excel

Note: Here, it multiplies the quantity with the unit price to get the amount.

  • Total Amount and VAT:
    • In cell D19, sum the amounts (G13:G17):
=SUM(G13:G17)
    • Press ENTER.

Non GST Invoice Format in Excel

    • For VAT (7.5%), use cell D20:
=D19*7.5%
    • Press ENTER.

Non GST Invoice Format in Excel

    • Finally, calculate the Total Amount in cell D21:
=D19+D20
    • Press ENTER.

Non GST Invoice Format in Excel

Read More: How to Create Proforma Invoice for Advance Payment in Excel


Step 5 – Save and Resume Invoice in Excel

Refresh the Invoice Form:

  • Open the Developer tab. If it’s not visible, follow the instructions to display it on the ribbon.
  • Select Visual Basic in the Code group or press ALT+F11.

Applying VBA Code

  • The Microsoft Visual Basic for Applications window will open.
  • Move to the Insert tab and choose Module.

Applying VBA Code

  • In the Code Module, enter the following code to go to the next invoice:
Sub next_invoice()
Range("C4").Value = Range("C4").Value + 1
Range("C13:C17").ClearContents
Range("E13:E17").ClearContents
Range("C9").ClearContents
End Sub

Applying VBA Code

  • Repeat the steps to create another module.
  • In the new module, write this code to save the invoice:
Sub save_invoice()
Set ws = Worksheets("Invoice")
ws.Range("A1:G27").ExportAsFixedFormat xlTypePDF, _
Filename:="Invoice" & ws.Range("C4").Value, _
openafterpublish:=False
End Sub

Applying VBA Code

  • Create Buttons for Next and Save:
    • Return to the Invoice worksheet.
    • Go to the Developer tab again.
    • Click Insert in the Controls group.
    • Choose Button under the Form Controls section.

Applying VBA Code

  • Create a button in the designated region (as shown in the image). Name it Next.

Applying VBA Code

  • Right-click the button and select Assign Macro.

Applying VBA Code

  • Assign the macro next_invoice.

Applying VBA Code

  • Similarly, create another button named Save.

Applying VBA Code

  • Assign the macro save_invoice to the Save button.

Applying VBA Code

  • Save as PDF:
    • Click the Save button. The invoice will be saved as a PDF.

Applying VBA Code

  • Note: The image you see is the saved PDF version of Invoice No. 1.

Applying VBA Code

  • Click the Next button to automatically set the Invoice No. to 2 and clear the values in the highlighted cells.

Applying VBA Code

Read More: Proforma Invoice Format in Excel with GST


Download Practice Files

You can download the practice workbook from here:


Related Articles

<< Go Back to Excel Invoice Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

2 Comments
  1. I maintain a workbook for my little business. In column H, there are names of my representatives. At the end of this column, i count the numbers of representatives by count function. Like, if they are A,B,X,Y the output will be 4. But i get 0. I introduced this field recently.but cannot get the result.what should i do?

  2. Hello Jane,
    Hope this article is useful for you. I’ve got your problem. The main reason behind it is using the COUNT function. The COUNT function cannot count the Text values. So, you’ve to use the COUNTA function in this case. You may download the workbook for a better understanding. See the following image.

    Here, in cell B10, we can see the total count as 0 and in cell C10, the total count is 5. Because in the left cell, we used the COUNT function which is unsuccessful to retrieve the total number of sales reps. But, the COUNTA function in the right cell gives us the right result.
    The formula we used in cell B10 is the following.
    ="Total: "&COUNT(B5:B9)

    And the formula in cell C10 is given below.
    ="Total: "&COUNTA(C5:C9)

    That’s all from me on this topic. Happy Excelling…

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo