Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Create Non GST Invoice Format in Excel (with Easy Steps)

Need to learn how to create a non-GST invoice format in Excel? GST means Goods and Services Tax which is a value-added tax imposed on goods and services for domestic consumption. A non-GST invoice excludes the above term. If you are looking for such unique tricks, you’ve come to the right place. By following this article, you will be able to make an invoice easily for your business.


Download Practice Files

You may download the following Excel workbook and PDF file for better understanding and practice yourself.


5 Steps to Create Non-GST Invoice Format in Excel

Here, we will discuss the ways of forming a non-GST invoice format thoroughly for a company named ABC Computer & Technology. After completing all of these steps, we will be able to make a bill for the supplied products of this company.


Step 01: Make Outline of Non-GST Invoice Format in Excel

At the very beginning, we need to have the basic inputs such as the Product List in the Product sheet and the Customer List in the Customer sheet like the following two figures.

  • Here, the Product List contains the HSN Code of the products, Product Name, and Unit Price in Columns B, C, and D respectively.
  • On the other hand, the Customer List includes the Customer ID, Company Name, Address, and Contact No.

Non GST Invoice Format in Excel

Non GST Invoice Format in Excel

  • After that, we created the necessary outline of the non-GST invoice for ABC Computer & Technology.
  • Here, we input the address and contact number of the company in cells in the D6:D8 range.
  • Also, we put the TIN number in cell E9.

Non GST Invoice Format in Excel

  • Now, it is time to give some elementary fixed inputs in the required places. So, we will fill up the indicated region first.
  • In cell C4, type 1 as Invoice No as it’s our first invoice.
  • Then, select cell F4.
  • After that, write down the following formula in the Formula Bar.
=TODAY()

The Today function returns the current date.

  • Later, press ENTER.

Non GST Invoice Format in Excel

Read More: Create GST Invoice Format in Excel (Step-by-Step Guideline)


Step 02: Create Drop-down List

Here, you will get to know the way to create a dropdown list here to change the values easily in the invoice. First of all, we will make a dropdown list of the ID numbers of the companies to whom the products will be supplied. Let’s see it in action.

  • At first, select cell C9 where you want to have the dropdown list.
  • Then, go to the Data tab.
  • After that, select Data Validation in the Data Tools group.

Non GST Invoice Format in Excel

  • Instantly, it opens the Data Validation dialog box.
  • Later, move to the Settings tab.
  • Then, select List from the drop-down list of Allow section.
  • Afterward, click on the upside arrow beside the Source box.

Non GST Invoice Format in Excel

  • Thus, it takes you to the Data Validation input box.
  • Then, select cells in the B5:B9 range in the Customer worksheet.
  • After that, click on the down-side arrow at the right of the box.

Non GST Invoice Format in Excel

  • Hence, it returns us to the Data Validation dialog box again.
  • Finally, click OK.

Non GST Invoice Format in Excel

  • Now, we successfully created the dropdown list in these cells.
  • For testing purposes, select cell C9 and click on the dropdown arrow icon.
  • Then, select R.C.1.3 from the list.

Non GST Invoice Format in Excel

  • Thus, we can see the Customer ID in our cell.

Non GST Invoice Format in Excel

Read More: GST State Code List in Excel (Detailed Analysis)


Step 03: Acquire Shipping Details

Now, we will use the ID number to look for the values of the Company Name in the Customer sheet and show the value in cell B6. It’s simple & easy, just follow along.

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

Here, $C$9 is the lookup value, Customer!$B$4:$E$9 is the table array where Customer! Is the sheet name, 2 is the column number of which we want the values and FALSE is for an exact match.
If sometimes the VLOOKUP function returns an error then the IFERROR function will convert it into a blank cell.

  • Later, press the ENTER key.

Non GST Invoice Format in Excel

Here, you got the Company Name A for the specific ID R.C.1.3.

  • Similarly, you can use the following two formulas for getting the Address and the Contact No of the company respectively.
=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

  • So, we can see that, we have the following Company Name, Address and  Contact No for the corresponding ID number R.C.1.3.

Non GST Invoice Format in Excel

  • And, when you change the ID number by using the dropdown list then the corresponding Company Name, Address, and  Contact No will be changed.

Non GST Invoice Format in Excel

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


Similar Readings


Step 04: Apply Formulas to Modify Non-GST Invoice Format in Excel

