Excel Chart from Multiple Sheets

chartsmicrosoft excel

I have a bunch of sheets and each one has a value in cell K15 and call K20. How do I make a stacked column chart that shows these values stacked for each sheet?

Forgive my crude drawing, but something like this

enter image description here

Best Answer

The easiest method is to use an additional sheet to aggregate your data and locate your chart. Just create a column for each sheet, with a 3D reference to the appropriate other sheets for that data. Then, create your chart from this new, aggregated sheet.

multi-sheet chart

Just below Sheet4, K20 is the formula to get that particular value. Just copy, paste and update the values to make sure they're referencing the appropriate cells. Obviously, you could also use formula's to look up the values based upon your table's column and row labels.

Related Question