Advertisements

Microsoft Excel Cannot Add New Cells – How to Fix?

The Microsoft Excel app is popular among office workers for its simple and advanced features. You can use it for so many tasks like making a budget report, an expense report, and putting a lot of data in an organized manner.

But the users have been complaining that they can’t insert new cells in Microsoft Excel. If you are also facing this same trouble with your Excel, then don’t worry. In this article, we are going to discuss various solutions to tackle the Excel Cannot Add New Cells issue.

Microsoft Excel Cannot Add New Cells - How to Fix?

Why cannot Microsoft Excel add new cells?

In the error message, Microsoft Excel explains that if you add new cells, it will push non-empty cells off the end of the worksheet. Then, these cells may appear empty but will have blank values, some formatting, or a formula. But there can also be other problems behind this issue, such as-

  • Cell protection is On.
  • Unknowingly you have formatted an entire row or column
  • The file path is too long
  • Merged rows/columns
  • Freeze pane feature is active
  • You have put data in the last row/column
  • The file format is not accepted
  • Excel assumes your file is not from a trusted source.

How to solve the Excel Cannot Add New Cells issue?

Solution 1: Unmerging the rows/columns

If you have merged all the cells in a row, you can’t create another column. Similarly, if you have merged all the cells in a column, you can’t create another row. To solve this issue, you have to unmerge the rows/columns.

  • Check your worksheet and find out the merged rows/columns.
  • For a merged column, choose the column header. And click on the Merge and Center option on the Home tab.
  • You have to repeat this step if you have more than one merged column.
  • For a merged row, choose the row header. And click on the Merge and Center option on the Home tab.
  • Repeat the above step for other merged rows.
  • Press Ctrl key+S.
  • Save your file.
  • Re-open the workbook to check for the error.

Solution 2: Disable Cell Protection

Cell protection is essential to keep your data safe. But if you want to add new cells, you need to disable the cell protection feature. Here’s a quick guide on how to disable the cell protection.

  • Press Ctrl + A to choose all the cells on the workbook.
  • Select the Format option present on the Home
  • Under the Protection tab, click on the Format Cells options.
  • Next, choose the Protection tab.
  • Uncheck the box that says “Locked“.
  • Press Enter.
  • Click on the Review tab and select Protect workbook or Protect Sheet.
  • You have to put your password to remove protection from the workbook.
  • Press Ctrl key + S.
  • Save your files and close the window.
  • Open Excel again to insert a new row/column.

Solution 3: Unfreezing the panes

The Freeze pane feature allows you to keep a selected area visible while you scroll through other regions. But it prevents creating new cells. To solve this issue, you just need to unfreeze the pane.

  • Choose the View tab.
  • Click on the Freeze panes.
  • You will find the option “Unfreeze Panes” in the drop-down menu.
  • Click on the Unfreeze Panes option.
  • Now, save the file and then, close the Excel window.
  • Then reopen the file and see if you can add new cells again.

Solution 4: Create a copy on a new sheet

It’s possible that the file you are working on is corrupted. To solve this issue, you need to follow these steps below and create a copy of the file on a new sheet.

  • Press Ctrl + A to choose all your data, then press Ctrl + C to copy it.
  • Open the File tab and choose the “New” option and select Blank Workbook.
  • Select the Create button.
  • When you use the usual copy-paste method, the data gets to unwanted rows/columns, which creates problems later.
  • So, use this method instead. In the Home tab, choose the “Paste” option.
  • Select “Paste Special
  • Click on “Values,” and thereafter click “OK.”
  • Now, save the new file.
  • Close the Excel window.
  • Open the new file and see if you can add new cells here.

Solution 5: Modifying the File Format

Sometimes your file format is not supported by Excel. To solve this issue, you can modify your file format to a different one. Follow these steps to change your file format.

  • Click on the File tab and choose the “Save as” option.
  • Then, expand the “Save as type:
  • Select a file format other than the original file format. (For example the current format is CSV switch to XLS)
  • Select “Save
  • Leave the Excel window and reopen it to check if the issue is over.

Solution 6: Change the file path

When the file path is too long, it is difficult for the file to create new cells. The location of your file is known as the file path. To solve the new cells issue, you can change the file path by changing the location of your file.

Advertisements
  • Click on the File tab.
  • Choose the “Save as” option.
  • When the dialogue box opens, set the Desktop as the location for the file.
  • Select the “Save” button.
  • Leave the Excel window.
  • Reopen the newly located file in Excel and check if you can add new cells.

