Table Style after Clearing Advanced Filter

New Contributor

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

6 Replies


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


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



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.



Would you please share sample data in Excel?





Thank you for looking at this.

This is a sample with only one criterion but it shows the issue. I realise now that if a row was already light in colour it stays light after the Advanced Filtering and the subsequent Clear. It is the non-selected dark rows that are turned to light and remain light after Clear.


I thought about trying to copy the filtered data to another tab but you can't do that - it has to be to the same tab. The filtered data in this case would have no colors and the original data table isn't touched. I currently copy the bottom part of the filtered data based on date in column 1, manually to the end of another tab and it is no longer is a table or has colors which is fine for my requirement (The real source table has dates in the first column and I just copy the filtered rows added to the first tab since the last time I copied it)


I have too many columns for this to be really practical. It would be easier to re-select the table design. In fact now I think about it, it probably doesn't really matter that the columns now won't have alternate coloring. However I would like to understand if what I find with Advanced Filtering is working as designed.


The steps I use is that the real file has 3 tabs. I filter the first tab data and copy the filtered data to the end of the second tab data. The third tab isn't relevant to this problem.





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




Thanks Nabil

Alt E A, F works for me! 

Alternatively I can click on the Table Style.


Thanks again!

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
Filter complex table to simple table
Sam2009 in Excel on
1 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
How do I Find/Replace or Filter with exact words/characters?
MattC475 in Excel on
5 Replies