Microsoft Excel cannot add new cells [Fixed]

While working on an Excel spreadsheet, you may run into the problem of not being able to add new cells. This issue is very common and can be easily resolved. Keep reading to find out how.

Why Can’t I Create New Cells in Excel on Windows 10?

In most cases, the supposed ‘problem’ serves the purpose of preventing the loss of data on your sheet. However, exceptions do exist, such as in the case of corrupt files or due to the file format you are using.

Any of the following factors can prevent the creation of new cells in Microsoft Excel:

• Cell protection: In Excel, there are different types of cell protection for your data. If you have one active, it could be the reason why you cannot create a new cell.
• Merged rows/columns: When you merge entire rows or columns to make a single cell, you won’t be able to insert a new row/column.
• Formatting applied to an entire row/column: You may have unintentionally formatted an entire row/column. This could be the cause of the problem you are facing.
• Freeze Panes: The Freeze Panes option helps facilitate data entry and management. However, it can prevent you from adding new cells.
• Entries in the last rows/columns: If you are trying to replace entries in the last row/column of the sheet, Excel will restrict the addition of new cells so as to avoid data loss.
• A data range formatted as a table: You may experience the problem in question when you try to add cells in a selected area that includes a table and a blank space.
• File format limitations: Different file formats are available in different versions of Excel. Each file format has its unique purpose and limitations. You may thus encounter a problem when trying to add new cells if you are using a file format with limited functionality.
• Files from untrusted sources: For your protection, Excel often prevents the execution of files from untrusted sources. It may well be that the error you are currently facing stems from the file itself.

Now that we have seen the various reasons why you can’t add a column or line in Microsoft Excel, let us now go ahead and dive into how to get the issue resolved.

How to Fix “Cannot Add New Cells in Excel”

Here are the solutions to the problem:

1. Remove cell protection
2. Unmerge the rows/columns
3. Unfreeze the panes
4. Copy your data to a new sheet
5. Choose a shorter file path
6. Change the file format
7. Format the table as a range
8. Set the file source as trusted
9. Clear the formatting in unused rows/columns
10. Customize the used range using VBA
11. Use Office Online

By the time you have tried the solutions listed above, you are sure to get on with your work without further trouble. So let’s get started:

Fix 1: Remove Cell Protection

The cell protection functionality in Excel preserves the current state of your sheet or workbook by locking the cells so that your data can’t be wiped or edited. Therefore, if you have cell protection active, the creation of new cells will not be allowed in order to preserve your existing data. So all you have to do is deactivate the functionality. Follow these easy steps to get it done:

1. Select all the cells on your worksheet by pressing Ctrl + A on your keyboard.
2. On the Home tab, click the Format drop-down.
3. Select Format Cells under Protection at the bottom of the menu.
4. In the window that opens, click on the Protection tab and unmark the option that says ‘Locked.’
5. Click OK.
6. Now, go to the Review tab and click on Protect workbook or Protect Sheet.
7. Enter your password to remove protection from the sheet or workbook.
8. Press Ctrl + S to save your file. Close the window and then open it again. You can now try inserting a new row/column. See if it works.

Fix 2: Unmerge the Rows/Columns

As already mentioned, you may have merged an entire row or column rather than just a few cells. In this case, Excel is programmed to restrict the addition of new cells so as to keep your data from getting lost. Merging all the cells in a row prevents the addition of another column, and merging all the cells in a column prevents the addition of new rows. Unmerging the columns/rows can resolve the issue. Here’s what you should do:

1. Look through your worksheet and locate the merged rows/columns.
2. If it is a column that is merged, click the column header (for example A, B, C, etc.).
3. Now, on the Home tab, click on Merge and Center to unmerge the highlighted column.
4. Repeat Steps 2 and 3 for any other merged column(s).
5. If there is any merged row, click on the row header (for example 1, 2, 3, etc.) and then click Merge and Center displayed in the Home tab.
6. Press Ctrl + S on your keyboard to save your file. Close the workbook and then open it again. You can now check whether the issue in question has been resolved.

