Forum Discussion
Solved
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...
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:
- Select the whole range
- 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
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
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.
Nabil
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.
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:
- Select the whole range
- 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
