Please click here for our new and improved resource covering how to fix this Excel error.
Excel spreadsheets have become larger over time when it comes to entering data. An Excel sheet has 16,384 columns and 1,048,576 rows where you can add data. However, despite this, people still manage to fill the sheet up with enormous amounts of data, so much so that Excel physically can’t add any more cells in the worksheet — upon the addition of new cells the error “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet” will show, inhibiting your ability to add new cells to the sheet.
However that may not always be the case — cells that appear visually empty may still have some bits of data still within; you need to clear the cells of this data before you could add more in.
In this blog, we will be looking at solutions to help users resolve the error “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet” and find possible solutions to help prevent the error from occurring again.
Step by step process – How to fix Excel error “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet”
- Firstly, open the sheet the sheet that shows this error.
- Than ensure there is content in cell A1.
- After that click on cell “B1”.
- Now click on “CTRL + SHIFT + RIGHT” or “CMD + SHIFT + RIGHT”.
- Now select the column “XFD” (last column on the sheet).
- Now go to the “Home” tab.
- From the “Clear” dropdown, select “Clear Formats”.
- Now select the cell “XFD1”.
- Now click on “CTRL + SHIFT + LEFT” or “CMD + SHIFT + LEFT”.
- Finally, test the fix by inserting a new column.
What this process does is remove all the formatting from cells towards the end which make way for new columns to take their place. This process can be replication for rows; if row 1,048,576 has some content or even formats, you can use the process above however this time to go to cell 1,048,576 — you will need to click on “CTRL + SHIFT + DOWN” or “CMD + SHIFT + DOWN”.
  | |
 Compatible with Windows, Mac OS, and Android. | FROM $6.00 user/month |
What is Conditional Formatting, and how does it help to insert new cells in Excel?
You may have noticed we stated conditional formatting in cells to be a cause for this particular error to form. You need to remove conditional formatting from cells in order to make them fully empty so Excel sheets will accept a new row or column. In Excel, conditional formatting is a tool that allows you to apply particular formatting to cells that satisfy certain conditions. It’s most commonly used to highlight, emphasise, or distinguish facts and information in a spreadsheet using colour-based formatting.
Users of spreadsheets may accomplish a variety of things with conditional formatting. First and foremost, it highlights critical information such as deadlines, high-risk assignments, and budget items. It may also break up a wall of statistics with a visual organising component, making big data sets more consumable. Finally, conditional formatting may turn your spreadsheet (which was previously simply used to store data) into a reliable “alert” system that emphasises important facts and keeps you on top of your task.
   |
If-Then Thinking
All conditional formatting rules are based on basic if-then logic: if X requirements are met, Y formatting is applied (commonly expressed as p q, or if p is met, apply q). However, you won’t have to hard-code any logic – You may choose the conditions you want the rules to fulfil using the built-in parameters in Excel and other spreadsheet programmes. Advanced users can utilise the built-in formulae in the software to create logic rules.
Conditions that have been set
Excel includes a large library of pre-programmed rules that cover virtually all of the tasks that novice users would need. In the next part, we’ll go through a few of the more common ones.
Application of multiple conditions
Multiple rules can be applied to a single cell or a group of cells. However, keep in mind rule hierarchy and precedence; throughout the walk-through, we’ll show you how to manage stacked rules.
Overall, conditional formatting is a simple method to keep you and your team informed about your data. For example, highlighting significant dates and deadlines, projects and assignments, financial limitations, and anything else you want to draw attention to visually. Conditional formatting, when used appropriately, will increase your productivity by decreasing time spent manually integrating data and making it simpler to detect trends, allowing you to focus on the major choices.
Step by step process – How to fix Excel error “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet”
 |
Step by step breakdown
- Firstly, open the sheet the sheet that shows this error.
- Than ensure there is content in cell A1.
- After that click on cell “B1”.
- Now click on “CTRL + SHIFT + RIGHT” or “CMD + SHIFT + RIGHT”.
- Now select the column “XFD” (last column on the sheet).
- Now go to the “Home” tab.
- From the “Clear” dropdown, select “Clear Formats”.
- Now select the cell “XFD1”.
- Now click on “CTRL + SHIFT + LEFT” or “CMD + SHIFT + LEFT”.
- Finally, test the fix by inserting a new column.
That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.