Copy Without Changing Borders

October 10, 2023 - by Bill Jelen

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.

Fancy borders with the top of the first row forming a box around all the data. But that top border in E3 is about to be screwed up when you copy the formula from E3 down — Figure 1357. 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.

As predicted, the top border is copied in to the data and looks ugly. — Figure 1358. 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.

The Paste Special dialog box as a All Except Borders choice. — Figure 1359. Copy all except borders.

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

After choosing all except borders, the number formatting and formula is pasted but the borders stay as they were. — Figure 1360. 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.

In the Paste drop-down, choose Formulas and Number Formatting to paste without pasting borders. — Figure 1361. The Paste dropdown offers Paste Formulas.

This article is an excerpt from Power Excel With MrExcel

Title photo by Will Francis on Unsplash