Your CSV file won't open in Google Sheets. Or worse — it opens, cuts off halfway through, and you only notice later when a row count doesn't add up.
This is a real problem, and it hits at predictable thresholds. Here's what you're up against, and five practical ways to deal with it.
Why Google Sheets Struggles With Large CSV Files
Google Sheets has two hard limits that affect large CSV imports:
| Limit | Cap |
|---|---|
| Cell count | 10 million cells per spreadsheet |
| File upload size | 100 MB |
| Rows per sheet | ~250,000 rows (varies with columns) |
The cell count limit is the one that catches people off guard. A file with 500,000 rows and 25 columns hits 12.5 million cells — over the limit before you even open it. The math is: rows × columns = cells.
When you exceed these limits, you get one of three outcomes:
- Upload fails with a vague "too large" error
- Import succeeds but silently truncates your data — this is the dangerous one
- Google Sheets crashes or becomes unresponsive
The good news: there are reliable workarounds for each scenario.
Method 1: Split the CSV Into Smaller Chunks
The fastest fix for most situations. Divide your file into pieces small enough for Google Sheets to handle, then import each chunk into a separate sheet.
How to split on Mac:
# Split into files of 50,000 rows each (keeping the header)
split -l 50000 yourfile.csv chunk_
This creates chunk_aa, chunk_ab, chunk_ac, etc. You'll need to add the header row back to each chunk:
head -1 yourfile.csv > header.csv
for f in chunk_*; do cat header.csv "$f" > "${f}_with_header.csv"; done
Or use Python (cleaner and cross-platform):
import pandas as pd
df = pd.read_csv('yourfile.csv')
chunk_size = 50000
for i, chunk in enumerate(range(0, len(df), chunk_size)):
df[chunk:chunk + chunk_size].to_csv(f'chunk_{i+1}.csv', index=False)
This approach preserves headers automatically and is easier to adjust.
When to use this:
- Your file is between 100 MB and a few GB
- You need the full dataset in Google Sheets
- You're comfortable with multiple tabs and can use IMPORTRANGE to combine views
Watch your row count before splitting. A file with 200,000 rows and 40 columns = 8 million cells per sheet — still under the limit. A file with 200,000 rows and 60 columns = 12 million cells — over the limit. Calculate first, then decide how small each chunk needs to be.
Method 2: Reduce the File Before Importing
Often you don't need every column in your CSV. Trimming unused columns before import can cut your cell count dramatically.
Remove unused columns on the command line:
# Keep only columns 1, 3, and 5 from a CSV
cut -d',' -f1,3,5 yourfile.csv > trimmed.csv
With Python (safer for CSVs with quoted commas):
import pandas as pd
df = pd.read_csv('yourfile.csv', usecols=['date', 'revenue', 'channel'])
df.to_csv('trimmed.csv', index=False)
Other things worth stripping out:
- Empty rows (
df.dropna(how='all')) - Duplicate rows (
df.drop_duplicates()) - Columns you'll never use in your analysis
A raw export from a CRM or ad platform often includes 30–50 columns when your actual analysis needs 8–12. Cutting down before import is the quickest win and keeps your sheet performant.
Method 3: Import With IMPORTDATA (For URLs)
If your CSV lives at a public URL — an API endpoint, a published Google Sheet export, a public dataset — you can use Google Sheets' IMPORTDATA formula instead of uploading the file directly.
=IMPORTDATA("https://example.com/your-data.csv")
This fetches the data at request time and streams it into the sheet. It still respects the 10 million cell limit, but it bypasses the 100 MB upload restriction, which helps if your file is large but not over the cell limit.
Limitations of IMPORTDATA:
- Only works with publicly accessible URLs (no authentication)
- Refreshes automatically every few hours — good for live data, bad if you want a static snapshot
- Slow for very large files
- Still subject to the cell count limit
For private data or local files, this method doesn't apply.
Method 4: Use BigQuery + Connected Sheets
This is the right solution if you're regularly working with datasets over 100,000 rows and need full analysis capability. Google Sheets connects directly to BigQuery, letting you run queries on datasets with billions of rows without hitting any cell limits.
How it works:
- Upload your CSV to Google BigQuery (free up to 10 GB storage / 1 TB queries per month)
- In Google Sheets: Data → Data connectors → Connect to BigQuery
- Select your table, write a query, and pull results into Sheets
The query runs server-side. Your sheet only sees the results — not the full dataset — so there's no cell count problem regardless of how large the underlying data is.
When BigQuery makes sense:
- You work with datasets over 500,000 rows regularly
- You need to run SQL queries rather than just viewing raw data
- You're comfortable with SQL or willing to learn it
For most people importing monthly reports or ad exports, this is overkill. But if you're hitting Google Sheets limits every week, BigQuery + Connected Sheets is worth a two-hour setup investment.
Method 5: Pre-Filter With Python Before Importing
If you only need a slice of your data — the last 30 days, a specific region, a particular product category — filter before you import.
import pandas as pd
df = pd.read_csv('yourfile.csv', parse_dates=['date'])
# Keep only last 30 days
recent = df[df['date'] >= pd.Timestamp.now() - pd.Timedelta(days=30)]
recent.to_csv('filtered.csv', index=False)
This is the most targeted approach. Instead of importing 500,000 rows and filtering inside Sheets (which is slow), you reduce the dataset to exactly what you need first.
Useful filter patterns:
# Filter by value
df[df['region'] == 'North America'].to_csv('output.csv', index=False)
# Filter by date range
mask = (df['date'] >= '2026-01-01') & (df['date'] <= '2026-03-31')
df[mask].to_csv('q1_data.csv', index=False)
# Sample a large file for exploration
df.sample(n=10000, random_state=42).to_csv('sample.csv', index=False)
For first-pass exploration of a massive dataset, the sample approach is underrated — pull 10,000 random rows, build your analysis logic, then apply it to the full dataset with BigQuery or a script.
Which Method to Use
| Your situation | Best method |
|---|---|
| File under 100 MB, just too many cells | Trim unused columns (Method 2) |
| File over 100 MB, need everything in Sheets | Split into chunks (Method 1) |
| CSV at a public URL | IMPORTDATA formula (Method 3) |
| Hundreds of thousands of rows, recurring analysis | BigQuery + Connected Sheets (Method 4) |
| Only need a date range or segment | Pre-filter with Python (Method 5) |
| Normal-sized files, just tedious to import | CSVtoSheets automates the double-click workflow |
For the day-to-day — exporting from a CRM, pulling ad reports, downloading transaction data — your CSV is probably well under Google Sheets' limits and the problem is friction, not size. CSVtoSheets turns that 13-click import process into a single double-click that opens any CSV directly in Google Sheets on Mac.
Preventing the Problem: Know Your File Size Before Importing
A quick check before importing saves the headache:
# Check row count
wc -l yourfile.csv
# Check file size
ls -lh yourfile.csv
# Check column count (from first row)
head -1 yourfile.csv | tr ',' '\n' | wc -l
Multiply rows × columns to get your cell count. If it's above 8 million, plan for one of the methods above — you're close enough to the 10M limit that performance will degrade even if the import technically succeeds.
Frequently Asked Questions
Q: What's the actual row limit for Google Sheets?
A: There's no fixed row limit — the real constraint is 10 million cells total. A sheet with 10 columns has a practical row limit of 1,000,000. A sheet with 50 columns has a limit of 200,000 rows. The formula is 10,000,000 ÷ number of columns = max rows.
Q: Why does Google Sheets sometimes import my file without erroring but lose rows?
A: Silent truncation happens when the import runs but hits the cell limit mid-way. Google Sheets stops importing rather than throwing an error. Always verify your row count after importing a large file: =COUNTA(A:A) - 1 (minus the header).
Q: Can Google Sheets handle a 1 GB CSV file? A: Not directly — the 100 MB upload limit blocks files that size. You'd need to split the file first (Method 1) or use BigQuery (Method 4).
Q: Does the 10 million cell limit apply per sheet or per file? A: Per spreadsheet file, across all sheets combined. If you have 3 sheets each with 4 million cells, you're over the limit even though no single sheet exceeds it.
Q: What's the fastest way to split a large CSV on Mac?
A: The split command in Terminal is fastest for very large files. For files where you need to preserve headers or filter data, Python with pandas is cleaner and easier to adjust.
Q: Will splitting a CSV lose any data? A: No, as long as you do it correctly. The key is preserving the header row in each chunk (see the Python example above). A common mistake is splitting without copying the header, which means everything after the first chunk has no column names.