This is the kind of problem that sounds simple until you try to solve it, and then it becomes a quiet nightmare. The user here has a clear goal: take one row of data from a source sheet, expand it into five formatted rows on a second sheet, and then repeat that pattern for every record. The drag handle should work. The logic seems linear. But spreadsheets don't think in blocks of five rows, they think in single rows, one after another. So when you highlight five cells and drag down, the formula shifts by five rows on the source sheet, skipping data that should have been pulled. The formatting looks perfect. The numbers are wrong. And after hours of Googling, there is no answer that fits.
We have a plain opinion on this: the problem isn't the user's understanding of mail merge or formatting. It's that traditional spreadsheets were built for flat tables, not for the kind of structured data transformation that modern workflows demand. The user is trying to perform a one-to-many mapping, take one record, repeat its data across multiple rows, and apply formatting that survives the repetition. That is a fundamental operation in data preparation, yet it requires workarounds involving OFFSET formulas, INDEX-MATCH gymnastics, or manual VBA scripts. None of those are intuitive. None of them scale. And none of them belong in a process that should be as simple as drag-and-drop.
For our readers, this is the moment to ask whether the tool you are using is designed for the task you are actually doing. If you are spending hours constructing formulas to do something that feels like it should be native, like repeating a row of data across a fixed number of formatted rows, then the spreadsheet is working against you, not for you. An AI-native spreadsheet would recognize the pattern you are trying to create: you want to take one row, expand it into a block of five, then move to the next row and do the same. That is not a formula problem. It is a structural transformation that should be handled by the tool itself.
The concrete point is this: if you are fighting your spreadsheet to do something that feels like a basic data operation, stop fighting. Look for a tool that understands your intent, not just your cell references. The user who posted this deserves a solution where highlighting five rows and dragging down produces exactly the right data every time, no workarounds required. That is not a dream. It is a specification for what a modern spreadsheet should do.