Excel: Dynamic Ranges with periodically skipped cells (to create Dynamic Charts)

your English is just fine. There may be a better solution, but if your data are now fixed and not being added anymore, why wouldn't you just use another (possibly hidden) column that would be the source for data of your chart?

You could create that column for instance in two steps. First, create a column with the addresses of the data you want to have in your chart in that order.

For instance, if your data would be in cells C1,C2,... and you would like to skip one row after each 12 rows, you could create column E like this:

=CONCAT("C",ROW()+QUOTIENT(ROW()-1,12))

(put this same formula into all rows of E, just copy&paste)

This creates the names of the cells in column E in a consecutive manner: C1, C2, ..., C12, C14, C15, ..., C25, C27, ...

Then in another column, for instance G, enter the following formula to the first row and then copy it to all other rows of G:

=INDIRECT(E1)

(while copying, it will automatically refer to E2, E3, etc.)

Now your column G contains continuous data from column C, skipping one row after every next 12 rows, and you can use it to create your chart.

And yet another solution using only one extra column (same idea, but more simple), use the INDEX() function. Create a column E containing the following formula in all rows:

=INDEX($C$1:$C$35,ROW()+QUOTIENT(ROW()-1,12))

It will do the same - retrieve the contents of C1 into E1, C2 int E2, etc. C12 into E12, C14 into E13, etc.

And in your particular case, when the data start in C4, instead of C1, you would use:

=INDEX($C$4:$C$35,ROW()-3+QUOTIENT(ROW()-4,12))

If you also like to have your chart automatically updated each time you add a value, one way to do it is like this. You can define a dynamic range:

In the "Formulas" menu select "Name Manager", click "New..." and give it some name, such as "chartrange", and into the Refers to: row enter this formula:

=OFFSET(Sheet1!$E$5,0,0,COUNT(Sheet1!$E$5:$E$1000),1)

This will define a dynamic range from E5 until there are some non-blank values in that column. And then click on your chart with right mouse - Select data... click the Edit... button on the left, and enter the name "chartrange" into the Series values: line.

I have posted an example here: chart with dynamically added data

See also:

• How to Create a Dynamic Chart Range in Excel
• Can I hide columns without excluding the data from a graph?