digestVidSign in
summary

excelisfun - Excel Magic Trick 1143: Extract Content From Non-Empty Cells: Filter or Array Formula?

Thumbnail of the video “excelisfun - Excel Magic Trick 1143: Extract Content From Non-Empty Cells: Filter or Array Formula?”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: For a one-time extraction of non-empty cells from a list with blanks, use AutoFilter; for an automatic, self-updating result, use an array formula built with INDEX, SMALL (or AGGREGATE in Excel 2010 or later), and ROW/ROWS. The video notes that the AutoFilter method keeps the original list intact while showing only the populated cells, and that the array formula updates instantly when data changes.

Key Points

  • The filter method is a manual one-time fix: apply AutoFilter, check Blanks, copy the visible non-empty cells, paste them below the data, then turn the filter off.
  • The formula method updates automatically when the data changes, but it is more complex.
  • In Excel 2010 or later, AGGREGATE (function 15, SMALL with the error-ignoring option 6) replaces SMALL and avoids the need for Control+Shift+Enter.
  • In Excel 2007 or earlier, the same logic uses SMALL inside an IF, which requires entering the formula as an array formula with Control+Shift+Enter.
  • The formula builds a list of relative positions of non-empty cells, then uses INDEX to look up each item, with an IF wrapper to hide the #N/A errors.

Details Worth Noting

  • Concrete instructions:
    • One-time method: Highlight the data, press Control+Shift+L to add an AutoFilter, click the filter and check Blanks, press Control+C to copy only the visible cells, click below the data set and press Control+V, then press Control+Shift+L again to remove the filter.
    • Excel 2010 or later (AGGREGATE) in the first cell of the results column: =IF(ROWS(D$2:D2)>COUNTA(F4:F10), "", INDEX(F4:F10, AGGREGATE(15, 6, (ROW(F4:F10)-ROW(F4)+1)/(F4:F10<>""), ROWS(D$2:D2)))). Enter with Control+Enter (no Control+Shift+Enter needed), then copy down. Note: the video's exact range references are F4:F10 for the list; adjust to your own range.
    • Excel 2007 or earlier: Replace AGGREGATE with an IF over the array, e.g. SMALL(IF(F4:F10<>"", ROW(F4:F10)-ROW(F4)+1), ROWS(D$2:D2)), entered with Control+Shift+Enter (curly brackets appear), then copy down.
    • Use the IF wrapper that returns an empty string when the row count exceeds the number of non-empty items; the speaker notes this also improves performance on large data sets, since the full array formula is skipped once the row count exceeds the item count.
  • The AGGREGATE functions 1 to 13 do not handle array calculations; only functions 14 to 19 do.
  • Using ROWS(D$2:D2) with a locked start reference lets the counter increment (1, 2, 3, ...) as the formula is copied down.
  • The formula can handle numbers or text, and the speaker tested that adding an entry updates the extracted list instantly.

Bottom Line

Use the AutoFilter approach for a quick, static copy, and the AGGREGATE or SMALL-based array formula when the extracted list must refresh automatically.