[Webinar] Learn how to a build a cloud-first strategyRegister Now



Posted on 2012-09-10
Medium Priority
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
  • 2
  • 2
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 668 total points
ID: 38382933
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 14

Assisted Solution

frankhelk earned 1332 total points
ID: 38383183
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

ID: 38383238
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 14

Accepted Solution

frankhelk earned 1332 total points
ID: 38383326
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

ID: 38383435
Solution as I expected

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

864 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