The user asking this question has stumbled onto one of the most frustrating gaps between Google Sheets and Excel, and their frustration is completely justified. The ability to populate a cell with a formula-generated value while keeping that formula invisible in the destination cell is a feature that should be standard in any modern spreadsheet tool. Google Sheets handles this elegantly with its array literal syntax; Excel, despite its power, forces users into clunky workarounds that undermine the very efficiency they are trying to achieve.
What this user describes is not a minor preference, it is a fundamental workflow requirement. They want to pull default data from a source tab into a destination tab, but retain the ability to manually override that data without breaking the underlying logic. In Sheets, the curly-brace array syntax lets them write one formula that spills results across multiple cells, and those cells become independent once overwritten. Excel's dynamic array formulas, while powerful, do not offer the same clean separation. The result is that users must choose between rigid formulas that cannot be edited manually or manual data entry that loses all connection to the source. Neither option is acceptable for anyone managing real-world data that requires both structure and flexibility.
This is where the conversation needs to shift from "how do I make Excel do this" to "why should I have to fight this hard." The user's question reveals a deeper truth: spreadsheet tools are still built around outdated assumptions about how people actually work. They assume data is either purely formula-driven or purely manual, when in practice most workflows exist somewhere in between. The smartest solution here is not to learn a dozen Excel workarounds, it is to use a tool that treats this hybrid workflow as a first-class feature rather than an edge case. That is the direction the industry should be moving, and it is the direction we are building toward.
For anyone reading this who shares the user's frustration, the practical takeaway is simple: do not settle for a tool that makes you choose between automation and control. The ability to populate cells with default values that remain manually editable is not a luxury, it is a basic productivity requirement. If your current spreadsheet forces you to hack around this limitation, it is time to explore alternatives that respect how you actually work. The technology exists to bridge this gap; the only question is whether you are willing to demand it.