Cleaning a messy spreadsheet before you chart it

Charting tools get blamed for import failures that are almost always spreadsheet problems. Five fixes handle nearly all of them.

1. One header row, and only one

Two-level headers with merged cells above them are readable to humans and meaningless to software. Flatten them into single names like Revenue Q1 2026 before exporting.

2. Get the totals out of the data

A Total row inside the range gets charted as another category, producing a bar twice as tall as everything else. Keep totals in a separate area or let the chart compute them.

3. Numbers must be numbers

Currency symbols, thousands separators and stray spaces inside a cell turn it into text. Strip them and put the unit in the header instead — Revenue (€K), not €1,240 in every cell.

4. One row per observation

Wide layouts with a column per month are readable but awkward to chart. Long format — one row per category per period — imports cleanly into practically anything.

5. Kill the empty rows

Blank spacer rows used for visual separation become empty categories or break the range detection entirely. Use formatting for spacing, never empty rows.

• Flatten multi-level headers.
• Remove totals and subtotals from the data range.
• Strip symbols and separators from numeric cells.
• Prefer long format over wide.
• No blank spacer rows.

Ten minutes of this once turns every future chart into a paste. It is the highest-return data work most people never do.