'Insert options' does not appear after inserting new columns in excel

**Bradex** · 12-23-2017, 01:19 AM

Whenever I insert columns in excel, the formatting of the columns to the left is copied to it automatically.

The default behavior should have been the appearance of the paintbrush icon (insert options) which would have offered me three options: format same as left, format same as right, clear formatting.

But, this is not happening.

Please note that I have 'insert options' enabled. File > Options > Advanced > 'Show Insert Options buttons' box is ticked.

I have tried disabling and re-enabling this setting, but it still does not work.

This issue is not specific to a particular excel sheet but occurs in all newly created excel sheets as well.

How can I get the 'Insert options' paintbrush icon back?

NOTE: Surprisingly, the paintbrush icon appears every time I insert a new row. I am having this issue only when I try to insert a new column!

Thanks in advance!

**AliGW** · 12-23-2017, 01:54 AM

NOTE: Surprisingly, the paintbrush icon appears every time I insert a new column. I am having this issue only when I try to insert a new column!

Sorry - this makes no sense! Please clarify what you mean.

**Bradex** · 12-23-2017, 02:05 AM

Sorry!

I meant to say that "the paintbrush icon appears every time I insert a new row. I am having this issue only when I try to insert a new column!"

I have corrected the error.

**AliGW** · 12-23-2017, 02:09 AM

I am not seeing the issue here - sorry. All is working as expected.

**Bradex** · 12-23-2017, 02:17 AM

When I insert a new row, the paintbrush icon appears and I am given the options: format same as above/below, clear formatting. I can select one of these.

However, when I insert a new column, the paintbrush icon does not appear (I am not given the options: format same as left/right, clear formatting). Instead, excel automatically formats the inserted column (with the the same format as that of the column to the left of the inserted column).

I hope you are able to see the issue I am facing.

**AliGW** · 12-23-2017, 02:20 AM

Yes, of course I understand, but as I said in post #4, I am not seeing the issue. Have you tried repairing your Excel (Office) installation?

**Bradex** · 12-23-2017, 02:30 AM

No. I have not tried that yet.

I also noticed just now that the function works for both rows and columns in 'newly created' excel sheets. Perhaps, I overlooked it when I stated earlier that the error occurred in new sheets as well or this error could be occurring randomly (the former appears more likely).

For the time being, I am encountering this error in 'specific' excel sheets. So I think this is unlikely to be an error in excel itself.

NOTE: I am well aware of the fact that the paintbrush will appear only if the formatting of the column (row) to the left/right of the newly inserted column (above/below the newly inserted row) is different from that of the default.

**AliGW** · 12-23-2017, 02:32 AM

OK - so you now think it is specific Excel sheets, so provide a sample file where it is happening. Otherwise there is NOTHING we can do to help. Please note that I am not saying that you are wrong, merely that it isn't happening to me.

**Bradex** · 12-23-2017, 03:16 AM

Here is a sample file

Between the No and Date columns, try adding a column.

**sandy666** · 12-23-2017, 03:57 AM

maybe turn off flash fill

flashfill.jpg

**Bradex** · 12-23-2017, 04:03 AM

Originally Posted by sandy666

maybe turn off flash fill

Attachment 553183

Thanks for the suggestion!

I tried it, but the issue persists.

**sandy666** · 12-23-2017, 04:07 AM

Did you try (after un-tick FlashFill) save, close, reopen workbook then insert column?

**Bradex** · 12-23-2017, 04:14 AM

Originally Posted by sandy666

Did you try (after un-tick FlashFill) save, close, reopen workbook then insert column?

Initially, I just saved the file (after the setting change) and tried inserting a column. Now, I saved, closed, and then reopened the file as you had suggested. But still, the newly inserted column does not display the paintbrush.

**AliGW** · 12-23-2017, 04:26 AM

I see the problem with your file, and it now appears to have 'infected' my laptop! Very odd ...

**sandy666** · 12-23-2017, 04:26 AM

Do you've all three checkbox ticked in Advanced - Cut/Copy/Paste?

ccp.jpg

**AliGW** · 12-23-2017, 04:27 AM

I have, Sandy. Have you tried the OP's file?

**sandy666** · 12-23-2017, 04:28 AM

Yeah, it's clean file for me

**AliGW** · 12-23-2017, 04:29 AM

Weird. I wonder if this has anything to do with items on the clipboard?

**sandy666** · 12-23-2017, 04:31 AM

here is with OP file

opfile.jpg

maybe OP's 2016 is for H&S ?

there is a connection, I suggest to remove it first and check again

**AliGW** · 12-23-2017, 04:34 AM

On reading the Microsoft support page, I think Excel decides whether or not to offer the button:

The Insert Options button may become available when you insert cells, rows, and columns into your worksheet. The button appears on the UI only when one or more of the following conditions are true:

The row or any cell in the row above or below the inserted row has formatting different from the default.
The column or any cell in the column to the left or right of the inserted column has formatting different from the default.
Any cell above, below, to the right, or to the left of the inserted cell or cells has formatting different from the default.

