Spreadsheet Formulas in Format Conversion

Formulas are the logic layer of spreadsheets. A budget workbook's value isn't in the numbers — it's in the SUM, IF, and VLOOKUP formulas that compute those numbers from inputs. When you convert between formats, the critical question is: do the formulas survive, or are they replaced by static values?

The answer depends entirely on the source and target formats. CSV has no concept of formulas. ODS supports most Excel formulas but not all. Google Sheets has functions that don't exist elsewhere. Each conversion path has specific rules about what translates, what becomes a value, and what becomes an error. Knowing these rules before converting prevents the surprise of opening a file and finding #NAME? errors where calculated values should be.

XLSX to CSV: All Formulas Become Values

When you convert XLSX to CSV, every formula is replaced by its last computed result. The cell that contained =SUM(A1:A100) displaying 5280 becomes the static number 5280 in the CSV. The formula is permanently gone.

This is inherent to the format: CSV stores text values in cells, nothing more. There's no way to represent =SUM(A1:A100) in CSV — it would be interpreted as the literal text string "=SUM(A1:A100)" by any CSV parser, not as a formula.

Consequences:

• No recalculation. If input values change, the formulas won't update because they no longer exist.
• Circular reference resolution. Formulas involved in circular references export their last iterated value.
• Error values export as text. A cell showing #REF! exports as the literal string #REF! in CSV.
• Only the active sheet exports. Formulas that reference other sheets are resolved to values, and the reference context is lost.

The same applies to XLSX to TSV, XLSX to JSON, and XLSX to Markdown — all non-spreadsheet formats strip formulas and keep only values.

XLSX to ODS: Most Formulas Translate, Some Don't

Converting XLSX to ODS preserves formula logic for the vast majority of functions. Both formats implement the same core spreadsheet functions — they just store them in different XML syntax.

Functions that translate perfectly (300+ functions):

SUM, AVERAGE, COUNT, COUNTIF, COUNTIFS, SUMIF, SUMIFS, IF, AND, OR, NOT, VLOOKUP, HLOOKUP, INDEX, MATCH, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, CONCATENATE, DATE, TODAY, NOW, YEAR, MONTH, DAY, DATEDIF, EOMONTH, NETWORKDAYS, ROUND, ROUNDUP, ROUNDDOWN, ABS, MOD, INT, MAX, MIN, LARGE, SMALL, RANK, PERCENTILE, STDEV, VAR, TEXT, VALUE, IFERROR, ISBLANK, ISERROR, INDIRECT, OFFSET, CHOOSE — and many more.

Excel-Specific Functions That Break in ODS

These functions exist only in Excel (or Excel 365) and have no ODS equivalent:

The pattern: anything added in Excel 365 (especially dynamic array functions) will fail in ODS. Functions from Excel 2019 are a coin flip depending on LibreOffice version. Functions from Excel 2016 and earlier almost always work.

ODS to XLSX: LibreOffice-Specific Functions

Converting ODS to XLSX has the reverse problem. LibreOffice Calc has functions that don't exist in Excel:

• STYLE() — Applies a cell style within a formula. No Excel equivalent.
• CURRENT() — Returns the current result of a formula (used in iterative calculations). No Excel equivalent.
• MULTIPLE.OPERATIONS — LibreOffice's version of Data Tables. Different from Excel's TABLE() function.
• WEBSERVICE() — LibreOffice's version works differently from Excel's.
• Some statistical functions from the OpenFormula specification have slightly different parameter orders than Excel.

In practice, ODS-to-XLSX formula compatibility is slightly better than XLSX-to-ODS, because LibreOffice is designed to be compatible with Excel, so it avoids introducing functions that clash with Excel's namespace. The problematic area is macros — LibreOffice Basic and VBA are completely different languages, and no automatic translation exists.

Google Sheets Functions in Export

Google Sheets has the most unique functions of any spreadsheet platform. When exporting to XLSX or ODS, these become static values:

