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.
Read More: How to Create a Tally VAT Invoice Format in Excel
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.
Read More: Tally Sales Invoice Format in Excel
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.
Read More: Proforma Invoice Format in Excel with GST
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.
Read More: How to Make GST Export Invoice Format in Excel
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.
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
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!
