I'm trying to make a chart that will expand automatically to show the new data. Now using google I can find how to do that pretty easily if for example I am only wanting to show current data (ie one data series) but I can't work out how to do it so that it only shows a selection of the old data too.</p> Say i'm trying to show sales for a company to compare this years sales to previous years sales and i've already got the previous years data in a spreadsheet so it is just this years data i'm adding to. Is there a quick and easy way to tell excel only to include the same data range for the previous years as i have included for the current year? (so that at the moment for example it will only show Jan-Sept sales for all the years but as soon as I type in the October sales figure the chart increases to show october for every year)</p> Anybody any ideas? Thanks a lot</p>
Rather than using excel, which I find can be very fickle in these situations, and prone to going wrong, could it not be done better using a database package instead?
not veryneat but couldn't you just hide the unused columns and then unhide each new one as you need it at the beginning of each month?
I think this would work Use =if statements.</p> </p> For example if sales for Sept 2006 is in cell A9 and for Sept 2007 is in B9</p> Replace the actual sales figure for Sept 2006(SAY 10,000), with =if(b9>0.01),100000,0)</p> </p> By doing this, if there is no sales figure in year 2007 column, year 2006 will show as 0. (nodding2fingers) </p>
I'd do it this way Use rows 1 and 2 as your data range for the graph. Use row 3 for last years sales figures. In cell A2 use this formula =if(A1="","",A3) Copy this formula across cells A2 to L2 Now, all cells in row 2 will stay blank, whilst row 3 contains all last years sales figures. Row 2 will only become populated when you add data to the corresponding month's figures for this year. If you can't get this to work PM me - if you can, I'll have a large one !
