Why can’t I insert new rows and columns in Microsoft Excel?

Q A few years ago, I created a large spreadsheet to track my household expenses. I spent quite a lot of time on this, formatting the cells carefully and colour-coding various sections to make them easier to analyse.

It has worked fine all this time but now I’ve hit a problem: I wish to add some new rows and columns to let me track some additional categories but Excel simply refuses to let me add columns.

When I highlight one or more columns and select Columns from the Insert menu, Excel displays the following error message: ‘To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet’.

However, inserting rows works without problem. I’ve inserted lots of columns and rows over the years without Excel objecting, so why is it complaining now?
David Hall

A The error message window you describe includes the answer to your question. However, we know that verbose wording in dialogue boxes can put users off actually reading them, so it’s understandable that you didn’t notice.

The answer to the problem lies in the fact that, despite appearances, individual Excel 2003 worksheets have a finite size - of 65,536 rows by 256 columns. While this is an undeniably sizeable matrix it is one that allows for very many more rows than columns.

Where a spreadsheet has grown organically over a long period it is conceivable that, with the regular insertion of new data categories, the number of columns now exceeds the 256 limit. When you attempt to insert a new column, then, Excel is warning you that to do so would push cells in the last column (labelled ‘IV’) off the right-most side of the sheet.

However, this is not the only possibility. Logically speaking, cells (or columns of cells) don’t have to contain anything that’s necessarily meaningful to humans for Excel to consider them occupied.

For example, simply applying formatting to a cell is sufficient for it to be flagged as logically occupied. If an attempt to insert a new column would result in a formatted cell (or column of formatted cells) being shifted off the end the spreadsheet, then Excel will display the same message.

At some point, for instance, you may have applied particular types of formatting to the whole spreadsheet: the later attempt to insert new columns would force these formatted cells off the right-most side of the spreadsheet.

There are a few possible solutions. The first is to examine the right-most side of your worksheet to see what’s there. To do this, hold down Ctrl on the keyboard and tap the right cursor (arrow) key: this will instantly switch the view the last column (IV). If there’s nothing of import there, first click to select one or more redundant columns. Now click Edit, point to Clear and then choose All.

Now return to the point where you’d like to insert new columns and you should be able to insert as many new columns as you’ve just cleared.

On the other hand, if your spreadsheet has really grown to the extent that all 256 columns contain required data you have a couple of options that we can see. The easiest, though certainly not the cheapest, would be to upgrade to the most recent edition of Excel: the 2010 version allows for truly colossal worksheets – up to 1,048,576 rows by 16,384 columns.

Alternatively, you could change the orientation of the data in your spreadsheet in order to take advantage of the substantial number of rows compared to columns. With a complex spreadsheet this won’t be an easy undertaking – indeed, it may not be at all practical.

However, on the off-chance that your spreadsheet is reasonably simple, but merely very large, try using Excel’s Transpose function to switch the axes (though this will obviously only work if the sheet has fewer than 256 rows).

To do this, first click to highlight the whole sheet (or relevant parts of it) and then select Copy from the Edit menu (or use the Ctrl and C keyboard shortcut). Now scroll down to a blank part of the sheet (or start a new sheet) and choose the Paste Special option from the Edit menu. In the Paste Special dialogue box, click to tick the Transpose box, then click OK.