Copy formula to several 1000 cells at once | TechBuzz

- Advertisement -

You need to copy a formula into several hundred or thousands of cells. However, autofilling with the mouse is a bit tedious. With this trick you can do it in a second.

Autofilling cells with a mouse is a handy thing in Excel or LibreOffice Calc. You click on the original cell, grab it with the mouse small green square in the lower right corner of the cell and pull it down while holding down the mouse button.

But that’s only fun as long as it’s a few dozen cells at most. However, if it’s several hundred or thousands of cells, your mouse finger will fall off before you get to the bottom.

Solution: It’s so much easier, and it’s exactly the same whether it’s LibreOffice Calc or Microsoft Excel. Type the formula in the top cell. There can also be two formulas in adjacent cells. Select the cell(s) containing the formula. Again, the little green square in the lower right corner of the selected cell(s) comes into play: double click on that small green square, Excel automatically copies the formula to all cells below. It also stops at the bottom of your table or the sheet range used, as desired.

Screenshot Excel with two top cells selected
Double click on the small green square in the cell corner, it automatically copies the contents of the current cell down

And horizontally?

However, the above only works if you want to autofill perpendicularly (vertically). If it’s horizontal autofill and you need it often, grab a macro.
- Advertisement -

Alternative to faster horizontal fill: Copy the first cell with Ctrl+C (ctrl+C); those with the formula. Press Shift+Ctrl+right arrow (shift+ctrl+right arrow). This will mark all cells right to the end of the used range. Press Ctrl+V (ctrl+V), Excel pastes the copied formula.

(Original 06/04/2004, update with new Excel and LibreOffice version 10/28/2021)

Source link

- Advertisement -
I am admin of blog & I provide tech-related news. As a part of my hobby, I make content related to technology and gadgets reviews too. I love to be a content creator apart from it, I am a full-time employee in an MNC company and manage blogs systematically. You can mail me at [email protected]

More from author

Related posts


Latest posts

Want to stay up to date with the latest news?

We would love to hear from you! Please fill in your details and we will stay in touch. It's that simple!