Underneath every workbook, template and config file is a document built from XML: readable if you know where to look, editable if you're careful. This is where BlackTor Group works when the standard interface isn't enough.
BlackTor Group LtdDartmoor, UK
A note on what follows
No client names, no client data.
BlackTor's work is covered by client NDAs, so the examples below don't describe a real client, project or dataset. To keep things concrete, they're all set at "Ride Me Cycles": a fictitious multi-branch bike retailer invented for this site. The techniques are real; Ride Me Cycles and everything about it are not.
Latest update
Latest: patched cross-sheet formula references directly in a workbook's XML markup (a personal project, not client work), editing the OOXML source instead of rebuilding the sheet by hand.
A few examples
Three problems solved below the interface.
Illustrative, per the note above: not real client work.
The problem: Ride Me Cycles' finance team exported its P&L report from the accounting system every month, and it lost its currency formatting every time the sheet was rebuilt from source data.
The approach: Patched the styles part of the workbook's XML to reattach the original format codes to each rebuilt cell, instead of reformatting by hand after every refresh.
<numFmt numFmtId="164" formatCode="£#,##0.00"/>
The result: The P&L export keeps its formatting automatically, however often the source refreshes.
The problem: Ride Me Cycles' new product catalogue was hand-edited so often that one missing closing tag brought the whole till import down.
The approach: Wrote a small validating pre-processor that checks the catalogue file against its schema before it reaches the import step, catching a malformed tag in seconds rather than after the import fails.
The result: Bad catalogue imports get caught before they cost a branch a day's trading.
The problem: Ride Me Cycles' HR system and its payroll provider described the same staff record in incompatible XML shapes, so every payroll run needed someone to reconcile them by eye.
The approach: Built an XSLT transform mapping one schema onto the other, so the two systems exchange staff records without a person in the loop.
<xsl:template match="Employee">
The result: Payroll runs that used to need a person now run unattended.
Notes in full
Every note above, in full.
The sidebar carries the short version; this is the longer one, for whoever wants the detail behind it.
SEP 2026
An OOXML file is a ZIP archive
Every modern Office file, whether it is a workbook, a document or a presentation, is a ZIP container holding a set of XML parts plus a manifest that describes how they relate to one another. Rename the extension to .zip and any standard archive tool will open it to reveal folders such as xl, word or ppt, each containing the XML that actually defines the content, the formatting and the structure. The relationships between those parts are themselves recorded in XML files with a .rels extension, and a top level [Content_Types].xml declares what each part is, so the format is self describing right down to the packaging.
This matters in practice because the ribbon interface only ever exposes a fraction of what the format supports. Custom document properties, orphaned styles, stray external references, or a formula stored in a form the application will not let you edit directly, are all visible and editable once the archive is opened up. It is often faster to unzip a workbook, inspect the relevant part, make a small change and rezip it than to hunt through several layers of dialog boxes looking for a setting that may not be exposed at all.
The one thing to watch is that rezipping has to produce a structurally valid archive again: the mimetype and part ordering conventions that some OOXML consumers expect, and the exact relative paths recorded in the .rels files, need to be preserved. Most archive tools handle this correctly for OOXML specifically, but a tool that recompresses aggressively or alters file ordering can produce a ZIP that is technically valid yet still rejected by Office, which is a good reason to test the rezipped file before treating the edit as finished.
An XSD schema defines, precisely, which elements and attributes are allowed where, in what order, and with what data types, so validating a generated document against the schema for its target part is a mechanical check rather than a judgement call. Running that check as a step in the generation pipeline, before the file is handed to whatever system will consume it, means a missing closing tag, an attribute of the wrong type, or an element in the wrong sequence is caught in the seconds it takes the validator to run, with a line number pointing straight at the fault.
The alternative is finding out the hard way. Many consumers, Office included, process a document part by part, so a malformed tag partway through a large import can mean several thousand records go in cleanly and then the process stops, leaving the target system in a partially updated state that then has to be diagnosed and unwound before the import can be retried. Reproducing that failure locally against a raw XML file is far slower than reading a validator's output.
It is also worth validating even when the immediate consumer is a lenient parser that tolerates minor deviations from the schema, because a file that only ever passes through one lenient system can still end up read by a second, stricter one later on, whether that is an audit tool, a different version of Office, or another organisation's import pipeline entirely. Validating against the schema, rather than against what one particular parser happens to accept, is what keeps the file genuinely portable.
XML namespaces exist so that elements from different vocabularies can sit inside the same document without their names colliding, and each namespace is identified by a URI that is bound to a prefix, or to the default namespace, at the point in the document where it is declared. A file can be entirely well formed, meaning every tag is properly opened, closed and nested, and still fail to parse correctly in a strict consumer because an element that is meant to belong to one namespace is, through a missing or mismatched xmlns declaration, actually sitting in the wrong one, or in none at all.
The awkward part is how quietly this fails. A strict schema aware consumer will typically reject the file outright and report a namespace error, which at least points at the problem directly. A more permissive consumer, though, may simply ignore any element it does not recognise as belonging to a namespace it understands, so the data is silently dropped rather than flagged, and the first sign of trouble is a report or a formula referencing a field that appears to be empty even though the source XML clearly contains a value.
A common variant of the problem is a prefix that gets reused with a different URI in a nested scope, for example when XML from two different tools is concatenated or merged by hand rather than through a proper XML library. The prefix looks identical in both places, so the mismatch is invisible on a casual read, and the fastest way to confirm it is the cause is to check every xmlns declaration in scope at the point the element appears, rather than assuming the prefix means the same thing throughout the file.
XSLT works by matching templates against nodes in the source document and producing output for each match, so a mapping from one schema to another is expressed as a set of declarative rules rather than as a sequence of imperative steps that build the output up piece by piece. For a transform that will be run repeatedly against a source and target schema that are both reasonably stable, that declarative style has a real advantage: the mapping between the two schemas exists in one place, in a form that reads close to a specification, rather than being scattered across conditional branches in general purpose code.
The practical benefit shows up most clearly when someone other than the original author needs to understand or amend the mapping later. A stylesheet's templates can usually be read element by element against the two schemas side by side, whereas bespoke parsing code tends to accumulate incidental logic (string handling, loop bookkeeping, error handling) that has nothing to do with the mapping itself and makes the actual transformation rules harder to isolate. An XSLT transform is also straightforward to test in isolation, since it takes XML in and produces XML out with no other dependencies.
The trade off is that XSLT suits a mapping that is genuinely stable more than one that is still evolving quickly. If either schema is changing on a regular basis, or the transform needs to call out to external services, handle complex procedural logic, or maintain state across records, general purpose code with a proper XML library is usually the better fit. The decision is really about how often the mapping itself needs to change, not a general preference for one approach over the other.
Editing OOXML parts by hand, outside the application that normally writes them, has no safety net: there is no undo, no autosave, and no validation on save, so a single misplaced character or a badly closed tag can leave the file in a state that Excel or Word cannot open at all. When that happens, the error message the application gives is rarely specific enough to point at the actual fault, often amounting to little more than a generic message about unreadable content, which is not much help when the file might contain dozens of edited parts.
Keeping an unmodified copy of the file alongside the one being patched turns that unhelpful error into a solvable problem. With both versions available, a text diff between the original and the patched XML part shows exactly what changed, which narrows the search from the entire document down to a handful of edited lines, and a corrupted file can simply be reverted to the known good original rather than repaired blind. This is particularly valuable when several edits have been made in one sitting, since the diff will show all of them at once rather than requiring each to be recalled from memory.
It is worth keeping the original for a while even after a patch is confirmed to work, because some corruption is not immediately obvious. A file can open cleanly, appear correct on screen, and only reveal a problem later when a formula recalculates unexpectedly, a saved copy fails to reopen, or a downstream tool that reads the file more strictly than Excel does rejects it. Having the original on hand at that point avoids having to reconstruct, from memory, what the file looked like before the edit was made.
XML reserves a small set of characters for its own syntax: the less than and greater than signs mark tag boundaries, the ampersand introduces an entity reference, and quote characters delimit attribute values. Any of those characters appearing in ordinary string data, rather than as XML syntax, has to be replaced with its entity equivalent before it is written into the document, because the parser has no way to tell an ampersand that is meant literally from one that is meant to start an entity reference.
This is one of the most common ways a generated file ends up malformed, precisely because the failure is invisible until real world data hits it. Test data rarely contains an ampersand, a stray angle bracket, or an apostrophe in exactly the position that breaks the generator, so code that has been tested and appears to work can still produce an unparseable file the first time it processes a genuine record containing one of those characters, and the resulting error can be well downstream of the actual cause.
A properly used XML writing library handles this automatically, escaping text content and attribute values as it serialises them, which is the reason to prefer a library over building XML by string concatenation wherever practical. Where string concatenation is unavoidable, for instance when patching a single value inside an existing part, a CDATA section is sometimes offered as a shortcut around escaping, but it only suppresses markup interpretation within its own bounds and cannot itself contain the literal closing sequence for CDATA, so it is not a general substitute for escaping and needs the same care applied to it.
Shared strings are referenced by index, not by value
A workbook's text values are not stored inline in each sheet's XML. Instead, every unique string used anywhere in the workbook is held once in sharedStrings.xml, and each cell that contains text stores only the numeric index of the entry it points to. This design exists to avoid repeating the same string in the file every time it appears, which for a workbook with a lot of repeated text (categories, labels, headers) can meaningfully reduce file size, but it means a cell's XML on its own does not tell you what text it displays without also consulting the shared string table.
The consequence is that the shared string table cannot be edited as if it were just a list of strings sitting in isolation. Inserting a new entry in the middle of the table, or removing one, shifts the index of every entry that follows it, and every cell across every sheet that referenced one of those shifted entries by its old index is now pointing at the wrong string, or at nothing at all if the index runs past the end of the table. The workbook can still open without complaint, showing plausible looking but wrong text in cells that were never touched.
The safe pattern is to only ever append new strings to the end of the table, leaving existing indices undisturbed, and to update the sst element's count and uniqueCount attributes to match. If an entry genuinely needs to be removed, the correct approach is to find every reference to its index across every sheet part first and repoint them, rather than deleting the entry and hoping nothing downstream still points at it.
Office writes its XML parts without indentation or line breaks, as a single long line per part, which is efficient for the application to parse and write but close to unreadable for a person, and it is actively hostile to diffing. A version control diff tool compares files line by line, so if an entire part is one line, a single attribute changed anywhere within it makes that whole line register as changed, and the diff shows a wall of red and green with no indication of which one attribute actually moved.
Running generated or hand edited XML through a consistent formatter, so that each element and attribute lands on its own predictable line with consistent indentation, turns that unreadable diff into something a reviewer can actually use: a one line change in the source shows up as a one or two line change in the diff, exactly where it occurred, and everything else in the file appears unchanged. This matters most when reviewing someone else's patch to a workbook or template, where the reviewer needs to be confident about exactly what changed without re-deriving the whole document from scratch.
The one case where reformatting needs care is text content marked as space-preserving, most often seen in cell values that begin or end with a space, since those spaces are semantically significant and a formatter that is not aware of that marker can strip or alter them while tidying up the surrounding markup. A formatter that respects it is worth confirming before adopting it as a routine step, and it is generally safer to format for review purposes on a working copy rather than as an in-place step in a generation pipeline.
Keep ribbon customisation XML under source control
A workbook's ribbon customisation, whether that is a custom tab, group or button, is stored as its own XML part, separate from the sheet and style parts, and referenced through the same relationships mechanism that ties every other part of the package together. Each control in that XML can carry a callback attribute naming a VBA procedure to run when it is clicked, so the ribbon definition and the macro code behind it are two halves of the same feature, held in two different places inside the workbook.
Kept only inside the workbook, that XML is effectively invisible to normal review: it does not show up in a VBA project export, it is not something most reviewers think to check, and a change to it (a control moved, a callback renamed, an icon swapped) leaves no trace anywhere a colleague reviewing the workbook would naturally look. If the callback name in the ribbon XML and the procedure name in the VBA module drift apart, the button simply stops working, with nothing in the interface to explain why.
Extracting the customUI XML into its own file and keeping it under version control alongside the VBA source brings ribbon changes into the same review process as any other code change: a diff shows exactly which control or callback moved, and the file can be searched, linted or cross checked against the VBA project just like any other source file. The only extra step is that the extracted file then has to be re-embedded into the workbook's ZIP package as part of the build, so it stays a genuine two way relationship rather than a one off export that quietly falls out of sync with the workbook again.
When a workbook fails to open because one of its XML parts is malformed, Office's response is typically a generic message about unreadable content, occasionally accompanied by an offer to attempt automatic repair, and neither of those tells you which part, or which edit, is actually responsible. If several changes have been made since the file was last known to open cleanly, that generic error leaves a wide search space to work through, and working directly on the only copy of the file makes every attempt to narrow it down a further risk to the one asset that matters.
Working from a copy removes that risk entirely, since a failed experiment just means going back to the copy rather than having lost anything. The more effective habit alongside that is keeping each change small enough to test in isolation: rather than applying several edits and then checking whether the file still opens, apply one, save, and open it, so that if it fails, the cause is immediately obvious rather than being one of several candidates.
This matters more than it might seem because Office's automatic repair can make the problem harder to see rather than easier. Repair sometimes succeeds by silently discarding the offending part or the data within it, so the file opens without complaint but is missing content that was there before, and unless the before and after are compared directly, that loss can go unnoticed until much later. Isolating changes one at a time, on a disposable copy, avoids ever reaching the point where repair is the only way back into the file.
