Boost Excel Productivity: How to Use ChatGPT for Formulas, Macros, and Data Extraction

Since its launch on November 30, 2022, ChatGPT has become a versatile AI assistant capable of chatting, writing code, fixing bugs, and more. Integrating it with Office tools like Word, PowerPoint, and especially Excel can dramatically improve productivity.

AI enthusiast PyCoach showcases how to use ChatGPT to write Excel formulas and macros, claiming a ten‑fold efficiency boost.

Using ChatGPT to Generate Excel Formulas

After creating a ChatGPT account (https://chat.openai.com/auth/login), you can ask it to produce formulas in natural language. For example, to sum a column of expenses, simply describe the task and ChatGPT returns a ready‑to‑use =SUM(B2:B13) formula that can be placed in a cell.

ChatGPT can also handle more complex queries such as counting months with expenses over $100,000 using COUNTIF, or summing unpaid fees with SUMIF. It even generates correct syntax for functions like VLOOKUP when you provide the required parameters.

Extracting Data

To pull area codes from a list of phone numbers, describe the task and ChatGPT returns a formula using MID and FIND:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Replace A1 with the appropriate cell reference to obtain the result.

Counting Unique Values

To count distinct area codes, ChatGPT suggests a SUMPRODUCT(1/COUNTIF(range,range)) formula. Adding the specific range yields:

=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))

Creating a VBA Macro with ChatGPT

ChatGPT can also generate VBA code. For example, to sort worksheets by tab name, it provides a macro that can be inserted into the VBA editor. If the macro produces errors, you can describe the issue to ChatGPT for debugging and refinement.

Overall, PyCoach’s experiments demonstrate that ChatGPT is a powerful assistant for Excel users, capable of generating accurate formulas, handling complex calculations, and even writing VBA macros, making it a valuable tool for anyone struggling with spreadsheet tasks.