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!

**Table of Contents**hide

## 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.

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

- 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**.

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

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

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

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

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

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

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

- 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**

**How to Create GST Rental Invoice Format in Excel****How to Create GST Late Fees Calculator in Excel****Create Non GST Invoice Format in Excel (with Easy Steps)**

### 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.

- 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**.

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

- Similarly, type the following formula in cell
**F13**and press**Enter**.

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

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

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

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

`=E13*F13`

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

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

- Now, we will get the
**CGST Amount**by using the following formula:

`=G13*I13`

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

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

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

- Moreover, go to
**Insert**tab >>**Module**Option again.

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

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

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

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

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

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

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