Where are Filter drop-down buttons?

Have you ever encountered a filtered range without Filter drop-down buttons available on the top?  No idea what I am talking about?  Take a look at the following screenshot.Excel Tips - Where is the filter icon 1Excel Tips - Where is the filter icon 2

Without the Filter buttons on the top, I tried to show hidden rows by unhiding rows without success…

Excel Tips - Where is the filter icon 3

So what to do to show all value?

No panic.  What you saw was a result from Advanced Filter.  You may clear the filter easily: Data->Filter->Clear

Excel Tips - Where is the filter icon 4

Here we go!

Excel Tips - Where is the filter icon 5

If your hidden rows was not a result from advanced filter, you may want to read When unhide rows doesn’t work.

Question:

How can we filter a specific date ignoring years?  For example, show only 1st of January of all years (say you have 10-years data). If you know how, please feel free to share with us by leaving a comment. Answers to be posted next week. 🙂

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

18 Responses to Where are Filter drop-down buttons?

  1. Caro says:

    I know it’s late, but you just saved my life.

    Like

  2. Ahmed Daoud says:

    Do pivot report from your data after adding a helper column to source containing dd-mm without year
    Then filter by required date of month

    Like

  3. Steve says:

    Thank you. I had filters on. I had forgotten that!
    Cheers
    Steve

    Like

  4. tsuyong says:

    Thanks for saving my life….. 🙂 I was just about to go crazy if it didn’t work …. but magically it work for me.. thanks thanks thanks.. (1000 times)

    Liked by 1 person

  5. Lindley says:

    I was so hoping this would work. I have a “hidden” column A. I’ve tried selecting it and resizing. I’ve tried “unhiding” the whole spread sheet. I’ve tried everything, including this…. which had me excited. Sadly, the CLEAR option was not available. But I’ll remember it for another time.

    Until then I have a hidden column A (or A1\ as the spreadsheet insists on saving as the option… which may be the only clue as to what is happening here) that I can’t get to. Very very odd.

    Like

  6. Thanks a million – have stressed out for some hours trying to ‘fix’ my spreadsheet and in a panic as I couldn’t figure out where my stock take rows and columns had gone! Done now and very happy. Your instructions were clear and easy to follow and VERY accurate!

    Like

    • MF says:

      Hi Lori
      Thanks for your kind words. Glad it helps.

      Like

      • Les Logar says:

        Hey smarty pants. I need some help. I would like to set up a view of a sheet that hides dup values occurring in one column. I want both views available for the user. One without dups and one with dups. Can you assist? I sure hope you are still around.

        Like

        • Michael (Micky) Avidan says:

          @Les Logar,
          Assuming your values are in column “A” starting in cell A1 (no empty cells in range) – try to run (one at a time) the two following Macros

          I used the “Conditional Formatting” feature to change the duplicates font color into White.

          Sub Hide_Duplicates()
          LR = Cells(Rows.Count, 1).End(xlUp).Row
          Set Rng = Range(“A1:A” & LR)
          Rng.FormatConditions.AddUniqueValues
          Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
          Rng.FormatConditions(1).DupeUnique = xlDuplicate
          With Rng.FormatConditions(1).Font
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
          End With
          End Sub

          Sub Unhide_Duplicates()
          LR = Cells(Rows.Count, 1).End(xlUp).Row
          Set Rng = Range(“A1:A” & LR)
          Rng.FormatConditions.Delete

          End Sub

          Michael (Micky) Avidan

          Like

          • Michael (Micky) Avidan says:

            As a matter of fact – I would slightly change the Code to the following:

            Public LR As Integer, Rng As Range
            Sub Hide_Duplicates()
            LR = Cells(Rows.Count, 1).End(xlUp).Row
            Set Rng = Range(“A1:A” & LR)
            Rng.FormatConditions.AddUniqueValues
            Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
            Rng.FormatConditions(1).DupeUnique = xlDuplicate
            With Rng.FormatConditions(1).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            End With
            End Sub

            Sub Unhide_Duplicates()
            Rng.FormatConditions.Delete

            End Sub

            Michael (Micky) Avidan

            Like

  7. Micky says:

    Two “fast” ways to filter a specific date (1st of January) by ignoring the years:

    1) Add the formula to an Helper Column to be filtered by: =TEXT(A2,”MMDD”) and copy down.

    2) Use Advanced Filter with a criteria: =AND(DAY(A2)=1,MONTH(A2)=1)

    3) OR Use Advanced Filter with a criteria: =DAY(A2)+MONTH(A2)=2
    [This one is valid for the 1st of January only]

    Michael (Micky) Avidan

    Like

    • MF says:

      Hi Micky,
      Thanks for your input. They all work well!
      Actually, your solution 1) will be part of my next post.
      Cheers,

      Like

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