Column Harbor
THE FIELD GUIDES · 63 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.

5 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 7 min read · Worked example

Power Query Promote Headers: Skip Report Titles Without Losing the First Record

Fix the wrong header row in Power Query. Remove a fixed report preamble, promote headers once, preserve text IDs, and check refresh changes.

Read the guide
02

Numbers & identifiers

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

10 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 6 min read · Worked example

Excel PivotTable Shows Count Instead of Sum: Fix the Source and the Value Field

Change a PivotTable from Count to Sum, convert amount text safely, retain real blanks and zeros, and verify totals against a small source table.

Read the guide 4 min read · Worked example

Excel Formulas Not Calculating? Check Text, Display Mode, and Calculation Settings

Diagnose Excel formulas showing as text or keeping old results with a small control formula, safe re-entry steps, and an explicit recalculation check.

Read the guide 4 min read · Worked example

Percentage Change in Excel: Increase, Decrease, and a Zero Starting Value

Calculate percentage change from a positive baseline, separate percentage points, and flag zero or negative starting values with a reproducible example.

Read the guide 6 min read · Worked example

Google Sheets SUMIF Wildcards: Match Text or a Literal Asterisk

Use Google Sheets SUMIF for starts-with, contains and literal asterisk matching. Compare worked totals and use equality for punctuation-bearing product codes.

Read the guide 7 min read · Worked example

Google Sheets QUERY Missing Values: Repair Mixed Text and Number Columns

Find why QUERY drops text or numeric IDs, then normalize an identifier column without turning amounts into text. Includes typed fixtures and expected results.

Read the guide
03

Dates & regional formats

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

10 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 7 min read · Worked example

Power Query Change Type Using Locale: Fix Dates and Decimal Commas

Fix Power Query date and number conversions with explicit source locales, a mixed-format example, expected output, and checks for silent day-month swaps.

Read the guide 4 min read · Worked example

Excel Sum of Hours Over 24: Show Elapsed Time Without Wrapping

Keep Excel duration totals numeric with [h]:mm, convert them to decimal hours, and handle overnight or multi-day intervals without losing whole days.

Read the guide 5 min read · Worked example

Excel WORKDAY with Holidays: Add Business Days Without an Off-by-One Error

Calculate Excel business-day deadlines with WORKDAY and WORKDAY.INTL. Check start-day counting, real holiday dates, custom weekends, and sample results.

Read the guide 7 min read · Worked example

Google Sheets: Sum by Month and Category Without Mixing Years

Use SUMIFS to total a month and category in Google Sheets. Keep years separate and include late-night timestamps with an exclusive next-month boundary.

Read the guide
04

Text & Google Sheets cleanup

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

15 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 7 min read · Worked example

Excel TRIM Not Working? Find and Remove Hidden Spaces

Fix Excel TRIM not removing spaces. Identify nonbreaking spaces, tabs and zero-width characters, choose a safe formula, and compare checked results.

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 6 min read · Worked example

Power Query Fill Down: Handle Nulls, Empty Text, and Group Boundaries

Fill repeated labels in Power Query while keeping leading blanks, zeros, and batch boundaries correct. Includes a grouped query and expected CSV.

Read the guide 4 min read · Worked example

Highlight Duplicate IDs in Google Sheets Without Deleting Rows

Use Google Sheets conditional formatting to highlight complete rows with repeated IDs, ignore empty IDs, and choose every occurrence or later occurrences only.

Read the guide 4 min read · Worked example

Excel Drop-Down List Not Updating? Check the Source Range and Validation Rule

Create and repair a status dropdown in Excel, include a newly added option, test invalid entries, and audit old values without changing them silently.

Read the guide 4 min read · Worked example

Combine Cells in Excel Without Losing Data: TEXTJOIN, Spaces, and Blank Fields

Join name fields into a new Excel column without merging away source values. Compare TEXTJOIN with an older-version formula and verify missing middle names.

Read the guide 4 min read · Worked example

Freeze Rows and Columns Together in Excel: Choose the Correct Cell

