AI is moving fast. 50 years fast but who's counting? As with any new wave, there is initial excitement and the world has seen it in bright and colourful fireworks with Open AI’s new conversational front-end, ChatGTP. This technology has some emergent properties that were originally unexpected.
While the ai specialist knew that it would generate English text that would mimic human speech based on the late dataset of textual and other information that they collected. They did not realize it would write code or have answers for pretty technical questions. And that is why we are here.
While we are pretty sure you know that ChatGPT can generate a limerick or it can have Jerry Seinfeld explain quantum physics to George and Kramer, it can also help you with Excel. A lot has been written about code generation, but ChatGPT can give answers all about Excel. It can do simple things, like check your syntax or explain a formula, it can also do more complex things like help you set up a budget analysis or a discounted cash flow model.
Excel was first released in 1985, making it almost 40 years old. It is one of the most popular business apps and programming languages in the work and almost all businesses use it in some shape or form from keeping lists to developing demand forecasting supply chain management systems based on stochastic analytics and live data feeds.
If you’ve ever learned it in finance class or had to dive into to the help files, it has hundreds of functions related to layout, data manipulation, arithmetic, trigonometry, statistics and more. It has uses across all sectors and has general and specific uses that keep it growing. Without counting special add-ins, Excel has over 500 formulas and even more function points when you look at the ever-evolving ribbon bar.
So while it won't generate a whole workbook for your next quarterly business review (yet), let’s look at something that you can use to make your life easier than digging around to find out how to concatenate strings from 3 different columns while reformating the result into something readable.
First, let’s look at how you find out how to do things now in Excel. Most people just learn it “organically”, you just use it starting with simple things, and then explore it to keep adding complexity. This is a superpower of all spreadsheets. They are easy to use to do simple things. But as things get harder, what do people do?
Here’s a sample problem, some simple formatting of a file you just got, and it has a person's first name and last name in a column, phone number with area code, in different formats, and email address. For your needs, you want to have the following columns formatted: First Name, Last Name, Area Code, Phone (999–9999). email and company domain.
You could do this manually, but, surely there is a fancy formula you could use.
Here are the most common approaches:
- Phone, SMS, Slack, a friend. Everyone has an Excel Wizard in their lives
- Follow along the type-ahead hints that help you fill in the parameters you need to calculate something like a mortgage payment
- Use Microsoft Help… be prepared to spend some time. Often the help needs help.
- Read the Manual, or get a simpler guide like “A Dummies Guide to Excel”
- Take a course, there are lots of free ones available, but that takes lead time and patience to cram all that in
- Do a Google Search? Youtube?
Ok let's try a Google Search with the question I posed…
Well, that looks like 30 or 40 minutes of research.
I will skip phoning a friend for now, and use ChatGPT Instead to see what kind of help I can get…
Me: Here’s a sample problem, some simple formatting of a file you just got, and it has a person’s first name and last name in a column, phone number with area code, in different formats, and email address. For your needs, you want to have the following columns formatted: First Name, Last Name, Area Code, Phone (999–9999). email and company domain.
ChatGPT: Sure, I’d be happy to help with that! It sounds like you need to extract specific information from the file and format it into separate columns. Here’s a possible way to do it:
Open the file in a spreadsheet program, such as Microsoft Excel or Google Sheets.
Create five new columns next to the existing column with the person’s name, phone number, and email address. Label them “First Name,” “Last Name,” “Area Code,” “Phone (999–9999),” and “Company Domain.”
For the “First Name” column, use the formula =LEFT(A2,FIND(“ “,A2)-1), where A2 is the cell containing the person’s name. This will extract the first name from the full name. Drag the formula down to apply it to all the rows.
For the “Last Name” column, use the formula =RIGHT(A2,LEN(A2)-FIND(“ “,A2)), where A2 is the cell containing the person’s name. This will extract the last name from the full name. Drag the formula down to apply it to all the rows.
For the “Area Code” column, use the formula =LEFT(B2,3), where B2 is the cell containing the phone number. This will extract the first three digits of the phone number, which is the area code. Drag the formula down to apply it to all the rows.
For the “Phone (999–9999)” column, use the formula =TEXT(B2,”000–0000"), where B2 is the cell containing the phone number. This will format the phone number as XXX-XXXX. Drag the formula down to apply it to all the rows.
For the “Company Domain” column, use the formula =RIGHT(C2,LEN(C2)-FIND(“@”,C2)), where C2 is the cell containing the email address. This will extract the domain name from the email address. Drag the formula down to apply it to all the rows.
You can now sort or filter the table by any of the columns to easily find the information you need.
I hope this helps! Let me know if you have any further questions
Ok, now we are talking. This is the advantage of the idea of ChatGPT — it is conversational and generative. Google used the first 32 words of my problem and using its massive index of internet files, it provided the closest matches for me to explore. That is helpful but it leaves a set of research for me to do.
ChatGPT is different. It reads my whole problem, makes sense of it, and then generates what it thinks is the answer to the problem following the rules and grammar of Exel formulas. It explains the steps in clear English and I can cut and paste formulas to try them on my sheet. Bliss.
Be warned, the chatbot is not always correct, but as you can see, it’s close enough to try, and depending on your phone pal, is cheerful and uses super clear language for you to use and for you to learn from.
There is a lot more of course — you are limited by your imagination, and skill in asking clarifying questions. Here is another scenario. Say you change roles and inherit a complicated workbook that is part of your monthly closes, and you have to do it. Say you are not clear on what a formula does, like a nasty Let(). Here is the prompt
What does this formula do: LET(coupon, 0.05, face_value, 1000, maturity, 10, discount_rate, 0.08, coupon_payment, coupon*face_value, pv_coupon, coupon_payment/(1+discount_rate)^SEQUENCE(maturity), pv_face_value, face_value/(1+discount_rate)^maturity, present_value, SUM(pv_coupon)+pv_face_value)
ChatGPT — The given function uses the LET function in Excel to define several variables and then calculates the present value of a bond with the given parameters.
Here is a step-by-step breakdown of what the function does:
The LET function assigns values to four variables:
coupon = 0.05 (the annual coupon rate)
face_value = 1000 (the face value or par value of the bond)
maturity = 10 (the number of years until the bond matures)
discount_rate = 0.08 (the discount rate or yield to maturity)
The function then calculates the annual coupon payment by multiplying the coupon rate (0.05) by the face value (1000), resulting in a coupon_payment of 50.
Next, the function calculates the present value of each coupon payment using the SEQUENCE function. This function creates an array of numbers from 1 to maturity (10), which is then used as the exponent in the discount factor (1+discount_rate)^n. The result is an array of discounted coupon payments.
The function calculates the present value of the face value by dividing the face value (1000) by the discount factor (1+discount_rate)^maturity.
Finally, the function calculates the total present value of the bond by summing the discounted coupon payments (pv_coupon) and the discounted face value (pv_face_value).
Overall, the function calculates the present value of a bond with a fixed coupon rate, face value, maturity, and discount rate.
This is pretty useful, quick and useful help. There a lot more, and here are some example uses for using ChatGPT and Excel to learn faster and be more efficient. Questions are not limited to formatting formulas, you can go wider in seeking information on using Excel in different ways It is interesting to mix your specific question and assume more information into the conversation. Here are some more useful prompts:
- How do I create a 3-sheet financial model with all the common ratios
- Where can I find a data source to add city and state to a spreadsheet where I have IP addresses?
- How can I use a web service to get live stock price history for IBM in 2021?
- How do I add a Monte Carlo to a 3-sheet model to test scenarios?
- What are 5 different types of DCF analysis? Give a summary of each, the formulas used and an example of usage.
- What are the pros and cons of VAR analysis?
- What are good ways of presenting charts of data for stock trend analysis?
- Make me a 20-question quiz to practice xlookup with financial examples. After the quiz, give the answers with formulas and logical steps.
The last thing I tried to prepare for this post was a continuing education quiz. I just searched for one online Excel test and found one from the Corporate Finance Institute. It was a sample Financial Analyst Certification practice test. You can try it here. The good news, it was easy to find. Honestly, there were so many questions on the ctrl key shortcuts, that I could not pass the test. Interestingly, ChatGPT couldn't pass it either, it got wrong answers, that I knew. Together we got 85% and passed together.
I’d did learn. But, let’s be clear, I would have learned more if I studied the work and practiced exercises. For education purposes, I know I will strongly recommend my team to support their work, and for training, I won't be using certification tests anymore, they are too easy to game. I will develop case base tests using GPTs to actively train resources using real business cases with coaching.
So what does the future hold? With Microsoft’s partnership with OpenAI, everyone should expect more advanced integration right in Excel so it is easy to ask questions and have the Co-Pilot check and augment your work. I can see that portion of your sheets will be generated rather than clicking around the Ribbon or fussing with formula patterns. If advances in programming and gaming are a guide, we will get more and more advanced tools to create models and analysis and to check our work to get rid of error rates that happen today win Excel in enterprises.
I can’t wait.