NOTE: The button does not appear when you insert copied cells.

The button appears just below and to the right of the inserted selection. When you insert rows and columns, the button may also appear below and to the right of the row or column heading.

When you click the button, you are presented with a list of options that you can apply to the inserted selection.

**AliGW** · 12-23-2017, 04:36 AM

Originally Posted by sandy666

maybe OP's 2016 is for H&S ?

Mine isn't, so that's not it. I think it's just Excel applying the logic outlined on the MS support page.

https://support.microsoft.com/en-us/...utton-in-excel

**sandy666** · 12-23-2017, 04:37 AM

So I have Magic Excel

OP's file, no formatting from the left or right side

ccp2.jpg

**AliGW** · 12-23-2017, 04:40 AM

Have you changed your default formatting, Sandy? I haven't and the OP probably hasn't.

**Bradex** · 12-23-2017, 04:41 AM

Originally Posted by AliGW

I see the problem with your file, and it now appears to have 'infected' my laptop! Very odd ...

Oh my god! I am really sorry! I did not mean to do it!

I have been facing this problem for quite a while. Apart from this issue, there is no other issue with excel or my PC. It is most likely a minor bug in excel.

I hope you don't face any further issues!

**AliGW** · 12-23-2017, 04:42 AM

I don't think it is a bug, having now read the MS support page (see post #20). I think it's Excel's default behaviour.

**Bradex** · 12-23-2017, 04:43 AM

Originally Posted by sandy666

Do you've all three checkbox ticked in Advanced - Cut/Copy/Paste?

Attachment 553184

Yes. I do have all three options ticked.

**AliGW** · 12-23-2017, 04:44 AM

Please read post #20, which makes it clear what is happening.

**sandy666** · 12-23-2017, 04:45 AM

@Bradex
Call MS support on-line [it's free], (but for me they're amateurs, I agreed for remote service on-line, man tried different things for hour but on the end did nothng

)

but who knows, maybe they will help

@AliGw - I didn't change anything, all workbook is original

**Bradex** · 12-23-2017, 04:47 AM

Originally Posted by AliGW

On reading the Microsoft support page, I think Excel decides whether or not to offer the button:

How can I coax excel to offer me the options?

Does Microsoft have any explanation for the 'may'?

**AliGW** · 12-23-2017, 04:48 AM

I still think it's the correct behaviour - from the information on the support page, there is no reason for the button to appear in this file. I don't believe there is an issue here, at least not one that is really worth bothering about. If you find a solution, I'll be interested to know, though.

**AliGW** · 12-23-2017, 04:48 AM

Originally Posted by Bradex

How can I coax excel to offer me the options?

Does Microsoft have any explanation for the 'may'?

I just Googled it - why don't you do that yourself and see what you can find?

**sandy666** · 12-23-2017, 04:52 AM

Ok, I think I got it
View - Freeze Panes - Unfreeze Panes - Insert Column - Freeze Panes

I lost this option because I hate freeze Panes so I turn it off immediately

**AliGW** · 12-23-2017, 04:53 AM

Ah, Sandy - that's it!

**sandy666** · 12-23-2017, 04:55 AM

wait for OP's opinion first

**AliGW** · 12-23-2017, 04:55 AM

I've tested it on his file.

**Bradex** · 12-23-2017, 04:56 AM

Originally Posted by AliGW

I still think it's the correct behaviour - from the information on the support page, there is no reason for the button to appear in this file. I don't believe there is an issue here, at least not one that is really worth bothering about. If you find a solution, I'll be interested to know, though.

If there is no reason for the button to appear in this file, how does sandy666 make it appear?

Also, it 'is' a troublesome issue for me.

I had posted regarding the issue about an year back here, but I am yet to find a solution.

**AliGW** · 12-23-2017, 04:57 AM

See post #32.

**Bradex** · 12-23-2017, 05:09 AM

Originally Posted by sandy666

Ok, I think I got it
View - Freeze Panes - Unfreeze Panes - Insert Column - Freeze Panes

I lost this option because I hate freeze Panes so I turn it off immediately

Voila! That did it! sandy666 you are amazing!

This issue has been troubling me for years now! I can’t believe that this had such a simple solution!

I always work on my spreadsheets with the top row frozen. Now, I see that this has resulted in this issue. All this time, I used to think that the issue crops up randomly. I just never found the association!

Thanks to both of you for your valuable time and advice!

You made my day!

**AliGW** · 12-23-2017, 05:11 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Bradex** · 12-23-2017, 05:11 AM

Originally Posted by AliGW

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I just did that! Thanks!

**sandy666** · 12-23-2017, 05:13 AM

You are welcome

Have a nice day

**beeluten** · 12-24-2017, 07:15 AM

Thanks for the suggestion!

I tried it, but the issue persists.

**beeluten** · 12-24-2017, 07:29 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Re: 'Insert options' does not appear after inserting new columns in excel

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] inserting pictures in excel sheet duplicating on insert.

[SOLVED] unable to insert columns in excel, insert-> columns (disabled)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions