Column Harbor
THE FIELD GUIDES · 33 PRACTICAL ANSWERS

Excel, Google Sheets
& CSV guides.

Bring files into Excel and Google Sheets, clean up the awkward bits, and check what changes when you export. Start with the problem you see.

01

CSV structure & imports

Find the right delimiter, count records, and handle quotes without shifting your data.

4 guides
6 min read · Worked example

CSV Opens in One Column in Excel? Fix Comma or Semicolon Imports

Fix a CSV file opening in one Excel column with From Text/CSV or Text to Columns. Use a semicolon sample to check quotes, empty fields, and leading zeros.

Read the guide 4 min read · Worked example

CSV Rows Have Different Column Counts: Find and Fix the Cause

Diagnose CSV records with too many or too few fields, distinguish empty values from missing fields, and repair a small example without guessing.

Read the guide 4 min read · Worked example

CSV Row Count Is Wrong: Count Records with Quoted Newlines

Understand why CSV line counts differ from spreadsheet rows, count records with a CSV parser, and check quoted newlines without deleting valid data.

Read the guide 4 min read · Worked example

Commas and Double Quotes in CSV: Escape Fields Correctly

Write CSV fields containing commas, quotation marks, and line breaks correctly, then verify a complete example with a parser and round-trip check.

Read the guide
02

Numbers & identifiers

Keep IDs exact and make quantities behave like the numbers they are.

5 guides
5 min read · Worked example

Keep Leading Zeros in Excel CSV Imports and Exports

Stop Excel removing leading zeros from CSV imports. Keep IDs as text, check zeros after saving, and choose the right setting for Excel 365 or 2024.

Read the guide 9 min read · Worked example

Excel Changes the Last Digit to Zero: Keep Long IDs Intact

Fix long IDs in Excel: distinguish scientific notation from 15-digit precision loss, import CSV IDs as Text, and test exact digits with sample files.

Read the guide 4 min read · Worked example

Excel SUM Returns Zero or Misses Values: Convert Numbers Stored as Text

Diagnose amounts stored as text in Excel, convert them in a helper column with explicit separators, and verify both numeric count and total.

Read the guide 4 min read · Worked example

Import Decimal Commas in Excel Without Changing the Amounts

Convert decimal-comma amounts in Excel using explicit source separators or Power Query locale, while keeping CSV delimiters and numeric values separate.

Read the guide 6 min read · Worked example

Sum Only Visible Rows in Excel: SUBTOTAL 9 vs 109 Explained

Sum filtered Excel rows with SUBTOTAL. Compare 9 and 109 in separate filter-only and manual-hide exercises, with sample data and checked expected totals.

Read the guide
03

Dates & regional formats

Separate dates from codes, resolve day–month ambiguity, and sort in the right order.

6 guides
4 min read · Worked example

CSV Dates Have Day and Month Swapped: Import with the Right Locale

Stop CSV dates being read in the wrong day and month order. Set the source locale, verify ambiguous dates, and keep original values for comparison.

Read the guide 5 min read · Worked example

Excel Dates Not Sorting Correctly? Fix Text Dates

Excel only sorts dates A to Z? Convert text dates with a checked formula or Text to Columns, then sort oldest to newest while keeping every row together.

Read the guide 4 min read · Worked example

Excel Changes Codes into Dates: Preserve the Column as Text

Keep codes such as 3-4 and 12/2 from becoming Excel dates. Import identifiers as text, check the original values, and avoid unreliable format-only fixes.

Read the guide 6 min read · Worked example

Excel Date Serial Numbers and Time Fractions Explained

Convert Excel date serial numbers into dates and times. Understand 0.5, 0.75 and fractions of a day, inspect hidden times, and check the 1900 or 1904 system.

Read the guide 5 min read · Worked example

Excel SUMIFS Date Range: Include the Whole End Date, Even with Times

Sum Excel amounts between dates without missing late entries on the end date. Use a next-day boundary, real date-time values, category criteria, and checked examples.

Read the guide 6 min read · Worked example

Count Working Days Between Dates in Excel: NETWORKDAYS Includes Eligible Endpoints

Count Excel business days with NETWORKDAYS and NETWORKDAYS.INTL. Verify inclusive endpoints, custom weekends, closures, same-day cases, and an exclusive-start rule.

Read the guide
04

Text & Google Sheets cleanup

Deal with broken accents, invisible spaces, duplicate rows, and missing fields.

6 guides
4 min read · Worked example

CSV Accents Look Broken in Excel: Import the File as UTF-8

