ChatGPT Can Help You Excel

Accountants and Excel go hand-in-hand. You will be hard-pressed to find another profession that spends as much time looking at spreadsheets. Despite this, most accountants come nowhere close to utilizing the potential of this software. Regardless of the simple design and interface, you can take advantage of the capabilities of this system and its functions to create powerful solutions.

One area that we have found ChatGPT to be surprisingly practical for accountants is with assisting in the creation of complex Excel formulas. Why spend time searching through results on Google to try to find a solution that fits the context of your problem when you can simply ask ChatGPT?

Here are some example prompts I tested:

What Excel formula can I use to stack two ranges of data into one column?

=IFERROR(INDEX($A$1:$A$5, ROW()), IFERROR(INDEX($B$1:$B$5, ROW()-COUNT($A$1:$A$5)), ""))

This is an interesting example as Excel recently introduced a new function called VSTACK (Google Sheets uses FLATTEN) is an easy solution to this problem. The formula that ChatGPT generated isn’t the most efficient formula but is an effective solution that even considers the need to account for potential errors in the calculation.

I have a table of values in Excel that is organized by month (columns) and description (rows). What formula can I use to sum all of the values that have a description starting with the word "sales" and have a date greater than June 1, 2022?

=SUMIFS(INDEX(B2:Z100, MATCH("Sales*", A2:A100, 0), 0), B1:Z1, ">"&$A$1)

Part of the response provided by ChatGPT for this request asked me to store the June 1, 2022 date in cell A1.

As you can see from this prompt, you are able to provide ChatGPT with specific context of the structure of data you are working with so it can give you a relevant solution. If I included in the prompt the exact cell references of the data (ie. B1:K25), I could simply copy and paste the formula generated.

Using ChatGPT to assist with creating formulas may not result in the most efficient or direct solutions from a computing point of view but it is a highly effective tool. The suggestions it provides introduces you to different approaches to addressing your specific problem.