In this section, we’ll do some modifications in cells in the B13:G17 range. So, be with us.

  • Again, we’ll create drop-down lists for cells in the C13:C17 range just like we did in Step 02.

Non GST Invoice Format in Excel

  • Just the difference here is that we will select cells in the B5:B9 range in the Product sheet as Source.

Non GST Invoice Format in Excel

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

Non GST Invoice Format in Excel

  • After that, the selected HSN Code will appear in cell C13.

Non GST Invoice Format in Excel

  • At this moment, give other entries as well.

Non GST Invoice Format in Excel

  • Now, we will use the HSN Code to look for the values of the Product Name in the Product sheet and then have this value in cell D13.
  • At this time, select cell D13 and paste the formula below.
=IFERROR(VLOOKUP(C13,Product!$B$4:$D$9,2,FALSE),"")
  • Then, press ENTER.

Non GST Invoice Format in Excel

  • After that, drag down the Fill Handle icon up to cell D17 to get the remaining results.

Non GST Invoice Format in Excel

  • In this way, we’ll get the Product Names in the Product column.

Non GST Invoice Format in Excel

  • Similarly, you can get the Unit Price of the products in the Unit Price column.
  • To do this, select cell F13 and put the formula below.
=IFERROR(VLOOKUP(C13,Product!$B$4:$D$9,3,FALSE),"")
  • As always, hit the ENTER key.

Non GST Invoice Format in Excel

  • Later, give the respective quantity of the products in the Qty column.

Non GST Invoice Format in Excel

  • After that, select cell G13 and paste the formula below.
=E13*F13
  • Then, tap ENTER.

Non GST Invoice Format in Excel

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

  • At this moment, select cell D19 and write down the formula below.
=SUM(G13:G17)
  • Then, press ENTER.

Non GST Invoice Format in Excel

Here, we used the SUM function to sum up the amounts in the G13:G17 range.

  • To calculate VAT, select cell D20 and put the following formula.
=D19*7.5%
  • After that, press ENTER.

Non GST Invoice Format in Excel

  • Now, we’ll calculate the Total Amount. Simply, we’ll add the VAT with the Sales Amount.
  • At first, select cell D21 and paste the following formula.
=D19+D20
  • Lastly, press ENTER.

Non GST Invoice Format in Excel

Read More: How to Create GST Bill Format in Excel with Formula


Step 05: Save and Resume Invoice in Excel

Finally, we will use two VBA codes to refresh the invoice form for performing calculations for new data and then for saving the form.

  • First of all, jump to the Developer tab.
  • Secondly, select Visual Basic on the Code group.
  • Alternatively, press ALT+F11 to do the same task.

Applying VBA Code

  • Instantly, the Microsoft Visual Basic for Applications window opens.
  • Afterward, move to the Insert tab.
  • Later, select Module from the options.

Applying VBA Code

  • Immediately, it opens the Code Module.
  • Then, write down the following code in the Module 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

  • Then, repeat the above steps to create another module.
  • Later, write down the following code in the Module 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

  • Now, return to the Invoice worksheet.
  • Again, proceed to the Developer tab.
  • Then, click on Insert on the Controls group.
  • After that, select Button under the Form Controls section.

Applying VBA Code

  • Later, create a button in the region as shown in the image below. Give it a name Next.

Applying VBA Code

  • At this moment, right-click on the button.
  • After that, select Assign Macro from the context menu.

Applying VBA Code

  • Consequently, the Assign Macro dialog box opens.
  • After that, select the macro next_invoice.
  • Lastly, click OK.

Applying VBA Code

  • Similarly, create another button named Save.

Applying VBA Code

  • Next, assign the macro save_invoice to the button just like we did it before.

Applying VBA Code

  • Now, click on the Save button. And, you can get the invoice saved as a PDF.

Applying VBA Code

Note: This image is of the saved PDF version of Invoice No 1.

  • Then, click on the Next button.

Applying VBA Code

  • Thus, you can see that the Invoice No is automatically set to 2 and the values of the highlighted cells get vanished.

Applying VBA Code

Now, it is ready for further use.

Read More: Proforma Invoice Format in Excel with GST (Free Template)


Conclusion

This article provides easy and brief solutions to create a non-GST invoice format in Excel. Don’t forget to download the Template of the invoice. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

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

ExcelDemy
Logo