Will not create data table because excel says there is an array in the proposed data table area.

Deleted
Not applicable

Hi. I have a spreadsheet with an area that I want to convert to a data table. Excel comes back and says 

"multi-cell array formulas are not allowed in tables." So I cannot create the data table. I go back and look through the table area every way I know how but I cannot find an array of any type in the range where I want to create the data table. I have used online help but cannot find what the problem is exactly.

Any help much appreciated. Reuben Vabner

9 Replies

You'll need to look for an array formula.  You can tell that a formula is an array formula when it is enclosed in { }

Thank you very much. I did search for the first bracket "{" using FIND but it said there are none. I will try again but it may again tell me no such bracket in table area. Thanks for your help.

Reuben

Tried again.Cannot find any brackets. Also, if you try to "sort and filter" (custom), it will not do that either because there is supposedly an array in the range I want to sort and filter.

 

Thank you, Reuben

Hi Reuben.

 

I reproduced this issue in my spreadsheet and got the below result.

I've tried to convert the range B2:B4 to a table, but since the range has multi-cell array formula which is TRANSPOSE, I can't do that!

Multi-cell array formulas aren't allowed in tables.png

 

You should have a formula like this in the range you're trying to convert to a table!

If not, please attach the spreadsheet that has this issue in order to figure out what is the problem.

 

Regards,

Haytham

Find will not work.  You will have to select the cell, and you can see the brackets in the formula bar.  You can click Show Formulas on the Formulas tab to make it easier to see which cells have formulas in them, but you will still need to click on each cell to see the brackets.

Thank you Haytham! I don't have any formula such as "transpose".  I have copied page from spreadsheet here. I want to create data table in the cell range B3:AH38

Cells U31, U32, and U33 have array formulas that are different from the other formulas in the column.  

You are 100% right!! It is in a hidden column (or a column somebody narrowed so as to unreadable and effectively hidden) and many people have fooled around with this spreadsheet. But you are absolutely right! Thanks very much!

 

Reuben Vabner

@Bob Orrell hi bob, I got the same issue. And the formula is enclosed in {} . Is there any way to create a table with this?