• Excel Book Excerpt

Excel Copy Without Changing Borders

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

Copy Without Changing Borders

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.

LE10001254.jpg

Figure 1208 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.

LE10001255.jpg

Figure 1209 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.

LE10001256.jpg

Figure 1210 Copy all except borders.

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

LE10001257.jpg

Figure 1211 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.

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 and number formatting.

LE10001259.jpg

Figure 1212 The new dropdown in Excel 2010 offers Paste Formulas.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:



Popular Pages

More Info