digestVidSign in
summary

excelisfun - Excel Magic Trick 1373: Formula to Extract Every Nth Value & Conditionally Format Every Nth Value

Thumbnail of the video “excelisfun - Excel Magic Trick 1373: Formula to Extract Every Nth Value & Conditionally Format Every Nth Value”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology Education
Tags: science, technology, education
Views: 2 · Last viewed: 2026-10-16 00:54 UTC

Short Summary

Main recommendation: Use an INDEX-based formula with a ROWS-generated number increment (multiplied by N, such as 5) to extract every Nth value, and use a MOD-based TRUE/FALSE formula in conditional formatting to highlight every Nth row. The video demonstrates building both formulas in an Excel dataset and changing N (for example, from 5 to 6, 3, 7) so both the extracted list and the highlighting update instantly.

Key Points

  • Count the records to extract by dividing the row count by N and rounding down with ROUNDDOWN or INT (for example, 18.6 becomes 18).
  • Conditional formatting cannot use a built-in "every Nth row" option here, so a custom formula rule is used instead.
  • The MOD function (remainder after division) identifies every Nth row: the number increment modulo N equals zero flags a match.
  • The extraction formula uses INDEX over the full column, with a row-position argument of number increment times N.
  • The author prefers to avoid IFERROR, using an IF logic test against the record count instead, noting IFERROR would work fine here but he favors building efficient formulas as a habit.

Details Worth Noting

  • Concrete instructions:
    • Count records: in a cell, use the ROWS of the data range (for example, from row 2 to the last row), divide by 5, and wrap in ROUNDDOWN(…, 0). Alternatively, if a column lists the record count, use MAX of that column divided by 5 and wrap in INT(…).
    • Build a number increment: in a helper column, use ROWS(H$2:H2) (anchoring the start with F4 locking) multiplied by 5, then copy down. This yields 5, 10, 15, 20, and so on.
    • Conditional formatting: highlight the data range, go to Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format," and enter a MOD-based formula such as MOD(ROWS($J$2:J2), 5) = 0 (with the number increment and divisor locked using F4). Select Format, pick a fill color (yellow in the example), click OK.
    • Extraction formula: in the first output cell, enter an IF whose logical test is "number increment greater than the record count" (locked with F4); if TRUE, return double quotes (""), otherwise return INDEX(entire data column, number increment). Copy down with Control+Enter.
    • Use the Excel relative-position logic: INDEX's row-number argument is the relative position within the array, not the worksheet row.
  • The formulas are built in helper cells first, then copied into the conditional formatting dialog, so pattern TRUE/FALSE results can be checked before applying the rule.
  • Changing the N value in one cell updates both the highlighting and the extracted list automatically.
  • The author notes that ROW can give wrong results after inserting rows, while ROWS remains reliable; the topic is also covered in the author's book.

Bottom Line

A reusable Excel pattern (ROWS-based increment, MOD for conditional formatting, INDEX with an IF guard for extraction) lets you pull or highlight every Nth record and change N on the fly.