How to sort a table with partial array notice

Question

Can’t sort data because of array

Answer 1

Hello Molky58,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

When you encounter the "Can't sort data because of array" error in Excel, it typically means that part of your data is within an array formula, and Excel can't sort a range that includes both array formula cells and regular cells. Here's a simplified way to handle this:

1. Identify Array Formula: Locate the array formula in your data. It's usually indicated by curly braces {} around the formula in the formula bar.
2. Remove or Move Array Formula: You have two options:
   • Remove the array formula if it's no longer needed, or
   • Cut and paste the array formula to another part of the worksheet where it won't interfere with the sort range.
3. Sort the Data: Once the array formula is removed or moved, you should be able to sort your table as usual.
4. Reapply Array Formula if Needed: If you still need the array formula, reapply it after sorting. Ensure it doesn't overlap with the range you wish to sort in the future.

Please be cautious with array formulas as they can perform calculations on multiple cells and might be crucial for your data analysis. Always make sure that modifying them won't affect your data integrity.

I hope this helps,

Best Regards, Ibhadighi

Answer 2

Thank you. Problem solved.