Modify Series Data in the Peltier Tech Ribbon
Modify Data is the third button in the Chart Data section of the Peltier Tech custom ribbon tab. Before clicking the button, you must select a chart.
How the Feature Works
Modify Series Data opens a detailed dialog which provides information about all series in the active chart, allows you to select one or more of these series, and provides controls to move or resize the data ranges for each selected series, or switch their data to another sheet.
Example
Suppose I have the following data range and chart.
When I select the chart and click Modify Data from the ribbon, the following dialog appears.
I have already selected two of the series in the dialog. For each series, I can see the series name, the cell(s) that reference the series name, the rows and columns that reference the X and Y values, the series chart type, and which axis the series is plotted on. Following the example, the first series, named ‘alpha’, takes the series name from cell C2, its X values from B3:B8 (rows 3:8 of column B), and its Y values from C3:C8 (rows 3:8 of column C), it is a line chart, and it is plotted on the primary axis.
I can enter a number of rows or columns to move the X or Y range or series name for each selected series. This is helpful if I have several blocks of similar data on the active sheet, and I want to change the chart’s source data from one block to another block ten rows lower or six columns to the right. Alternatively, if I have a chart that plots data from one block, I can copy the chart, and run Modify Series Data on the copy so it plots data from another block.
I can enter a number of points that I want to add (or subtract) at the beginning or end of the series. If I added two more rows to the data above, I could simply select all series, enter 2 into the ‘Resize End of range by’ box, and click the ‘Resize Ranges for Selected Series’ button.
Finally, I can change the source data range of the selected series from one worksheet to another. This is great when you have multiple sheets with the same data layout. Copy the chart which plots data on one sheet, paste it onto another sheet, select all series in the dialog, select the new worksheet (the currently active sheet is selected by default), and click ‘Change Source Sheet for Selected Series’.