Save content
Have you found this content useful? Use the button above to save it to your profile.
Close-up of of the icon of the ChatGPT artificial intelligence chatbot app logo on a mobile screen
istock_stockcam_AW

How accountants are using ChatGPT with Excel

by

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.

2nd Nov 2023
Save content
Have you found this content useful? Use the button above to save it to your profile.

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.

Excel-ChatGPT
Journal of Accounting

As a follow-up, Williams also prompts the tool to produce step-by-step instructions to build a macro to accomplish a specific task.

Excel-ChatGPT
Journal of Accounting

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.

Replies (5)

Please login or register to join the discussion.

avatar
By johnjenkins
02nd Nov 2023 10:42

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.

Thanks (1)
By Duggimon
02nd Nov 2023 11:26

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.

Thanks (9)
avatar
By Calculatorboy
02nd Nov 2023 23:03

I cannot believe any sane accountant would use chatgbt , the answers I have had to some fairly basic questions were comical . Stop the hype .

Thanks (2)
avatar
By IanPay
03rd Nov 2023 15:59

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 :)

Thanks (1)
avatar
By Halex
04th Nov 2023 12:26

Bing seems to have come up with something similar I have noticed recently.

Thanks (0)