Handle Merged Cells, Blank Rows, and Table Layouts When Converting Excel to JSON

Why Excel Layouts Break JSON Conversion

Excel is designed for visual presentation — human readability, not data interchange. Three common patterns cause problems:

• Merged cells — a single value displayed across multiple rows. Without handling, only the first row gets the value and the rest are blank.
• Blank separator rows — empty rows used as visual dividers. In JSON, empty rows become null objects or are omitted entirely.
• Multi-row headers — headers that span two or more rows. These need flattening into a single header row for JSON key names.

The converter handles all three automatically, producing clean JSON even from messy spreadsheets.

1. Merged Cell Handling

When an Excel cell is merged across multiple rows, only the top-left cell contains the value. Consider this sales spreadsheet:

Region   | Product  | Sales
North    | Widget A | 12000
         | Widget B | 8500
         | Widget C | 9200
South    | Widget A | 9500
         | Widget B | 7200

In the raw spreadsheet, "North" is only in the first row. The converter fills the merged value down, producing:

[
  {"region": "North", "product": "Widget A", "sales": 12000},
  {"region": "North", "product": "Widget B", "sales": 8500},
  {"region": "North", "product": "Widget C", "sales": 9200},
  {"region": "South", "product": "Widget A", "sales": 9500},
  {"region": "South", "product": "Widget B", "sales": 7200}
]

Every row now has a complete set of values, ready for data processing.

2. Blank Row Handling

Blank rows in Excel are often used as visual separators. The converter has two modes:

• Skip blanks (default): Completely empty rows are omitted from the JSON output. The row numbering in the array is sequential without gaps.
• Preserve as null: Blank rows become null entries in the array. This is useful when you need to compare row positions with the original spreadsheet.

For most use cases, skipping blanks produces cleaner JSON. Preserve nulls only when position tracking is critical.

3. Multi-Row and Styled Headers

Some Excel files use two header rows — one with a category like "Financial" and another with sub-headers like "Revenue" and "Cost". The converter:

• Treats the first row of the selected range as the JSON key names.
• Ignores font styles, colors, bold, and cell formatting — only the cell text value matters.
• Auto-detects whether the first row is a header (if it's visually distinct) or treats it as data.

For multi-row headers, the recommendation is to clean up the file first — use the inline table editor to merge or flatten the header rows into a single row before exporting.

Best Practices for Excel to JSON Conversion

• Unmerge in Excel first. If possible, use Excel's "Unmerge Cells" feature before converting. This gives you full control over how merged values are distributed.
• Use a single header row. A clean, single-row header produces the most predictable JSON. Avoid stacked or multi-row headers.
• Remove blank rows. Delete visual separator rows in Excel before conversion for the cleanest output.
• Check the table preview. The converter shows a live table view of your Excel data — review it before exporting to catch layout issues.
• Combine with other tools. Use CSV to JSON for simple tabular data, and Excel to JSON for files with complex layouts and merged cells.

Related Tools

Frequently Asked Questions