I Consulted ChatGPT, Claude, and Gemini for the Ultimate Excel Formula—Their Answers Were Surprising!

I used to dedicate a significant amount of my time to constructing and troubleshooting intricate Excel formulas. Nowadays, I can simply explain a problem to an AI, and it generates a solution within moments. However, when I requested the “best” Excel formula from ChatGPT, Claude, and Gemini, I received varied responses.

Three AI Assistants Approached a Common Problem Differently

One Prompt Led to Three Distinct Formulas

For this experiment, I presented the same query to ChatGPT, Claude, and Gemini, ensuring that each AI operated from identical context and instructions, rather than utilizing Microsoft Copilot.

My prompt was as follows:

I have an Excel table named T_Sales, which includes the columns Product, Jan, Feb, Mar, Apr, May, Jun, and an empty column called First Month Over 10000. I need a formula for the First Month Over 10000 column that identifies the first month where a row’s sales exceed 10,000. If no month surpasses 10,000, return “None”. I’m using Microsoft 365 Excel. Please provide the best formula along with a brief explanation of your choice.

This wasn’t an exhaustive test; rather, I wanted to observe how different AI assistants would tackle an identical Excel challenge.

Despite having the same prompt and objective, each AI made a unique choice in its approach.

ChatGPT proposed:

=LET(sales, T_Sales[@[Jan]:[Jun]], months, T_Sales[[#Headers],[Jan]:[Jun]], match_pos, XMATCH(TRUE, sales>10000), IFERROR(INDEX(months, match_pos), "None"))

Claude offered:

=LET(sales_data, T_Sales[@[Jan]:[Jun]], month_names, {"Jan","Feb","Mar","Apr","May","Jun"}, match_idx, XMATCH(TRUE, sales_data>10000), IF(ISNA(match_idx), "None", INDEX(month_names, match_idx)))

Gemini provided two options:

=XLOOKUP(TRUE, T_Sales[@[Jan]:[Jun]]>10000, {"Jan","Feb","Mar","Apr","May","Jun"}, "None", 1)

and:

=XLOOKUP(TRUE, T_Sales[@[Jan]:[Jun]]>10000, T_Sales[[#Headers],[Jan]:[Jun]], "None", 0)

Determining the Best Formula is Context-Dependent

Different Trade-offs Were Made by Each AI

When I applied each formula to my dataset in Excel, all returned the correct months. The variations were evident in the design choices made by each AI.

ChatGPT and Claude both utilized the LET function, allowing them to frame their formulas around clarity and organization rather than brevity. While this approach enhances the readability of complex formulas, it can also result in solutions that are overly intricate for the task at hand.

ChatGPT’s and Gemini’s second formula could be considered the most dynamic, as they source the month labels directly from the table headers (T_Sales[[#Headers],[Jan]:[Jun]]), ensuring that any header changes automatically update the references.

Claude’s formula and Gemini’s first formula were less dynamic, hardcoding the month names into the formulas ({"Jan","Feb","Mar","Apr","May","Jun"}), meaning that any change to table headers would necessitate a manual formula update.

Gemini focused on brevity, favoring the XLOOKUP method, which eliminated the need for separate INDEX and XMATCH functions, resulting in shorter formulas. However, this omission of named variables can make comprehending longer formulas more challenging.

Gemini’s inclusion of unnecessary match mode arguments (0 and 1) didn’t affect the results here, but they reflect how AI can confidently generate functional formulas without ensuring the most efficient option. Furthermore, it illustrates that AI can offer differing approaches, as seen with Gemini presenting two slightly different solutions in a single exchange.

The explanations accompanying each AI’s formulas were insightful. They elaborated on the mechanics of their solutions but overlooked broader design considerations: ease of maintenance, adaptability to future changes, and the potential benefits of simpler alternatives.

This limitation of asking AI for the “best” formula arises because it can assess technical accuracy but cannot grasp the true priorities of workbook users.

The Definition of the “Best” Formula is Subjective

Effective Prompts Require Human Insight

All provided formulas were technically correct. Excel often supports multiple viable methods for solving a single problem, yet the notion of “best” relies on intricate factors. You can instruct AI about your Excel version, preferences for newer functions, or if using helper columns is suitable. Still, it cannot know the motivations behind existing workbook decisions, the familiarity of coworkers with certain formulas, or the rationale behind unconventional methodologies.

Sharing an entire workbook could give AI more context, but it still wouldn’t clarify the reasoning behind your choices. It would be able to observe the formulas and data but would lack insight into the history, priorities, or constraints that informed the workbook’s design. Additionally, you need to consider the presence of sensitive data before disclosing it to an AI service.

However, as spreadsheets become increasingly complex, crafting accurate prompts becomes more challenging. A multifaceted forecasting model or report could encompass years of assumptions, design choices, and adjustments disseminated across numerous sheets. When it comes to conveying this background to AI, you may compromise much of the convenience it was supposed to provide.

AI can support the exploration of potential solutions, but the responsibility of selecting the most appropriate method lies with someone who comprehends the workbook’s objectives.

Utilize AI as a Formula Learning Tool

Embrace AI’s Assistance While Maintaining Authority

There’s no need to eschew AI in your spreadsheet tasks. Instead, consider it a tool for brainstorming ideas, elucidating formulas, and proposing solutions—not as the ultimate authority.

To achieve improved outcomes with AI in spreadsheets, set clear parameters:

• Indicate the specific version of Excel you’re utilizing to avoid compatibility issues.
• Provide a concise, representative sample of your data layout.
• Articulate the anticipated output and any methods you wish to explore or steer clear of.
• Clarify the proficiency level of potential users or maintainers of the spreadsheet.
• Specify any functions you would prefer to avoid or utilize.
• State whether helper columns are permissible.

Once an AI proposes a solution, the final assessment rests with you. Test the formula with real data, ensure it operates correctly in your workbook, and verify it’s comprehensible for those who will maintain it.

Employ AI Where It Contributes Value

This doesn’t imply that AI lacks a role in your spreadsheet processes. When given a clearly defined task and the right context, AI proves invaluable for clarifying formulas, generating sample data, and automating repetitive tasks. I recently tested this approach by asking Claude to create a series of Excel automations from scratch. It effectively designed tools that saved time—but I still needed to validate each before depending on it.