Kenapa microsoft excel cant insert new cells because it would push non-empty cells

This tutorial solves a problem where Excel won’t allow you to insert new rows or columns in a worksheet.  When you try this, Excel displays the message “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet.  Those non-empty cells might appear empty but have blank values, some formatting or a formula.  Delete enough rows or columns to make room for what you want to insert and then try again.”

METHOD 1: Clear Last Row or Column

The most probable reason you are getting this message is that in the last row or column of your worksheet, there is either formatting, data or a formula.  This might not be easy to spot, but by following the instructions below you can easily clear the offending cells.

A quick way to navigate to the last column in your worksheet is to:

1. Click somewhere in the first empty column in your worksheet (to the right of any data)
2. Use the key combination CTRL ⇒ (CTRL and the right-arrow key)

A quick way to navigate to the last row in your worksheet is to:

1. Click somewhere in the first empty row in your worksheet (below any data)
2. Use the key combination CTRL ⇓ (CTRL and the down-arrow key)

If you can see either data, a formula or formatting in the last row or column, this is probably where the problem lies.

Here is a fairly reliable way to clear all cells to the right of or below your data (including the last column and row):

1. Select the whole of the first empty column in your worksheet (to the right of any data).  You can quickly select the whole column by clicking on the column letter that appears at the top of the worksheet
2. Use the key combination CTRL SHIFT ⇒ to select all columns to the right
3. On the ribbon’s Home tab, in the Editing group, click the Clear button and then Clear All
4. Now select the first empty row in your worksheet (below your data).  You can quickly select the whole row by clicking on the row number that appears on the left of your worksheet
5. Use the key combination CTRL SHIFT ⇓ to select all rows below
6. On the ribbon’s Home tab, in the Editing group, click the Clear button and then Clear All

Once you have completed these steps, try inserting a row or column.  You may get a warning message titled ‘Large Operation’.  The warning indicates that inserting new cells may take a significant amount of time to complete.  You can ignore the warning and click on OK.

METHOD 2: Copy to a New Worksheet

If the method above does not work, try this method:

1. Create a new worksheet in your workbook
2. Copy and paste your data into the new worksheet
3. Insert the new column or row in the copy of your data
4. Copy the copy of the data that now includes a new row or column
5. Navigate back to the original worksheet
6. Select the top left cell of the data that you originally copied
7. Paste

You will need to repeat these steps for each column or row you need to insert.

METHOD 3: Clear the ‘Used Range’

If method 1 and 2 fail,  Try method 3.

Method 3 requires you to reset the ‘used range’ in your worksheet. To do this:

1. Use the key combination ALT F11 to open the Visual Basic Editor (VBE)
2. Use the key combination CTRL G to open the Immediate window.  The Immediate window appears near the bottom of the VBE with the title ‘Immediate’
3. In the Immediate window type the following code: activesheet.usedrange.select
4. Press ENTER on your keyboard
5. Use the key combination ALT Q to close the VBE
6. Now try inserting a new column or row in your worksheet

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.

1. Firstly, open the sheet the sheet that shows this error.
2. Than ensure there is content in cell A1.
3. After that click on cell “B1”.
4. Now click on “CTRL + SHIFT + RIGHT” or “CMD + SHIFT + RIGHT”.
5. Now select the column “XFD” (last column on the sheet).
6. Now go to the “Home” tab.
7. From the “Clear” dropdown, select “Clear Formats”.
8. Now select the cell “XFD1”.
9. Now click on “CTRL + SHIFT + LEFT” or “CMD + SHIFT + LEFT”.
10. 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 work?

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.