Turning an Excel Spreadsheet into a Web Application

Can a Tool Convert It for You?

Several kinds of tool promise to. Each one does part of the job.

No-code app builders also make screens from your rows and columns. Ask what they do with the formulas, macros, and lookups, because that is where the business rules live. We suggest Power Apps when the spreadsheet is mostly a list with a form, few rules live in its formulas, and your team already works in Microsoft 365. A custom application fits when the rules are many, people outside your organization need to use it, or the data has to reach your other systems. Either way, the work below is the same, because it is about what the spreadsheet does, not the tool that replaces it.

What Carries Over, and What Changes

Almost everything in the workbook has a place in the application. Very little of it moves without a decision.

Reading the Formulas and Macros

The formulas are the part people underestimate. A workbook that has run a process for years holds rules nobody wrote down, and some of them contradict each other. We read them before we rebuild anything. You can see most of what we look for yourself:

• Show the formulas. Formulas > Show Formulas (Ctrl+`, the grave accent key) puts every formula in place of its result. Look for cells in a formula column that hold a typed number instead. Someone overrode the rule there, and the application needs to know whether that was an exception or a mistake.
• Trace what depends on what. Trace Precedents and Trace Dependents, on the same tab, draw arrows between cells. A total that feeds three other sheets is a rule the application has to reproduce exactly.
• Unhide everything. Hidden sheets, rows, and columns often hold the rates and lookup lists the formulas use.
• Open the macros. Alt+F8 lists the macros people run by hand: from that list, with a shortcut key, or from a button. Alt+F11 opens the Visual Basic Editor, which holds all the code, including macros that run on their own when the file opens or saves, a cell changes, or the sheet recalculates. Note who runs each macro, and when.
• Find the links out. Data > Workbook Links (Edit Links in older versions) lists the other workbooks its formulas read from, and Data > Queries & Connections lists the data it imports. Each one is another source the process depends on.

Each rule becomes a line in a list: what it calculates, who relies on it, and one example with a known answer. That list is the acceptance test. The new application has to produce the same answer, or someone with the authority to decide says the old answer was wrong.

Which Copy Is the Real One?

Shared spreadsheets multiply. One copy lives on the shared drive, another was emailed to a manager last spring, and a third sits on a laptop with this month's changes in it. Each one is somebody's real list.

Before anything moves, decide which copy is the source, then find what the others hold that it does not: rows added in one place only, columns someone inserted for their own tracking, a total someone corrects by hand every month. Those differences are requirements. A column one person added for their own use is often a feature the whole team needs. Collect every copy people work from, not only the one on the shared drive.

Switching Over Without Losing Numbers

1. 1
   One workflow first

   Build the part of the spreadsheet that causes the most trouble, end to end, with the people who use it. The rest stays in Excel for now.

2. 2
   Move the data with a script

   Clean and migrate the data with a script you can run again, because you will run it again at cutover.

3. 3
   Reconcile

   Compare row counts and totals between the spreadsheet and the application, by month, customer, or whatever the business counts by. Then compare the reports people use most.

4. 4
   Run side by side

   The team uses both until the numbers match and people trust the new system.

5. 5
   Retire the spreadsheet

   Make the file read-only and keep it. Excel stays useful for analysis, fed by exports from the application, which is now the source of truth.

Nobody trusts a new system because it is new. They trust it when it gives the same totals as the spreadsheet for a month they already closed.

What to Bring to the First Conversation

• The workbook. A copy with sensitive rows removed is enough, plus every other copy people work from.
• Who uses it. How many people, how often, and who edits it versus who only reads it.
• The rules that matter. The formulas and macros you know the business depends on, and the name of the person who understands each one.
• The numbers people check. The totals and reports someone looks at every week or month, and how often they turn out wrong.
• What it connects to. Accounting exports, emailed copies, other workbooks it reads from or feeds.
• The failure history. Lost edits, wrong totals, and the afternoon someone saved over the file.

With those, a Workflow & AI Review can map the process and price the first version. Our Spreadsheet & Access Replacement service covers the path from that review to cutover, and the review fee is credited toward the build. If the spreadsheet has already become an Access database, our guide to replacing an Access database covers that path.

Keep Reading