borders Excel

A reader posed the following question: “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 [Figure 1, below].

borders Excel

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. [Figure 2, below].”

Excel borders

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

Excel borders

The formula is successfully copied, but the borders remain as they were (Figure 4).

Excel borders

Alternate Solution: 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. In Excel 2010, 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 as well as number formatting (Figure 5).

Excel borders

CFO contributor Bill Jelen is an Excel MVP and the author of 35 books about Microsoft Excel. He is also editor-at-large of the CFO Excel Pro newsletter, where this article first appeared.

, , , ,

3 responses to “Excel Tip: Do Not Disturb My Borders”

  1. Utilizing the Paste Special menu to not copy the borders to the other cells is a great tip! We think the paste special formulas is much simple to do, but then you may lose any number formatting or fill/font color formatting as well from your data set.

    Another option is to not use a Top Border in cell E3, but rather put a Bottom Border in cell E2. This way, when you copy the formula down from E3, the border will not show up in the cells where you have filled the formula down.

    If you’re interested in learning quick tips about Excel, check out our blog at http://www.thekeycuts.com/excel-blog

Leave a Reply

Your email address will not be published. Required fields are marked *