Microsoft Excel: Copy Without Changing Borders

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

See all Microsoft Excel tips

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

Strategy: Use Edit – Paste Special – All except borders to copy the formula and the numeric formatting, but to not disturb the borders. See Fig. 1144.

Result: As shown in Fig. 1145, the formula is successfully copied, but the borders remain as they were.

Alternate Strategy: In the above dataset, it appears that you had 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 to just copy the formula.

Summary: To copy without disrupting borders, use the Paste Special – All Except Borders option.

Commands Discussed: Edit – Paste Special – All Except Borders; Edit – Paste Special – Formulas

See all Microsoft Excel tips

Images