This story captures something we hear often: the frustration of a tool that used to work perfectly, then stopped working for no clear reason. The user's old formula, =A:A=B:B, was a perfect example of Excel at its most intuitive. It compared whole columns, adjusted when rows moved, and required zero maintenance. Then dynamic arrays arrived, and that same formula started throwing a #SPILL! error. The user isn't asking for a more powerful solution. They're asking for the same simplicity they had before. That's a reasonable request.
The real issue here isn't that Excel changed. It's that the change broke a common workflow without offering an equally simple replacement. Dynamic arrays are genuinely useful for many tasks, but they introduced a constraint: formulas can't spill into occupied cells, and entire-column references like A:A now try to fill 1,048,576 rows. The user knows there's a workaround, they used one successfully before, but they lost it, and two different AI models couldn't reconstruct it. That's telling. If modern AI can't reliably produce a simple column-comparison formula, the problem isn't the user's technical skill. It's the tool's complexity.
There is a straightforward answer, and it deserves to be widely known. Wrap the column references with an INDEX function to clip them to the used range. For example: =INDEX(A:A,1):INDEX(A:A,COUNTA(A:A))=INDEX(B:B,1):INDEX(B:B,COUNTA(B:B)). That formula returns a dynamic array of TRUE/FALSE values for only the rows that contain data, avoids the spill error, and adjusts automatically when you insert or move rows. It's not as elegant as the old version, but it works. The user shouldn't have to piece this together from memory or failed AI queries.
This story is a reminder that progress in software isn't always linear. New features can quietly break the small, reliable behaviors users depend on. The best response isn't to blame the user for not adapting. It's to make the fix as discoverable as the original formula was. If you're wrestling with this exact problem, test the INDEX-based approach above. It's short, it's stable, and it doesn't require you to learn a new mental model just to compare two columns.