Good Work People Can't Stop Using Excel
Two ChatGPT-powered case studies to streamline Excel tasks and spark inspiration
Imagine tackling your most difficult work problems with a colleague who never gets tired and always has fresh ideas. That’s what my co-panelists and I explore in a webinar for National Association of Stock Plan Professionals (NASPP) members on “AI and the Future of Equity Plan Administration.” If you’re not a NASPP member but are interested in our niche, I highly recommend checking them out. They are not paying me! I pay them. I’ve been a NASPP member for the better part of a decade and still find their resources incredibly helpful.
In the webinar, we covered ways to use custom-built AI for equity administration and accounting. In addition, I created a use case demonstrating how ChatGPT can help with Excel when you don’t have the budget or time to build custom tools. I love creating new solutions, but sometimes you just need to get things done.
For me, Excel and ChatGPT are a natural pairing. I think in Excel. Before I jumped into the employee equity world I was in a series of finance roles where I relied on Excel modeling daily. I attended a four week on-the-job training under duress with the end goal to create huge, complex Excel models without using a mouse. Keyboard shortcuts only. If you really want to drive yourself nuts, might I suggest you try to do your job without using a mouse for a month?
Since the NASPP webinar, I’ve received requests for more examples of using ChatGPT with Excel. Here, I’ll dive deeper into the webinar use case and share how ChatGPT helped me create a new model to reconcile ESPP contributions in Excel.
Case Study 1: Comparing Excel Sheets with Sensitive Data
One of my co-panelists described an issue during a recent merger, comparing two Excel sheets to ensure everything from the merging company's equity platform made it into her own. While she’s proficient in Excel and knows how to compare data with formulas, she wanted to avoid spending time on what seemed like a simple task. She uploaded both sheets to her in-house GPT but…let’s just say it did not add value here.
I suggested trying ChatGPT. While it could likely handle the comparison, uploading sensitive employee data wasn’t an option. It’s not safe to load personally-identifiable information (PII) to any GPT unless it’s your own custom-built, firewalled solution. My priority was solving the task quickly without sharing PII. I described the problem in detail to ChatGPT, including the need to find discrepancies while avoiding both real data uploads and time-consuming anonymization:
My prompt is (overly?) detailed, because I’ve found that the more context I provide, the better the responses I get. While it might seem wordy, this approach ensures that ChatGPT fully understands the task and can suggest creative, relevant solutions right away. Note I copied and pasted the headers and an example first row right into ChatGPT, so it could understand the structure and formatting of both sheets.
There’s emerging research that suggests being polite to GPTs improves the quality of their responses. I’ve noticed this anecdotally, and it’s a great reminder to be nice - even to your AI! (I redacted the inevitable AI-apocalypse joke here, reach out if you must have it.)
The Process:
Create a Comparison Key: ChatGPT suggested combining key fields (like employee ID, grant type, and share count) into a single column. Jessie provided a super-secret Excel handshake (this formula) to combine key fields:
=A2 & "|" & B2 & "|" & TEXT(C2, "yyyy-mm-dd") & "|" & SUBSTITUTE(D2, "NSO", "NQ") & "|" & E2 & "|" & ROUNDDOWN(F2, 0) & "|" & TEXT(G2, "0.000000") & "|" & TEXT(H2, "0.00") & "|" & I2 & "|" & J2 & "|" & K2Detect Mismatches: ChatGPT then provided this formula to simplify spotting mismatches at a glance, turning a complicated cross-check into a simple "does this match?" question.
=IF(ISNUMBER(MATCH(M2, '[Outstanding_Equity_Compensation_12-31-24_Compare.xlsx]Sheet1'!$L$2:$L$2479, 0)), "Match", "Mismatch")Iterate and Adjust: ChatGPT helped uncover unexpected issues, like fractional shares in the original merger data even though their equity plan didn’t allow for fractional shares (hellooooo annoying equity platform data storage and formatting quirks). ChatGPT occasionally suggested overly complex solutions and reverted back to suggesting I add tons of columns and lookups, so I stayed critical of its output. You might sense a lil frustration below after the last of a series of suggestions to add more columns with formulas that didn’t work:
Key Takeaways:
Don’t Overlook the Basics: Simple checks you would likely think of on your own, like whether a grant exists in both sheets, might be lost when asking for a shortcut like this. The Comparison Key worked well for ensuring the grants listed in the new system matched the grant data in the old system, but didn’t tell us six of the grants from the original sheet were missing in action. Whoops! Next time I will ask ChatGPT to identify potential blind spots in its suggested solution.
Think Strategically: ChatGPT is a great collaborator, but you’ll need to guide it. If it goes down the wrong path, explain why you don’t want to do that and what you’d like to do instead.
Case Study 2: Building Semi-Automated Excel Models
This example demonstrates using AI to create a budget-friendly Excel model for reconciling Employee Stock Purchase Plan (ESPP) contributions across pay dates. The client needed something fast, easy to use, and maintainable without my ongoing involvement. I avoided Power Query and VBA because they can break when data formats change, a common issue with payroll and equity data.
Instead, I built a model that pulls data dynamically from future-dated tabs into a reconciliation tab without requiring constant manual adjustments. This was my idea sans GPT, but I got stuck on a formula to pull in the payroll deduction data without having access to or control over the future data size and structure.
The Process:
Dynamic Range Matching: we started with a formula to identify the range of rows in each future-dated tab. The number of rows will vary because number of employees paid changes over time. For example, cell
L$1dynamically calculates the number of rows in a tab based on the date header in cellL$31/15/2025:
=MATCH(1E+99, INDIRECT("'" & SUBSTITUTE(TEXT(L$3, "m-d-yy"), "/", "-") & "'!B:B"))Summing Contributions: ChatGPT then helped me create a formula to sum ESPP contribution data by employee (column B contains all employee IDs, so cell US!$B4 below is the first employee ID), by pay date, by tying the date in the column header to a date in a tab name. I had tried many iterations of this sort of formula in years past and could never make it work, so to learn it must’ve been a simple formatting issue is both annoying and enlightening!
Format the dated header as text in the formula (not by applying a particular date or text cell format to the header row) then look for the matching tab name. For example, in cell
L$4:
=IFERROR(
SUMPRODUCT(
(INDIRECT("'" & SUBSTITUTE(TEXT(US!L$3, "m-d-yy"), "/", "-") & "'!$B$2:$B" & US!L$1) = US!$B4) *
(INDIRECT("'" & SUBSTITUTE(TEXT(US!L$3, "m-d-yy"), "/", "-") & "'!$CE$2:$CE" & US!L$1))
),
0)Handle Exceptions: In order to handle duplicate pay dates for a bonus, ChatGPT guided me to add the word Bonus above the column header and in the relevant date tab name and adjusted the formula slightly:
=IFERROR(
SUMPRODUCT(
(INDIRECT("'" & SUBSTITUTE(TEXT(US!L$3, "m-d-yy"), "/", "-") & IF(US!L$2 = "Bonus", " Bonus", "") & "'!$B$2:$B" & US!L$1) = US!$B4) *
(INDIRECT("'" & SUBSTITUTE(TEXT(US!L$3, "m-d-yy"), "/", "-") & IF(US!L$2 = "Bonus", " Bonus", "") & "'!$CE$2:$CE" & US!L$1))
),
0)Key Takeaways:
Flexibility is Key: This method will handle future-dated tabs containing future payroll information without manual formula or data adjustments. Note the end solution is pretty much one formula, copied to all cells in all future dated columns. Super cool!
Budget-Friendly Alternatives: While a fully automated custom solution would be ideal, this client asked what I could do to help their manual reconciliation process in a few hours due to budget constraints. This approach replaces the client’s current error-prone, paste-as-values methodology with an easy and audit-friendly drop-in solution.
Plan for the Future: Building with future users in mind (re: not building something reliant on me or experienced IT pro to run it or fix it) prevents later headaches.
Next Steps
Describe your next Excel problem to ChatGPT or your work-approved alternative and see what it suggests. I’d love to hear your results, especially if you and your GPT create something extraordinary.
If you found this helpful or insightful, please share this with a colleague or friend. I appreciate you!