When you open a worksheet, Excel creates new rows and columns automatically. If you do not want to do that or prevent Excel from creating new rows and columns automatically, here is how you can do that. You can prevent or block Excel from creating infinite rows and columns automatically using Excel Options, Local Group Policy Editor, and Registry Editor.
Stop Excel from creating new Rows and Columns automatically
To prevent Excel from creating new rows and columns automatically, follow these steps:
- Open Microsoft Excel on your computer.
- Click on Options.
- Go to the Proofing tab.
- Click on the AutoCorrect Options.
- Switch to the AutoFormat As You Type tab.
- Remove the tick from the Include new rows and columns in table Automatically as you work checkbox.
- Click the OK button.
To learn more about these steps, continue reading.
First of all, open Microsoft Excel on your computer and click on the Options menu to open the Excel Options panel. Then, switch to the Proofing tab and click on the AutoCorrect Options button.
Following that, switch to the AutoFormat As You Type tab and remove the tick from the Include new rows and columns in table Automatically as you work checkbox.
Finally, click the OK button to save the change.
How to stop Excel from creating new rows and columns automatically
To stop Excel from creating new rows and columns automatically, follow these steps:
- Press Win+R to open the Run prompt.
- Type gpedit.msc and hit the Enter button.
- Go to Proofing > Autocorrect Options in User Configuration.
- Double-click on the Include new rows and columns in table setting.
- Choose the Disabled option.
- Click the OK button.
Let’s check out these steps in detail to learn more.
To get started, you need to open the Local Group Policy Editor on your computer. For that, press Win+R to open the Run prompt, type gpedit.msc, and hit the Enter button.
Then, navigate to the following path:
User Configuration >Administrative Templates > Microsoft Excel 2016 > Excel Options > Proofing > Autocorrect Options
Here you can find a setting named Include new rows and columns in table. You need to double-click on this setting and choose the Disabled option.
Lastly, click the OK button to save the change and restart Microsoft Excel to apply the change.
In case you want to opt the default setting, you need to open the setting in the Local Group Policy Editor, and choose the Not Configured option.
How to prevent Excel from creating new rows and columns automatically using Registry
To prevent Excel from creating new rows and columns automatically using Registry, follow these steps:
- Press Win+R to display the Run dialog.
- Type regedit > press the OK button > click on the Yes button.
- Go to Microsoft\office\16.0 in HKCU.
- Right-click on 0 > New > Key and name it as excel.
- Right-click on excel > New > Key and set the name as options.
- Right-click on options > New > DWORD (32-bit) Value.
- Call it autoexpandlistrange.
- Keep the Value data as 0.
- Restart your computer.
To know more about these aforementioned steps, keep reading.
For getting started, you need to open the Registry Editor on your computer. For that, press Win+R to display the Run dialog, type regedit, press the OK button and click on the Yes option in the UAC prompt.
Then, navigate to the following path:
HKEY_CURRENT_USER\Software\Policies\Microsoft\office\16.0
Right-click on 16.0 > New > Key and name it as excel.
Then, right-click on excel > New > Key and set the name as options. Following that, you need to create a REG_DWORD value. For that, right-click on the options key, select New > DWORD (32-bit) Value and call it autoexpandlistrange.
By default, it comes with a Value data of 0, and you need that Valued data to prevent Excel from creating new rows and columns automatically.
Finally, close all windows and restart your computer to get the change.
Note: If you want to revert to the original setting, you have two options. First, you can set the Value data as 1. Alternatively, you can delete this REG_DWORD value. For that, right-click on it, select the Delete option and click the Yes button. Lastly, restart your computer to apply the change.
Read: How to get real-time Currency Exchange Rates in Excel sheet
How do I get Excel to stop creating new columns?
To get Excel to stop creating new columns and rows automatically, you can use the Excel Options setting. For that, switch to the Proofing section and click on the AutoCorrect Options button. Then, switch to the AutoFormat As You Type tab and remove the tick from the Include new rows and columns in table Automatically as you work checkbox.
Read: How to modify the number of Undo levels in Excel
How to delete infinite columns and rows in Excel?
In case you do not need to display infinite columns and rows in Excel spreadsheet, you can delete or hide them using these steps. First, finish editing your spreadsheet and ensure the last column and row. Then, click on the first empty column and press Ctrl+Shift+Right arrow. Then, go to Format > Hide & Unhide > Hide Columns. Similarly, you can hide or delete all the empty rows but you need to choose the Hide Rows option and select all the rows by pressing Ctrl+Shift+Down arrow.
Read: Prevent Word, Excel, PowerPoint from hyperlinking screenshots automatically.
