How can I insert rows in Excel using openpyxl and ensure formulas and formatting update like in Excel?

[Parth Shukla]

Hello !!!!

I'm using openpyxl on a windows to manipulate an existing Excel workbook.

My goal is to insert rows in the middle of a worksheet, and have it behave like when you insert rows manually in Excel — where:

• ✅ Formulas update relatively (e.g., =SUM(A2:A5) becomes =SUM(A2:A6) after inserting a row inside that range)
• ✅ Cell formatting (e.g., borders, fonts, currency, merged cells) shifts down or gets copied appropriately

However, when I use:

pythonCopyEditws.insert_rows(idx=5, amount=1)

The row inserts fine, but:

• ❌ Formulas below don't update — they still point to the old range
• ❌ Formatting isn’t preserved — the inserted row has default styling

---

🔍 What I’ve Tried:

• Manually copying cell styles from the previous row using copy
• I can't modify formulas with new cell values because those are complex and many of them are there.

❓What I’m Looking For:

• Is there any solution for my problem using OpenpyXl??  Or is there any other alternatives available?
  • Inserts rows and automatically adjusts affected formulas
  • Shifts or replicates cell formatting properly

Any best practices, open-source packages, or working code snippets would be greatly appreciated!

[Charlie Clark]

On 10 Jul 2025, at 17:01, Parth Shukla wrote:

• Is there *any solution for my problem using OpenpyXl?? Or is there

any other alternatives available?*

You have to do this manually, but look at the move_cells() method for how this can be done.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Parth Shukla]

Thanks for your quick feedback, it helped to move fast.

Tried with move_range() , but couldn't get expected results yet, format is not happening properly for my sheet and manully updating the formula with new cell range is not feasible unfortunatelly as these formulas are linked and there are so many of them..

Really appriciate your kind support.

[Charlie Clark]

On 11 Jul 2025, at 8:17, Parth Shukla wrote:

Tried with move_range() , but couldn't get expected results yet, format is
not happening properly for my sheet and manully updating the formula with
new cell range is not feasible unfortunatelly as these formulas are linked
and there are so many of them..

I didn't say move_range() would be the solution, but you can look at the source to find out what you need to do.

The biggest problem, as you've discovered, is the relationships between formulae. You'll need to build and manage your own dependency graph for this. The complexity is why Openpyxl doesn't, and won't ever, do it. But you might be able to use the pycel library, which is all about Excel formulae.

Good luck!