In conducting research for computer science topics, I often find myself with huge CSV files that I open in Excel and try to make graphs. Often, I have way too many data points to graph. Sometimes, I do fancy things in my code to produce only a certain number of data points that are averages or maximums over a set, but other times I just want to pare down the points in Excel to make the graph.
The following shows how to use Excel formulas to get every nth value from a column.
- Data is in columns B and C, ranging from row 4 to row 10004
- Goal: put every 500th pair of values in Columns E and F, starting in row 4
- Put the value 500 in a cell to reference later, (example: C2).
- In E4, put the following formula: “=OFFSET(B$4,(ROW()-4)*$C$2,0)”
- Select E4 and use the fill box and drag to the right to fill F4
- Select both E4 and F4 and use the fill box to drag down as far as you need
That’s it, you now have every 500th data point. Simply change the value in C2 to 100, to get every 100th instead, etc.