NxFlatten - collapse a data table into a flat 3-columns form.

Flattens (or collapses) a 2-Dimensional table or matrix into a flat 3-columns (X,Y,Z) representation.


NxFlatten(Table, X-Levels,Y-Levels,KeepNA)
is a 2-dimensional array on a worksheet for the dependent variable (Z), such that rows define different levels/values for X (1st variable), and Columns defines different levels/values of the Y (2nd variable).
is a one dimensional array of cells (e.g. rows or columns) for corresponding levels/values of X (1st variable) in the data table.
is a one dimensional array of cells (e.g. rows or columns) for corresponding levels/values of Y (2nd variable) in the data table.
is a flag for leaving (or purging) observations with missing values (e.g. blank, #N/A, #VALUE!, #NUM!, empty cell) found in the input data set. If missing or omitted, keepNA is assumed False.


  1. The NxFlatten function must be entered as an array formula in a range that has 3 columns and rows equal to original table rows times its columns (minus any missing data-point if KeepNA is False).
  2. Use NxFlatten to transform tabular form representation of a three-dimensional data set into a flat 3-column (X,Y,Z) format.
  3. The observations in X,Y and Z can have any order, and are not assumed to be sorted.
  4. The data "Table" array may have missing values (#N/A). NxFlatten keep/remove those entries based on "KeepNA" flag setting, only if corresponding X-Level and Y-Level values are given (i.e. not missing).
  5. The X-Levels and Y-Levels may have missing values (#N/A), In this case, NxFlatten will remove those entries.
  6. The X-Levels and Y-Levels values can have any order, no sorting is required.
  7. The NxFlatten() function is available starting with version 1.66 PARSON.


Files Examples


  • John Walkenbach (2002). Excel charts. Wiley. ISBN 978-0764517648.


Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful