Home

Help with strange behavior on sliced and grouped table

%3CLINGO-SUB%20id%3D%22lingo-sub-1269062%22%20slang%3D%22en-US%22%3EHelp%20with%20strange%20behavior%20on%20sliced%20and%20grouped%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269062%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20experiencing%20some%20strange%20behavior%20when%20trying%20to%20use%20slicers%2C%20filters%20and%20groups%20at%20the%20same%20time%20in%20a%20Excel%20Table%20(NOT%20a%20PivotTable).%20I%20have%20been%20using%20Excel%20for%20several%20years%20now%20at%20various%20levels%20of%20expertise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20about%20700%20rows%2C%20and%20I%20have%20manually%20grouped%20several%20sections%20into%201-2%20layer%20deep%20groups%2C%20i.e.%2C%20the%20highest%20outline%20level%20in%20any%20row%20is%203.%3C%2FP%3E%3CP%3EI%20also%20have%20a%20slicer%20tied%20to%20a%20single%20column%2Ffilter%20within%20the%20table%2C%20call%20it%20WorkType%2C%20and%20I%20am%20trying%20to%20use%20it%20as%20a%20kind%20of%20GUI%20for%20the%20filter%20in%20that%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%20is%20that%20variously%2C%20when%20trying%20to%20expand%20groups%20by%20outline%20level%20(Using%20the%20upper-left%20outline%20level%20controls)%2C%20the%20current%20slicer%2Ffilter%20isn't%20obeyed%20(filtered-out%20data%20is%20shown)%2C%20and%20also%20when%20applying%20a%20slicer%2Ffilter%2C%20the%20groups%20are%20not%20collapsed%20in%20sensible%20ways%20(Several%20rows%20are%20shown%20at%20outline%20level%201%20and%2For%202%20which%20should%20be%20hidden%20according%20to%20the%20filter%2C%20and%20much%20data%20that%20should%20be%20shown%20isn't).%20I%20also%20noticed%20that%20the%20slicer%20is%20losing%20synchronization%20with%20the%20table%20column%20filter%20it%20is%20tied%20to%2C%20i.e.%2C%20when%20spot-checking%20the%20table%20header%20filter%2C%20it%20shows%20different%20options%20than%20the%20slicer%20GUI%2C%20some%20of%20which%20make%20no%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20first%20I%20thought%20that%20the%20rows%20were%20simply%20obeying%20the%20most%20recent%20command%20in%20terms%20of%20visibility%2C%20but%20it's%20apparently%20more%20complicated%20than%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20has%20to%20do%20with%20how%20hidden%20rows%20are%20revealed%20and%20queried%20with%20these%20functions%2C%20because%20they%20are%20all%20tied%20into%20manipulating%20rows%20based%20on%20hiding%20or%20revealing%20them%2C%20but%20fundamentally%20I%20just%20want%20to%20be%20able%20to%20hide%20the%20large%20subsections%20of%20my%20list%20while%20examining%20what%20remains%20in%20different%20ways.%20I%20have%20searched%20extensively%20about%20these%20functions%20and%20I've%20seen%20all%20the%20basic%20info%2C%20and%20I%20really%20need%20an%20expert%20in%20how%20the%20underlying%20mechanisms%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20all%20your%20help%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1269062%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Senior Member

Hello,

I am experiencing some strange behavior when trying to use slicers, filters and groups at the same time in a Excel Table (NOT a PivotTable). I have been using Excel for several years now at various levels of expertise.

 

I have a table with about 700 rows, and I have manually grouped several sections into 1-2 layer deep groups, i.e., the highest outline level in any row is 3.

I also have a slicer tied to a single column/filter within the table, call it WorkType, and I am trying to use it as a kind of GUI for the filter in that column.

 

My issue is that variously, when trying to expand groups by outline level (Using the upper-left outline level controls), the current slicer/filter isn't obeyed (filtered-out data is shown), and also when applying a slicer/filter, the groups are not collapsed in sensible ways (Several rows are shown at outline level 1 and/or 2 which should be hidden according to the filter, and much data that should be shown isn't). I also noticed that the slicer is losing synchronization with the table column filter it is tied to, i.e., when spot-checking the table header filter, it shows different options than the slicer GUI, some of which make no sense.

 

At first I thought that the rows were simply obeying the most recent command in terms of visibility, but it's apparently more complicated than that.

 

I think this has to do with how hidden rows are revealed and queried with these functions, because they are all tied into manipulating rows based on hiding or revealing them, but fundamentally I just want to be able to hide the large subsections of my list while examining what remains in different ways. I have searched extensively about these functions and I've seen all the basic info, and I really need an expert in how the underlying mechanisms work.

 

Thank you for all your help

Ben