Excel Pie in Pie Workaround

• 2 min read

The source data is in a formatted table called Table1.

In a separate sheet there are two input cells. One for the state to report on and the other to enter the number of “main” segments to use.

The formula in cell A5 uses the GROUPBY function to summarise the formatted table based on the state selected in cell B1.

=GROUPBY(Table1[Customer Category],Table1[Amount],SUM,,0,-2,Table1[State]=B1)

The formula in cell D5 takes the summary report from A5 and splits it up using TAKE and DROP.

VSTACK and HSTACK are then used to create the final data set for the pie chart.

=LET(top,TAKE(A5#,B2),other,DROP(A5#,B2),VSTACK(top,HSTACK(“Misc”,SUM(INDEX(other,,2)))))

The data source for the pie chart is the spill range in D5.

Changing the B2 number automatically updates the segments in the pie chart.

You can check out my file at the link below.

Download Example File

Update

After I had written this post (but not published it), I saw a post by Jon Peltier who had created a LAMBDA function to do this more elegantly is a single formula. Link below.

https://peltiertech.com/fix-pie-chart-source-data