One day, a friend asked me: “If she deletes contents from a filtered range, would she also delete the hidden content?”  I answered: “Try and observe!” 😛

I know that is not the answer she expected.  Nevertheless, I believe that is the best way to learn.

Honestly I had the same question and hesitation before.  I found that it is quite confusing about the different behavior between hidden rows by hiding rows or by Filter.  It took me some time to do some tests to observe and understand the differences.  Here’s my sharing:

Before we experiment the differences toward Hidden Rows and Filtered Rows, let’s first see how Filter affects hidden rows. (Note: What I mean “Filtered Rows” in this article is the row(s) hidden by Filter.)

In the screenshot below, you will see ROW 4 and 9 are hidden by the direct method of Hiding rows, i.e. selecting the rows–> Right-click on row labels –> Hide.

After the rows are hidden, let’s turn on Auto Filter.

Because Row 4 and 9 (where A4 and A9 contains “Wed”) have been hidden, “Wed” is not available on the Filter menu.

The screenshot below shows the result after applying the filter.

Now let’s click on filter button for Day and see what’s on the menu:

See! Filter detects the hidden rows in the range now and make it visible for selection in the filter.

Let’s do another test:  Clear the Filter and then Hide row 2 by hiding row.

And there is one row (7) of data with “Mon” visible to us.

Let’s Filter Day and select “Mon” to display

Interestingly, the hidden row (2) is back!

Observation 1: Filter unhides hidden row(s).  

Tips: When you are not sure if there is any hidden rows in your filtered range (i.e. rows being hidden after a filter is applied), simply Reapply the filter:

Data –> Reapply   

Hidden rows will be unhidden by Filter then.

Warm-up exercise finishes!

Let’s see what happens to Hidden Rows & Filtered Rows when we apply some common actions: 1) Copy & Paste, 2) Delete Contents, 3) Input same value to a range, 4) Fill Down

All the experiments started with same contents.  The only difference: the one on the left is the result from hiding rows; whereas the one on the right is the result from Filter.

Experiment 1 – Select A1:B11, Copy and Paste to a new worksheet

Result:

• Hidden – All hidden contents were being copy and paste
• Filter – Only visible contents were being copy and paste

Experiment 2 – Select B2:B11, Press “Delete” on the keyboard (not delete cells)

Result:

• Hidden – OMG! Everything in the range is gone
• Filter – Only visible contents were deleted; all Filtered contents remained.

Experiment 3 – Select B2:B11, Enter “Not in the mood for work”, Press Ctrl+Enter

Result:

• Hidden – The text string were input everywhere in the range
• Filter – Only visible contents had been changed; no change to Filtered contents

Experiment 4 – In C2, input a formula “=B2*10” (without quotation), Fill Down

Result:

• Hidden – The formula was input to the whole range C2:C11
• Filter – The formula was input to the visible cells only

Observation 2: When Filter is on, you may feel free to apply various actions to the range without worrying of altering invisible cells,  EXCEPT the following situation:

Final Experiment – Select A15:B18, Copy and Paste to A2

Result:

• Both situations give exactly the same result.  The continuous range copied will be pasted as a continuous range.  No difference between hidden or filtered rows.

If you have a workaround for this, please share with us in comment.

Confusing enough? @_@

Tips: When you are dealing with hidden rows and you just want apply action to visible rows only, you may select “Visible Cells only” from Go to Special before you take any action.  Alt+; is the shortcut for selecting visible cells only.  For related post, please read

• 1) How to copy visible cells only?
• 2) Another useful tips with Go to Special – Visible cells only