3

I have a large scheduling spreadsheet of dates and names, split up by week, with borders for clarity. I want to be able to autofill the dates by dragging down the cell. Sometimes I also want to fill many cells with the same name in a row. If I drag down to fill the cells, then any borders on the cell are erased, like so:

enter image description here

(I already manually fixed the borders on the dates when i took the screnshot.) Borders are apparently treated like they're part of the content of the cell, which seems completely wild to me. Borders are outside of the cell and I'd expect them to stay where I put them no matter what content I post or move around in the cells themselves

Is there a way to get borders to just stick where they're set and ignore any dragging, copy/pasting, etc. that I'm doing? Failing that, is there at least a fix for the specific trouble I'm seeing here with the drag-down?

1

Here's a way to auto fill dates without erasing border format :

Cell A1

1/1/2016

Cell A2

=ArrayFormula(ADD(A1,row(INDIRECT("A1:A"&30))))

Credit goes to Rubén

For the 'name' column you can copy a the cell Claire for example then select the range where you need the name then paste values only. Shortcuts are CTRL+C -> CTRL+SHIFT+V

Normal behavior when you drag a cell is to copy format.

More : If you have this problem with only 2 columns, you can drag, copy paste, and make wathever you need. Then just copy - paste format of another column using this tool enter image description here

1
  • This answer inspired this workaround: copy the format of the whole column to a spare one using the format tool highlighted in this answer. Then use the usual drag-copy method to fill the data. Finally, using the format tool, copy the format back, and delete the spare column. – Jerther Jan 4 at 13:13
1

You could simply copy your cell and then paste the formula only. That will preserve any borders.

  1. Copy cell (CTRL+C)
  2. Select the target-area (the area, which you'd normally drag that cell down to)
  3. Right Click the selection -> Paste special -> Paste Formula Only
1
  • Doesn't work as only the first row of the selection is updated – vozman Apr 9 at 14:01
0

For me the easiest way is:

  1. Copy the whole range you are planning to drag onto including the draggable item (select+ Ctrl+C)
  2. Drag the item and break the formatting
  3. Select the range and paste format only (Paste Special - Paste format only Or Ctrl+Alt+V)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.