Resources · Excel Tips
Twelve concrete tips that recur in our editorial review work. Each is short, specific, and assumes you already know the basics; the value is in the corner cases that only surface when a workbook crosses a locale boundary.
Saving a workbook to .xlsx does not embed the locale it was built under, but
you can leave a clue. Add a hidden sheet named _locale with cells
documenting the expected decimal symbol, list separator, and 1900/1904 setting. The
first user to open it on a different region knows immediately what to align.
NUMBERVALUE() at every import boundary
NUMBERVALUE(text, decimal_separator, group_separator) takes the source's
separators explicitly, regardless of the active locale. Wrap every textual numeric
import: =NUMBERVALUE(A2, ",", ".") for a German-formatted number, even on
a US-locale machine. It is the only built-in function that lets you name the source
conventions.
IFNA over IFERROR for lookups
IFERROR swallows every error type, including the genuine ones you want to
surface (#REF!, #VALUE!). IFNA only catches
#N/A, leaving the rest visible. In an audit-grade model, this distinction
is the difference between a clean review and a regretful one.
Alt+E, S, V, E, Enter on Windows. The legacy menu shortcuts still work and are faster than the dialog. On a German Excel the keys differ — see the Keyboard Shortcuts page.
Tax rates, FX assumptions, holiday lists, threshold flags. A =tax_rate_de
reference is unambiguous; a =Sheet2!$B$4 reference is not. Named ranges
also survive sheet renames, which the second pattern does not. Manage them in the
Name Manager (Ctrl+F3) so the list is visible.
INDIRECT is a smell, not always a bug
INDIRECT defeats the dependency tracker, breaks FILTER's
dynamic-array recalculation, and never updates when sheets are renamed. Use it when you
must (e.g., parameterising sheet names) but flag every use in a Documentation
sheet so reviewers know where the silent links are.
Build the list of allowed values once on a hidden sheet, with one row per value plus one column per locale displayed label. The data cells reference the canonical value (column A); the display validation list points at the user's locale column. The user sees translated options; the underlying data stays canonical.
Conditional-formatting rules silently break when rows are inserted because Excel
rewrites the rule's Applies to range. Lock the range to the entire used area
($A$2:$Z$10000) and gate the rule with an AND($A2<>"")
condition; the rule then survives row inserts and deletes intact.
LET() for readable nested formulas
Modern Excel supports LET(name, value, name, value, expression), allowing
intermediate variables. A nested IF(IFERROR(VLOOKUP(...))) is rewritten as
=LET(found, VLOOKUP(...), IF(ISNUMBER(found), found, "—")) — the
intent is visible, and the lookup is computed once, not twice.
Press Ctrl+Alt+F9 to force a full recalculation, then
Formulas → Calculation Options → Manual. Edit one cell and time the
recalculation with =NOW() before and after. Volatile functions
(NOW, TODAY, OFFSET, INDIRECT,
RAND) are the usual culprits.
SUM(A:A) on a column with totals
Self-referencing totals are the silent introduction of circular references that Excel
may not flag if iterative calculation is enabled. Write the total in a fixed cell
(A1000) and reference that. In a structured table, use the table's
built-in totals row instead of SUM on the column reference.
A two-column sheet named Versions: date, change. Update it every time you touch a formula that affects an output. It is the cheapest possible audit trail and the one finance reviewers reach for first when a number changes between drafts.