Excel comes with a vast array of functions and formulas that can be used to perform various calculations and operations. However, many users struggle to use these features effectively and may need help constructing the right formula for their needs.
ChatGPT can be used to construct custom formulas with minimal effort quickly. It uses natural language processing (NLP) software to understand user requests and returns a corresponding Excel formula that will do the job. This allows users to quickly build formulas without learning the complexities of Excel’s programming language.
In this tutorial, we will learn how to use ChatGPT to create simple formulas in Excel. Let’s begin!
We’ve published two short videos to give you a quick overview of how to use ChatGPT with Excel!
Building Excel Formulas with ChatGPT
Here are some examples of basic excel formulas that can be generated using ChatGPT:
1. If you want to generate a formula to return the first word from cell A2, you can give it the query
“write an excel formula that returns the first word from cell A2. Only provide the formula, do not provide an explanation.
ChatGPT returned the following: =LEFT(A2,FIND(” “,A2)-1)
2. Formula to return everything after the first word from cell A2:
write an excel formula that returns everything after the first word from cell A2. Only provide the formula, do not provide an explanation.
ChatGPT returned the following: =RIGHT(A2,LEN(A2)-FIND(” “,A2&” “))
3. Formula to return everything after the character “@” from a cell:
write an excel formula that returns everything after the character “@” from a cell. Only provide the formula, do not provide an explanation.
ChatGPT returned the following: =RIGHT(A2,LEN(A2)-FIND(“@”,A2))
4. Formula to give the age of a person based on their date of birth in cell F2:
write an excel formula to give the age of a person based on their date of birth in cell F2. Only provide the formula, do not provide an explanation.
ChatGPT returned the following: =DATEDIF(F2,TODAY(),”Y”)
More Advanced Excel Formulas using ChatGPT
ChatGPT can also generate complex formulas by understanding user queries. Here are some examples of advanced Excel Formulas that can be generated using ChatGPT:
1. Formula to Lookup and Grab Data from Other Sheets
Write an excel formula to output the company name based on its ID. Search cell A2 in the current sheet against column A in the ‘Company-List’ sheet and then output the value in column B for that row.
ChatGPT returned the following: =VLOOKUP(A2,’Company-List’!A:B,2,FALSE)
2. Formula to Lookup and convert currency
Cell D2 in the current sheet contains a currency code, and cell E2 contains the cost. The “Currency-Values” sheet contains the currency code in column A, and its value in USD in column B. Create an excel formula that takes the cost in cell E2 and outputs the cost in USD in cell F2.
ChatGPT returned the following: =VLOOKUP(D2,’Currency-Values’!A:B, 2, FALSE)*E2
Conclusion
Using ChatGPT to construct Excel Formulas can be a great time saver. It allows users to quickly generate formulas without understanding the complexities of the Excel programming language. With more advanced features, the possibilities are endless! Give it a try and see how ChatGPT can help you construct your own custom formulas. Good luck!
