Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.

share|improve this question
Set up a helper column and fill the first 9 cells with a no and the 10th with a yes. Select the range of these 10 cells and use Autofill Down to repeat the values over all rows. At last use an AutoFilter to filter for all lines with a no and delet them. (Not worth an answer) – nixda Nov 26 '13 at 17:08
Can you make this the answer? – Val Nov 26 '13 at 17:49
up vote 2 down vote accepted

Placed in cell A1, the formula


will repeat with values 0-9 when you fill it down. You can then use filtering to delete the rows that you don't want. Note that the result of the formula will then change if you delete (rather than clear) the rows. (Although filtering doesn't update until you ask it to.)



will only return TRUE every tenth row.

share|improve this answer

Use the analysis toolkits sampling function. It asks you for the input data range, the period you wish to decimate by and the an output cell or range (it does not really care it will fill whatever cells it needs starting at the first one you select)

share|improve this answer
It would improve your answer if you explained how to install this addin – DavidPostill Oct 9 '14 at 7:18

You can use the Sampling function in the Analysis Toolpak, which you will have to install. Unfortunately, MS makes you work to find it if you are using anything later than Office/Excel 2003.

  1. Go to File menu and click on Options. On Excel Options window, click on Add-Ins. At the bottom, select Manage: Excel Add-ins and click Go.

  2. Check the Analysis Tookpack and click OK. Now select the Data menu to see the associated ribbon. Over on the right, select Data Analysis.

  3. Scroll down to Sampling and click OK. You will see a window that allows you to specify the sampling period, cell ranges, etc. To sample every 10th cell, select Periodic and 10 for the period.

In Excel 2003, the above is accomplished in only a couple clicks, indicating how grossly inefficient the ribbon is.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.