Remember that this is a single formula, although it is shown on two lines here for clarity. Give cell B1 the name addrTitle, then place the following formula in cell B2: This formula provides a dynamic title that you will later use for your chart. ="Trends for " & IF(FromYear=ToYear,FromYear,FromYear & " to " & ToYear) Still working on the "Source Data" worksheet, start by placing the following formula in cell B1: There is nothing special about the chart, but the changes you are getting ready to make will make it dynamic, and therefore much more useful. In this example, the chart that is embedded on the other worksheet is based on the data in the range F2:I5. Above the years place a capital letter that is the same as the column letter, and in column D place a number that is the same as the row number of the data. Place the cost factors in column E, starting at row 3. Start your table in column F and place your years in row 2.
KUTOOLS EXCEL MOVE MULTIPLE RANGE SELECTION SERIES
On your data worksheet (the one without the chart I'll name the worksheet "Source Data"), the data is arranged with each year in a separate column and a series of cost factors in each row. Name these two cells something like FromYear and ToYear. On the same sheet as the chart, create two input cells which will serve as "from" and "to" indicators. Let's say that your chart is embedded on a worksheet, but the worksheet is different than the one where the source data is located. One way to do this is with the use of named ranges and several worksheet functions. Make the changes often enough and you'll start casting about for ways to make the changes easier (and more reliably). For instance, if you have a data table that includes several years' worth of data, you may want to view a chart that is based on the first five years of data and then change the data range so the chart refers to a different subset of the data.
If you change the data range for your chart quite often, it can get tiresome to continually change the data range reference. You can use the tools on the Insert tab of the ribbon to quickly identify an entire data table or you can select a portion of a data table and use the same tools to create a chart based just on that portion. Excel is great when it comes to creating charts based on data in a data table.