Take the 2-minute tour ×

I'm using LibreOffice on Ubuntu 12.10.

I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet.

I have bold horizontal borders every 4 rows. This is a pain to do because you can't specify "embolden the borders on every nth row". So I do it manually, for enough of the spreadsheet as I need to deal with.

I'm developing the spreadsheet, so the I modify the formulae frequently. When I get the formula working for a cell, I propagate that formula to a bunch of rows below by dragging the cell's fill handle down to the other (several hundred) rows. The problem is that dragging the fill handle copies the cell formatting from the source cell(s) as well as the formula(e). This undoes the emboldened borders on every 4th row and re-colors all the cells too. I then have to select a group of 4 rows of cells and manually embolden the top and bottom borders for each group of 4 rows. It's very tedious.

Is there a way to drag the fill handle and propagate the formula(e) but not the cell formatting?

share|improve this question

3 Answers 3

  • Copy your new formula
  • Select every cell where the new formula needs to be
  • Right click, and in the popup menu, select "Paste Only >> Formula"

This preserves your cells' color, border, etc.

share|improve this answer

You can do this using conditional formatting.

First define a new cell style for your underlined cells, e.g. Underlined. Then pick a cell and define a conditional format (Format->Conditional Formatting). For Condition 1, pick Formula is, and in the formula box enter "(ROW()/4=INT(ROW()/4))" (without the quotes). Choose the cell style to be Underlined. This is only true every 4th row.

Copy the cell (Ctrl-C), select all the area you want to modify and Paste Special (Ctrl-Shift-V). Clear all the check boxes in the selection section Except the Formats box and complete the copy. Or even easier, use the format painter.

You should now be able to click and drag a formula thorough those boxes without affecting your underline formatting.

share|improve this answer
Pretty good, but it wipes out the color in the box where (ROW()/4=INT(ROW()/4)). –  Chelmite Feb 2 '13 at 1:10
I'm not sure I follow. If the font color and background color are set in the cell style, then they are maintained. –  W_Whalley Feb 2 '13 at 14:54

I don’t have LibreOffice, I have Microsoft Excel 2007, so my mileage may have no bearing on yours.  But: in Excel, if I grab the fill handle with the right mouse button instead of the left, I get a menu when I finish dragging, and one of the options is “Fill Without Formatting”.

And I was also going to suggest the conditional formatting, using ROW(), before I saw that W_Whalley beat me to it (by a few hours!).  But I would add to that answer that MOD(ROW(),4)=0 is a little simpler.

share|improve this answer
The OP asks how do perform a function in a specific application and you describe how do do it in another? How does that help? –  Hydaral Oct 9 at 11:18
@Hydaral: It potentially helps because LibreOffice was designed to be similar to Microsoft Office, and many things work the same way in both. –  Scott Oct 9 at 19:08

Your Answer


