Build breakdown

Proving a rebuilt spreadsheet matches the original

Summary: A comparison tool that checks an original workbook against its rebuilt copy cell by cell, recalculates the key totals from the raw data, and produces a report where every difference is either fixed or explained.

A build I made for a posted project brief. I built this as a sample on made-up data. It is not a delivered engagement, and nothing here is a result from live use.

The problem, as a generic brief

Someone has a working Excel file and needs it rebuilt: same data, same formulas, same layout. The usual way to check a rebuild is to scroll through both files and eyeball the numbers, which misses small differences like a pasted value where a formula should be, a changed number format, or a total that is off by a few cents.

What I built

  • A fictional source workbook (a small sales sheet with a summary tab, named ranges, data validation, conditional formatting and print settings) and a rebuilt copy of it, both generated from one script so the sample can be reproduced.
  • A comparison script that checks:
    • sheets, used range, merged cells, freeze panes, filters, print area, tab colors, gridlines and page setup,
    • column widths, row heights, data validation, conditional formatting and named ranges,
    • every cell in the used range: value or formula, data type, number format, font, fill, alignment and border.
  • A second layer of checks: key figures such as total revenue, total commission, deal count, average and largest deal, and totals per region, computed separately from the raw data and compared with what both files calculate.
  • A report with three parts: a summary, the independent value checks, and a list of differences, each marked Accepted or Unexplained with a reason.

In the sample, the one difference is deliberate: the source had a typed in total, and the rebuild uses a SUM formula that gives the same value. The report lists it as Accepted, with the reason.

How it works

  1. Inventory the original and turn it into a checklist: sheets, formulas, formats, validation rules, named ranges, print setup, and any macros.
  2. Rebuild the data, formulas and layout.
  3. Compare the two files with the script. It reads both with a spreadsheet library and compares property by property.
  4. Recompute the key figures from the raw rows with rounding that matches Excel's, calculate both workbooks' formulas with a formula engine, and compare the results. This catches rounding differences that a visual check does not.
  5. Write the report. The script exits with an error if any difference is unexplained or any value check fails, so a rebuild cannot be signed off by accident.
  6. Fix or explain each difference, then rerun until the report is clean.

Stack

Python, openpyxl for reading and writing workbooks, the formulas library to calculate formula results, and the report as an XLSX plus a plain text copy.

What I'd do next

  • Handle files with macros and data model features by listing what each one does before rebuilding.
  • Cross-check the calculated results in a desktop spreadsheet application as well as the formula library.
  • Add a short "how to read this report" page for the person who signs off.
Figure placeholder

FREE CHECKLIST

Get the 10-minute lead leak check.

Seven quick tests you can run from your phone to see where calls, forms, and reviews are slipping away. Choose your business type and we will also send short tips that fit.