How to split table?

Copper Contributor

Hi , can i ask for help? is there a way or formula that can split tables with filter? Like this for example?

From this :

Screenshot 2023-09-19 094614.png

To This :

Screenshot 2023-09-19 094626.png

Or if possible, separate them by "LEVEL" into separate sheets?

PS : i am using Microsoft Excel 2019

Thank you so much!

6 Replies

Hi @RanzieJ,


As far as I know there is no formula that would automatically separate your table. For that you would need to write VBA/Macros.

One simple way you make "multilayered table" is to source table that you mentioned and insert pivot table. Choose LEVEL field and put for Rows and then add the rest fields for Rows. Amount field would be in Values.





If you had 365, you could create a simple function to pull certain records from the table.



'SplitTable Lambda
    header, Table1[#Headers],
    filtered, SORT(FILTER(Table1, Table1[Level] = level, ""), {1, 2}),
    VSTACK(header, filtered)



Then call the function where needed in a sheet:



Unfortunately, Excel 2019 does not support dynamic arrays nor Lambda.  It's still possible with a ctrl+shift+enter array (INDEX-SMALL) but it's not an elegant solution.  Using the Advanced Filter might be a better way to go in 2019.


Separate sheets might be easier because each sheet has a different formula and, also, one partial table cannot overlap the next.  The problem is that, in order to use recent methods, 2019 has to be removed in favour of using 365.  Then, the formula


    REDUCE({"",""}, UNIQUE(Level),
          headerRow, {"","","","";"Date","Name","Level","Amount"},
          selected,  FILTER(table, Level=grd),
          total,     SUM(TAKE(selected, ,-1)),
          totalRow,  HSTACK("Total","","", total),
          VSTACK(acc, headerRow, selected, totalRow)


will give


as a single output, with a bit of conditional formatting!

@RanzieJ You can certainly do this and get "nearly" what you are looking for by using the Show Report Filter trick. 


For your example, drop the "level" field in the Report Filter after you have laid out your Pivot Table. 


Here is how:



John Michaloudis

Microsoft MVP



Good idea. It's easy to overlook such functionality!