6

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?

Improve this question

4 Answers 4

Reset to default
2

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)
Improve this answer
3
  • I don't believe this will work with formulas
    – lockstock
    Sep 11, 2021 at 2:31
  • That's genius! Works very well with filled dates. Thank you @vozman
    – Sandro L
    Oct 27, 2021 at 10:23
  • Very nice - works for "move/drag rows to reorder them" as step 2 too.
    – Heath Raftery
    Feb 3 at 5:40
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

Improve this answer
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, 2021 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
Improve this answer
1
  • Doesn't work as only the first row of the selection is updated
    – vozman
    Apr 9, 2021 at 14:01
0

Another workaround which is pretty simple is to just always select the number rows that contains the complete pattern before dragging to fill.

So for example with your sheet, the border pattern repeats every 5 rows so if you select 5 rows, when you fill the border will continue to repeat every 5 rows, which is what you want.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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