Have you ever spent hours writing formulas in Excel, trying desperately to make the computer understand what you want it to do? It can be frustrating, or even seemingly impossible, to translate your broad desires into the hyper-literal language of a computer.
When we deal with any other language barrier, the answer to our problems is very simple: use a translator. Everyone gets to use a translator to interact with people: UN ambassadors, foreign pro athletes, even your aunt trying to order lunch on vacation. So why don’t people who interact with computers have that same luxury?
Well, it turns out the perfect translator has been widely accessible for months now. Since its launch in November 2022, ChatGPT has made waves in every area of professional life, from copywriting to the bar exam. But most useful for finance professionals is its ability to speak the same language as our beloved Microsoft Excel.
In this article we have compiled some of the best ChatGPT prompts for Excel to give to your new translator. View this guide as a good starting point, but feel free to modify our prompts to suit your specific needs.
Turn ChatGPT into a spreadsheet
Prompt: I want you to act as a text-based Excel. Only reply back with a text-based 10 row Excel sheet with row numbers and lettered columns. I will tell you what to write into cells and what formulas to use, and you will only reply with the result of the Excel table. First reply with the empty table.
One of the most impressive things you can do with ChatGPT is use it as a text-based spreadsheet. By entering the prompt above, ChatGPT will return a blank spreadsheet for you to work with. You can ask it to do virtually anything with the table, and it will return an updated version each time. In this environment, the need for perfect accuracy in formulas is gone, as you can tell ChatGPT what you want to happen and it will do the work for you.
Built-In Finance Knowledge
Prompt: Write an Excel formula to calculate profit margin. The net income is in cell A5 and the revenue is in cell C7.
We skipped a step here, did you catch it?
That’s right, we didn’t have to define profit margin! Given its massive knowledge base, ChatGPT already knows that the formula for profit margin is (Net Income/Revenue)*100. It even explained the steps it took in calculating the profit margin and explained its importance. This prompt is incredibly flexible: it works the same way for even the most complicated finance formulas.
Count Unique Values
Prompt: Write an Excel formula to count the number of unique values in a list.
A classic pain point resolved! Excel has never had one simple function to achieve this count, but ChatGPT has us covered. As you can see, it gives a phenomenal level of detail in its answer, providing a universal formula as well as an example and explanation.
Format Phone Numbers
Prompt: What formula can I use to put phone numbers into standard format in Excel?
With this formula, ChatGPT can help you automate the generally tedious process of formatting a column of phone numbers. ChatGPT offers a lengthy explanation of how its formula works, but the only work on your end is hitting copy and paste.
Explain Complicated Formulas
Prompt: Can you explain the index match formula in Excel in simple terms?
For some of Excel’s most complex formulas, the default explanations might leave you even more confused. ChatGPT can help by generating quick and concise explanations for Excel formulas. You can even modify the response by asking to make it shorter or simpler.
Bonus: Play Tic-Tac-Toe!
Prompt: Can we play tic-tac-toe on a 3×3 Excel sheet?
After a long day working alongside your new buddy ChatGPT, it might be nice to relax and enjoy some friendly competition. ChatGPT can create a 3×3 spreadsheet for you to play on, all you have to do is give the coordinates of your next move.
While they have the potential to save you hours of manual labor, these prompts are still just scratching the surface of what ChatGPT can do. We encourage you to experiment and modify the prompts as much as possible to extract the maximum value out of the software. Just remember, ChatGPT has only been working in finance for a few months! Make sure to double-check the information it gives you before you include it in any financial documents.