Posted on 2012-09-10
Last Modified: 2012-09-10
We have a a spreadsheet in excel 2003 format which has reached it's limit in terms of number of columns
We need to insert another column - is there a work around for this?
Also, if we delete columns we still cannot insert columns, getting the following error message:

To prevent possible loss of data excel cannot shift non-blank cells off the worksheet .......

We have checked for non-blank cells but cannot find any - unhiding fields still reveals no non-blank cells
Question by:doctorbill
    LVL 85

    Assisted Solution

    by:Rory Archibald
    If you need more columns then you'll have to use another sheet (unless you can transpose your data layout)
    Have you tried saving the workbook after deleting the columns to free up space? (it sounds as though the used range is not resetting)
    LVL 13

    Assisted Solution

    If saving/loading won't help, it might be useful to just copy/paste all the content to another table (maybe into another file).

    Do you really have an Excel sheet with 255 columns ?
    Sounds overwhelming. Is it still easy to work with ?

    I've had some sheets with about 100 columns and 2000 rows, and a bunch of formulas in there. Had to swich off auto calculation to keep it editable, and could do a lunch break while doing a recalc ...

    Author Comment

    It's not my sheet - belongs to a customer I support. I have suggested that the file is getting a bit cumbersome.
    They could migrate to excel 2010 where there are >16,000 columns allowed  I suppose
    LVL 13

    Accepted Solution

    They could migrate to excel 2010 where there are >16,000 columns allowed  I suppose

    That might be a short term solution, but maybe they should think about a redesign of the file. Or maybe some database thingie ... Excel is nice, and overwhelmingly mighty in many aspects, but it often becomes a torture when abused as database.

    BTW: If there are less than 255 rows, you could possibly transpose the table into another one. That would open 65535 in the former column direction.

    Author Closing Comment

    Solution as I expected

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now