Solution 7: Converting a table to a range

Sometimes when you are using Table in Excel, you can’t add or remove rows/columns. In this case, you need to convert the table to the range and fix the issue.

  • First, you have to click anywhere in the table.
  • Choose Design in the Table Tools.
  • Click “Convert to Range” in the Tools group.
  • Then, save the file. And close the window.
  • Reopen it to check if you can add new cells this time.

Solution 8: Use VBA (Visual Basic for Applications)

VBA is the programming language for applications like Excel. If you are still having trouble creating the new cells, then you can use VBA to fix the problem. Follow these steps.

  • First, open the troubling worksheet.
  • Right-click on the worksheet tab.
  • Click on the View Code option.
  • You have to press Ctrl + G to display the Immediate window in the next page.
  • Type “ActiveSheet.UsedRange” and press Enter.
  • Select the File tab.
  • Then, you have to click on “Close and Return to Microsoft Excel
  • Now, Save the file. Close the Excel file, then reopen the file to check if you can add row/column.

Solution 9: Use the online Office

If you are finding issues with the offline version of the Office, you can switch to the online version. You can add new cells in the Excel file using the online Office, then download it to your system.

Advertisements
  • First, sign in to your OneDrive account.
  • Select the Upload button and then choose Files.
  • Go to the location of the file you want to upload.
  • Click on the file, then click Open.
  • Add new rows/columns.
  • Then, download the Excel file to your system.

Solution 10: Trusted File Source

Excel creates a protective environment by allowing files only from trusted sources. Perhaps Excel is blocking the addition of new rows/columns to your file claiming the file is not from a trusted source. To avoid this, you have to set the locations of your file as a trusted location.

  • First, open the troubling file in your Excel.
  • Select the “File” tab and choose “Options“.
  • After the Excel Options page opens, choose the Trust Center.
  • Choose “Trust Center Settings” in the right pane.
  • Then, in the left pane, choose Trusted Locations
  • Click on the “Add new location” option.
  • A window will open with Microsoft Office Trusted Location.
  • Select the “Browse” button and go to the location of your Excel file.
  • Click “OK“.
  • Press “OK” and again click “OK”
  • Then, close Excel.
  • Thereafter, reopen it to check if you can add the new cells.

Solution 11: Clear the not used row/column

Sometimes users select/highlight the header of the row or column to add border or color. If you have mistakenly formatted (added border or color) to the entire row/column, Excel assumes some data is present there. Thus, it prevents adding new cells to the row/column. To prevent this issue, you have to clear the entire row/column.

For Column:

  • Open the problematic workbook and go to the column next to the last column that has data in it.
  • Then, click the header (to highlight the entire column) and press Shift + Ctrl + Right Arrow to highlight all the columns that have formatting.
  • Now, in the Home tab, you will find the Borders menu under Font.
  • Choose “No Border
  • Then, click on the drop-down arrow for Theme colors.
  • Choose “No fill“.
  • You can also press the “Delete” button to wipe any data mistakenly present on the unused cells.
  • Go to the Editing category.
  • By expanding the “Clear” option, choose “Clear Formats“.
  • Then, again click on the Clear drop-down arrow to select “Clear All“.
  • Save the changes you made to your workbook.
  • Close the Excel window and then re-open it to check if you can add new columns.

For Row:

  • Open the problematic workbook and go to the row next to the last row that has data in it.
  • Then, click the header (to highlight the entire row) and press Shift + Ctrl + Down Arrow to highlight all the unused rows right to the end of the sheet.
  • Now, in the Home tab, you will find the Borders menu under Font.
  • Choose “No Border
  • Then, click on the drop-down arrow for Theme colors.
  • Choose “No fill“.
  • You can also press the “Delete” button to wipe any data mistakenly present on the unused cells.
  • Go to the Editing category.
  • By expanding the “Clear” option, choose “Clear Formats“.
  • Then, again click on the Clear drop-down arrow to select “Clear All“.
  • Save the changes you made to your workbook.
  • Close the Excel window and then re-open it to check if you can add new rows.

While using Excel, many users face this trouble of not being able to add new cells to the workbook. There are so many reasons behind this issue. But don’t worry because we have covered them all.

Though Excel is the most used application to create spreadsheets, it may show some error now and then. After going through all the solutions, you will be able to find the best solution to add new cells in Excel. We hope this article made you learn more about Excel. If you have any queries or feedback, please write down the comment in the below comment box.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.