It’s 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open—some in Excel, some shared via Google Drive—and no idea which tool can actually merge them without breaking formulas or losing formatting.
Quick Answer
Yes, Google has something like Excel: Google Sheets. It’s free, cloud-based, and handles most everyday tasks—but it lacks Excel’s depth in Power Query, advanced macros, dynamic arrays (until very recently), and local performance with huge files. You can do 80% of what you do in Excel—but not the last 20% that saves hours every week.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Google Sheets (native) | Open sheets.google.com → New → Blank spreadsheet | Real-time collaboration, light analysis, mobile access | No VBA, limited pivot table calculated fields, max 10M cells |
| Excel Online (free) | Go to office.com → Sign in → Create Excel workbook | Familiar interface, basic formulas, file compatibility | No Solver, no Power Pivot, no XLOOKUP in older versions |
| Zapier + Sheets + Excel | Connect Sheets to Excel via Zapier → Auto-sync on edit | Cross-platform updates, automated reporting | Free tier caps at 100 zaps/month; delays up to 15 min |
| Office 365 + Shared OneDrive | Save Excel file to OneDrive → Share link → Set permissions | Full Excel feature set + real-time co-editing | Requires license ($6.99/user/month); no offline Sheets sync |
| Sheets Add-ons (e.g., Power Tools) | Extensions → Add-ons → Get add-ons → Install Power Tools | Batch formatting, formula auditing, sheet cleanup | Add-ons run slower than native functions; permissions required |
Method 1 Deep Dive
Let’s say you’re pulling weekly sales data from five regional managers—all sharing raw Sheets files in a shared Drive folder. You need a master summary tab showing total revenue per region, average deal size, and YoY growth.
You’ll use =IMPORTRANGE() to pull live data from each sheet. In your master Sheet, cell A1 is ‘Region’. In B1, paste this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xYz...", "Q1!A2:C100")
That pulls A2:C100 from the ‘Q1’ tab of another Sheet. But here’s what most miss: you must first manually open the source Sheet and click ‘Allow access’—once per source file. No pop-up? No data. (Trust me, I learned this the hard way during a QBR prep.)
Now build your summary. In D2, type:=SUMIF(IMPORTRANGE("1xYz...", "Q1!A2:A100"), A2, IMPORTRANGE("1xYz...", "Q1!C2:C100"))
This sums revenue (column C) where Region (column A) matches A2. Yes—it’s messy. Yes—you’ll get #REF! until all IMPORTRANGE links are authorized. Pro tip: Paste all your IMPORTRANGE formulas first, then go open each source file one by one to grant access. Saves 10 minutes.
Sample data pulled into your master Sheet (B2:D6):
| Region | Revenue | Avg Deal |
|---|---|---|
| North America | $248,750 | $12,438 |
| EMEA | $192,310 | $9,615 |
| APAC | $167,420 | $8,371 |
| LATAM | $89,630 | $7,469 |
| Canada | $64,180 | $6,418 |
Method 2 Deep Dive
Now imagine you’ve been sent an Excel file (.xlsx) from Finance—but you only have Sheets access. You need to run a quick pivot on expense categories across 12 months.
Don’t convert and lose formatting. Instead: Upload the Excel file directly to Google Drive → Right-click → Open with → Google Sheets. Sheets will preserve formulas, dates, and number formats—but not conditional formatting rules based on custom formulas. That’s the surprise most don’t expect.
Once open, go to Data → Pivot table. In the sidebar, drag ‘Category’ to Rows, ‘Amount’ to Values (set as SUM), and ‘Month’ to Columns. Done. But if your Excel file uses structured references like Table1[Amount], Sheets converts those to plain ranges like $C$2:$C$1256. So double-check totals against the original.
Keyboard shortcut alert: Press Alt + D + P in Excel Online to open the pivot wizard—same as desktop Excel. Not in native Sheets, though. That’s why we sometimes keep both tabs open.
Here’s how the pivot looks after setup (values auto-calculated):
| Category | Jan | Feb | Mar | Apr |
|---|---|---|---|---|
| Marketing | $14,230 | $15,670 | $13,890 | $16,410 |
| R&D | $42,800 | $43,250 | $41,920 | $44,360 |
| HR | $9,420 | $9,510 | $9,370 | $9,630 |
| Facilities | $7,850 | $7,920 | $8,010 | $7,890 |
Cheat Sheet
| Task | Google Sheets | Excel Online | Desktop Excel Shortcut |
|---|---|---|---|
| Open new blank sheet | sheets.google.com → + | office.com → Excel → New | Ctrl + N |
| Import external range | =IMPORTRANGE(url, range) | Data → From Web → Paste URL | Alt + D + D + W |
| Create pivot table | Data → Pivot table | Insert → PivotTable | Alt + N + V |
| Auto-fill series | Drag fill handle or Edit → Fill → Series | Same as Sheets | Ctrl + E (Flash Fill) |
| Find & replace across sheets | Ctrl + H → Select 'All sheets' | Ctrl + H → Options → Within: Workbook | Ctrl + H → Alt + A |
