Decimating rows in spreadsheet

14,469

Solution 1

Placed in cell A1, the formula

=MOD(ROW(A1),10)

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.)

Alternatively,

=MOD(ROW(A1),10)=0

will only return TRUE every tenth row.

Solution 2

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:
14,469

Related videos on Youtube

Val
Author by

Val

Updated on September 18, 2022

Comments

  • Val
    Val about 1 year

    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.

    • Val
      Val almost 10 years
      Can you make this the answer?
  • DavidPostill
    DavidPostill about 9 years
    It would improve your answer if you explained how to install this addin