Fix 3: Unfreeze the Panes

The Freeze Panes feature makes referencing easier by keeping a selected area of your worksheet visible as you scroll to other areas of the worksheet. However, the functionality can prevent the addition of new rows or columns to the sheet. Follow these steps to unfreeze the frozen panes:

1. Go to the View tab.
2. Click the Freeze Panes drop-down.
3. Select Unfreeze Panes from the menu.
4. Save your file by pressing Ctrl + S and then close it.
5. Reopen the file and see if the problem has been fixed.

Fix 4: Copy Your Data to a New Sheet

It could well be that the file you are working with is corrupt. Thus, try copying your data to a new file. Here’s how:

1. Open the sheet you are having problems with.
2. Press Ctrl + A to select your data and then press Ctrl + C to copy it.
3. Go to the File tab.
4. Click on New and select Blank Workbook.
5. Click Create.
6. Click the Paste drop-down arrow in the Home tab.
7. Click ‘Paste Special…’
8. Click on ‘Values’ and then click OK.
9. Save the new file and then close it. Reopen the file and check whether the problem you are facing has been resolved.

Fix 5: Choose a Shorter File Path

The address of your file in your OS is referred to as the file’s path. When it is too long, it could prevent the creation of new cells. Save the file in a location where the file path will be shorter. Follow these steps:

1. Open the file you are having trouble with.
2. Click on the File tab and select Save as.
3. In the dialogue box that opens, select Desktop as the location for the file to be saved and then click the Save button.
4. Close the workbook.
5. Open the newly saved file and check whether the problem you were facing will occur again.

Fix 6: Change the File Format

The file format you are using may be the cause of the error. Using a different format may help resolve the issue. For instance, you can switch from XLSM to CSV, XLS, or XLSX. Let’s take a look at how to do that:

1. Open the file you are having trouble with.
2. Go to the File tab and click on Save as.
3. In the Save as dialogue box that opens, expand the ‘Save as type:’ drop-down and choose a different file format. For instance, you can choose XLS if CSV is the current format.
4. Click the Save button.
5. Close the workbook.
6. Reopen the newly saved file and see if the issue has been resolved.

Fix 7: Format the Table as a Range

Although Excel supports the creation of tables, in some cases, tables can cause the problem of not being able to add or delete rows/columns in a worksheet. When that happens, try converting the table to a range. Follow these easy steps to do so:

1. Click on any area in the table you created.
2. Go to Design, which is under Table Tools, and click on Convert to Range.
3. Press Ctrl + S on your keyboard to save the file.
4. Close the file and reopen it.
5. Check whether you can now successfully create a new cell.

Fix 8: Set the File Source as Trusted

Excel is programmed not to support the execution of files from untrusted sources. This built-in functionality is meant to enhance your security and display an error message when you try to create new rows/columns in a sheet. The solution available to you is to set the location of the file as trusted. Here’s how:

1. Open the file you are having problems with.
2. Go to the File tab and click on Options.
3. Click on Trust Center. It is the last item in the left-hand pane of the Excel Options page.
4. Click on ‘Trust Center Settings…’ displayed on the right-hand side of the page.
5. In the left-hand pane of the new page that opens, click on Trusted Locations.
6. Now click the “Add new location…” button displayed on the right-hand side of the page. You will now be presented with the Microsoft Office Trusted Location window.
7. Click the ‘Browse…’ button and then navigate to the location where your Excel file is saved.
8. Click OK.
9. Click OK and then click OK once again.
10. Close Excel and then reopen the file you were having problems with. See if you can now add new cells to the sheet.

Fix 9: Clear the Formatting in Unused Rows/Columns

Does it seem that you have no content in the last row/column of your worksheet? That may not be the case. If you have highlighted the entire row/column by clicking the header and then applied some formatting (for example, introduced color or cell borders), Excel will assume that there’s content in the row/column and will therefore prevent you from creating new cells so as to prevent the loss of data. You can fix this by clearing the formatting in the entire row/column.

