Convert GST JSON to Table Format - Easy Excel Conversion 2026

Why Convert GST JSON to Table Format?

GST JSON files from the portal contain structured but nested data that’s hard to read directly. Converting to table format (Excel spreadsheet) transforms complex JSON into clean, organized rows and columns that you can:

• ✅ Sort and filter by invoice date, amount, customer
• ✅ Calculate totals using Excel formulas
• ✅ Create pivot tables for analysis
• ✅ Share easily with accountants and auditors
• ✅ Reconcile with your books of accounts
• ✅ Generate reports and charts

What is Table Format?

Table format means organizing data in rows and columns where:

• Each row = One invoice or transaction
• Each column = One data field (date, amount, GSTIN, etc.)
• Headers = Column names in first row
• Filters = Enabled for quick sorting

Example table structure:

Invoice No	Date	Customer GSTIN	Taxable Value	IGST	CGST	SGST	Total
INV001	01-03-2024	29AABCU1332L000	50000	9000	0	0	59000
INV002	02-03-2024	27AABCV1332L000	30000	0	2700	2700	35400

How to Convert GST JSON to Table Format

Method 1: Free Online Converter (Easiest)

Our GST JSON to Excel converter automatically converts JSON to table format:

Steps:

1. Download JSON from GST portal (GSTR-1, GSTR-2B, or GSTR-2A)
2. Visit our converter
3. Upload your JSON file
4. Automatic processing - Tool extracts and structures data
5. Download Excel with ready-to-use tables

What You Get:

✅ Clean table layout with proper headers
✅ Multiple sheets (B2B, B2CL, B2CS, CDN, etc.)
✅ Formatted columns (dates, amounts, GSTIN)
✅ Filters enabled on all headers
✅ Summary calculations included

Convert JSON to Table Now →

Method 2: Excel Power Query

For users comfortable with Excel:

Steps:

1. Open Excel → Data tab
2. Click Get Data → From File → From JSON
3. Select your GST JSON file
4. Power Query Editor opens
5. Click on “List” or “Record” to expand
6. Right-click columns → Expand to New Rows
7. Repeat for nested data
8. Click Close & Load

Limitations:

• Requires understanding of JSON structure
• Time-consuming for nested data
• Needs manual column selection
• Complex for users unfamiliar with Power Query

Method 3: Google Sheets Import

Not recommended for sensitive GST data as it requires uploading to Google servers.

Method 4: Python/Programming

For developers:

import json
import pandas as pd

with open('gstr1.json', 'r') as f:
    data = json.load(f)

# Extract B2B invoices
rows = []
for entry in data.get('b2b', []):
    ctin = entry['ctin']
    for inv in entry['inv']:
        for item in inv['itms']:
            rows.append({
                'Customer GSTIN': ctin,
                'Invoice No': inv['inum'],
                'Date': inv['idt'],
                'Taxable Value': item['itm_det']['txval'],
                'IGST': item['itm_det']['iamt'],
                'CGST': item['itm_det']['camt'],
                'SGST': item['itm_det']['samt']
            })

df = pd.DataFrame(rows)
df.to_excel('gst_table.xlsx', index=False)

Requirement: Python installation and programming knowledge.

What Tables Are Created from GST JSON?

GSTR-1 JSON Tables

When you convert GSTR-1 JSON to table format, you get these sheets:

1. B2B Table (Business to Business)

Column	Description
Customer GSTIN	Buyer’s 15-digit GSTIN
Customer Name	Buyer’s legal name
Invoice Number	Your invoice number
Invoice Date	Date of invoice
Invoice Value	Total invoice value with tax
Place of Supply	State code
Reverse Charge	Y/N
Invoice Type	R/SEWP/SEZWP/DE
HSN/SAC	Product/service code
Taxable Value	Value before tax
GST Rate	Tax rate %
IGST Amount	Inter-state GST
CGST Amount	Central GST
SGST Amount	State GST
Cess Amount	Cess if applicable

2. B2CL Table (B2C Large)

