A senior auditor is three weeks into fieldwork when she notices a variance she cannot explain. The trial balance figures in the workpaper do not match the client's general ledger. After two hours of tracing, she finds the source workbook was renamed when the client reorganized a shared drive. Fourteen cross-workbook formulas silently broke that day. Excel never flagged an error. The manager reviewed cached values that looked correct and signed off.
This is not a skills problem. It is a structural problem with how audit teams rely on Excel linkage across multi-file workpaper sets. Broken links return stale values, not errors. Visual review does not catch them. And the consequences range from a wasted fieldwork day to a restatement.
This article gives you a step-by-step method to locate every external link in a workpaper, test each one safely, and harden your file structure before a broken path corrupts your evidence or triggers a regulator's question.
Answers
Excel linkage refers to formulas in one workbook that pull values directly from cells in a separate source workbook. In audit workpapers, this creates dependencies between files: a summary workpaper may link to a trial balance file, a cash flow schedule, and a supporting schedule simultaneously. When any source file moves, is renamed, or is updated without version control, those dependencies break or return stale values.
Go to Data > Edit Links to see every source workbook Excel has registered. Then run a secondary check using Find and Replace with "[" as the search string in the Formulas search option. Edit Links misses references created through Power Query and some named ranges. The second search catches formula-level external references that the dialog omits.
When you email an Excel file, the recipient saves it to their own local path. Any links inside the file still point to the original sender's local drive path, which the recipient cannot access. Excel displays cached values rather than an error, making the break invisible until someone refreshes links on the recipient's machine.
Yes, with conditions. Links must reference the full SharePoint or OneDrive URL path rather than a locally synced folder path. Locally synced paths differ across machines and break when a user syncs to a different device. Store all source workbooks in a shared library that every team member accesses via the same URL, and test links from at least two different machines before fieldwork begins.
A connection file (.odc or .iqy) is a stored definition of a data connection to an external database, web source, or data model. Excel linkage refers specifically to cell-to-cell references between workbooks using bracket notation ([SourceFile.xlsx]Sheet!Cell). Connection files feed data into Excel through Power Query or legacy data connections. Both can break when source paths change, but they require different diagnostic steps: Edit Links for Excel linkage, and Data > Queries and Connections for connection file issues.
More Blogs
