• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel charts: variable data range?

X

Xesh

Member
Does anybody know how to create a chart whose data range changes as data is input? For instance, if the chart references 5 column series with 10000 rows but only the first 1000 rows have data, the chart should display only those 1000 data points; when another 1000 are input, it should display 2000. As I have it, the chart leaves blank space for all missing data points.

Any help is greatly appreciated.
 
You can do it by creating dynamic named ranges using a combination of the OFFSET and COUNTA functions.

For example if your workbook is named Book1, worksheet with data is named Sheet1, has 5 columns starting in A1 with the first column being x-axis labels and data starting in row 2 then the following should work as long as you do not have any other data in those columns except what you want to graph and no skipped(blank) data in column A.

create the following named ranges

labels =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
col1 =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
col2 =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
col3 =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
col4 =OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

create a chart and use the named ranges for the data source

x-axis labels =Book1!labels
series 1 values =Book1!col1
series 2 values =Book1!col2
series 3 values =Book1!col3
series 4 values =Book1!col4
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top