Similar columns minus Customer GSTIN (as buyers are unregistered).

3. B2CS Table (B2C Small)

Consolidated data by tax rate and state:

Supply Type	Place of Supply	Type	Rate	Taxable Value	IGST	CGST	SGST
INTRA	29	OE	18	500000	0	45000	45000

4. CDN Table (Credit/Debit Notes)

Customer GSTIN	Note Type	Note Number	Note Date	Original Invoice	Reason	Value	Tax Amounts

GSTR-2B JSON Tables

1. B2B Purchases Table

Supplier GSTIN	Supplier Name	Invoice No	Date	Value	Taxable Value	IGST	CGST	SGST	ITC Eligible

2. CDN (Credit/Debit Notes) Table

Purchase-side notes with similar structure.

3. ISD Table (Input Service Distributor)

ISD GSTIN	Document Number	Date	ITC IGST	ITC CGST	ITC SGST

4. IMPG Table (Import of Goods)

Port Code	Bill of Entry No	Date	Taxable Value	IGST	Cess

Customizing Table Columns

After converting JSON to Excel table, you can customize:

Add Calculated Columns

Total Tax Column:

=SUM(IGST + CGST + SGST + Cess)

Total Invoice Value:

=Taxable_Value + Total_Tax

Month Extraction:

=TEXT(Invoice_Date, "MMM YYYY")

Hide Unnecessary Columns

Right-click column header → Hide for columns you don’t need.

Format Columns

• Dates: Format → Date → DD-MM-YYYY
• Amounts: Format → Currency → ₹ (INR)
• GSTIN: Format → Text

Apply Conditional Formatting

Highlight high-value invoices:

1. Select Value column
2. Home → Conditional Formatting → Highlight Cell Rules
3. Greater Than → Set threshold (e.g., ₹1,00,000)

Using Excel Table Features

Once you have tabular data:

1. Apply Excel Table Format

1. Select all data (Ctrl+A)
2. Home → Format as Table
3. Choose style
4. Check “My table has headers”

Benefits:

• Auto-filters on headers
• Structured references in formulas
• Auto-expand when adding rows
• Banded rows for readability

2. Enable Filters

If not auto-enabled:

1. Select header row
2. Data → Filter
3. Click dropdown arrows to filter

3. Sort Data

Click column header dropdown:

• Sort A to Z (ascending)
• Sort Z to A (descending)
• Sort by Color
• Custom Sort

4. Create Pivot Tables

Analyze large datasets:

1. Select data → Insert → PivotTable
2. Drag fields to areas:
   • Rows: Customer GSTIN or Month
   • Values: Sum of Taxable Value
   • Filters: Invoice Type or HSN

5. Use VLOOKUP for Reconciliation

Match invoice numbers between your books and GST table:

=VLOOKUP(A2, GST_Table, 4, FALSE)

Where A2 is your invoice number, GST_Table is converted data, 4 is column index.

Benefits of Table Format Over JSON

Aspect	JSON File	Table Format (Excel)
Readability	Complex nested structure	Clean rows and columns
Analysis	Requires programming	Excel formulas and pivot tables
Filtering	Not possible	Easy with filters
Sharing	Needs conversion	Everyone understands Excel
Editing	Risk breaking structure	Safe cell-level edits
Formulas	Not applicable	SUM, AVERAGE, VLOOKUP, etc.
Charts	Can’t create	Easy chart generation
Printing	Not formatted	Print-ready

Common Table Formatting Issues and Fixes

Issue 1: Dates Show as Numbers

Cause: Excel interprets date strings as numbers

Fix:

1. Select date column
2. Format Cells → Date → DD-MM-YYYY
3. Or use formula: =DATEVALUE(A2)

Issue 2: GSTIN Shows in Scientific Notation

Cause: Excel treats 15-digit GSTIN as number

Fix:

1. Format column as Text before importing
2. Or add apostrophe: '29AABCT1332L000

Issue 3: Decimal Places Missing in Amounts

Cause: Currency formatting not applied

Fix:

