3

When I filter out a row in excel, if I edit the entire range (including the cell that is filtered out), only visible cells get edited. When my boss does this on his computer, the entire range gets edited, including what is hidden... There must be an option somewhere to change this.

A more visual example: Here are some cells:

     A 
1    1
2    0
3    0
4    1

If I filter column A to only keep the 1s, here is what I get:

     A 
1    1
4    1

I now select everything (the 1s) and I type ZZZ and press CTRL + ENTER to edit the entire range. I now unhide everything, here is my result:

     A 
1    ZZZ
2    0
3    0
4    ZZZ

Here is my boss's result:

     A 
1    ZZZ
2    ZZZ
3    ZZZ
4    ZZZ

What setting do I have to edit to make it so that we have the same answer?

  • I found someone with the same problem but no solution. I don't want to have to press F5, select visible cells. I don't have to do it on my machine, it does it automatically. Only my boss's machine has this issue. superuser.com/questions/642178/… – Sorin Ionescu Aug 14 at 13:52
0

This could be a difference on how these cells are hidden. With your sample data, try the following:

First:

  • Use a filter on your range to only see the 1s
  • Select range A2:A5 including the hidden cells
  • Type ZZZ and confirm with Ctrl+Enter
  • Remove filter to show all cells, result:

enter image description here

Now:

  • Select rows 3 and 4 and hide them
  • Select range A2:A5 including the hidden cells
  • Type ZZZ and confirm with Ctrl+Enter
  • Unhide the hidden rows, result:

enter image description here

So the conclusion might be that your boss is approaching "hidden" cells differently than you are doing. A more in depth comparison between filtered and hidden cells is made here.

Direct solution:

Make sure to, when the range A2:A5 is selected, you hit the Alt+; key combination to deselect all invisible cells in your selection before you start making changes.

  • My boss's computer is being used by me, I am running the exact same filtering operation (as your first demonstration) but his excel behaves like hidden cells rather than filtered cells. Super strange. I am going to assign CTRL + ENTER to a vba macro that does ALT + ., CTRL + ENTER. – Sorin Ionescu Aug 15 at 2:55
  • @sorinlonescu, which version of Excel is running on your boss pc, and what version on the other pcs? – JvdV Aug 15 at 5:11
  • Office 365 on all PCs – Sorin Ionescu Aug 15 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.