Spreadsheet Troubleshooting and Guides by Task

You don’t need to know a function name to solve a spreadsheet problem. Start with your task, then follow a guide for your application. Features, syntax and argument separators vary by program and version.

Quick tools for everyday problems

• CSV Import Doctor — inspect a file in your browser for delimiter, header, date and identifier risks before opening it in an app.
• Spreadsheet Problem Solver — choose a symptom such as a failed lookup, incorrect COUNTIF or a formula that breaks in another application.
• Formula Migration Helper — compare curated formula approaches across twelve spreadsheet programs.
• Compare function guides — find published formula references and documented compatibility differences.

These tools complement the step-by-step guides below. A CSV inspection highlights possible problems; it does not alter your file or guarantee how your spreadsheet program will import it.

Import CSV files without damaging data

• Fix a CSV that opens in one column (LibreOffice Calc)
• Preserve leading zeros in IDs (LibreOffice Calc)
• Fix special characters and text encoding (LibreOffice Calc)
• Stop dates from changing during import (LibreOffice Calc)
• Import CSV data while keeping leading zeros (PlanMaker)

Why this matters: CSV does not preserve number formats or cell data types. Import identifiers as text before software strips their leading zeros.

Diagnose formula errors or wrong results

• COUNTIF unexpectedly returns zero (LibreOffice Calc)
• COUNTIFS between dates (LibreOffice Calc)
• Fix Err:502 invalid arguments (LibreOffice Calc)
• Fix Err:509 missing operator (LibreOffice Calc)
• Lock cell references when copying formulas (LibreOffice Calc)

First check: Are the values actually numbers or dates, and does your software expect a semicolon or comma between function arguments?

Clean, split and extract text

• Split text into columns (LibreOffice Calc)
• Remove duplicate records (LibreOffice Calc)
• Convert imported numbers stored as text (LibreOffice Calc)
• Convert imported dates stored as text (LibreOffice Calc)
• Extract identifiers using REGEX (LibreOffice Calc)
• Sort while keeping headers and rows together (Calligra Sheets)

Lookups, business calculations and dates

• XLOOKUP with sample data (LibreOffice Calc)
• VLOOKUP exact matching (LibreOffice Calc)
• LOOKUP and sorted thresholds (LibreOffice Calc)
• COUNTIFS matching dates and criteria (LibreOffice Calc)
• NPV: future cash flows and initial investments (LibreOffice Calc)
• PMT: recurring loan repayments (LibreOffice Calc)
• DATE formula examples (PlanMaker)

Scientific and statistical analysis

• Descriptive statistics (Gnumeric)
• Linear regression (Gnumeric)
• One-way ANOVA (Gnumeric)
• Linear programming with Solver (Gnumeric)
• CORREL with test data (LibreOffice Calc)
• STDEV.P: population standard deviation (LibreOffice Calc)

Some Gnumeric workflows follow documentation and independent arithmetic checks rather than native Gnumeric tests. See each guide’s testing note.

Compare software and formula availability

• Compare function guides across twelve programs
• LibreOffice Calc A–Z
• Apache OpenOffice Calc A–Z
• Gnumeric A–Z
• Editorial and formula testing standards

A function without a guide for a specific program may simply be missing from our library; it does not prove that the application lacks the feature.