1. Select amount columns
2. Format → Number → 2 decimal places

Issue 4: Nested Items Not Separated

Cause: Multiple items in one invoice not split

Fix: Use our converter tool which automatically creates one row per item.

Advanced Table Analysis Techniques

1. Customer-Wise Sales Summary

Create pivot table:

• Rows: Customer GSTIN
• Values: Sum of Taxable Value, Count of Invoices
• Sort by total value descending

2. Month-Wise Trend Analysis

Add helper column for month:

=TEXT(Invoice_Date, "MMM-YYYY")

Create pivot table:

• Rows: Month
• Values: Sum of Taxable Value
• Insert chart: Line chart

3. GST Rate-Wise Breakdown

Pivot table:

• Rows: GST Rate
• Values: Sum of Taxable Value, Sum of Tax Amount

4. Top 10 Customers

1. Sort by Taxable Value descending
2. Filter top 10 rows
3. Create chart

5. State-Wise Supply Analysis

Pivot table:

• Rows: Place of Supply (state code)
• Values: Sum of Invoice Value
• Insert map chart (if using Excel 365)

Exporting Table to Other Formats

From Excel table, you can export to:

PDF

File → Export → Create PDF

CSV

File → Save As → CSV (Comma delimited)

Google Sheets

Upload Excel file to Google Drive

Accounting Software

Most accounting software can import Excel/CSV tables

Tips for Working with GST Tables in Excel

1. Freeze header row: View → Freeze Panes → Freeze Top Row
2. Use named ranges: Select data → Define Name
3. Protect sheets: Review → Protect Sheet (prevent accidental edits)
4. Add auto-totals: Use =SUBTOTAL() for filtered sums
5. Save templates: Save formatted Excel as template for future use
6. Use data validation: Prevent invalid entries in manual columns
7. Conditional formatting: Highlight errors or duplicates
8. Split panes: View multiple sections of large tables

Free vs Paid JSON to Table Converters

Feature	Our Free Tool	Paid Tools
Cost	Free forever	₹500-₹3000/month
Table creation	Automatic	Automatic
Multiple sheets	Yes	Yes
Custom columns	Standard GST columns	Sometimes customizable
File size limit	Up to 50MB	Usually 10-20MB
Privacy	100% local processing	Often server upload
Updates	Always latest	Version dependent
Support	Community	Paid support

Frequently Asked Questions

Can I convert multiple JSON files to one table?

Our tool processes one file at a time. To combine multiple periods, convert each separately then use Excel’s “Consolidate” feature or copy-paste into one sheet.

Will formulas be included in the converted table?

Basic sum formulas are included in summary sections. You can add your own formulas after conversion.

Can I edit the table after conversion?

Yes! Once in Excel, you can freely edit cells, add columns, delete rows, etc. Just keep a backup of original JSON.

What if my JSON has thousands of invoices?

Our tool handles up to 50MB files. Excel can display 1 million+ rows, so even very large files work fine.

Does the tool create charts automatically?

No, but with table format data, you can easily create charts yourself: Select data → Insert → Chart.

Can I convert GSTR-3B JSON to table?

GSTR-3B is typically a summary form, not detailed invoices. Our tool is optimized for GSTR-1, GSTR-2B, and GSTR-2A which contain transactional data.

Conclusion

Converting GST JSON to table format transforms complex, nested data into user-friendly Excel spreadsheets that you can analyze, filter, and share easily.

Best Method:
Use our free GST JSON to Excel converter for instant table creation with:

• ✅ Proper column headers
• ✅ Clean row structure
• ✅ Multiple sheets for different sections
• ✅ Formatted dates and amounts
• ✅ Filters enabled
• ✅ Summary calculations

Table format enables:

• Quick reconciliation
• Easy analysis with pivot tables
• Professional reports
• Simple sharing with teams
• Excel formula capabilities

Convert Your GST JSON to Table Now →

---

Related Articles:

• How to Open GSTR-1 JSON Files
• GSTR-1 JSON Structure Explained
• Extract HSN from GST JSON