Home

EXCEL AUTOFILL NEXT TO HIDDEN COLUMN

Highlighted
sam_cee1992
New Contributor

Hi All,

 

I have just recently been upgraded to Excel 2016 from 2007.

 

See attached pictures

 

I could double click the autofill button when highlighting the formulas on the right of the black line and they would fill down to the last row.

 

The issue I now have is I can no longer do this, the column CM, which is hidden normally, is blank, however, when hidden, it seemed to be fine before in 2007 for the autofill, now it doesn't work.

 

How can I fix this?

 

any help would be appreciated

thanks

 

 

Sam

Capture1.JPGCapture2.JPG

3 Replies
Highlighted

Hi,

Autofill requires data in the cell immediately to the left for it to work.  Otherwise, it doesn't know how far down the page to fill.  It will work correctly if you have contiguous blocks of data i.e. no blank rows &/or no blank columns in your data table.

 

You can also use Fill from the Home Tab --> Editing block and "Fill" option.

 

In this case you have two options...

1. Delete the column "CM" to get back to a contiguous block or...

2. Move the "thing" you have in "CN2" over to "CM2" use Fill, the way you always used to, then - with the range still highlighted - drag that range back over to column "CN"

 

Kind regards

N

Highlighted

Thanks for your reply, this is a report run out of the Dynamics AX system, that takes up all the hidden columns. the CM info is blank on AX when I copy over, this is run as a report and because when it is usually hidden, it seemed to take the next unhidden column as a autofill reference, but it no longer does.

that's my issue, is it a change in the software? or can I fix it through the settings/code

Highlighted
Hi Sam
Not sure I can answer this as I don't use Dynamics but, sounds as though it might be Dynamics settings - if it's just been updated and was working for you before. Also, reports that are created from other systems (such as Dynamic AX, Dynamics 365, SQL, Vector etc.) may be set up to add or remove columns. My previous response should solve the problem but will need to be carried out every time you get a new report. If it can be fixed at source (Dynamics) that's the thing you should do. If not, you will need to think of using Power Query in Excel and creating a template - but that takes a little more effort.
:-(
Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
0 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
3 Replies