Hi
I'm hoping someone can help me! I have used the Table Style feature from Excel 2007 to format my chart data source. I want to do this as this way the chart will automatically update whenever I add a new row - saving me having to use the OFFSET function to make my chart dynamic (this is also important as I want to include another summary table in the same sheet and I can't do this if I'm using OFFSET as it will calculate incorrectly).
However, the Table Style forces me to have a heading in the top left corner of the table - which should be left blank so Excel can accurately detect that my first column of data is the headings for my legend series. This means that when I generate a chart, it thinks Column 1 is part of chart and charts this data accordingly.
Below is a copy of the data in my table:
Column1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000 441.2 667.4 363.6 307.6 8.6 0.2 0 0 0.4 77.4 68.8 205.6
2001 262.8 429.4 293.4 47.2 7.4 0 26.6 0 0 30.4 164.6 182
2002 147.6 407.4 247.4 55.8 44.2 0 0 0.2 26.6 9 181.4 94.8
2003 466.2 727.2 121.2 12.2 0 5.8 0.2 0 6.4 51.2 200.6 377.2
2004 460.8 350.4 413.8 60.4 54 50.6 0 0.8 2.8 42.6 105.2 236.8
2005 408 265.4 202.2 68.6 0.6 0 0 1.6 1.4 77 184.4 151
2006 410 227.6 455 396.2 22.4 0 0 0 6.4 1.8 43.6 240
2007 206.8 422.6 689.8 18 18.8 4.2 0 0.4 43.6 13.2 143.2 247.8
2008 515.2 670 264.7 10.2 0 0 0 0 5.2 117 54.6 325.8
2009 411 495.8 115.6 63.6 0.4 0 0 0 34.4 33.4 131 457.2
2010 630.2 428.8 198.4 136.6 66.2 0 0.2 0 40.4 157.8 208.2 390.4
2011 627.2 1110.2 231.6 193 0.6 0 0 0 3.2 141 152 227.2
2012 459.2 250.2 572.4 70.2 51.4 0 0 0 21.2 16.8 79.3 203
2013 219.8 398.4 381 101.6 31.6 0.6 0 2.6 0.2 98.6 314.2 210.8
Of course, I can manually go in and rename each item in the Legend (series) but this would take a while and would mean I have to do this every time a new year is added. I am creating this workbook for someone else to use (who is new to Excel) so I'm trying to keep this as simple as possible. Any help would be gratefully appreciated.
Thank you.
I'm hoping someone can help me! I have used the Table Style feature from Excel 2007 to format my chart data source. I want to do this as this way the chart will automatically update whenever I add a new row - saving me having to use the OFFSET function to make my chart dynamic (this is also important as I want to include another summary table in the same sheet and I can't do this if I'm using OFFSET as it will calculate incorrectly).
However, the Table Style forces me to have a heading in the top left corner of the table - which should be left blank so Excel can accurately detect that my first column of data is the headings for my legend series. This means that when I generate a chart, it thinks Column 1 is part of chart and charts this data accordingly.
Below is a copy of the data in my table:
Column1 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000 441.2 667.4 363.6 307.6 8.6 0.2 0 0 0.4 77.4 68.8 205.6
2001 262.8 429.4 293.4 47.2 7.4 0 26.6 0 0 30.4 164.6 182
2002 147.6 407.4 247.4 55.8 44.2 0 0 0.2 26.6 9 181.4 94.8
2003 466.2 727.2 121.2 12.2 0 5.8 0.2 0 6.4 51.2 200.6 377.2
2004 460.8 350.4 413.8 60.4 54 50.6 0 0.8 2.8 42.6 105.2 236.8
2005 408 265.4 202.2 68.6 0.6 0 0 1.6 1.4 77 184.4 151
2006 410 227.6 455 396.2 22.4 0 0 0 6.4 1.8 43.6 240
2007 206.8 422.6 689.8 18 18.8 4.2 0 0.4 43.6 13.2 143.2 247.8
2008 515.2 670 264.7 10.2 0 0 0 0 5.2 117 54.6 325.8
2009 411 495.8 115.6 63.6 0.4 0 0 0 34.4 33.4 131 457.2
2010 630.2 428.8 198.4 136.6 66.2 0 0.2 0 40.4 157.8 208.2 390.4
2011 627.2 1110.2 231.6 193 0.6 0 0 0 3.2 141 152 227.2
2012 459.2 250.2 572.4 70.2 51.4 0 0 0 21.2 16.8 79.3 203
2013 219.8 398.4 381 101.6 31.6 0.6 0 2.6 0.2 98.6 314.2 210.8
Of course, I can manually go in and rename each item in the Legend (series) but this would take a while and would mean I have to do this every time a new year is added. I am creating this workbook for someone else to use (who is new to Excel) so I'm trying to keep this as simple as possible. Any help would be gratefully appreciated.
Thank you.
