How to Create a Tally GST Invoice Format in Excel (with Easy Steps)

A template or a format of an invoice can be really handy and it saves a lot of time. In this article, we will show you 8 quick steps to create a tally GST invoice format in Excel.

What Is a Tally GST Invoice?

GST means Goods and Services Tax which is a value-added tax imposed on goods and services for domestic consumption.

There is some information that should be present in a tally GST invoice –

Company Name, Logo, Phone Number, Address, and GSTIN
Customer Name, Address, and Phone Number
Invoice number and Date of issuance
Applicable taxes such as – CGST, SGST, IGST, etc.
Amount to be paid by the customers
Signature from the in-charge
Lastly, any additional notes regarding company policies

We’ll incorporate this information into our tally GST invoice format. Here is the final snapshot of our invoice format.

How to Create a Tally GST Invoice Format in Excel: 8 Quick Steps

Step 1: Adding Invoice Header to Tally GST Invoice

In the very first step, we’ll create the top header of our invoice.

First, create the following fields at the top row of the invoice.
- Invoice No. – invoice number of the company.
- Customer ID – unique customer ID for the customers, this will help us to identify them later.
- Date – the date of issuance of the invoice.

Step 2: Adding Company Details to Tally GST Invoice Format

In the second step, we will add company details to the top left side of our invoice.

Then, we’ll add the company details to the invoice.
- Company Logo – Add the company logo to make it more meaningful.
- Company Name – Obviously, we need to add the company name.
- Address – Location of the company.
- Phone – Phone number of the company.
- GSTIN – The GST Identification Number of the company.

Step 3: Including Customer Details to Tally GST Invoice

For the third step, we will add customer details to the top right section of our invoice.

Afterward, we add the customer details to that invoice under the “Bill To” section with the following fields:
- Name – Name of the customer.
- Address – Location of the customer.
- Phone – Phone number of the customer.
- GSTIN – The GST Identification Number of the customer.

Step 4: Description of the Products

In this step, we will add the product details after the customer and company details section. Moreover, we will use the SUM function here.

Next, we will add the following columns –
- Serial Number – Serial number of the products.
- Product – Names of the products.
- SKU – Product codes.
- Amount – The number of units purchased.
- Unit Price – Price of a single unit.
- Total Price – We use a formula here that is Total Price = Amount * Unit Price.
Then, we will use the SUM function to get the total value.

To do that, we have typed the following formula in cell G16 and pressed ENTER.

=SUM(G13:G15)

This formula adds the prices of the 3 products.

Step 5: Addition of GST Taxes in Invoice Format

In this section, we will add the GST to our invoice.

To begin with, type the following formula in cell G17 and press ENTER.

=G16*0.12

This formula finds 12% of the $1,095.

Then, we find the CGST by typing the following formula in cell G18.

=G16*0.08

Here, we calculated the CGST as 8% of $1,095.

Step 6: Calculating Gross Total Using Formula

In this section, we will calculate the Gross Total for our invoice, which we will get by adding the GST values. Moreover, we will use ROUND with SUM functions to achieve this.

Then, type the following formula in cell G19 and press ENTER.

=ROUND(SUM(G16:G18),0)

Here, we add the taxes with our value of $1,095. After that, we round this figure using the ROUND function. As we want it to be rounded to the nearest integer, we have provided 0 in the formula.

Step 7: Automating Amount in Words Using VBA

We can type the Gross amount in words. However, we can easily do it using Excel VBA. In this step, we will use that to create a User Defined Function (UDF) to return numbers to words.

To begin with, press ALT+F11 to bring up the VBA window.
Then, from Insert >>> select Module. We’ll type our code here.

Then, type the following code.

