Forum Discussion

Possiexx's avatar
Possiexx
Copper Contributor
Jul 19, 2019

Table Style after Clearing Advanced Filter

Is using the Clear button under Sort & Filter the correct way to display the full Table again after the running of Advanced Filter?  I find that the rows are no longer showing in alternate colors.  I have to select the Style again.  Is this working as designed?  I am using Office 365.  (I THINK it's the hidden rows from the Filter in the paler color and the visible ones stay the darker color)

 

Originally I had a Table with No Style. However my table has data added to it every couple of days via copy and paste and I thought it would be easier for the user to see that the Table had extended with the new data pasted if I selected a style.

 

I could just set the cell fill of the table to be a color and that may be sufficient, but I was surprised to see the Advanced Filter not reset itself with the alternate row colors, the way clearing a regular Filter does.

Thank you

  • Possiexx 

    Hi

    I get your point.

    The result of the Advanced filter will never carry the original formatting. If you copy that result to another sheet, you can reapply the same table style if you like.

    However, after releasing your filter, the banded color of the rows are messed up.

    To fix it:

    1. Select the whole range
    2. Go to the Editing Group of the Home Tab >> Clear >> Clear Formats (alternatively hit the shortcut ALT, E, A,F)

    NB: You can create a Macro that combines All these steps together.

     

    Hope that helps

    Nabil Mourad

  • Possiexx 

    Hi Applying or removing a Filter to a Table should not affect the banded row formatting.

    Whether you clear the filter from the down pointing arrow in the column header or by using the clear command on the Data Tab or by using the shortcut ALT, A, C

    However,

    If you already applied formatting manually to your list BEFORE converting it to Table (CTRL + T), then the table style will not overwrite the manually applied formatting.

    I suggest, 

    1- Converting your Table to a List (Table Design Tab)

    2- Clear All Formatting (Home Tab >> Editing >>Clear Format (shortcut ALT, E, A, F)

    3- Re-apply the Number , Date, Currency formats

    4- Convert to Table (CTRL + T)

    5- Test again Filtering and Sorting.

     

    Hope that helps

    Nabil Mourad

     

    • Possiexx's avatar
      Possiexx
      Copper Contributor

      nabilmourad 

      Hi Nabil

      Thanks for your response.

       

      My problem concerns the use of Advanced Filtering using this "To open the Advanced Filter dialog box, click Data > Advanced."  There is no downward arrow in this case.

      I don't have an issue with standard column Filtering.

Resources