Preparing data in Google Sheets for data visualizations

The rules every sheet must follow

Before creating any chart, make sure your dataset follows these basic rules:

Use the first row as the header row

The first row defines the column names for the dataset.

What this means in practice:

• Every column name is part of the chart setup

• Column names must match exactly when referenced later. It means: you must use the exact same column name everywhere.

• Header names are case-sensitive

Best practice

• Keep header names short and clear

• Avoid changing names after a chart has been configured

• Remove extra spaces at the beginning or end

Good example

Use the page language for column names

Column names are not just technical labels. They may appear directly in the chart, for example:

• as axis titles (X and Y axis)

• in the legend

• in tooltips

What this means in practice

Always use the same language as the page content.

Example

• If your page is in English: City, Average price (€/m²)

• If your page is in German: Stadt, Durchschnittspreis (€/m²)

Why this matters

• Chart labels are visible to users

• Mixed languages look inconsistent and unprofessional

• It ensures clarity and better user experience

Make every header unique

Each column needs its own unique name.

Bad

Good

Treat every row after the header as data

There is no special handling for helper rows.

That means you should not add:

• totals rows

• note rows

• separator rows

• unit rows

Bad

Good

Do not merge cells

Merged cells may make a sheet look cleaner, but they break a reliable table structure.

Use one value per cell and keep the layout simple.

Keep each column consistent

Each column should contain one data type only.

Possible imported types are:

• number

• string

• boolean

• mixed

• unknown

For editors, the important rule is simple:

• text columns should stay text columns

• number columns should stay number columns

• avoid mixing both in the same column

How values are interpreted

The backend reads cells based on their actual value type.

What counts as a real metric

Metrics must always be stored as real numeric cells.

This matters because only numeric columns can be aggregated. Supported aggregations include:

• sum

• average

• maximum

• minimum

• count

• count distinct

Avoid in metric columns

• n/a

• tbd

• -

• numbers stored as text

Sort your sheet manually when needed

There is no automatic sorting.

Important behavior:

• table charts keep the original row order

• aggregated charts preserve the first-seen order

So if the order matters, sort the data in Google Sheets before using it. Read more about sorting.

Use real Google Sheets files (not just uploaded Excel files)

When preparing data for charts, it is important to ensure that your file is a true Google Sheets file, not just an uploaded Excel file.

If you upload an .xlsx file to Google Drive and open it, it may still remain an Excel file—even though it looks similar in the interface. This can lead to issues with data visualization components.

How to check your file type

• Look at the top of the document

• If you see a green badge with “XLSX”, the file is still an Excel file

How to convert to Google Sheets

You have two reliable options:

Option 1: Save as Google Sheets (recommended)

• Open the file in Google Drive

• Click File → Save as Google Sheets

• A new, fully compatible Google Sheets file will be created

Option 2: Import the Excel file into Google Sheets

• Create a new Google Sheets file

• Go to File → Import

• Upload your Excel file and choose how to import the data

Editor checklist

Before you use a sheet for a chart, confirm:

• the file is a Google Sheets file (not an uploaded Excel file)

• no “XLSX” badge is visible at the top of the document

• one header row only

• all headers are unique

• no merged cells

• no total or note rows

• consistent value types per column

• numeric metrics are real numbers

• the row order is already correct

Quick tip

If you see an “XLSX” badge, convert the file via:

• File → Save as Google Sheets

This ensures full compatibility with chart components and prevents data issues.