Filtering the display of Chart information

[ddub25]

I have a chart linked to a table which displays profits for multiple categories and multiple items of the same category. See table below.

I have output the cumulative profits for all categories to a line graph, but want to occasionally be able to filter individual items, such as bananas or oranges lets say, and see cumulative profits for them.

How can this be done?

Dan

<table style="border-collapse: collapse; width: 207pt;" width="275" border="0" cellpadding="0" cellspacing="0"><col style="width: 71pt;" width="94"> <col style="width: 48pt;" width="64"> <col style="width: 88pt;" width="117"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 71pt;" width="94" height="20">Item</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Profit</td> <td class="xl65" style="border-left: medium none; width: 88pt;" width="117">Cumulative Profit</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">apples</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">20</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">20</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">bananas</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">-10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">bananas</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">30</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">40</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">pears</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">70</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">110</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">oranges</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">-5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">105</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">oranges</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">15</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">120</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">oranges</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">35</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">155</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">kiwi</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">-25</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">130</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">strawberries</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">30</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">160</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">strawberries</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">170</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">strawberries</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">-20</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">150</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">strawberries</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">15</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">165</td> </tr> </tbody></table>

 

[Lori_M]

With data in A1:C13, try populating the cumulative profit column by filling down from C2:

=SUBTOTAL(9,B2)+N(C1)

 

[ddub25]

thanks, thats great. If want to get more advanced with filtering chart output should I be using pivot tables or can I do all I need with a Table and standard chart linked to it?