• QUERY() — SQL-like queries on cell ranges. No Excel or LibreOffice equivalent. Becomes cached values on export.
• IMPORTRANGE() — Pull data from other Google Sheets workbooks. Becomes static values.
• IMPORTDATA(), IMPORTHTML(), IMPORTXML() — Web data import functions. Become static values.
• GOOGLEFINANCE() — Stock data. Becomes last-fetched static value.
• GOOGLETRANSLATE() — Translation. Becomes translated text (static).
• DETECTLANGUAGE() — Language detection. Becomes detected language string.
• SPARKLINE() — Inline mini-charts. Disappears completely (cell becomes empty).
• IMAGE() — Embedded images via URL. May or may not survive depending on implementation.

Google Sheets also implements some functions with subtle differences from Excel: FILTER() in Google Sheets accepts multiple conditions directly, while Excel's FILTER requires combining with *. ARRAYFORMULA() in Google Sheets has broader behavior than @ implicit intersection in Excel. These nuances can cause formulas to produce different results even when the function name matches.

Formula Auditing Before Conversion

Before converting a workbook with formulas, audit which functions are used and whether they'll survive:

In Excel:

1. Go to Formulas > Show Formulas (or Ctrl+`) to display all formulas in cells.
2. Use Formulas > Trace Precedents / Trace Dependents to visualize formula dependencies.
3. Use Find & Replace (Ctrl+H) to search for specific function names: search for XLOOKUP, FILTER, SORT, LET, LAMBDA to find Excel-365-specific formulas.
4. Check Formulas > Error Checking for existing errors that will carry over.

In Google Sheets:

1. View > Show Formulas to see all formulas.
2. Search for QUERY(, IMPORTRANGE(, GOOGLEFINANCE(, SPARKLINE( to identify Google-specific functions.
3. Check if any formulas reference other workbooks via IMPORTRANGE.

Programmatically (Python with openpyxl):

from openpyxl import load_workbook
import re

wb = load_workbook('file.xlsx')
functions_used = set()
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for cell in row:
            if cell.data_type == 'f':
                funcs = re.findall(r'([A-Z]+)\(', str(cell.value))
                functions_used.update(funcs)
print(sorted(functions_used))

This gives you a complete list of functions used in the workbook. Cross-reference with the compatibility tables above to identify what will break.

Strategies for Formula-Safe Conversion

When you need to convert a formula-heavy workbook and preserve as much as possible:

1. Replace platform-specific functions before converting. Replace XLOOKUP with INDEX/MATCH (compatible everywhere). Replace FILTER+SORT with helper columns and standard formulas. Replace QUERY in Google Sheets with FILTER + SORT or rewrite as standard formulas.
2. Freeze volatile formulas. Cells using NOW(), TODAY(), RAND(), or INDIRECT() will produce different values when recalculated in a different application. If the current values matter, convert those cells to static values before exporting.
3. Test with a sample first. Convert the workbook, open in the target application, and check every critical formula. Don't assume that function name compatibility means result compatibility — subtle differences in how applications handle array formulas, date serial numbers, and rounding can produce different results.
4. Document the formulas. If the workbook will be used by someone else after conversion, add a sheet documenting which formulas were changed, which were converted to values, and what the original logic was.
5. Consider the two-file approach. Keep the original workbook as the source of truth (with all formulas intact). Produce a separate, values-only export for sharing. This way, the formulas aren't lost — they're just not in the shared copy.

Formula preservation during conversion is a spectrum, not a binary. CSV preserves zero formulas. ODS preserves most. XLSX preserves all (within Excel). The decision about which format to use for sharing should account for which formulas the recipient needs.

If they need the calculated values: any format works, since values are preserved everywhere. If they need the computational logic: stay within the same platform (XLSX for Excel users, ODS for LibreOffice users, Google Sheets link for Google users) or replace platform-specific functions with universal alternatives before converting. The worst outcome is sending a converted file with #NAME? errors in critical cells — a problem that's entirely preventable with a pre-conversion audit.