Search code examples
google-sheetschartsbar-chartstacked-chart

Google Sheets: How to make a stacked/aggregate chart

I have made a bar chart which aggregates my data, but is there any way I can split each bar based on the data it is aggregating - similar to how a stacked bar chart would look?

Here is a bad artists impression (thick blue lines mine). The idea is that it's important to know from looking at the graph if I sold 5 at £1, or 1 at £5.

What I want....

Ideally this would work even if the price for each item is variable, but that is not essential (eg: if there is a 'hack' with hardcoding Apple = 3, I can live with that.)

I'm also fine inputting helper columns etc, within reason, but I would want to be able to easily continue to add things to the list on the left without having to add new helper columns each time (calculated ones are fine, of course.)

Thanks in advance.

UPDATE: With thanks to Kin Siang below, I ended up implementing a slightly modified version of their solution, which I am posting here for completeness.

I added a very large (but finite) number of helper columns to the right, with a formula in each cell which would look for the nth occurrence of the item in the main list (wrapped in an iferror to make the unused cells blank).

=iferror(index(FILTER($A:$B,$A:$A=$D2),E$1,2))

Solution

Theoretically it could run out of space one day, but I have made it suitably large that this should not be an issue. It has the advantage over the other solution that I do not need to sort or otherwise manipulate the input range and can continue trickling in data to the main list and have the chart automatically update.

Solution

  • Yes, it is possible to display the chart in your case, however need some data transpose in order to do so, let me show you the example with dataset

    Assuming this is your original data:

    enter image description here

    First sort the data by alphabet, and enter this formula in new column

    =if(G39="",1,if(G40=G39,I39+1,if(G40<>G39,1)))

    enter image description here

    Next add new column for categorical purpose, by using concatenate function

    ="Price"&I40

    enter image description here

    In the transform data for chart purpose, enter this formula to split all price into different row, different column for different product

    =sumifs($H$40:$H$47,$G$40:$G$47,$A41,$J$40:$J$47,B$40)

    enter image description here

    After that i select stack bar chart and ensure the price in under series, in case in 23 will have some problem to set price at series correctly, you can use 33 data create stack bar chart and update the data range again, it will work also

    Here is the cute chart you expected, accept if help :)

    enter image description here

    *When certain fruit has less price record, it is advised to fill in 0, as the data table need in same column (see the orange price 3), although I didnot test if blank