To insert a new column, here’s what you have to do:

1. Open the problematic file.
2. Go to the column on the right-hand side of the last column that contains data in your sheet. Click the header to highlight the entire column and then press Shift + Ctrl + Right Arrow on your keyboard. This will highlight all the columns that do not contain data on your sheet but may have formatting.
3. In the Home tab, under Font, click the drop-down arrow to reveal the Borders menu.
4. Select ‘No Border.’
5. While still under Font in the Home tab, click the drop-down arrow for Theme Colors and then select ‘No fill.’
6. Press the Delete key on your keyboard to wipe any data that you may have mistakenly entered in the unused cells.
7. Now, under the Editing category in the Home tab, click the Clear drop-down arrow and select Clear Formats.
8. Click the Clear drop-down arrow again and select Clear All.
9. Click Ctrl + S on your keyboard to save the file.
10. Close Excel and then reopen the file.

To insert a new row, here’s what you have to do:

1. Open the sheet you are having problems with.
2. Go to the row next to the last row that contains data. Click the header to highlight it and then press Shift + Ctrl + Down Arrow to highlight all the unused rows right to the end of the sheet.
3. In the Home tab, under Font, click the drop-down arrow to reveal the Borders menu.
4. Select ‘No Border.’
5. While still under Font in the Home tab, click the drop-down arrow for Theme Colors and then select ‘No fill.’
6. Press the Delete key on your keyboard to wipe any data that you may have mistakenly entered in the unused cells.
7. Now, under the Editing category in the Home tab, click the Clear drop-down arrow and select Clear Formats.
8. Click the Clear drop-down arrow again and select Clear All.
9. Click Ctrl + S on your keyboard to save the file.
10. Close Excel and then reopen the file. See if you can now insert a new row.

There’s a suggestion that one should not use the Ctrl + V shortcut to paste data into an Excel sheet as that might cause a number of problems, including not being able to add new rows/columns. Instead, use this method:

1. Click the Paste drop-down arrow in the Home tab.
2. Click ‘Paste Special…’
3. Click on ‘Values’ and then click OK.

Fix 10: Customize the Used Range Using VBA

Don’t lose heart if you have come this far and you are still unable to create new rows/columns on your Excel worksheet. VBA (Visual Basic for Applications) is Excel’s (and other Microsoft Office programs’) programming language. We can use it to solve the issue you are currently facing. Simply follow these easy steps:

1. Open the problematic file.
2. Right-click on the worksheet tab at the bottom of the screen (for example Sheet 1).
3. Click on View Code from the context menu.
4. In the page that opens, press Ctrl + G on your keyboard to display the ‘Immediate’ window.
5. Now type ‘ActiveSheet.UsedRange’ (don’t include the inverted commas) and press Enter. This ensures that the used range of your worksheet will lie only within the area where your data is.
6. Now, click on the File tab and select ‘Close and Return to Microsoft Excel.’
7. Press Ctrl + S to save the file. Close Excel and then reopen the file. Check whether you can now add new columns or rows.

Fix 11: Use Office Online

If the problem persists after you’ve tried all the above fixes, there’s one more option left. It could be that there is an issue with your system. You can use Office Online to get rid of the setback you are facing. Follow these easy steps:

1. Go to your browser and log in to OneDrive.
2. Click the Upload button.
3. Click on Files.
4. Navigate to the location where your problematic Excel file is stored.
5. Select the file.
6. Click Open.
7. Try adding new rows/columns to the sheet.
8. If successful, you can then download the file to your system.

There you have it. By the time you’ve tried these fixes, you will have succeeded in fixing the ‘Microsoft Excel Cannot Add New Cells’ problem.

If you have any further suggestions, questions, or comments, please feel free to leave them in the comments section below. We’ll be glad to hear from you.

To ensure that you don’t run into unnecessary problems while trying to complete important tasks on your PC, we recommend you run regular scans with a trusted antivirus program. Get Auslogics Anti-Malware today and be rest assured that your system is in good hands.