Excel: Copy Without Changing Borders

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I have built a report in Excel and used numerous borders to outline the data. After entering a formula to calculate profit in E3, I want to copy the formula down to E4 through E7.

  1. Copy this formula.

    However, because cell E3 has a top border, copying the formula causes all the cells in E4 through E7 to also have a top border, ruining the effect of my borders.

  2. Excel copies the borders, too.

    Strategy: You can select Home, Paste dropdown, Paste Special, All Except Borders to copy the formula and the numeric formatting but not disturb the borders.

  3. Copy all except borders.

    Results: The formula is successfully copied, but the borders remain as they were.

  4. Excel will not disturb the borders.

    Alternate Strategy: In the data set described here, it appears that you decided to show the currency symbol on only the first row and the total row. In this case, it might have been more appropriate to use Paste Special, Formulas just to copy the formula.

    The Paste dropdown offers two icons in the first row that will help in this situation. The fx icon will copy formulas. The %fx icon will copy the formulas and number formatting.

  5. The Paste dropdown offers Paste Formulas.

For more resources for Microsoft Excel