How accountants are using ChatGPT with Excel
When old tech meets new tech it's generally a recipe for disaster, but in combining the latest generative AI tools with the accounting world's favourite software, accountants around the world have found plenty of benefits.
As sure as night follows day, the advent of a new type of technology always brings portents of doom for Excel in accounting. Over the years there have been a great number of contenders, from cloud accounting and forecasting software to workflow and automation tools.
However, the emergence of generative artificial intelligence (AI) programs has somewhat bucked this trend. Instead, intrepid accountants are using their existing knowledge of Excel to trial the limits of the new tools on the market, and potentially open up new areas of efficiency for the profession and beyond.
In this piece, AccountingWEB rounds up a small sample of the ways accountants are using ChatGPT with Excel. If you’ve used it in ways that aren’t included or mentioned, please feel free to add them in the comments section below.
As with all software that processes confidential data, always follow the correct due diligence procedures before entering any information.
Pivot Tables and macro explainers
Across the pond first, and in this excellent introductory piece for the Journal of Accounting, accounting professor Kelly L. Williams puts ChatGPT through its Excel paces, such as creating PivotTables and writing macros.
By using a series of prompts, Williams was able to get the chatbot to describe how to make a Pivot Table using multiple sources of data in Excel.
As a follow-up, Williams also prompts the tool to produce step-by-step instructions to build a macro to accomplish a specific task.
Formula building
Remaining stateside, the granddaddy of generative AI for accounting Jason Staats has been at the forefront of exploring how far the technology can be applied when it comes to the work accountants do on a daily basis.
In the video below (starting at around the 10-minute mark, there may be ads) Staats first outlines how to make a pivot table from sample data.
He then goes on to give an example of how he uses Excel to make formulas.
“Getting a list of unique things in Excel is always a pain,” said Staats, who then prompts ChatGPT to write him a formula to create a list of the unique items he’s looking for from his sample data – in this case individual numbers of kills in a game of Fortnite.
ChatGPT returned the formula and also explained what the formula does and where to use it.
Staats also outlined how ChatGPT could be used to explain existing formulas.
“If you open up a spreadsheet that your colleague wrote and you don’t know what that formula means, you could copy-paste that formula in with some [anonymised] example data and ChatGPT will give you a step-by-step explanation of how
Expense categorisation and data cleansing
As part of a longer explainer on ChatGPT for accountants, CPA and QuickBooks adviser Hector Garcia lays out how the tool can be used for expense categorisation.
In the video below (starting at around the 17-minute mark, there may be ads), Garcia specifically gives ChatGPT a role as ‘Categorise-bot’, listing its key responsibility as taking a credit card or bank statement and presenting cleaned-up, categorised data for his chart of accounts – together with a percentage rating for how confident it feels in its answers.
Garcia then adds a sample line from the credit card statement (pasted in from a spreadsheet) and ‘Categorise-bot’ goes to work, assigning a hotel or lodging expense category to the item. In a twist to the video, ChatGPT actually mis-categorises the item, so Garcia adds a further prompt to the system to help it remember in future when the same tab is used.
Once the initial training was complete, Garcia pasted in the items and asked ChatGPT to build a table He then went on to ask the bot to present the results in CSV format that can be copy-pasted back to Excel, and ChatGPT produced the results complete with a ‘copy code’ button.
Garica also demonstrates how ChatGPT can be used to clean messy data from a credit card or bank statement by removing superfluous numbers, commas or other unnecessary items – this time as ‘Payee-cleanup-bot’.
“Imagine you have a year’s worth of transactions, let’s say 1,000 lines, and you want to import your payee list into QuickBooks or Xero but you don’t want to input the items with all the extra code [from the statement],” said Garcia
He then demonstrates how this can be beneficial to accountants – having a clean payee list it makes matching bank feed items easier, for example.
Spreadsheet co-pilot
In an article and video for In the Black, Australian accountant and Excel trainer Neale Blackwood analyses whether ChatGPT can be used as a kind of spreadsheet co-pilot for accountants when using the tool.
Blackwood kicks off his ChatGPT-Excel analysis with a couple of easy questions about date and data validation shortcuts but quickly goes through the gears, asking it to produce a formula that rounds to the nearest five cents and provide a formula to extract the sheet name – both of which it gets right.
He also asks what the top ten Excel functions accountants should learn. ChatGPT replied that it doesn’t give recommendations, but instead responded with 10 functions it said accountants use a lot, including SUM, AVERAGE, IF and VLOOKUP. Blackwood agreed with some of the functions listed but noted a lot of popular functions were missing.
Blackwood then serves up a curveball for the bot, asking a question that often splits the Excel community: “Should you use range names in Excel?” ChatGPT replies that you should, and provides what it believes to be evidence to back up its answer. It doesn’t provide any downsides to range names.
You might also be interested in
Tom is AccountingWEB's technology editor, providing unbiased news and analysis from the accounting tech universe. Got a tech tale to tell? Message me on the site or email [email protected]
Replies (5)
Please login or register to join the discussion.
A rather interesting article, Tom. I've always seen excel as the most versatile piece of software on the market complete with add-ons, limited only by memory. That's one of the reasons I use VT as it is an excel add-on.
I don't want to come across too much of a sceptic, but you can type the same questions about pivot tables and formulae in to google and get the instructions back.
Having it read and analyse data for you is potentially a bigger boon, but it does sound an awful lot like bank rules in cloud bookkeeping which you can use without firing your client's data in to the ethically dubious pit of Large Language Models where it may or may not be stored forever and used without consent.
I cannot believe any sane accountant would use chatgbt , the answers I have had to some fairly basic questions were comical . Stop the hype .
Some good ideas but there are two ways mentioned in which you should *not* use ChatGPT:
- Don't use it to build a formula to find unique values. Just use the UNIQUE function.
- Don't feed it sensitive client or personal information, like, say, bank statements... *actual facepalm*
www.icaew.com/GenAIGuide for all the Dos and Don'ts :)