Dynamic line charting of multiple series of data

rickcb

New Member
Joined
Oct 23, 2006
Messages
23
I have a worksheet with 180 columns of data (17565 rows of data). I have to create a chart with each column. I'm looking for a way to do this dynamically instead of creating 180 line charts.

Help anyone !
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You can define a dynamic chart based on named ranges. To name a range, go to the Insert menu > Names > Define. In the Name box, enter the name, and in the Refers To box, enter the formula that defines the range.

So your data starts in A1, is 180 columns wide and 17565 rows high, in Sheet 1 of Book1.xls. In Sheet 2 cell B2, enter a value between 1 and 180.

Now to define the range:

Insert > Names > Define

Name: MyData
RefersTo:
=OFFSET(Sheet1!$A$1:$A$17565,0,Sheet2!$B$2-1)

Create a chart with the first column. Select the series, and the formula bar should show this:

=SERIES(,,Sheet1!$A$17565,1)

Change this to read

=SERIES(,,Sheet1!MyData,1)

Press Enter, and Excel will change it to:

=SERIES(,,Book1.xls!MyData,1)

Change the value in Sheet2!B2 and the chart will reflect the data in the indicated column. You can make this more fun using a scroll bar linked to Sheet2!B2.

More on Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
 

Watch MrExcel Video

Forum statistics

Threads
1,114,400
Messages
5,547,735
Members
410,809
Latest member
lilwayne
Top