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.

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

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.

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.

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.

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.

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.

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.

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

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.

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

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.

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),"")

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

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

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

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.

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

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.

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

At this moment, give other entries as well.

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.

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

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

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.

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

After that, select cell G13 and paste the formula below.

=E13*F13

Then, tap ENTER.

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.

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.

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.

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. If you don’t see the Developer tab, then, follow this link to display the Developer tab on the ribbon.
Secondly, select Visual Basic on the Code group.
Alternatively, press ALT+F11 to do the same task.

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

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

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

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.

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

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

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

Similarly, create another button named Save.

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

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

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

Then, click on the Next button.

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

Now, it is ready for further use.

Download Practice Files

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

Non-GST Invoice Format.xlsm

Invoice1.pdf

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Jane D. Sanders Nov 1, 2022 at 5:07 PM

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?
Shahriar Nov 3, 2022 at 9:39 AM

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…