Need to copy items into an Excel table without changing the borders? Here’s how.
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.
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. Since reformatting the table would be a pain, is there a better way to copy the data?
Note: Click any image to see a larger view of the figure.
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
Images
References and Additional Resources
If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel . This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, various formatting techniques and even how to play games like Craps in Excel.
Other Resources:
Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com .
