Most frequent errors in spreadsheet tables

There is a certain moment every spreadsheet user knows: you enter a formula, press Enter, and instead of a number you get a short, cryptic message in red. The instinct is to feel like something broke, but in reality, nothing broke — the spreadsheet is trying to tell you something specific, and if you know how to read it, fixing the problem takes seconds rather than minutes of blind guessing.

This article goes through the most common errors, explains what triggers them, and shows how to resolve them — or better yet, how to avoid them in the first place.

How formulas work

Spreadsheets operate on strict, unambiguous rules. Every cell either contains a value, a formula, or nothing, and every formula must be able to resolve to a single, unambiguous result. A function either receives the type of input it expects, or it cannot proceed. A lookup either finds an exact match, or it comes back empty.

This rigidity is precisely what makes spreadsheets reliable for calculation, but it also means that when something in the chain is slightly off, the whole formula stops and raises its hand. Error codes are that raised hand. They are not a sign that the software has failed or that your data is beyond saving; they are the spreadsheet’s way of being precise about what went wrong, which is exactly the information you need to fix it.

Now, let’s have a look at the most common errors.

1. #NULL! — The intersection that does not exist

#NULL! appears when a formula tries to find the intersection of two ranges that do not actually overlap.

The most common cause is a typo: a space used where a colon or comma was intended. In many spreadsheet applications, a space between two range references means “find the cells these two ranges share.” If the ranges have nothing in common, the result is #NULL! because the intersection is empty.

For example, writing =SUM(A1:A5 C1:C5) with a space instead of a comma tells the spreadsheet to sum the intersection of A1:A5 and C1:C5. Since those columns do not overlap, there is nothing to sum.

What to do instead: Check for stray spaces between range references in your formulas. If you want to sum two separate ranges, separate them with a comma: =SUM(A1:A5,C1:C5). If you genuinely want an intersection, make sure the two ranges share at least one cell.

2. #VALUE! — The wrong type of data

#VALUE! is one of the most common errors and one of the most frustrating, because it often appears without an obvious reason. The underlying cause is always the same: a formula is receiving a type of data it cannot work with.

Mathematical operations expect numbers. When they encounter text instead — even text that looks like a number, such as "42" stored as a string — they cannot proceed and return #VALUE! to flag the problem. The same happens when a date function receives something that is not a valid date, or when a function expects a single value but receives incompatible input.

A particularly sneaky version of this error comes from data imported from external sources. Numbers that arrived as text, values with invisible non-breaking spaces, or cells formatted as text before the data was entered can all produce #VALUE! in formulas that reference them, even though the cell displays what looks like a perfectly normal number.

What to do instead: Use the ISNUMBER() function to check whether a cell contains a real number or text that looks like one. For imported data, VALUE() can convert text-formatted numbers into actual numeric values. The TRIM() function removes invisible spaces. In some editors like ONLYOFFICE Spreadsheet Editor, you can also use Data > Text to Columns to force-reparse a range and convert text values to their correct types.

3. #NAME! — The unrecognized word

#NAME! means the spreadsheet encountered a word in a formula that it does not recognize. It looked for a function, a named range, or a defined name with that spelling, found nothing, and flagged the error.

Typos are the most common culprit. Writing VLOKUP instead of VLOOKUP, or AVERGE instead of AVERAGE, will produce #NAME! immediately. The same happens if you reference a named range that has not been defined yet, or if you use a function that is not available in the version of the application you are working in.

Another frequent source: forgetting to wrap text in quotation marks. A formula like =IF(A1="yes","true","false") will produce #NAME! because yes without quotes is interpreted as a name, not as a text string.

What to do instead: Read the formula carefully, character by character. ONLYOFFICE Spreadsheet provides autocomplete suggestions as you type a function name, which prevents most typos before they happen. If a named range is the issue, verify it exists via Sheet > Named Ranges. If a function appears correct but still triggers #NAME!, check whether it is supported in your current version.

4. #NUM! — The number that cannot exist

#NUM! appears when a formula produces a number that is mathematically impossible or too large for the spreadsheet to handle. Unlike the previous errors, this one is not about wrong input types or broken references — it is about the result itself being invalid.

The classic example is asking for the square root of a negative number: =SQRT(-4) returns #NUM! because the result does not exist in the real number system. Similarly, =1/0 does not produce #NUM! (that is #DIV/0!, covered next), but a formula like =LOG(0) does, because the logarithm of zero is undefined.

Financial functions are another frequent source. An IRR or RATE calculation that does not converge — because the inputs describe a situation with no valid solution — will return #NUM!. Very large or very small numbers that exceed the spreadsheet’s numeric limits can also trigger it.

What to do instead: Check the inputs going into the formula. If the error comes from a function like SQRT() or LOG(), ensure the input is always non-negative or positive respectively — validate the input before performing the calculation, or use IFERROR() to display a meaningful message when the result is undefined. For financial functions, review whether the inputs are realistic and whether the calculation has a valid solution.