Fix garbled CSV accents by selecting the correct encoding during import. Check UTF-8 previews, understand the BOM, and preserve the original source file.

Read the guide 5 min read · Worked example

TRIM Not Working? Remove Hidden Spaces in Excel and Sheets

Fix TRIM not removing spaces in Excel or Google Sheets. Replace nonbreaking spaces, clean a column, or trim only the edges while preserving internal spacing.

Read the guide 4 min read · Worked example

Remove Duplicates in Google Sheets Without Losing Distinct Records

Remove duplicate rows in Google Sheets using a clear column rule. Compare Remove duplicates with UNIQUE, preserve originals, and review conflicting IDs.

Read the guide 4 min read · Worked example

Google Sheets SPLIT Drops Empty Fields: Preserve Their Column Positions

Keep empty fields in Google Sheets SPLIT with remove_empty_text set to FALSE. Set multi-character delimiter behavior and avoid using SPLIT as a CSV parser.

Read the guide 5 min read · Worked example

Remove Duplicates in Excel and Keep the Latest Record

Keep the newest Excel record per ID by sorting before Remove Duplicates. Handle equal dates, blank IDs, invalid dates, and verify the retained rows.

Read the guide 5 min read · Worked example

Highlight Duplicate Rows in Excel When Both Columns Must Match

Highlight repeated two-column Excel records with an exact conditional-formatting range. Compare whole pairs, exclude incomplete rows, and choose all or later occurrences.

Read the guide
05

Compare & combine files

Combine monthly exports, find missing IDs, and choose the right matching operation.

8 guides
4 min read · Worked example

Power Query Append vs Merge: Stack Rows or Match Columns?

Choose append or merge for spreadsheet exports with a small order example, expected row counts, duplicate-key checks and practical Power Query steps.

Read the guide 4 min read · Worked example

Combine CSV Files in Excel with Power Query: A Folder Workflow

Combine monthly CSV exports through Power Query with a source-file column, schema checks and an exact row-count example you can reconcile after refresh.

Read the guide 4 min read · Worked example

Compare Two CSV Files in Python: Find Added and Missing IDs

Compare old and new CSV snapshots by a unique text ID with a local Python script, exact sample results and checks for duplicate keys and mismatched rows.

Read the guide 4 min read · Worked example

VLOOKUP returns #N/A for IDs that look identical: what to check

Diagnose VLOOKUP #N/A with exact-match settings, text-versus-number checks, hidden-space tests and a small SKU example that preserves meaningful zeros.

Read the guide 5 min read · Worked example

VLOOKUP Between Two Sheets in Excel: Exact Matches and Missing IDs

Pull a value from another Excel sheet with VLOOKUP. Lock the source range, check duplicate IDs, and distinguish a missing match from a blank or zero.

Read the guide 5 min read · Worked example

XLOOKUP with Multiple Criteria in Excel: Match Two Columns

Match two Excel columns with XLOOKUP, count duplicate matches, and handle empty criteria or return cells. Includes a FILTER option for all matches.

Read the guide 5 min read · Worked example

Google Sheets QUERY GROUP BY: Sum by One or More Columns

Group Google Sheets rows with QUERY and SUM. Set input headers explicitly, group by two columns, exclude blank keys, and check mixed-type amounts.

Read the guide 8 min read · Worked example

Compare Two Lists in Excel: Find Missing IDs and Duplicate Counts

Compare two lists in Excel for missing values and matches. Highlight differences, extract results, compare sheets and audit duplicate counts with sample CSVs.

Read the guide
06

Convert & export data

Move between CSV, XLSX, JSON, and TSV with explicit rules for what stays and what changes.

4 guides
4 min read · Worked example

CSV vs XLSX: what you lose when saving a spreadsheet as CSV

Choose CSV or XLSX by what the recipient needs. See a small formula example, a practical export checklist and the workbook features a text table cannot keep.

Read the guide 4 min read · Worked example

Nested JSON to CSV in Python: One Row per Array Item

Convert a small nested order JSON file to CSV locally, preserving string IDs and empty orders while making array expansion and expected row counts explicit.

Read the guide 5 min read · Worked example

CSV to JSON in Python: Preserve Leading Zeros and Empty Fields

Convert CSV to JSON in Python without pandas. Keep IDs as strings, preserve empty fields, or map blank quantities to null with an explicit schema.

Read the guide 4 min read · Worked example

Convert TSV to CSV in Python Without Changing Field Values

Use a local Python script to convert quoted tab-separated data to CSV while preserving commas, quotes, blank fields and embedded line breaks in the sample.

Read the guide