Option Explicit
Function Number_To_Words(ByVal xNumber)
        Dim xDollars, xCents, xT
        Dim xPlace, xCount
        ReDim Place(9) As String
        Place(2) = " Thousand "
        Place(3) = " Million "
        Place(4) = " Billion "
        Place(5) = " Trillion "
 
        xNumber = Trim(Str(xNumber))
        xPlace = InStr(xNumber, ".")
        If xPlace > 0 Then
                xCents = xTens(Left(Mid(xNumber, xPlace + 1) & "00", 2))
                xNumber = Trim(Left(xNumber, xPlace - 1))
        End If
        xCount = 1
        Do While xNumber <> ""
                xT = xHundreds(Right(xNumber, 3))
                If xT <> "" Then xDollars = xT & Place(xCount) & xDollars
                If Len(xNumber) > 3 Then
                        xNumber = Left(xNumber, Len(xNumber) - 3)
                Else
                        xNumber = ""
                End If
                xCount = xCount + 1
        Loop
        Select Case xDollars
                Case ""
                        xDollars = "No Dollars"
                Case "One"
                        xDollars = "One Dollar"
                 Case Else
                        xDollars = xDollars & " Dollars"
        End Select
        Select Case xCents
                Case ""
                        xCents = ""
                Case "One"
                        xCents = " and One Cent"
                            Case Else
                        xCents = " and " & xCents & " Cents"
        End Select
        Number_To_Words = xDollars & xCents
End Function
 
Function xHundreds(ByVal xNumber)
        Dim xResult As String
        If Val(xNumber) = 0 Then Exit Function
        xNumber = Right("000" & xNumber, 3)
        If Mid(xNumber, 1, 1) <> "0" Then
                xResult = xDigit(Mid(xNumber, 1, 1)) & " Hundred "
        End If
        If Mid(xNumber, 2, 1) <> "0" Then
                xResult = xResult & xTens(Mid(xNumber, 2))
        Else
                xResult = xResult & xDigit(Mid(xNumber, 3))
        End If
        xHundreds = xResult
End Function
 
Function xTens(tt)
        Dim xResult As String
        xResult = ""
        If Val(Left(tt, 1)) = 1 Then
                Select Case Val(tt)
                        Case 10: xResult = "Ten"
                        Case 11: xResult = "Eleven"
                        Case 12: xResult = "Twelve"
                        Case 13: xResult = "Thirteen"
                        Case 14: xResult = "Fourteen"
                        Case 15: xResult = "Fifteen"
                        Case 16: xResult = "Sixteen"
                        Case 17: xResult = "Seventeen"
                        Case 18: xResult = "Eighteen"
                        Case 19: xResult = "Nineteen"
                        Case Else
                End Select
        Else
                Select Case Val(Left(tt, 1))
                        Case 2: xResult = "Twenty "
                        Case 3: xResult = "Thirty "
                        Case 4: xResult = "Forty "
                        Case 5: xResult = "Fifty "
                        Case 6: xResult = "Sixty "
                        Case 7: xResult = "Seventy "
                        Case 8: xResult = "Eighty "
                        Case 9: xResult = "Ninety "
                        Case Else
                End Select
                xResult = xResult & xDigit(Right(tt, 1))
        End If
        xTens = xResult
End Function
 
Function xDigit(dd)
        Select Case Val(dd)
                Case 1: xDigit = "One"
                Case 2: xDigit = "Two"
                Case 3: xDigit = "Three"
                Case 4: xDigit = "Four"
                Case 5: xDigit = "Five"
                Case 6: xDigit = "Six"
                Case 7: xDigit = "Seven"
                Case 8: xDigit = "Eight"
                Case 9: xDigit = "Nine"
                Case Else: xDigit = ""
        End Select
End Function

VBA Code Breakdown

First, We are creating a custom function named Number_To_Words.
Next, we define the variable types.
Then, we use the VBA Case statement to apply the word converter for our numbers.
Afterward, we create 3 more functions – xHundreds, xTens, and xDigit to get the hundred, tens, and single-digit values respectively.
Thus, this code works to convert numbers to words.

Then, Save and close the Module.
Afterward, type the following formula in cell G20.

=Number_To_Words(G19)

This formula converts the number to words as per the VBA code breakdown.

Step 8: Adding Notes and Footer Section

This is our last step and we’ll add the footer section in this segment.

We have added Notes for the customer.
Then, we have provided space to input the signature of the in-charge.
Next, we are adding the contact details of the company.
Lastly, we’re thanking the customer for this transaction.

Finally, these 8 steps will accumulate to this final tally GST invoice format.

Download Practice Workbook

Creating Tally GST Invoice.xlsm

Conclusion

We have shown you 8 easy steps of how to create a tally GST invoice format in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!

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

Get FREE Advanced Excel Exercises with Solutions!