On
Can Gemma 4 Replace ChatGPT for Spreadsheet Analysis? We Tested It

Every time Google releases a new open-weights AI model, the internet asks the same question: "Can I use this instead of paying for ChatGPT?" With Gemma 4, that question actually deserves a thorough answer—because for the first time, a free, locally-running model is genuinely capable enough at spreadsheet work to make the comparison meaningful.

We put Gemma 4 and ChatGPT through a series of real-world spreadsheet challenges. Not synthetic test cases, but messy actual data, broken formulas from production environments, and genuine VBA requests pulled from actual projects.

How We Set This Up

For consistency, we ran Gemma 4 (the 27-billion parameter version) locally via Ollama on a standard computer and tested ChatGPT (GPT-4o) through the web interface. Both models received identical prompts, word for word.

We scored each test on three criteria: Did the output actually work? (Does the formula or code run correctly?) How useful was the explanation? (Would a user understand what's happening?) And did it handle edge cases? (Does it account for empty cells, errors, or unusual data patterns?)

Test 1: Data Cleaning—A Messy CSV With Inconsistent Dates and Mixed Formats

The scenario: A 2,000-row CSV export from a legacy CRM system. Dates appear in at least four formats—dd/mm/yyyy, mm-dd-yyyy, yyyy.mm.dd, and plain text like "15 March 2025". Phone numbers mix country codes with local formats. Product names have random capitalization and trailing spaces.

Here's the prompt we gave both models:

I have a CSV with a Date column that mixes dd/mm/yyyy, mm-dd-yyyy, yyyy.mm.dd, and written dates like '15 March 2025'. I need a single formula approach to normalise everything to dd/mm/yyyy in Excel. Also suggest a strategy for cleaning phone numbers that mix +91-XXXXXXXXXX with 0XX-XXXXXXXX formats and product names with inconsistent capitalisation.

Gemma 4's answer was solid. It proposed a nested approach using DATEVALUE combined with TEXT and SUBSTITUTE, and correctly identified that written dates like "15 March 2025" need separate handling. It suggested a helper column strategy—parsing each format using IFERROR and combining them. For phone numbers, it recommended chaining SUBSTITUTE calls to strip dashes and spaces, then using RIGHT to extract the last 10 digits. For product names, it correctly suggested PROPER(TRIM()).

ChatGPT's answer was more polished. It provided a single nested formula using LET to define intermediate variables, making the formula easier to read. It also proactively mentioned Power Query as an alternative—something Gemma 4 didn't. On phone number cleaning, ChatGPT added a warning that certain Indian mobile prefixes could be misinterpreted and suggested a validation step.

Winner: ChatGPT—but not by a landslide. Gemma 4's approach works fine and is production-ready. ChatGPT's answer is more comprehensive, better structured, and shows deeper awareness of real-world edge cases. If you're already familiar with data cleaning strategies, Gemma 4 gives you enough to work with.

Test 2: Pivot Table Logic—Designing and Building Pivot Formulas

The scenario: A sales dataset with Region, Salesperson, Product Category, Quarter, and Revenue columns. We asked each model to propose a suitable Pivot Table layout, then provide SUMIFS formulas for users who need a formula-based approach (common when data updates frequently and you want automatic recalculation).

The prompt:

I have a sales table with Region (North/South/East/West), Salesperson (names), Product Category (Electronics/Furniture/Software), Quarter (Q1-Q4), and Revenue. Suggest a pivot table layout to analyse revenue by region and category, then give me the SUMIFS formulas to replicate this as a formula-based summary table.

Gemma 4 proposed a clear two-dimensional layout with regions as rows and categories as columns—exactly what most analysts want. Its SUMIFS formulas were correct, with proper absolute and relative references. It also suggested adding Grand Total rows and columns using SUM.

ChatGPT created a similar layout but went further. It suggested GETPIVOTDATA for users who prefer actual Pivot Tables, offered SUMPRODUCT as an alternative for older Excel versions, and included formatting tips—color schemes, axis labels, and warnings against 3D charts for executive presentations. It also proposed a filter-based dashboard approach for interactivity.

Winner: ChatGPT. Mainly because of depth and supplementary suggestions that less experienced users find helpful. Gemma 4's core answer is accurate and useful—the SUMIFS formulas work perfectly. The gap here is in "what else should you consider" rather than accuracy.

Test 3: Building VBA Macros—Consolidating Multiple Worksheets

This is where ChatGPT was expected to dominate. It did—but Gemma 4 surprised us.

The task: Write a VBA macro that loops through all worksheets in a workbook (except a "Summary" sheet), copies data from a consistent range (A2 to the last row in column D) on each sheet, and sequentially pastes it into the Summary sheet, adding a source sheet name column.

Gemma 4 produced a working macro. It correctly used For Each ws In ThisWorkbook.Worksheets, included the check If ws.Name <> "Summary", found the last row using Cells(Rows.Count, 1).End(xlUp).Row, and appended data to the Summary sheet. The source sheet name was added to column E. The code ran without errors on our test workbook.

ChatGPT built a more robust version. It included error handling with On Error Resume Next around worksheet operations, added a confirmation dialog at the end showing how many rows were merged, cleared the Summary sheet before writing (with user confirmation), and included comments explaining each section. It also wrapped everything with Application.ScreenUpdating = False for performance.

Winner: ChatGPT—on production quality. Gemma 4's macro works, which is genuinely impressive for a free local model. But ChatGPT's version is what you'd actually want to deploy in a business setting—with error handling, user feedback, and performance optimization. For anyone learning VBA through AI-assisted macro building, both are useful starting points.

Test 4: Chart and Visualization Recommendations

We described a dataset to both models: monthly revenue and customer count for four product lines over two years, aimed at non-technical executives. We asked each model what chart types to use and how to structure the visuals.

Gemma 4 suggested a line chart for revenue trends over time (one line per product), a grouped column chart for comparing product lines by quarter, and a combo chart (line + column) for revenue versus customer count on dual axes. Solid, conventional recommendations.

ChatGPT offered similar suggestions but added sparklines for an executive summary table, a waterfall chart to show year-over-year revenue changes, and specific formatting advice—color palettes, axis label formatting, warnings against 3D charts for exec presentations. It also proposed a dashboard layout with logically ordered charts.

Winner: ChatGPT—on presentation awareness. If you've worked with charts before, Gemma 4's recommendations are perfectly adequate. ChatGPT's advantage lies in design and communication strategy—the kind of advice that separates a technically correct chart from one that actually communicates effectively to stakeholders.

Test 5: Formula Debugging—Diagnosing a Broken Formula

We pasted this broken formula into both models and asked them to identify and fix all issues:

=IFERROR(VLOOKUP(A2,Sheet2!B:F,5,TRUE),"Not Found")+IF(C2>"100",D2*0.1,D2*0.05)

There are multiple problems here: The VLOOKUP match type should probably be FALSE for exact matching; the IF condition compares C2 to the text string "100" instead of the number 100; and IFERROR only wraps the VLOOKUP but the whole expression can still error if the IF part fails.

Gemma 4 caught two of three issues. It correctly identified the TRUE/FALSE match type problem and the text-versus-number comparison in the IF statement. It missed the incomplete IFERROR coverage.

ChatGPT caught all three. It rewrote the formula with IFERROR wrapping the entire expression, changed TRUE to FALSE, removed quotes around 100, and suggested XLOOKUP as a modern alternative for Microsoft 365 users. It also explained why each fix mattered.

Winner: ChatGPT, clearly. Formula debugging requires multi-step reasoning across interacting parts of an expression, and ChatGPT's deeper analysis proved it. That said, Gemma 4 catching two of three is genuinely useful—many users would solve their problem with just those two fixes. For complex formula debugging, though, ChatGPT pulls ahead.

Results Comparison

TaskGemma 4ChatGPTWinnerNotes
CSV data cleaningGood—effective formulas, sound approachExcellent—LET formula, Power Query suggestionChatGPTGemma 4 sufficient for experienced users
Pivot Table logicGood—accurate SUMIFS, clean layoutExcellent—added GETPIVOTDATA, conditional formattingChatGPTCore formulas equally sound
VBA macro creationGood—functional macro, basic structureExcellent—error handling, user feedback, optimizationChatGPTGemma 4 code runs correctly as-is
Chart recommendationsGood—standard recommendations, reliableExcellent—design advice, dashboard layoutChatGPTGemma 4 adequate for experienced analysts
Formula debuggingAdequate—caught 2 of 3 issuesExcellent—found all 3 bugs, modern alternativesChatGPTMulti-layered reasoning favors ChatGPT
Data privacy and controlExcellent—runs entirely local, no data leaves your machineAdequate—cloud-based, data sent to OpenAIGemma 4Critical for sensitive business data
CostFreeFree tier with limits; Plus at $20/monthGemma 4No subscription required, unlimited usage
Speed (after initial load)Fast on GPU, slower on CPUFast and consistentTieDepends on local hardware

Practical Advice

After running these tests and using both models extensively, here's what we'd actually recommend:

Start with Gemma 4 for everyday work. Writing formulas, basic data cleaning logic, simple VBA—Gemma 4 handles all of this well and costs nothing. Install it through Ollama and let it run in the background.

Switch to ChatGPT for complex problems. When you hit something that requires deep multi-step reasoning, code analysis, or production-grade output, move to ChatGPT. The free tier covers most needs; the Plus subscription is worth it if you use it daily.

Use both for learning. Ask Gemma 4 a formula question, then ask ChatGPT the same thing. Compare the approaches. This is genuinely one of the most effective learning strategies available.

Default to Gemma 4 for sensitive data. If you're unsure whether your data belongs in the cloud, the answer is to use a local model. You can always ask ChatGPT again later with anonymized or sample data if you need its extra capability.

The real insight here is that we're finally at a point where "free and local" isn't a consolation prize—it's a genuine alternative for real work. ChatGPT still wins on depth and polish, but Gemma 4 has moved from "interesting experiment" to "actually useful."

Description: We compared Gemma 4 and ChatGPT across five real-world spreadsheet tasks. Here's what we found.

Related Articles