Maximize Productivity: Replace Complex Excel Formulas with ChatGPT for Effortless Solutions

Quick Links

Key Takeaways

You can use ChatGPT to create Excel formulas even without prior knowledge of the tool.
ChatGPT can help you fix errors and incorrectly configured formulas.
ChatGPT can make mistakes, so verify formula results manually for accuracy.

In a fast-paced work environment, you need to maximize efficiency, and AI tools like ChatGPT can make short work of tedious tasks, such as creating Excel formulas. If you haven’t yet tried ChatGPT for custom Excel formulas, you’re missing out on a serious game changer.

The tips provided in this article aren’t limited to Excel; they’re also applicable to other spreadsheet tools, such as Google Sheets and Apple Numbers. Additionally, this guide focuses on the free version of ChatGPT based on the GPT 3.5 model. If you have access to GPT 4, you can use the Advanced Data Analysis tool to take screenshots and upload Excel files directly to GPT 4 for a more seamless experience.

How to Get a Quick Excel Formula From ChatGPT

If you dread the mere thought of opening Excel, let alone creating formulas, you’ve come to the right place. ChatGPT offers you an easy way to create Excel formulas with zero practical experience required.

All you have to do is give ChatGPT an explanation of what you want to do, and it’ll give you a formula, along with detailed instructions on how to implement it. Just make sure to mention the range (columns and rows) that you want to apply the formula to, and it’ll do the rest for you. In this example, I have an assortment of orders for various items with varying prices and profit margins.

My goal is to calculate the revenue and profit for each order. While the formula required to do this isn’t complex, let’s just assume that I knew nothing about Excel, and it was my first day on the job. I’d turn to ChatGPT and ask it for a formula:

After pasting the formula into the rows, dragging it down to automatically fill the data , and changing the format to USD , I was left with exactly what I was looking for.

This is just a basic example to show you how ChatGPT can determine what formulas you need based on what you’d like to do. Let’s do something slightly more complicated by asking it to help us calculate the average profit margin and most profitable item.

After implementing the formula, I was once again left with exactly what I was looking for. I double-checked the numbers by calculating everything manually to confirm the formulas worked correctly.

If you’re already an Excel aficionado and prefer making formulas yourself, you can ask ChatGPT to suggest the best function for your unique scenario. Let’s say I wanted to import information about the revenue and profits from my orders sheet to a tax sheet. ChatGPT gave me four viable formulas that I could use, along with a brief explanation of how they work.

These are just a few basic examples demonstrating what you can do with ChatGPT and Excel. You can use your AI buddy to build upon formulas and organize your data in any way you can imagine. There’s even a new experimental function for custom GPT prompts . The point is that you never have to think about which formula to use or even know how it works. Still, it helps to know a thing or two about Excel to get the most out of it. Check out our Excel for Beginners guide for a quick crash course.

If you’ve subscribed to Copilot Pro, you can add GPT-4 Turbo directly to Excel . This integration allows Copilot to analyze your data and suggest relevant formulas in addition to providing direct access to GPT.

You Can Create Formatting Rules With ChatGPT

Excel has a complex interface that makes it hard to navigate, especially for novices, and conditional formatting rules require you to poke around to figure them out. In the following example, I have a list of articles with columns that track whether they have been written and have photos.

I gave ChatGPT detailed instructions to help me color-code the table so that I could keep track of what articles were still in progress. Here’s my prompt, followed by ChatGPT’s response:

I went through the steps exactly how ChatGPT laid them out, and the results are perfect. Even if I had no prior knowledge of using Excel, I would be able to create the formatting rule to keep the project organized.

ChatGPT Can Troubleshoot and Tweak Your Formulas

Don’t worry if one of the formulas ChatGPT gave you isn’t working as intended—it happens to me all the time. The fix can range from telling ChatGPT, “Hey, this doesn’t work.” to explaining the problem in detail until ChatGPT figures out the issue.

In the following example, I wanted to calculate the return on investment for my articles, but the first formula that ChatGPT gave me didn’t take into account whether the article had been written yet, making the formula show that I lost $100 for the last two articles. I informed ChatGPT about the discrepancy, and it gave me a new and improved formula.

You can also use ChatGPT to identify what’s causing an error. Let’s say an event organizer was trying to calculate the total number of passengers from each city using a simple addition formula and encountered the #VALUE! error because they mixed text and numbers. After explaining the issue to ChatGPT, it immediately recognized the error and gave a fix accompanied by a simple explanation.

You Still Need to Check the Formulas Manually

While ChatGPT is undoubtedly one of the “smartest” tools humans have ever created, it’s not perfect. It might create a formula that doesn’t work correctly, which isn’t a problem when Excel spits out an error. However, in instances where a formula seemingly functions correctly but has omitted an important parameter, it can create a false sense of security.

This behavior can mislead you into thinking that the data shown is correct when it’s actually being miscalculated. This is why you have to double- and triple-check that your formulas are correct by calculating a few random entries manually. Also, if you gave ChatGPT specific cell ranges and then made changes after inserting the formula, you must ensure that your changes didn’t affect the formula.

ChatGPT is a real Excel pro, with the caveat that it can’t actually see or understand your sheets, so you have to make sure the formulas work correctly. All you need is a basic understanding of how Excel works, and ChatGPT can create advanced formulas tailored to your unique scenario.

Win11 Tips

Maximize Productivity: Replace Complex Excel Formulas with ChatGPT for Effortless Solutions