Hidden Rows vs. Filtered Rows

Excel Tips - Filter vs. Hidden 0

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.

Excel Tips - Filter vs. Hidden

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

Excel Tips - Filter vs. Hidden 1

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

Excel Tips - Filter vs. Hidden 2

The screenshot below shows the result after applying the filter.

Excel Tips - Filter vs. Hidden 3

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

Excel Tips - Filter vs. Hidden 4

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.

Excel Tips - Filter vs. Hidden 5

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

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

Excel Tips - Filter vs. Hidden 7

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   Excel Tips - Filter vs. Hidden 7.1

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.

Excel Tips - Filter vs. Hidden 0

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

Excel Tips - Filter vs. Hidden 8

Result:

Excel Tips - Filter vs. Hidden 9

  • 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)

Excel Tips - Filter vs. Hidden 10

Result:

Excel Tips - Filter vs. Hidden 11

  • 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

Excel Tips - Filter vs. Hidden 12

Result:

Excel Tips - Filter vs. Hidden 13

  • 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

Excel Tips - Filter vs. Hidden 14

Result:

Excel Tips - Filter vs. Hidden 15

  • 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

Excel Tips - Filter vs. Hidden 16

Result:

Excel Tips - Filter vs. Hidden 17

  • 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

This entry was posted in Excel Basic and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s