'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stop Excel from Copying Formatting When Inserting Rows or Columns
Question details
The user wants to insert new rows or columns in Excel without the software automatically applying the formatting, data validation, and input messages from adjacent cells.
- Product
- Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- Modifying an existing, highly formatted spreadsheet by adding new data rows or columns.
- Observed behavior
- Excel automatically inherits the surrounding row or column's formatting and validation rules when new cells are inserted, and there is no native default setting to disable this behavior.
Inheriting formats is a default Excel behavior by design. If you plan to use a VBA macro workaround to bypass this, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm).
Use a VBA Macro to Insert Unformatted Rows
Since Excel lacks a built-in toggle for unformatted insertions, utilizing a simple VBA macro is the most efficient workaround to insert a clean row.
A macro can automate the process of inserting a row and immediately wiping all inherited formatting, data validation, and input messages from the new cells.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Click 'Insert' from the top menu, then select 'Module'. Paste a standard VBA script designed to insert a row and clear formats (e.g., Selection.EntireRow.Insert followed by Selection.ClearFormats and Selection.Validation.Delete).
Close the VBA editor, return to Excel, press Alt + F8 to open the Macro dialog, select your new macro, click 'Options', and assign a convenient shortcut key for future use.
Manually Clear Formatting and Validation
If you prefer not to use macros, you can manually strip the inherited formats immediately after inserting the new row or column.
Submit Feedback to Microsoft
Because this behavior is by design, submitting feature requests can help Microsoft prioritize adding a native setting for unformatted insertion in future updates.
Try WPS Office for Seamless Spreadsheet Management
If you are frustrated by rigid default behaviors in Microsoft Excel, consider switching to WPS Office. It offers a lightweight, highly compatible alternative for your spreadsheet needs, providing a familiar interface without the bulk.
- 1. Download WPS Office: Visit the official WPS website to download and install the free suite.
- 2. Open WPS Spreadsheets: Launch the application and select Spreadsheets from the home dashboard.
- 3. Edit Seamlessly: Open your existing Excel files and enjoy a smooth, familiar editing experience.
Frequently Asked Questions
Why does Excel automatically format newly inserted rows?
Excel is designed to anticipate your needs by assuming that new rows inserted into a data set should match the existing visual structure, including cell colors, borders, fonts, and data validation rules.
Does the 'Clear Formats' tool remove Data Validation?
No, the standard 'Clear Formats' option in the Home tab removes visual styling like colors and fonts, but it leaves data validation rules and input messages intact. You must clear validation separately via the Data tab.
Can I permanently disable the 'Insert Options' paintbrush icon?
Yes. If you find the Insert Options button distracting, you can hide it by going to File > Options > Advanced, scrolling down to the 'Cut, copy, and paste' section, and unchecking 'Show Insert Options buttons'.
Will a VBA macro work in Excel online?
No, VBA macros are only supported in the desktop versions of Excel for Windows and Mac. They will not execute in Excel for the Web.