Keep two header rows and an ID column visible while scrolling in Excel. Use a B3 control, reset the boundary safely, and separate freezing from printing.

Read the guide 5 min read · Worked example

Count Distinct Values in Excel Without Counting Blanks as One

Count distinct nonblank Excel codes with UNIQUE and FILTER, distinguish values occurring once, and test an entirely blank range with downloadable expected results.

Read the guide 5 min read · Worked example

Remove Completely Blank Rows in Excel Without Losing Partial Records

Use a row-wide COUNTA check to remove completely blank Excel rows while keeping partial records, zero amounts, and the original data intact.

Read the guide 5 min read · Worked example

Excel TEXTSPLIT with Multiple Delimiters: Keep Empty Fields in the Right Column

Split Excel text on commas, semicolons, and pipes with TEXTSPLIT. Preserve empty fields, handle row delimiters, and avoid mistaking simple splitting for CSV parsing.

Read the guide 6 min read · Worked example

Power Query Split Column into Rows: Keep IDs, Order, and Empty-Token Rules

Split semicolon-separated tags into Power Query rows with repeated IDs, token positions, blank handling, and an auditable six-row result.

Read the guide
05

Compare & combine files

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

17 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 9 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 5 min read · Worked example

Google Sheets FILTER No Matches: Diagnose the Criteria Before Hiding the Error

Fix no matches in Google Sheets FILTER with aligned ranges, separate condition checks, and a safe no-results message. Download a complete worked example.

Read the guide 8 min read · Worked example

Power Query Merge Creates Duplicate Rows? Fix Extra Rows

Fix extra rows after a Power Query merge. Count matches before expanding, diagnose duplicate keys, and run downloadable M queries with a checked example.

Read the guide 5 min read · Worked example

Excel FILTER #SPILL! Error: Find the Blocker Without Deleting Your Data

Fix Excel FILTER spill errors with an exact three-row example. Check occupied cells, tables, merged cells, output size, and a separate no-match result.

Read the guide 5 min read · Worked example

Count Checked Boxes in Google Sheets: Tasks, Categories and Completion Rate

Count checked and unchecked Google Sheets boxes without including empty template rows. Handle custom values and calculate a task-based completion rate.

Read the guide 5 min read · Worked example

PivotTable Missing New Rows After Refresh? Check Its Source Range

Include new rows in an Excel PivotTable by checking its source range, switching to an Excel Table, refreshing, and checking filters against sample totals.

Read the guide 7 min read · Worked example

Combine CSV Files in Power Query When Headers Change: A Safe Schema Contract

Combine reordered CSV columns, fill a missing optional field, and stop on missing required headers with a Power Query schema contract and expected output.

Read the guide 5 min read · Worked example

XLOOKUP Return All Matches? Use FILTER for Every Matching Excel Row

Return every matching Excel order with FILTER instead of stopping at one XLOOKUP result. Keep duplicate transactions, add a status condition, and check row totals.

Read the guide 6 min read · Worked example

Power Query Left Anti Join: Find Missing Rows Without Losing Duplicates

Find items absent from another table with a Power Query left anti join. Keep duplicate requests, separate blank IDs, and verify exact text matches.

Read the guide 6 min read · Worked example

Sort by Multiple Columns in Google Sheets: Keep Rows Together and Break Ties

Build a Google Sheets SORT formula with department, descending score and a final ID tie-breaker. Keep complete records together and verify exact row order.

Read the guide
06

Convert & export data

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

6 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 6 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 5 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 5 min read · Worked example

Repeat an Excel Header Row on Every Printed Page Without Shrinking the Report

Set Excel Print Titles, verify the second page, and keep large reports readable with one-page-wide scaling and an unrestricted page height.

Read the guide 6 min read · Worked example

Unpivot Month Columns in Power Query: Keep IDs, Zeros, and a Clear Blank Rule

Turn monthly Excel columns into product-month rows with Power Query. Choose growing or fixed month columns and verify missing values, zeros, and totals.

Read the guide