# Plotting a graph with every nth cell

#### davyj93

##### New Member
Hi guys,

I am trying to plot a graph but I have a lot of data. I have 213,792 rows of data but Excel only lets you plot 32,000 data points on a graph.

How do I plot every 7th row of data instead?

Below is a small look at what my columns look like (and the rows would be filled in of course):

 Time (mins) Engine Power (kW)

<tbody>
</tbody>

Thanks,
David

#### JustynaMK

##### Active Member
Hi David,

I am afraid that the only solution would be to create a separate table with cell references to the original table, i.e.
Time | Engine Power
=A2 =B2
=A9 =B9
=A16 =B16
etc.
...and then creating a chart linked to the new table.

#### gaz_chops

##### Well-known Member
You could use below formula to give you every 7th row in the new table.

for the 1st row just use =A1
then use & drag down
=INDEX(\$A\$2:\$A\$60,ROW(A1)*7)

Someone may have a better solution.

#### davyj93

##### New Member
I tried the formula.

It gave me the correct starting value but then gave the 14th value. After this it started giving every 7th value.

Is there a reason why the 2nd value (on the newly created column) came up as the 14th original value rather than the 7th?

#### gaz_chops

##### Well-known Member
Post the formula you are using here.

##### MrExcel MVP
do you have any hidden rows, perhaps? As is stands, the formula should do exactly what it says on the tin...

#### davyj93

##### New Member
Here is the table again with added columns:

 nth cell values: Time (mins) Power (kW) Time (mins) Power (kW) 1 12 1 12 2 12 22 40 3 15 29 50 4 17 36 57 5 17 #REF! #REF! 6 18 #REF! #REF! 7 19 #REF! #REF! 8 20 #REF! #REF! 9 22 #REF! #REF! 10 25 #REF! #REF! 11 25 #REF! #REF! 12 25 #REF! #REF! 13 26 #REF! #REF! 14 27 #REF! #REF! 15 28 #REF! #REF! 16 30 #REF! #REF! 17 33 #REF! #REF! 18 35 #REF! #REF! 19 37 #REF! #REF! 20 38 #REF! #REF! 21 38 #REF! #REF! 22 40 #REF! #REF! 23 44 #REF! #REF! 24 44 #REF! #REF! 25 44 #REF! #REF! 26 47 #REF! #REF! 27 48 #REF! #REF! 28 49 #REF! #REF! 29 50 #REF! #REF! 30 52 #REF! #REF! 31 53 #REF! #REF! 32 53 #REF! #REF! 33 54 #REF! #REF! 34 55 #REF! #REF! 35 55 #REF! #REF! 36 57 #REF! #REF!

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

In cell D3, I used
=A3

Similarly, in cell E3, I used
=B3

Then for cell D4, I used
=INDEX(\$A\$4:\$A\$38,ROW(A3)*7)

And for cell E4, I used
=INDEX(\$B\$4:\$B\$38,ROW(B3)*7)

Then I dragged down the formulas from E4 and D4.

#### gaz_chops

##### Well-known Member
What row number is the 1st value in, Time Mins = 1?

#### gaz_chops

##### Well-known Member
Ah the formula is incorrect

=INDEX(\$A\$4:\$A\$38,ROW(A1)*7)

The row(A1)*7 = 7
as you drag it down then
row(A2)*7 = 14
row(A3)*7 = 21 etc

#### davyj93

##### New Member
The 1st value (Time = 1) is in row 3. This is cell A3.

That new formula does work. It just seems a bit strange to me because cell A1 is a blank cell.

1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...