KEMBAR78
Option #2: Using A Dynamic Named Range | PDF | Teaching Methods & Materials
Scribd
Upload
26 views1 page

Option #2: Using A Dynamic Named Range

The document discusses two options for creating dynamic named ranges in Excel charts. The first option uses OFFSET to define a range that adjusts as rows of data are added or removed. The second option creates a similar dynamic range but fixes the number of rows and columns to reference a set number of cells. Both options allow the chart to automatically update as the source data changes, making the chart dynamic.

Uploaded by

Efren Gustavo Chaves Villalobos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views1 page

Option #2: Using A Dynamic Named Range

The document discusses two options for creating dynamic named ranges in Excel charts. The first option uses OFFSET to define a range that adjusts as rows of data are added or removed. The second option creates a similar dynamic range but fixes the number of rows and columns to reference a set number of cells. Both options allow the chart to automatically update as the source data changes, making the chart dynamic.

Uploaded by

Efren Gustavo Chaves Villalobos
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Animation, Interaction and Dynamic Excel Charts The Excel Charts Blog Page 1 of 1

Option #2: Using a Dynamic Named Range


The second option is to create a dynamic named range. Create a named range and, instead of entering a fixed
range, enter this formula:

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$A$13,Sheet1!$B$1:$E$1,0),10,1)

As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a
range of 101 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA
(A:A)-1 to count the number of regions, excluding header and dont forget to move the year to a different
cell).

When you verify your range this is what you get:

You just need to use this range in your chart (when entering the range you must add the workbook name:
=Book1!SourceData). Youll also need a range for the category axis labels:

=OFFSET(Sheet1!$A$1,1,0,10,1)

You can download the spreadsheet here.

http://www.excelcharts.com/blog/animation-interaction-and-dynamic-excel-charts/ 10/2/2012

You might also like