SOLVED
Home

Table Style after Clearing Advanced Filter

%3CLINGO-SUB%20id%3D%22lingo-sub-764190%22%20slang%3D%22en-US%22%3ETable%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-764190%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20using%20the%20%3CSTRONG%3EClear%3C%2FSTRONG%3Ebutton%20under%20%3CSTRONG%3ESort%20%26amp%3B%20Filter%3C%2FSTRONG%3Ethe%20correct%20way%20to%20display%20the%20full%20Table%20again%20after%20the%20running%20of%20Advanced%20Filter%3F%26nbsp%3B%20I%20find%20that%20the%20rows%20are%20no%20longer%20showing%20in%20alternate%20colors.%26nbsp%3B%20I%20have%20to%20select%20the%20Style%20again.%26nbsp%3B%20Is%20this%20working%20as%20designed%3F%26nbsp%3B%20I%20am%20using%20Office%20365.%26nbsp%3B%20(I%20THINK%20it's%20the%20hidden%20rows%20from%20the%20Filter%20in%20the%20paler%20color%20and%20the%20visible%20ones%20stay%20the%20darker%20color)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOriginally%20I%20had%20a%20Table%20with%20No%20Style.%20However%20my%20table%20has%20data%20added%20to%20it%20every%20couple%20of%20days%20via%20copy%20and%20paste%20and%20I%20thought%20it%20would%20be%20easier%20for%20the%20user%20to%20see%20that%20the%20Table%20had%20extended%20with%20the%20new%20data%20pasted%20if%20I%20selected%20a%20style.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20just%20set%20the%20cell%20fill%20of%20the%20table%20to%20be%20a%20color%20and%20that%20may%20be%20sufficient%2C%20but%20I%20was%20surprised%20to%20see%20the%20Advanced%20Filter%20not%20reset%20itself%20with%20the%20alternate%20row%20colors%2C%20the%20way%20clearing%20a%20regular%20Filter%20does.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-764190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-764616%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-764616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378822%22%20target%3D%22_blank%22%3E%40Possiexx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Applying%20or%20removing%20a%20Filter%20to%20a%20Table%20should%20not%20affect%20the%20banded%20row%20formatting.%3C%2FP%3E%3CP%3EWhether%20you%20clear%20the%20filter%20from%20the%20down%20pointing%20arrow%20in%20the%20column%20header%20or%20by%20using%20the%20clear%20command%20on%20the%20Data%20Tab%20or%20by%20using%20the%20shortcut%20ALT%2C%20A%2C%20C%3C%2FP%3E%3CP%3EHowever%2C%3C%2FP%3E%3CP%3EIf%20you%20already%20applied%20formatting%20manually%20to%20your%20list%20BEFORE%20converting%20it%20to%20Table%20(CTRL%20%2B%20T)%2C%20then%20the%20table%20style%20will%20not%20overwrite%20the%20manually%20applied%20formatting.%3C%2FP%3E%3CP%3EI%20suggest%2C%26nbsp%3B%3C%2FP%3E%3CP%3E1-%20Converting%20your%20Table%20to%20a%20List%20(Table%20Design%20Tab)%3C%2FP%3E%3CP%3E2-%20Clear%20All%20Formatting%20(Home%20Tab%20%26gt%3B%26gt%3B%20Editing%20%26gt%3B%26gt%3BClear%20Format%20(shortcut%20ALT%2C%20E%2C%20A%2C%20F)%3C%2FP%3E%3CP%3E3-%20Re-apply%20the%20Number%20%2C%20Date%2C%20Currency%20formats%3C%2FP%3E%3CP%3E4-%20Convert%20to%20Table%20(CTRL%20%2B%20T)%3C%2FP%3E%3CP%3E5-%20Test%20again%20Filtering%20and%20Sorting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765257%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765257%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Nabil%3C%2FP%3E%3CP%3EThanks%20for%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20concerns%20the%20use%20of%20Advanced%20Filtering%20using%20this%20%22To%20open%20the%20%3CSTRONG%3EAdvanced%20Filter%3C%2FSTRONG%3Edialog%20box%2C%20click%20%3CSTRONG%3EData%3C%2FSTRONG%3E%26gt%3B%20%3CSTRONG%3EAdvanced%3C%2FSTRONG%3E.%22%26nbsp%3B%20There%20is%20no%20downward%20arrow%20in%20this%20case.%3C%2FP%3E%3CP%3EI%20don't%20have%20an%20issue%20with%20standard%20column%20Filtering.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765273%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378822%22%20target%3D%22_blank%22%3E%40Possiexx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20please%20share%20sample%20data%20in%20Excel%3F%3C%2FP%3E%3CP%3ENabil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765325%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENabil%3CBR%20%2F%3EThank%20you%20for%20looking%20at%20this.%3C%2FP%3E%3CP%3EThis%20is%20a%20sample%20with%20only%20one%20criterion%20but%20it%20shows%20the%20issue.%20I%20realise%20now%20that%20if%20a%20row%20was%20already%20light%20in%20colour%20it%20stays%20light%20after%20the%20Advanced%20Filtering%20and%20the%20subsequent%20Clear.%20It%20is%20the%20non-selected%20dark%20rows%20that%20are%20turned%20to%20light%20and%20remain%20light%20after%20Clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20about%20trying%20to%20copy%20the%20filtered%20data%20to%20another%20tab%20but%20you%20can't%20do%20that%20-%20it%20has%20to%20be%20to%20the%20same%20tab.%20The%20filtered%20data%20in%20this%20case%20would%20have%20no%20colors%20and%20the%20original%20data%20table%20isn't%20touched.%20I%20currently%20copy%20the%20bottom%20part%20of%20the%20filtered%20data%20based%20on%20date%20in%20column%201%2C%20manually%20to%20the%20end%20of%20another%20tab%20and%20it%20is%20no%20longer%20is%20a%20table%20or%20has%20colors%20which%20is%20fine%20for%20my%20requirement%20(The%20real%20source%20table%20has%20dates%20in%20the%20first%20column%20and%20I%20just%20copy%20the%20filtered%20rows%20added%20to%20the%20first%20tab%20since%20the%20last%20time%20I%20copied%20it)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20too%20many%20columns%20for%20this%20to%20be%20really%20practical.%20It%20would%20be%20easier%20to%20re-select%20the%20table%20design.%20In%20fact%20now%20I%20think%20about%20it%2C%20it%20probably%20doesn't%20really%20matter%20that%20the%20columns%20now%20won't%20have%20alternate%20coloring.%20However%20I%20would%20like%20to%20understand%20if%20what%20I%20find%20with%20Advanced%20Filtering%20is%20working%20as%20designed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20steps%20I%20use%20is%20that%20the%20real%20file%20has%203%20tabs.%20I%20filter%20the%20first%20tab%20data%20and%20copy%20the%20filtered%20data%20to%20the%20end%20of%20the%20second%20tab%20data.%20The%20third%20tab%20isn't%20relevant%20to%20this%20problem.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20633px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123877iABD190233355D2FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765420%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378822%22%20target%3D%22_blank%22%3E%40Possiexx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20get%20your%20point.%3C%2FP%3E%3CP%3EThe%20result%20of%20the%20Advanced%20filter%20will%20never%20carry%20the%20original%20formatting.%20If%20you%20copy%20that%20result%20to%20another%20sheet%2C%20you%20can%20reapply%20the%20same%20table%20style%20if%20you%20like.%3C%2FP%3E%3CP%3EHowever%2C%20after%20releasing%20your%20filter%2C%20the%20banded%20color%20of%20the%20rows%20are%20messed%20up.%3C%2FP%3E%3CP%3ETo%20fix%20it%3A%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20the%20whole%20range%3C%2FLI%3E%3CLI%3EGo%20to%20the%20Editing%20Group%20of%20the%20Home%20Tab%20%26gt%3B%26gt%3B%20Clear%20%26gt%3B%26gt%3B%20Clear%20Formats%20(alternatively%20hit%20the%20shortcut%20ALT%2C%20E%2C%20A%2CF)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CEM%3ENB%3A%20You%20can%20create%20a%20Macro%20that%20combines%20All%20these%20steps%20together.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765455%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20Style%20after%20Clearing%20Advanced%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Nabil%3C%2FP%3E%3CP%3EAlt%20E%20A%2C%20F%20works%20for%20me!%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20I%20can%20click%20on%20the%20Table%20Style.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Possiexx
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

@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

 

Highlighted

@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.

@Possiexx 

Would you please share sample data in Excel?

Nabil

@nabilmourad 

 

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.

Capture.JPG

Solution

@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

@nabilmourad 

 

Thanks Nabil

Alt E A, F works for me! 

Alternatively I can click on the Table Style.

 

Thanks again!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies