Click & drag for visible cells only

[niravrph]

Hi,

I want to copy & paste (click & drag) a formula but have it apply to the visible cells/rows only. I have hidden a bunch of rows that I do not want the formula to apply to. How can I do this?

Thanks!
NIRAV

 

[circledchicken]

Hi Nirav,

One option:

• Copy the cell (Ctrl-C).
• Select the area you want to paste it to.
• Press F5>Special...>Visible cells only>OK
• Paste (Ctrl-V)

 

[niravrph]

Thank you for the reply. I followed your directions but I must be doing something wrong because when I click on F5>Special...>Visible cells only, it highlights the entire page and then when doing Ctrl-V, it tries to copy the formula to every cell in the worksheet! Obviously that's not right. Where is my error?

Thanks,
NIRAV

 

[circledchicken]

Thats weird. These are the steps I would do:

• Copy the cell you want to copy.
• With the hidden rows hidden, select the entire range you want to copy to.
• Press Alt>; (that's the shortcut for visible cells only). This should now select the visible cells only (you should notice a change in the selection with small gaps near the hidden cells)
• Paste, while the cells are still highlighted

If those are the steps you've followed, I'm really not sure why it would select everything, sorry! (I've tried it on Excel 2010 and it seems to work as expected).