asked on

Excel 2016 - How to prevent inserted column from inheriting attributes of column to the left

Excel 2016/Win10:

In my spreadsheet, column E contains hyperlinks. I just inserted a column to the right via
HOME TAB --> INSERT --> INSERT SHEET COLUMNS

The new column defaulted to hyperlink display format. i.e. when I type something, it appears in blue, underlined. (However, Excel does not see to think it's a live hyperlink: when I right-click on it, one of the options is "Hyperlink" rather than (e.g.) "Edit Hyperlink".

How can I prevent Excel from making the incorrect (even counterintuitive) assumption I wanted this col. to have a hyperlink attribute? I'm hoping there's an option somewhere...or perhaps a different way of inserting....

Thanks,
Steve

Nikolay Tomov

Hello Steve,

When you insert new column is the column E selected?
Also do you see a litle painter icon after? Like this one:

Have you tried the Clear Formatting option in Home tab?

Regards,
Nikolay

Stephen Kairys

ASKER

@Nikolay:
Column E was not selected. I was sitting on Column F.

When would I see the painter icon? Right after I insert? I ask b/c I'm not seeing it...

Thanks.

ASKER CERTIFIED SOLUTION

Nikolay Tomov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Kairys

ASKER

Second suggestion (Clear Formatting) does not seem to work. I'll try looking at that Excel Option now...

Stephen Kairys

ASKER

OK, I checked the options. "Show insert options buttons" was the only one not checked. So, I checked it, and I'm good.

That said, I'm not seeing the button come up when I insert a row. Instead, it's inheriting the previous row formatting, which I don't want. Is there a separate option to help me here?

Thanks.

Nikolay Tomov

That is very interesting. If you can see the painter icon when inserting columns this should apply to rows as well. The option you have just enabled relates to both of them.

Looks more or less like some Excel fault.

I have another suggestions that will reset Excel to its default state, although please keep in mind that you might have to re-enable your add-ins after that (if any). This action includes changing registry keys in the Registry Editor but the changes can be reversed. Please be very carefull when you perform this:

1. Close all Excel windows.
2. In the Start Menu type regedit. Blue cube icon will appear. Click it and Registry Editor will open.
3. Navigate to: HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\
4. Find the Excel key/folder and Right-Click on it.
5. Select Rename and rename the key to Excel.old. Do not delete the key just rename it!
6. Start Excel again.

Try to instert rows and columns again. You can check the Advanced options as well to be sure that the Cut, Copy and Paste options are all checked.

The Excel key in HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0 will be recreated with the default options for the program.

If you want to revert the changes made to the Registry just go again to HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0, rename the Excel key to Excel.temp and then rename the Excel.old to Excel.

Regards

Stephen Kairys

ASKER

Tell you what: at this point, I don't have the time to go through these steps per the registry editor. You answered my original question per columns so I will accept it and deal with the row issue at another time. Thank you.

Stephen Kairys

ASKER

Thanks again.