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

If you are looking for some of the easiest ways to create Excel invoice format GST, then you are in the right place. GST means Goods and Services Tax which is a value-added tax imposed on goods and services for domestic consumption. By following this article, you will be able to make an invoice easily for your products.

Download Workbook


Procedures of Creating GST Invoice Format in Excel

Here, we will discuss the ways of forming a GST invoice format thoroughly for a fruit supplier company. After completing all of these steps, we will be able to make a bill for the supplied products of this company.


Step-01: Making an Outline of GST Invoice Format

1. First, we need to have the basic inputs such as the Product Details in the product sheet and the Company Details in the Customer sheet like the following two figures.

Excel invoice format GST

Excel invoice format GST

2. After that, we have created the basic outline of the GST invoice for “N” Fruit Supplier.

Excel invoice format GST

3. Now, it is the turn to give some basic fixed inputs in the required places. So, we will fill up the indicated region first.

making an outline

Type 1 as Invoice No, ON as State Code (Code for Ontario), and for having today’s date we have used the TODAY function.

making an outline

4. Now, we will move into the filling of the banking details.

making an outline

For this purpose, you can use your desired A/C Name and A/C No. (here, we have used Michael James and 0034567)

Excel invoice format GST

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


Step-02: Creating Dropdown List

Here, you will get to know the way to create a dropdown list here to change the values easily in the bill form.

1. Firstly, we will make a dropdown list for the GSTIN numbers of the companies to whom the products will be supplied.

Excel invoice format GST

➤ Select the cell B8 where you want to have the dropdown list
➤ Go to Data Tab >> Data Tools Group >> Data Validation Option

using formulas

After that, the Data Validation dialog box will pop up
➤ Select the List Option in the Allow box
➤ Type the following formula in the Source box and press OK

=Customer!$B$5:$B$10

Here, Customer! Is the sheet name and $B$5:$B$10 is the range containing the GSTIN numbers in that sheet for different companies.

using formulas

In this way, you will get the dropdown sign in your desired cell and now you can choose any of the numbers from this list.
We have selected the GSTIN number ONAAACXXXX5D1E5 for this example at first.

using formulas

After that, the selected number will appear in cell B8

Excel invoice format GST

2. Now, we will use the GSTIN number to look for the values of the Company Name in the Customer sheet and then have this value in the indicated region.

using formulas

➤ Type the following formula in the selected region

=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,2,FALSE),"")

Here, $B$8 is the lookup value, Customer!$B$5:$E$10 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 VLOOKUP returns an error then IFERROR will convert it into a Blank

Excel invoice format GST

➤ Press ENTER
Then, you will get the Company Name, A,  for the GSTIN number ONAAACXXXX5D1E5

using formulas

Similarly, you can use the following two formulas for getting the Area and the Contact No. of the company respectively.

=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,3,FALSE),"")

Excel invoice format GST

=IFERROR(VLOOKUP($B$8,Customer!$B$5:$E$10,4,FALSE),"")

using formulas

So, we can see that, we have the following Company Name, Area and  Contact No. for the corresponding GSTIN number ONAAACXXXX5D1E5

using formulas

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

using formulas

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


Similar Readings


Step-03: Using Excel Formulas to Modify GST Invoice Format

Here, we will use some formulas for making an automated GST invoice.

1. Type the HSN Codes of your products in the HSN Code

using formulas

2. 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 C13.

➤ Type the following formula in this cell

=IFERROR(VLOOKUP(B13,product!B5:F9,2,FALSE),"")

Here, B13 is the lookup value, product!B5:F9 is the table array where product! is the sheet name, 2 is the column number of which we want the values and FALSE is for an exact match.
If sometimes VLOOKUP returns an error then IFERROR will convert it into a Blank

using formulas

➤ Press ENTER
➤ Drag down the Fill Handle tool

using formulas

In this way, we will get the Product Names in the Product column

Excel invoice format GST

Similarly, you can use the following formula for getting the Unit Price of the products.

=IFERROR(VLOOKUP(B13,product!B5:F9,3,FALSE),"")

using formulas

3. Type the number of your products in the Qty

Excel invoice format GST

4. Now, we will get the prices by multiplying the Qty and Unit Price in the Amount column

=D13*E13

using formulas

5. You can get the CGST rates and the SGST rates by using the values of the HSN Codes by using the following formulas respectively

=IFERROR(VLOOKUP(B13,product!B5:F9,4,FALSE),"")

using formulas

=IFERROR(VLOOKUP(B13,product!B5:F9,5,FALSE),"")

Excel invoice format GST

6. Now, it is the turn to get the CGST Amount by using the following formula

=F13*H13

using formulas

Similarly, we have used the following formula for having the SGST Amount

=F13*J13

Excel invoice format GST

For calculating the Taxable Amount you can use the following formula

=I13+K13

Excel invoice format GST

7. Finally, we will calculate the Total Amount of these products.

By using the following formula, we will sum up the amounts of the products

=SUM(F13:F17)

Here, SUM will add up the values of the Amount column

using formulas

To get the total CGST Amount we will use the following formula

=SUM(I13:I17)

using formulas

For having the total SGST Amount you can use the following formula

=SUM(K13:K17)

using formulas

After having all of these amounts we will sum up them by using the following formula to get the Total Amount

=SUM(F19:K21)

using formulas

After performing all of these calculations we will get the following bill form

Excel invoice format GST

Read More: Excel Invoice Tracker (Format and Usage)


Step-04: Saving and Resuming Invoice Format in Excel

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

1. Go to Developer Tab >> Visual Basic Option

Excel invoice format GST

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

using formulas

After that, a Module will be created.

using formulas

 

➤ Write the following code for resuming the sheet

Sub resumeinvoice()

Range("C4").Value = Range("C4").Value + 1
Range("B13:B17").ClearContents
Range("D13:D17").ClearContents
Range("B9").ClearContents

End Sub

Here, this code will clear these ranges and add up 1 with the value in the cell

using formulas

➤ Type the following code for saving the code in your desired location

Sub savegst()

Set ws = Worksheets("Invoice")

ws.Range("A1:K27").ExportAsFixedFormat xlTypePDF, _
Filename:="C:\Users\Mima\Downloads\" & ws.Range("C4").Value, _
openafterpublish:=False

End Sub

Here, the Invoice is the sheet name and A1:K27 is the range of the sheet you want to save

using formulas

2. Go to Developer Tab >> Insert Group >> Button Option

Excel invoice format GST

Then, a plus sign will appear and drag down, and to the right side this sign

Saving and resuming

Now, the Macro name dialog box will open up

Saving and resuming

➤ Select the resumeinvoice macro name for creating a button for resuming the operation
➤ Press OK

Saving and resuming

After that, we have edited the name of the button as Resume

Excel invoice format GST

Similarly, you can create a Save button for saving the bill form as a PDF by using the savegst macro name.

Saving and resuming

3. After pressing the Save button the bill form will be saved as a pdf like below in your desired location

Excel invoice format GST

4. When you press the Resume button, the Invoice No will be increased by 1 and the values of the indicated boxes will be removed

Saving and resuming

After again giving the inputs in the indicated boxes of the previous figure you can get a new bill form like below

Excel invoice format GST

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


Conclusion

In this article, we have tried to cover the easiest ways to create Excel invoice format GST effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo