This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(156.8, 38%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EIH%3C/text%3E%3C/svg%3E)
I have a column of 1000 data points. I want to copy every tenth cell contents into another column, so this column has a compact array of only one hundred data points. Cheers, Ian H
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Thanks Ashish, That works very well. I now have three good solutions. Cheers, Ian
Hi,
Try this formula
=INDEX(A1:A12000,SEQUENCE(100,,,10),1)
Hope this helps.
To extract every 10th row from your 1000-point column into a compact 100-row array, use this formula in the first cell of your destination column (Excel 365 will auto-spill the results):
Recommended (Excel 365):
=CHOOSEROWS(A:A, SEQUENCE(100, , 1, 10))
Alternative (compatible with all versions):
=INDEX(A:A, (ROW(1:1)-1)*10 + 1)
Drag or let it spill down as needed. Both pull rows 1, 11, 21, etc. If your data starts at a different row, adjust the last number (+1) accordingly.
My answer comes without any guarantee or warranty! I
hope this helps you.
Both of those solutions work brilliantly, thank you. The first one (Chooserows . . ) even fills itself automatically as soon as I entered it into the first cell. I had too much data of a slow-moving variable with far more precision than needed. Much appreciated. Cheers, Ian
If there is currently no data in the new column you want to add the formula in every 10th cell, enter the first formula and second formula in the column, then select the group of cells that contain the first formula, the blank cells and the second formula, at the bottom of that selection is a small green box, select that box and drag down all the way to use Autofill to automatically fill in the appropriate formulas into every 10th cell.
I don't want a "formula in every 10th cell". I want each consecutive cell down the new column to have the value of every 10th cell in the source column. i.e. If I have 100 rows of data in column A I want an array of only 10 rows in column B, sampling every 10th value from column A. The other two solutions worked perfectly. Cheers, Ian