So I have an Excel spreadsheet that I must have completed for a meeting at 3pm today. I’ve been working on it all week.
The spreadsheet contains a lot of information, so for easier reading, I formatted it as a table with alternating rows shaded.
Now, for some reason, I can’t insert columns. I’ll select an entire column by clicking the top part, but on right-click, “Insert” is greyed out. I have no problem adding rows, only columns.
I’m freaking out. This is an extremely important piece of work. I’ve scoured the internet for half an hour, and I’ve only found one person having the same problem, but no solution was posted. Can anyone help, please?
The arrows are for filters set up on the columns. I don’t think that’s the issue - I put filters on a spreadsheet just now and am still able to insert columns.
I agree - the OP should try doing it from the main menu.
It’s possible that spreadsheet already has the maximum number of columns, which may be 256 or may be 16,384 - I’m unable to tell. Anyway, note that Excel might think that columns contain data even if they don’t appear to - try deleting columns on the far right and then inserting.
If he ran out of columns, it still gives you the option to insert, but it gives you an error message saying it cannot shift nonblank cells off the sheet.
On the Review tab, check your protection settings. Is it possible that you have, or someone else has, set the protection for either the spreadsheet or the workbook so that columns cannot be inserted?
Do you have any array formulas in your spreadsheet? possibly something in the insert is messing up a formula (I’m just trying to brainstorm a little)
Well, I can’t find anything either but I’d try the following just in case.
1 - Using the view ribbon you can unfreeze panes and clear off any split settings (you can always reset them when done)
2 - Using the Data ribbon you can click on the funnel (filter) and remove all filtering (again you can turn filtering on after the insert)
3 - Right click anywhere in the table and you can choose the table option in the pop-up menu and convert the table back to a range.
Open a new spreadsheet and copy the table range to the new sheet. (caution- if anything in the table refers to data in your original workbook you may have to copy that data and re-point the references)
I think the 255 character limit is for column width, not total amount of characters that can be contained in a cell. I supposed it’s possible that having strings of unbroken text longer than 255 characters could cause problems if the cells were not formatted to permit wrapping, but I’m not sure if it would prevent insertion of columns.
Assuming, of course, that the OP’s spreadsheet hasn’t already reached the maximum number of columns, which is 16,000-something in Excel 2010.
Everyone, I just ended up copying all the values and pasting them to a new worksheet without source formatting. Not as pretty as it was before, but it’ll work. Thanks for responding!
I tried about six or seven different things with tables, arrays, merged cells etc. and could not reproduce your situation. In some attempts I could make both the delete and insert choices go gray but never just the insert by itself. That happens when you select an area which crosses the boundary of a table or array, but not when you choose a column. (Yes, it’s a slow day here and I was looking for something to work on)
I’m glad you were able to present the information to others but somewhat disappointed that we couldn’t find a reason for the conditions you saw.
Sorry I’m here after you have the work around, but have you tried removing the table formatting, inserting the column(s), then reapplying the table format? Also, if you want to shade every other row, have you tried conditional formatting and the ROW/ROUND functions? It may be easier to modify than formatting as a table.