5. #DIV/0! — Dividing by nothing

#DIV/0! is the error people encounter most often, and it is also the simplest to understand: something in the formula is dividing by zero, or by a cell that is empty (which the spreadsheet treats as zero).

It appears constantly in templates and dashboards that are built before the data is filled in. A column calculating percentage change, for example, will show #DIV/0! in every row until the baseline values are entered. It also appears in running averages, conversion rate calculations, and any ratio formula where the denominator can legitimately be zero under certain conditions.

What to do instead: The standard solution is to wrap the formula in IFERROR() or use IF() to check the denominator before dividing: =IF(B2=0,"—",A2/B2). This returns a dash (or whatever placeholder you prefer) when the denominator is zero, instead of an error. In dashboards meant to be filled in over time, this approach keeps the file readable at every stage, not just when it is complete.

6. #N/A — The value that was not found

#N/A stands for “not available.” It appears when a lookup formula searches for a value and cannot find it. VLOOKUP, HLOOKUP, MATCH, and XLOOKUP all return #N/A when the search value is not present in the lookup range.

The tricky part is that #N/A can appear even when the value seems to be there. A lookup for "Apples" will fail if the table contains " apples" (with a leading space). Numbers stored as text will not match numbers stored as values. The error surfaces in the formula but the root cause is in the data.

#N/A is also contagious: if a cell contains #N/A and another formula references that cell, it will also return #N/A, which can cascade across an entire sheet and make the source of the original problem hard to trace.

What to do instead: First, verify that the lookup value and the lookup range store data in the same format — both text or both numbers, with no extra spaces. TRIM() and VALUE() are your tools for cleaning the source data.

If #N/A is expected in some cases (for example, a product code that genuinely does not exist in the reference table), wrap the lookup in IFERROR() or IFNA() to handle the missing result gracefully. IFNA() is more precise than IFERROR() because it only catches #N/A specifically, leaving other errors visible.

7. #REF! — The reference that no longer exists

#REF! is perhaps the most dangerous of the seven errors, because it is often the result of an action that seemed harmless at the time. It appears when a formula references a cell or range that no longer exists — most commonly because a row or column that was part of the reference was deleted.

Delete a column that feeds into a VLOOKUP, and every formula that depended on it will immediately turn into #REF!. Delete or improperly rename a referenced sheet, and the same happens. Even cutting and pasting cells (as opposed to copying) can break references if not done carefully.

What makes #REF! particularly disruptive is that it propagates: once a formula contains #REF!, any other formula that depends on it will also return #REF!, creating a chain of errors that can be difficult to untangle if the original deletion is not immediately obvious.

What to do instead: Before deleting any row, column, or sheet, check whether it is referenced by other formulas. In ONLYOFFICE, you can use the trace dependents feature to see what references a given cell. If you need to remove data but keep the structure intact, clear the cell contents rather than deleting the row or column.

When restructuring a workbook, update cross-sheet references explicitly and verify them afterward. Named ranges help here too: a formula that references a named range will be easier to update centrally than one hardcoded to a specific cell address.

What these errors have in common

Every one of these seven errors follows the same logic: the spreadsheet detected a condition it cannot resolve and reported it as precisely as it could.

Reading these messages as information rather than failure changes how you approach debugging. Instead of undoing everything and starting over, you can ask the right question immediately: is this a type mismatch, a missing reference, a structural problem? Each error points directly at the answer.

The deeper lesson, though, is that most of these errors are preventable. Cleaning data before building formulas, using IFERROR() and IFNA() to handle expected edge cases, checking references before deleting rows, and using named ranges instead of raw cell addresses — these habits do not just fix errors after they appear. They stop most of them from appearing at all.

Get ONLYOFFICE and do more with your spreadsheets

ONLYOFFICE Spreadsheet Editor is a free, open-source editor that covers everything from everyday data entry to complex financial modelling. It supports over 450 functions, pivot tables, conditional formatting, data validation with dropdown lists, named ranges, and macros — giving you the full toolkit to build spreadsheets that are both powerful and maintainable. You can also decide to enable optional AI functions to support your workflow.

Collaboration is built in from the ground up. Multiple people can work on the same spreadsheet at the same time, leave comments on individual cells, and track who changed what. Whether your team works in a browser, on the desktop, or on a mobile device, everyone edits the same file in real time without format conversion or compatibility headaches.

ONLYOFFICE is fully compatible with the XLSX format, which means files created in Excel open without issues, and anything you build in ONLYOFFICE transfers cleanly to other tools if needed. It also integrates with platforms like Moodle, SharePoint, ownCloud, Box, Confuence and many more, making it straightforward to embed into whatever workflow your team already uses.

If you want to explore it, the desktop version is available for Windows, Linux, and macOS. The cloud version, ONLYOFFICE DocSpace, lets you get started directly in the browser without installing anything.

START ONLINE    GET DESKTOP APP