Formulas, names and errors
DataMind Oda explains how the figures in your workbook are built and checks them for problems. It
traces what a formula reads and which formulas read a cell, finds error values and other problems
with their likely cause, manages defined names and controls how Excel calculates. Tracing and checking
only read the workbook, so they run in every permission mode without a card.
Trace a formula
Ask, for example, What feeds the total in C42? or What depends on the discount rate?. The step row
reads Tracing a formula and the cell, then, for example, Reads 3 ranges, Read by 5
ranges, Reads no other cells or No formula reads it.
- Precedents are the cells a formula reads; dependents are the formulas that read a cell. Oda
traces one level, or the whole chain, up to 8 levels and 60 ranges, across sheets.
- Oda can trace a single cell or a range of up to 10,000 cells, and gets the formula and the value of
each single cell it finds.
- Oda uses Excel's own tracing. Where Excel refuses it, Oda reads the formulas instead, which follows
defined names and tables, but not the references that
INDIRECT or OFFSET build while Excel
calculates. Other workbooks that formulas read are named, not opened.
- A search for dependents reads the formulas of at most 500,000 cells. Snapshot sheets are left out
unless the trace starts on one.
- Tracing changes nothing in the workbook and draws no arrows on the sheet.
Find errors and other problems
Ask, for example, Check this model for errors or Why does Summary show #REF!?. Oda checks a range,
a sheet or every sheet, snapshot sheets left out. The step row reads Looking for errors, then, for
example, 6 problems found, No problems found or 200 of 340 problems.
| Problem | What Oda reports |
|---|
Error values: #REF!, #DIV/0!, #VALUE!, #N/A, #NAME?, #NUM!, #NULL!, #SPILL!, #CALC! | The likely cause, and the error cell a formula reads, so you find the cell an error starts from |
| Circular references | The cells of the cycle |
| Inconsistent formulas | A formula whose pattern differs from most formulas in its row or column, with the formula expected there |
| Typed numbers among formulas | A number typed between formulas of one pattern, often a formula pasted over with its value |
| Links to other workbooks | Formulas and defined names that read another workbook file, and whether the link shows an error |
- A check of error values alone uses Excel's own search and has no size limit; the other checks read
up to 500,000 cells. Results come in pages of up to 500 problems, 200 by default.
- Oda reports each problem with its likely cause and offers to fix it, and fixes only what you ask it
to. An inconsistent formula or a typed number may be intended, for example where actuals end and a
forecast starts.
Defined names
Oda lists, adds, changes and deletes defined names, such as TaxRate for =Inputs!$C$4. The step row
reads Working with defined names, then, for example, 12 names, Added the name TaxRate,
Changed the name TaxRate or Deleted the name TaxRate.
- A name belongs to the workbook or to one sheet, and refers to a reference or a formula, such as
=Inputs!$C$4, =Sales!$B$2:$B$13 or =0.21. It can carry a comment of up to 255 characters, and
can be hidden from Name Manager.
- A name starts with a letter or an underscore, uses only letters, digits, underscores and full stops,
has no spaces, and does not look like a cell address such as
A1 or R1C1.
- What a name refers to passes the same check as every formula Oda writes; see
Formulas Oda does not write.
- Adding a name runs without a card in Accept edits, as in Add the name TaxRate, which refers to
=Inputs!$C$4.
- Changing what a name refers to changes every formula that uses it, so it asks first in Accept
edits, on a card titled Allow Oda to replace what is there?, as in Change the name TaxRate to
refer to =Inputs!$C$5 instead of =Inputs!$C$4; the formulas that use it change with it. So does
adding a sheet's own name where the workbook has a name of that name, which the sheet's formulas then
use instead, and hiding a name.
- Deleting a name asks first in every mode except Skip all permissions: Delete the name TaxRate,
which refers to =Inputs!$C$4; formulas that use it show #NAME?.
- The Changes panel lists the name itself, not cells.
How Excel calculates
Oda reads how Excel calculates, recalculates, and switches the calculation mode or iterative
calculation. The step row reads Working with calculation, then, for example, Calculation:
manual, Recalculated the workbook, Calculation is now automatic or Already set as asked.
- With each message, Oda is told when Excel calculates manually, or automatically except data tables.
Then results can be stale, so Oda recalculates before it reads or reports them, and tells you.
- Reading the mode and recalculating the workbook, a sheet or a range run in every mode without a
card.
- Switching the mode to automatic, automatic except data tables or manual, or turning iterative
calculation on or off or changing its limits, asks first in Ask before edits and Accept
edits, on a card titled Allow Oda to change how Excel calculates? that offers no Always
allow in this chat: in Excel on Windows and Mac the setting applies to every open workbook. A line
reads, for example, Switch calculation to manual (now automatic): formulas update only when you
recalculate (F9). In Excel on Windows and Mac this applies to every open workbook.
- A switch to a mode that calculates less than the reply began with, such as manual for a large
change, lasts until Oda's reply ends. Then the pane switches the mode back, unless it was changed
again meanwhile, and the card says so: When Oda’s reply ends, the pane switches calculation back to
automatic. The Changes panel lists the switch as Calculation settings. To keep manual
calculation, set it in Excel under Formulas > Calculation Options.
- Oda turns on iterative calculation, which lets circular references calculate up to a number of
times, only when you ask; otherwise it reports a circular reference as a problem.
- Revert turn cannot undo a change of how Excel calculates, nor a change of a defined name; ask Oda
to change it back.
Questions, answered
Why does Oda ask before changing the calculation mode?
In Excel on Windows and Mac the calculation mode applies to every workbook you have open, not only
this one. So Oda asks in Ask before edits and Accept edits, and the card offers no Always
allow in this chat.
Why did calculation switch back to automatic after Oda's reply?
Oda switched to manual for a large change, and the pane switches a mode Oda lowered back when the
reply ends. To keep manual calculation, set it yourself under Formulas > Calculation Options.
Can Oda find circular references?
Yes. Ask Oda to check the workbook for errors. It lists each circular reference with the cells of the
cycle.
Will Oda fix the errors it finds?
Only the ones you ask it to fix. It reports each problem with its likely cause and the cell an error
starts from, and offers to fix it.
Why did the trace not follow an INDIRECT formula?
INDIRECT and OFFSET build their references while Excel calculates. Where Oda traces by reading
formulas, it cannot follow them, and the result says so.
Does tracing change my workbook?
No. Tracing only reads formulas and draws no arrows on the sheet, so it runs without a card in every
mode.
Why did Oda refuse to add a defined name?
What the name refers to could reach outside the workbook, such as another workbook file or a refused
function. The step row reads Oda does not write formulas that can reach outside the workbook.
What happens to formulas when Oda deletes a name?
Formulas that use the name show #NAME?. The approval card says so before the delete runs.