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

How to Create GST Bill Format in Excel with Formula

Every company, shop, bank, or other financial organization needs a GST bill format to provide an invoice to their customer for every transaction. We can easily create a GST bill format in Microsoft Excel. In this article, we will learn how to create GST bill format in Excel with formula. Let’s get started!


Download Practice Workbook

You can download the free template from here.


Step-by-Step Procedures to Create GST Bill Format in Excel with Formula

There are 4 steps to create a GST bill format in Excel with formula. We will learn these steps serially in this article. If we want, we can also reduce or increase the number of steps according to our needs. Here we will learn to create a demo GST bill format with formula in Excel.


Step 1: Create Outline of GST Bill Format

  • First, we need to create a basic dataset of the products like the image below.

Creating Outline to Create GST Bill Format in Excel with Formula

  • Second, we need to create another dataset of the Company Details like the below one in another sheet.

Creating Outline to Create GST Bill Format in Excel with Formula

  • After that, we have to create the basic outline of the GST invoice for the seller. For example, we have created the basic outline for The Blue Door Book Store.

Creating Outline to Create GST Bill Format in Excel with Formula

  • Now, we will give some basic fixed inputs in the required places. So, we will fill up the indicated cells first.

Creating Outline to Create GST Bill Format in Excel with Formula

  • Furthermore, type 2 as Invoice No, ON as State Code (code for Ontario), and for having today’s date we have used the TODAY function.

  • Next, we will fill in the banking details.

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

Read More: Creating GST Purchase Order Format in Excel (Free Template)


Step 2: Make Dropdown List

Now we will create a dropdown list here to change the values easily in the bill form.

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

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • To begin with, select cell C9 where you want to have the dropdown list.
  • Then, go to the Data tab >> Data Tools Group >> Data Validation option.

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • As a result, the Data Validation dialog box will open.
  • Now, select the List option in the Allow field.
  • Next, select cells ($B$5:$B$10) in the Source box and press OK.

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

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • In this way, we will get the dropdown sign in our desired cell and now we can choose any of the numbers from this list. For example, we have selected the GSTIN number ONFSDCXXXX5D1ZR for this example at first.

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • After that, the selected number will appear in cell C9.

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • Furthermore, 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.

Making Dropdown List to Create GST Bill Format in Excel with Formula

  • Afterward, type the following formula in the selected region and press Enter:
=IFERROR(VLOOKUP($C$9,Customer!$B$5:$E$10,2,FALSE),"")
  • Consequently, you will get the Company Name, Z, for the GSTIN number ONFSDCXXXX5D1ZR.

Here, $C$9 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 the VLOOKUP function returns an error then the IFERROR function will convert it into a Blank.

  • Similarly, you can use the following formula and press Enter for getting the Area of the company:
=IFERROR(VLOOKUP($C$9,Customer!$B$5:$E$10,3,FALSE),"")

  • Now, type the following formula and press Enter in the following region to get the Contact No. of the company:
=IFERROR(VLOOKUP($C$9,Customer!$B$5:$E$10,4,FALSE),"")

  • As a result, we can see that we have the following Company Name, Area and Contact No. for the corresponding GSTIN number ONFSDCXXXX5D1ZR.

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

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


Similar Readings


Step 3: Apply Excel Formula to Modify GST Bill Format

Now we will use some Excel formulas for making an automated GST bill format.

  • First, type the Serial Numbers and HSN Codes of your products in the Serial and HSN Code columns.

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • 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.
  • Next, type the following formula in cell D13 and press Enter.
=IFERROR(VLOOKUP(C13,product!B5:F9,2,FALSE),"")

Here, C13 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 the VLOOKUP function returns an error then the IFERROR function will convert it into a Blank.

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • After that, drag the Fill Handle tool down. In this way, we will get the Book Names in the Book Name column.

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Similarly, type the following formula in cell F13 and press Enter.
=IFERROR(VLOOKUP(C13,product!B5:F9,3,FALSE),"")

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • After that, drag the Fill Handle tool down. In this way, we will get the Unit Prices in the Unit Price column.

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Subsequently, type the number of your products in the Qty column.

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Now, we will get the prices by multiplying the Qty and Unit Price in the Amount column.
=E13*F13

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • We can get the CGST rates by using the values of the HSN Codes by using the following formula:
=IFERROR(VLOOKUP(C13,product!B5:F9,4,FALSE),"")

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Similarly, we can get the SGST rates by using the values of the HSN Codes by using the following formula:
=IFERROR(VLOOKUP(C13,product!B5:F9,5,FALSE),"")

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Now, we will get the CGST Amount by using the following formula:
=G13*I13

Applying Excel Formula to Create GST Bill Format in Excel with Formula

  • Similarly, we have used the following formula for having the SGST Amount.
=G13*K13

  • To calculate the Taxable Amount we will use the following formula:
=J13+L13

  • By using the following formula, we will sum up the amounts of the products:
=SUM(G13:G17)

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

  • To get the total CGST Amount, we will use the following formula:
=SUM(J13:J17)

  • To sum the SGST Amount, we will use the following formula:
=SUM(L13:L17)

  • After having all of these amounts, we will sum up them by using the following formula to get the Total Amount.
=SUM(G19:L21)

  • Finally, after performing all of these calculations we will get the following bill form.

Read More: How to Remove GST from Total Amount in Excel (With Easy Steps)


Step 4: Save and Resume Bill Format in Excel

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

  • Firstly, go to the Developer tab >> Visual Basic editor.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • As a result, the Visual Basic Editor will open up.
  • Next, go to the Insert tab >> Module option.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • After that, a Module will be created.
  • Now, write the following code for resuming the sheet.
Sub resumeinvoice()
Range("D4").Value = Range("D4").Value + 1
Range("C13:C17").ClearContents
Range("E13:E17").ClearContents
Range("C9").ClearContents
End Sub

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

Using VBA Code to Create GST Bill Format in Excel with Formula

  • Moreover, go to Insert tab >> Module Option again.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • Then, type the following code for saving the code in your desired location.
Sub savegst()
Set ws = Worksheets("Invoice")
ws.Range("A1:L27").ExportAsFixedFormat xlTypePDF, _
Filename:="D:\Shipon\" & ws.Range("D4").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 VBA Code to Create GST Bill Format in Excel with Formula

  • Furthermore, go to Developer tab >> Insert Group >> Button option.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • As a result, a plus sign will appear, and drag down and right this sign like the following figure.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • Now, the Macro name dialog box will open up like the below one.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • Then, select the resumeinvoice macro name for creating a button for resuming the operation and press OK.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

  • Furthermore, we have edited the name of the button as Resume.

Saving and Resuming Bill Format to Create GST Bill Format in Excel with Formula

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

  • After pressing the Save button, the bill form will be saved as a PDF which will be displayed below in your desired location.

  • After pressing the Resume button, the Invoice No will be increased by 1 and the values of the indicated boxes will be removed.

  • Next, we have to give the inputs in the indicated boxes of the previous figure. Then, we can get a new bill form like the below one.

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


Things to Remember

  • We can increase or remove the number of steps to create a GST bill format according to our needs.
  • In order to save the bill on your pc using the VBA code, you have to clearly mention the path correctly.

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to create GST bill format in Excel with formula. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo