What's the difference between WRAPCOLS and WRAPROWS?

WRAPCOLS wraps data vertically into columns (fills down, then moves right), while WRAPROWS wraps data horizontally into rows (fills right, then moves down). Choose WRAPCOLS when you want to organize data into multiple columns and WRAPROWS when organizing into multiple rows.

Can WRAPCOLS work with formulas as the vector parameter?

Yes, absolutely. You can nest functions like FILTER, SORT, or UNIQUE as the vector parameter. For example: =WRAPCOLS(FILTER(A:A,A:A<>""),5) wraps filtered data into columns with 5 items per column.

What happens if my data count doesn't divide evenly by wrap_count?

The last column will have fewer items than specified by wrap_count. If you want to fill empty cells, use the optional pad_with parameter. For example: =WRAPCOLS(A1:A22,5,0) fills empty cells with zeros.

Is WRAPCOLS available in older Excel versions?

No, WRAPCOLS is exclusive to Excel 365 and requires a recent update. It's not available in Excel 2019, 2016, or earlier versions. Users with older versions must use alternative methods like INDEX, SMALL, and ROW combinations.

How does WRAPCOLS handle empty cells in the source data?

WRAPCOLS treats empty cells as data and includes them in the wrapping process. If you want to exclude blanks, wrap the vector with FILTER: =WRAPCOLS(FILTER(A:A,A:A<>""),5) to remove empty cells before wrapping.

WRAPCOLS Function: Complete Guide to Wrapping Data Into Multiple Columns

Intermediate

=WRAPCOLS(vector, wrap_count, [pad_with])

The WRAPCOLS function is a powerful dynamic array formula introduced in Excel 365 that transforms how you organize and restructure data. This function takes a single row or column of data and wraps it into multiple columns based on a specified wrap count, making it invaluable for data reorganization tasks. Whether you're working with product lists, customer data, or any linear dataset that needs to be reformatted into a columnar structure, WRAPCOLS automates what would otherwise require manual rearrangement or complex helper columns. Understanding WRAPCOLS is essential for modern Excel users working with large datasets that need dynamic restructuring. Unlike traditional methods that rely on INDEX, MATCH, or helper columns, WRAPCOLS provides a clean, formula-based solution that automatically adjusts when your source data changes. This intermediate-level function represents the evolution of Excel's data manipulation capabilities, offering efficiency gains particularly valuable in data analysis, reporting, and dashboard creation scenarios.

Syntax & Parameters

The WRAPCOLS syntax follows this structure: =WRAPCOLS(vector, wrap_count, [pad_with]). The first parameter, vector (required), accepts either a row or column of data that you want to reorganize. This can be a cell range, an array, or the result of another formula. The second parameter, wrap_count (required), specifies how many items should appear in each column of the output. For example, if your vector contains 12 items and wrap_count is 3, WRAPCOLS will create 4 columns with 3 rows each. The third parameter, pad_with (optional), allows you to specify a value that fills empty cells when the total number of items doesn't divide evenly by the wrap count. If omitted, empty cells remain blank. A critical practical tip: ensure your wrap_count is a positive integer; using zero or negative values will trigger an error. Additionally, remember that WRAPCOLS works horizontally from the source data, filling columns from top to bottom, then moving to the next column—understanding this directional behavior prevents formula logic errors.

vector

Row or column to wrap

wrap_count

Number of items per column

pad_with

Padding value

Optional

Practical Examples

Reorganizing Product List for Display

=WRAPCOLS(A2:A21,5)

This formula takes the product list from A2:A21 and wraps it into columns with 5 products per column. The result automatically creates 4 columns (20 items ÷ 5 per column) for catalog layout.

Creating Balanced Team Assignment Grid

=WRAPCOLS(EmployeeList,5)

The formula wraps the employee list with 5 employees per column, automatically creating 3 columns representing 3 balanced teams. This approach ensures even distribution without manual sorting.

Formatting Survey Responses with Padding

=WRAPCOLS(B2:B23,4,"N/A")

This formula wraps 22 survey responses into columns with 4 responses per column. Since 22 doesn't divide evenly by 4, the pad_with parameter fills the final empty cell with 'N/A', creating a complete 6x4 grid.

Key Takeaways

WRAPCOLS is an Excel 365 dynamic array function that transforms linear data into multi-column layouts based on specified wrap counts.
The formula requires a vector (data source) and wrap_count (items per column), with an optional pad_with parameter for filling empty cells.
WRAPCOLS fills data vertically (top to bottom) then moves to the next column, differing from WRAPROWS which fills horizontally.
Combining WRAPCOLS with FILTER, SORT, and UNIQUE creates powerful data preprocessing workflows without helper columns.
WRAPCOLS eliminates manual data reorganization, reduces formula complexity, and automatically updates when source data changes.

Pro Tips

Combine WRAPCOLS with conditional formatting to create visual dashboards. Wrap data into columns, then apply color scales or data bars to highlight patterns and trends.

Impact : Transforms raw data into visually compelling reports that stakeholders can quickly interpret without additional analysis.

Use WRAPCOLS as a preprocessing step before SUMPRODUCT or other aggregate functions. Wrap data into columns, reference the specific column needed, and calculate aggregates.

Impact : Simplifies complex multi-step calculations by organizing data into logical column groups that are easier to reference and manipulate.

Nest WRAPCOLS inside IFERROR to handle edge cases gracefully. For example: =IFERROR(WRAPCOLS(vector,wrap_count),"Data unavailable") prevents formula errors from disrupting dashboards.

