How to use ChatGPT to write Excel formulas

ChatGPT has become one of the most useful tools for non-technical people. It doesnt completely replace the need to understand Excel – but it significantly reduces the barrier to performing complex tasks.

Many Excel users know what result they need, but don't know which formula to use. That's exactly the problem ChatGPT solves brilliantly. You simply describe what you want in simple language, and it will provide you with a formula ready to paste into your spreadsheet.

 

ChatGPT has become one of the most useful tools for non-technical people. It doesn't completely replace the need to understand Excel – but it significantly reduces the barrier to performing complex tasks. This article will guide you on how to use ChatGPT effectively, with helpful prompts, practical formulas, and honest limitations you need to know.

Write complex formulas using ChatGPT.

 

Let's look at some real-world examples. These are the kinds of recipe requests that would normally take experts 15-30 minutes to search on Google and test – but ChatGPT handles them in seconds.

 

Combining multiple conditions

Let's say you have a sales report: Column A is the name of the salesperson, Column B is the product category, Column C is the region, and Column D is the revenue. You want to calculate the total revenue when the salesperson's name is "Priya" and the region is "West".

Prompt:

Tính tổng tất cả các giá trị trong cột D, thay cột A bằng 'Priya' và cột C bằng 'West'

Result:

=SUMIFS(D:D, A:A, "Priya", C:C, "West")

ChatGPT will also note that you can replace hardcoded strings with cell references, making the formula more flexible.

Conditional text extraction

You have a column of email addresses and need to extract only the domain names (everything after the @ symbol).

Prompt:

Trích xuất tên miền từ các địa chỉ email trong cột A - mọi thứ sau ký hiệu @

Result:

=MID(A2, FIND("@", A2) + 1, LEN(A2) - FIND("@", A2))

On Microsoft 365 , ChatGPT can offer a more streamlined TEXTAFTER solution:

=TEXTAFTER(A2, "@")

This is why specifying your version of Excel is important – you might be presented with a more powerful function that you didn't even know existed.

Ranking with equal cases is handled correctly.

The standard RANK function handles equals arbitrarily. Ask ChatGPT for a formula that assigns the same rank to equal values ​​and ignores the next rank number, and it will give you RANK.EQ—or guide you to a COUNTIF-based approach for older versions.

 

=RANK.EQ(B2, $B$2:$B$100, 0)

Clean and transform data with the help of ChatGPT.

One of the most time-consuming parts of actual Excel work isn't the analysis itself—but rather getting the data into a usable format first. ChatGPT is a great help for this.

Data often comes from legacy or third-party systems with issues such as: leading and trailing spaces, inconsistent capitalization, dates stored as text, numbers formatted as text, merged cells that corrupt everything behind them, and columns that need to be split or merged.

For each of these cases, ChatGPT can generate both error correction formulas and step-by-step instructions on using Excel's built-in tools, such as converting text to columns or quick fill. Some useful prompts include:

• "Remove all leading and trailing spaces in column B, and convert it to uppercase/lowercase."
• "Column C contains dates stored as text in DD-MM-YYYY format. Please provide me with a formula to convert them to the standard Excel date format."
• "Separate the full name in column A into the first name and last name in separate columns."
• "Column D contains numbers formatted as text - some numbers have commas as thousands separators. Let's convert them to numerical values."

For converting dates to text, ChatGPT typically provides the following formula:

=DATEVALUE(TEXT(A2,"DD-MM-YYYY")) -- hoặc, trực tiếp hơn đối với chuỗi văn bản DD-MM-YYYY: =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

 

For a more comprehensive strategy in handling messy entered data, the article on how to quickly clean up messy data in Excel will be very helpful when using ChatGPT as a formula creation tool.

Create VBA macros without knowing VBA.

This is where ChatGPT truly bridges the gap. VBA—Excel's built-in automation language—has a syntax that's quite difficult to learn. Most professionals never learn it because the initial cost is too high for their daily needs. ChatGPT eliminates that barrier almost entirely.

You simply describe what you want to automate in plain language, and ChatGPT will write the macro. Paste it into the Visual Basic Editor ( Alt + F11 , then go to Insert → Module ) and run it. That's it!

Here's an example. Prompt:

Viết một macro VBA lặp qua tất cả các hàng trong Sheet1, và nếu giá trị trong cột C nhỏ hơn 0, hãy highlight toàn bộ hàng đó bằng màu đỏ.

Result:

Sub HighlightNegativeRows() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row For i = 2 To lastRow If ws.Cells(i, 3).Value < 0 Then ws.Rows(i).Interior.Color = RGB(255, 0, 0) Else ws.Rows(i).Interior.ColorIndex = xlNone End If Next i End Sub

ChatGPT will also explain the function of each part of the macro, which is very helpful if you need to modify it later or explain it to colleagues. Here are a few practical tips for creating good macros using ChatGPT:

• Always specify the worksheet name and the associated columns or ranges.
• Mention whether the macro should run once or be triggered by an event (such as opening a file or changing a cell).
• We request ChatGPT to add error handling so that macros don't silently fail when encountering unexpected data.
• Test on a copy of the file first - macros that modify data cannot be undone using Ctrl + Z

Conclude

ChatGPT cannot replace mastering Excel. Understanding why a formula works will help you work faster, more flexibly, and more effectively when AI tools are unavailable or provide incorrect answers. What ChatGPT does is shorten the time between "knowing what you need" and "having a formula to do it."

The professionals who gain the most value from ChatGPT are those who use it as a thinking partner: They clearly describe their problem, provide the formula, ask ChatGPT for an explanation, and then try to understand the logic purposefully. After a few months of working this way, they have mastered functions they would never have learned from scratch on their own.

Let's start simply. Take the formula you hate the most—the one you always Google whenever you need it—and ask ChatGPT to write it for you and explain it. Build from there. After just a few uses, you'll have a workflow that will significantly speed up any Excel-related task.

You've just finished reading the article "How to use ChatGPT to write Excel formulas" edited by the TipsMake team. We hope this article has provided you with many useful tech tips and tricks. You can search for similar articles on tips and guides. Thank you for reading and for following us regularly.