• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Not able to insert column in the worksheet

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


My friend is facing this problem, he is not able to insert additional column in the worksheet.

I have “clear”, “delete” blank cells but still facing the same problem.


I am getting alert as “To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet”.


Can anyone help me in this please.
 
Good day sachinbizboy


Have you checked to see that there are no merged cells, merged cells are bad and should not be used. Also if the worksheet is protected that could stop the insert new column/row.
 
bobhc, thanks a lot for the help, yes there are various merged cells in the sheet.


I un-merged cells which were in that particular column but now I will unmerge all and recheck.


The sheet is not protected.


Have a nice day.
 
sachinbizboy


Merged cells restrict what you can do to cells/columns and rows, you should consider using “Center Across Selection” instead. Please follow these steps to achive the same result but without the restrictions of merged cells.


1 - Select the cells that you want to “merge”,


2 – Open the Format Cells dialog box (Home Ribbon, Font group, little button in bottom right or right click and select Format Cells from the list) or better still Ctrl key plus the number one key, Ctrl+1


3 – On the Format Cells dialog box, go to the Alignment tab and on the “Text alignment”-Horizontal, select the “Center Across Selection” option and click OK.


Your table will look like the one with merged cells.You can get the same look on your worksheets using either merged cells and Center Across Selection methods but if you need to use your data to do further operations, like sorting, or inserting rows and columns using merged cells, you will be restricted. With Center Across Selection you will not have this kind of problems.
 
Does the sheet have the "extra" columns hidden perhaps? You might try the following steps:


1. Unhide all extra columns to the right.

2. Select these extra columns, right-click, delete. This should reset the right-hand boundary
 
Also check that cells with values such as a space hasn't been inserted all the way to the edge of the worksheet, Pressing Ctrl End will shows you the last used cell which shouldn't be at the right hand side or bottom of the worksheets natural maximum ranges
 
sachinbizboy,

I think any value is existed in the last column XFD (Excel-2007). You can check it according to Hui's statement. Press Ctrl+End. If it highlight in any cell of XFD, delete any column. You can insert a column.


Regards,


Muneer
 
Hi everyone, thanks a lot for the help, extreamally sorry for late reply.


I will try all this on my friend's PC.


Have a nice day.
 
You must log in or register to reply here.
Back
Top