Impact : Improves spreadsheet reliability and user experience by providing meaningful error messages instead of cryptic error codes.

Reference WRAPCOLS output indirectly using INDEX to extract specific wrapped columns. This allows you to use wrapped data in downstream calculations without displaying the entire array.

Impact : Enables sophisticated multi-step workflows where wrapped data feeds into subsequent analyses without cluttering the spreadsheet with intermediate results.

Useful Combinations

WRAPCOLS with FILTER for conditional wrapping

=WRAPCOLS(FILTER(A2:A100,B2:B100="Active"),6)

Combines FILTER to select only 'Active' records from a dataset, then wraps them into 6-column format. This eliminates the need for helper columns to filter data before restructuring.

WRAPCOLS with SORT for organized output

=WRAPCOLS(SORT(A2:A50),4)

Sorts the source data alphabetically before wrapping into 4 columns. Ensures the wrapped output displays organized, sorted data without manual pre-sorting.

WRAPCOLS with UNIQUE for deduplication

=WRAPCOLS(UNIQUE(A2:A200),5,"EMPTY")

Removes duplicate entries from a list using UNIQUE, then wraps the unique values into 5-column format with 'EMPTY' padding. Ideal for creating clean, deduplicated data layouts.

Common Errors

#VALUE!

Cause: The wrap_count parameter contains a non-numeric value, is zero, or is negative. For example: =WRAPCOLS(A1:A10,"five") or =WRAPCOLS(A1:A10,-2)

Solution: Ensure wrap_count is a positive integer. Use =WRAPCOLS(A1:A10,5) instead. If referencing a cell, verify it contains a valid positive number.

#REF!

Cause: The vector parameter references deleted cells or an invalid range. This commonly occurs when source data is deleted after the formula is created.

Solution: Verify that the range in the vector parameter still exists and contains data. Update the formula to reference the correct range if data has been moved.

#SPILL!

Cause: The output range is blocked by existing data or formatting. WRAPCOLS creates a dynamic array that needs contiguous empty space to display results.

Solution: Clear the cells where WRAPCOLS results should appear. Ensure the destination area has sufficient empty columns and rows for the wrapped output.

Troubleshooting Checklist

1.Verify that wrap_count is a positive integer greater than zero. Check for text values, negative numbers, or zero values.
2.Confirm the vector parameter references valid cells containing data. Check for deleted ranges or incorrect cell references.
3.Ensure the output area has sufficient empty space. Clear any blocking data or formatting in the columns where results should appear.
4.Validate that you're using Excel 365 with the latest updates. WRAPCOLS requires current Excel versions and won't work in 2019 or earlier.
5.Check if pad_with parameter is necessary. If data doesn't divide evenly, add padding to create consistent output dimensions.
6.Test the formula with a small sample dataset first. Verify behavior before applying to large datasets to catch issues early.

Edge Cases

Vector contains single cell (e.g., =WRAPCOLS(A1,3))

Behavior: Returns the single value in a 1x1 array. Wrap_count is ignored when only one item exists.

Solution: This works as intended but produces minimal output. Ensure your vector contains multiple items for meaningful wrapping.

Common when testing formulas; verify vector range includes all intended data.

Wrap_count equals vector length (e.g., 10 items with wrap_count=10)

Behavior: Creates a single column with all items stacked vertically. No horizontal wrapping occurs.

Solution: Use a smaller wrap_count value to create multiple columns. For example, use wrap_count=5 to create 2 columns from 10 items.

This represents the maximum wrap scenario; useful when you want to preserve original order without restructuring.

Pad_with parameter contains formula result (e.g., =WRAPCOLS(A1:A22,5,TODAY()))

Behavior: Fills empty cells with the formula result (current date in this example). Padding updates dynamically with formula changes.

Solution: Use static values for consistent padding or formulas if dynamic updates are desired. Be aware that formula-based padding recalculates.

Advanced use case; typically static text or numbers are preferred for clarity and performance.

Limitations

•WRAPCOLS is exclusive to Excel 365 and unavailable in Excel 2019, 2016, or earlier versions, limiting cross-version compatibility.
•The function cannot customize fill direction (always fills vertically then horizontally). WRAPROWS must be used for horizontal-first filling.
•WRAPCOLS treats all cells as data, including blanks. Filtering must be done separately using FILTER function if you want to exclude empty cells.
•Performance may degrade with extremely large datasets (100,000+ rows) as dynamic array recalculation increases. Consider data segmentation for massive datasets.

Alternatives

INDEX with SMALL and ROW combination

Works in all Excel versions including 2019 and earlier. Provides precise control over data arrangement.

When: Use when WRAPCOLS is unavailable and you need to maintain compatibility with older Excel versions. Requires array formulas and helper columns.

TRANSPOSE with manual column arrangement

Simple to understand and implement for basic data restructuring. Requires no advanced formula knowledge.

When: Use for one-time data reorganization tasks where you don't need dynamic updates. Less efficient for large datasets.

Power Query or Get & Transform

Handles complex data transformations with visual interface. Creates reusable query steps for recurring tasks.

When: Use for enterprise-level data processing, data cleaning, and complex restructuring operations. Better for large datasets and automated workflows.

Compatibility

✓ Excel

Since Excel 365 (2020 or later)

=WRAPCOLS(vector, wrap_count, [pad_with])

✗Google Sheets

Not available

✗LibreOffice

Not available

Frequently Asked Questions

Master dynamic array formulas and advanced Excel techniques with ElyxAI's comprehensive formula guides. Explore how WRAPCOLS integrates with other modern Excel functions to transform your data workflows.

HSTACK

WRAPROWS