Ask Your Question

Calc how to drag down hidden formulas

asked 2015-12-03 11:08:03 +0200

bobmould21 gravatar image

updated 2015-12-04 12:29:27 +0200

we have just moved from OO to LO

I have a number of spreadsheets that utilise formulas in hidden columns. when attempting the drag down multiple cells across numerous columns including hidden columns everything drags down apart from the formulas in the hidden columns.

can't seem to find an answer - any ideas?


Additional Info (04/dec/15)

I have tried to attach a sample doc to illustrate but i need points to do this apparently.

Here goes on extra explanation

i would normally highlight cells F1-L1 and then drag the columns/formulas down. Col J is hidden containing a formula which is needed to provide a result in Col K

i) if i do this with Col J hidden then the formula in Col J doesn't copy down

ii) if i do this with Col J shown the dragging down does copy the formula in Col J down.

The spreadsheet is used by a number of people who have a basic knowledge only so i need to keep it as simple as possible - highlighting and dragging down is ok, but i'd rather they did not start unhiding columns and potentially messing with formulas

edit retag flag offensive close merge delete


Please can you detail a bit?, maybe me, but I'm not sure what you a trying to drag.

m.a.riosv gravatar imagem.a.riosv ( 2015-12-03 23:58:23 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2015-12-08 22:22:56 +0200

m.a.riosv gravatar image

Perhaps to make it easy, instead of hide columns directly, is use the Menu/Data/Group & outline - Group [F12], so all desired columns can be showed and hidden in one step. Remember to have active the option Menu/Tools/Options/LibreOffice calc/View - Outline symbols.

edit flag offensive delete link more

answered 2015-12-04 20:29:39 +0200

Lupp gravatar image

updated 2015-12-08 13:04:21 +0200

I think it is the way spreadsheet software does it, at least Calc does so. (An unwanted fill might cause problems.)

I f you do not want to show a hidden column, fill a formula down, and hide the column again, in specific cases you can do the following:

1) Enter the master cell of the hidden column inputting its address into the little name edit.

2) Shift+MoveCursorDown (single stepps or pages...).

3) 'Fill' > 'Down' via menu or by Ctrl+D.

I do not know a way to do what you actually asked for without showing all the columns spanned. As hidden columns mostly are containing helpers not needed in a specific place you may collect them all in a contiguos range of columns which will ease Show/HideAgain steps. The columns also can simply be placed out of sight.

Editing: @bobmould21 : Try uploading again.

edit flag offensive delete link more

answered 2015-12-08 19:21:15 +0200

pierre-yves samyn gravatar image


If the "spreadsheet is used by a number of people who have a basic knowledge" I guess they know copy and paste?

If so they can do this:

  • Select F1-L1, Copy (menu Edit, Ctrl+C, right click...)
  • Select the target range (e.g. F2:L10), Paste

Or, as Lupp said use fill :

  • Select source & target (e.g. F1-L10)
  • Type Ctrl+D
edit flag offensive delete link more

answered 2015-12-04 17:13:22 +0200

redart gravatar image

Probably not what you want to hear, but I had the same problem and the only way I could solve it was to do the copy/drag operation via a macro. For some reason hidden cells get copied using a macro command, but not using click and drag.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-12-03 11:08:03 +0200

Seen: 342 times

Last updated: Dec 08 '15