Every few months, a client comes to me with the same nervous energy. They've been running their business on a beautifully engineered Excel workbook — VBA macros, pivot tables, conditional formatting, the works — and someone on the leadership team has decided it's time to move everything to Google Workspace. The ask seems simple. The execution rarely is.
I've done this migration enough times now that I've developed a repeatable process. This post walks through exactly what I do, what breaks most often, and how I protect data integrity from the first export to the final validation check.
Why This Migration Is Harder Than It Looks
The core problem is that Excel and Google Sheets are not the same product wearing different clothes. Excel macros are written in Visual Basic for Applications (VBA), a language that doesn't exist inside Google Sheets. Google Sheets uses Google Apps Script, which is JavaScript-based. These two languages think differently, handle objects differently, and interact with spreadsheet data differently.
On top of that, certain Excel features — named ranges with complex scope, array formulas using legacy syntax, and some data validation rules — either translate imperfectly or break silently. Silent breakage is the dangerous kind. Your data looks fine. Your formulas return values. But those values are wrong, and you won't know it until someone catches an error three reports later.
Step 1: Audit the Workbook Before You Touch Anything
I never start a migration by opening the file in Google Drive. I start by opening it in Excel and running a full audit. Here's what I'm looking for:
- All VBA modules: Open the VBA editor (Alt + F11) and document every macro, its trigger, and what data it reads or writes.
- Named ranges: Check the Name Manager for any ranges that macros depend on.
- External data connections: Are any cells pulling from SQL, Power Query, or an external API?
- Volatile functions: Functions like INDIRECT, OFFSET, and NOW behave slightly differently in Sheets.
- Conditional formatting rules: Complex rules with VBA triggers won't survive the move.
I document all of this in a simple audit sheet. It's unglamorous work, but it's the difference between a clean migration and a week of debugging on the other side.
Step 2: Export and Preserve the Raw Data First
Before I rewrite a single line of script, I extract the data layer cleanly. I export each tab as a CSV, then import those CSVs into a fresh Google Sheet. This gives me a clean, verified baseline of the raw data with no formula interference.
Why CSVs instead of just uploading the .xlsx file? Because when you upload an Excel file to Google Drive and convert it, Google attempts to translate everything simultaneously — formulas, formatting, and macros — and the result is often a partially broken sheet where some things look right but aren't. Starting from clean CSVs means I know exactly what the data layer contains before I layer logic back on top of it.
Once the data is in Sheets, I run a row count and spot-check key columns against the original Excel file. Numbers should match exactly. If they don't, I find the discrepancy before moving forward.
Step 3: Rewrite Macros as Google Apps Script
This is where the real work lives. There's no automated tool that reliably converts VBA to Apps Script — I've tried them, and they save maybe 20% of the effort while introducing subtle bugs in the other 80%. I rewrite from scratch, using the VBA logic as a functional specification.
Here's my translation approach for the most common macro types:
Looping Through Rows
VBA loops using For Each over ranges. In Apps Script, I use getValues() to pull an entire range into a JavaScript array, loop through the array, then write results back with setValues(). This is dramatically faster than cell-by-cell operations and reduces the risk of timeout errors on large datasets.
Triggered Automation
VBA macros are often triggered by events like Worksheet_Change. In Apps Script, I replicate these using installable triggers — specifically onEdit triggers for real-time responses and time-based triggers for scheduled tasks.
Formatting Macros
Any VBA that applied formatting (bold, color fills, borders) needs to be rewritten using the SpreadsheetApp service. I usually take this opportunity to ask whether the formatting logic is still necessary, since many of these macros were workarounds for limitations that don't exist in Sheets.
Step 4: Rebuild Formulas and Validate Output
With the data in place and the scripts rewritten, I rebuild all complex formulas natively in Google Sheets. A few things to watch here:
- Array formulas: Excel's legacy array formulas (Ctrl+Shift+Enter) should be replaced with ARRAYFORMULA() or Sheets' native dynamic array behavior.
- VLOOKUP vs. XLOOKUP: If the original workbook used XLOOKUP, Sheets supports it now — but syntax differences occasionally cause issues.
- Date formatting: Excel and Sheets handle date serials slightly differently. Always verify date columns explicitly.
After rebuilding, I run a parallel validation. I keep the original Excel file open alongside the new Sheet and compare outputs from every formula-driven column across at least three representative rows. If the numbers match, I move to final testing. If they don't, I trace the formula chain until I find the divergence.
Step 5: Test With Real Users Before Decommissioning Excel
The migration isn't done when the data looks right to me. It's done when the people who use the spreadsheet daily confirm it works for their actual workflows. I run a one-week parallel period where users work in both environments and flag any discrepancies.
This catches edge cases I missed — unusual inputs, regional formatting differences, browser-specific behavior — and it builds user confidence in the new system. The parallel period also gives me a clean exit point: once users sign off, we archive the Excel file and make Google Sheets the system of record.
Common Mistakes I've Seen (and Made)
A few honest lessons from migrations that didn't go perfectly on the first pass:
- Skipping the audit: Rushing to upload the file and hoping for the best. This always costs more time than the audit would have.
- Trusting auto-conversion: Google's built-in conversion handles simple files fine. For anything with macros, treat it as a starting point, not a finished product.
- Not testing triggers: Apps Script triggers behave differently depending on whether they're simple or installable. I've had onEdit triggers that worked in testing fail in production because of permission scope differences.
- Ignoring timezone settings: If macros ran on a schedule, Google Apps Script uses the script's timezone, not the user's. This has caused more than one missed automated report.
The Payoff Is Real
When this process is done right, the result is a workbook that's faster to share, easier to collaborate on, and accessible from any device — without sacrificing the automation logic the business depends on. I've watched teams go from emailing Excel files back and forth to working simultaneously in a single live document with scripts running reliably in the background.
The migration isn't simple, but it's absolutely manageable with the right process. If you're staring down a complex Excel workbook and wondering whether the move to Google Sheets is worth the effort, the honest answer is: yes, but don't do it without a plan.