Unable to insert a column without losing data

Hi uyanga 

If I understood your query correctly, you may still need an answer.

Excel has a limit of 16,384 columns, and if you have already filled all those columns with data, you cannot insert any more columns without first deleting some data to make space.   These are your options:-

A)  If the columns at the end of the sheet appear to be empty, first make sure they are:

Select the columns you think are empty, then select 'Column differences' from 'Go To Special' (under Find and Select on the Ribbon 'Home' tab). 

> This should display a message "No cells were found". 

    * It should then be safe to delete those columns to remove formatting that could be blocking the insert.   
   * Alternatively, if you want to play safe, you can use the options under the 'Clear' button (also on 'Home' tab), starting with 'Clear Formats' but skipping 'Clear all' and 'Clear contents' (which would delete any data).     

> However, if you don't get the above message, then all the cells with data should be highlighted, and the first cell with data will be selected.  If the cell(s) look empty, the data may be hidden, then look in the Formula Bar, and not the cell itself.  If you find valid data you want to keep, then you are stuck with a workaround.

B)  If all your columns have data, you have limited options, which all amount to workarounds.

B.1) Start a new sheet in the same workbook or start a new workbook to contain the new data.  

B.2) Similar to B.1, copy (some) data from your current sheet to a new sheet/workbook and delete the copied data from your original sheet so that you can insert again.  This might be better if you have processes that depend on your (latest) data remaining in a known place.

#Note:  The 'copy sheet' feature would be one way to achieve either of the above especially if you want to retain formatting - right click on the sheet tab to display the context menu, select 'Move or Copy' and remember to click on the 'Create a copy' checkbox.  Then delete whatever makes sense from the original and copy so that you have a complete set between the two.  It may be wise to make a backup copy first.

B.3)  If you need all the data on the same sheet, it then provided you have enough rows, your only option is to add your new data on a new set of rows.  You will no longer be able to insert an entire column, but you can still insert new columns on those rows you are adding data to.

C)  If none the above help, it may be time to leave Excel behind, at least in terms of data storage, and use something like Access to store the data, and possibly use Excel to just run queries on it.

uyanga 

Insert or delete rows and columns

Formatting options

When you select a row or column that has formatting applied, that formatting will be transferred to a new row or column that you insert. If you don't want the formatting to be applied, you can select the Insert Options button after you insert, and choose from one of the options as follows:

If the Insert Options button isn't visible, then go to File > Options > Advanced > in the Cut, copy and paste group, check the Show Insert Options buttons option.