I just finished watching this video and decided to try to convert 1 Excel worksheet in a workbook to a table, as an experiment for ease of reading (Banded Rows etc).
The conversion has created #SPILL! errors all over the worksheet...
Any ideas where to start to fix the errors greatly appreciated. TIA
Structured tables don’t allow dynamic array functions that spill results into multiple cells. It defies the whole nature and purpose of structured tables. Hence the SPILL error.
Can you give an example of the formula(s) that spill their result? Shouldn’t be too difficult to transform them into formula(s) that don’t spill but will work in tables. Upload your file. That would make it easier.
Structured tables don’t allow dynamic array functions that spill results into multiple cells. It defies the whole nature and purpose of structured tables. Hence the SPILL error.
Can you give an example of the formula(s) that spill their result? Shouldn’t be too difficult to transform them into formula(s) that don’t spill but will work in tables. Upload your file. That would make it easier.
Thanks for your reply. Things have gone from bad to worse, as Version History doesn't work so I can't reverse the Table changes - just keeps saying "There was a problem restoring to this version. Please try again later."
I don't know much about Sharing files - HowTo, Security issues etc - but see if this works...
Flex Instant Saver - daily balance.xlsx
@ukdj Thanks, that worked.
I replaced the formulas in B3 and E8 with ones that create the sequences you had in mind, though without dynamic array functions. Only in the 2025 sheet, by the way!!. That eliminates the SPILL errors everywhere. And I assume that column E will be downloaded or manually entered real amounts and not values based on the weekday number 🙂
I've also replaced direct cell references in formulas (where possible) with structured references, just to demonstrate how you should work with Tables. But, having said that, the column names are too long for my test and include special characters and line feeds. It works, but the formulas become a bit harder to read.
Then, I removed blank rows, the subtotal row, the average row and the summary at the bottom as they should not be part of the table itself.
One total row can be added from the table style options in the table ribbon, but you should not add one manually inside a table.
Have a look if this will work for you. And, please have look at Mynda's blog post on the subject. You will find it helpful to get started with tables.
https://www.myonlinetraininghub.com/excel-tables
I am forever grateful to you, even if I don't fully understand HOW your edits worked!@ukdj Thanks, that worked.
I replaced the formulas in B3 and E8 with ones that create the sequences you had in mind, though without dynamic array functions. Only in the 2025 sheet, by the way!!. That eliminates the SPILL errors everywhere. And I assume that column E will be downloaded or manually entered real amounts and not values based on the weekday number 🙂
I've also replaced direct cell references in formulas (where possible) with structured references, just to demonstrate how you should work with Tables. But, having said that, the column names are too long for my test and include special characters and line feeds. It works, but the formulas become a bit harder to read.
Then, I removed blank rows, the subtotal row, the average row and the summary at the bottom as they should not be part of the table itself.
One total row can be added from the table style options in the table ribbon, but you should not add one manually inside a table.
Have a look if this will work for you. And, please have look at Mynda's blog post on the subject. You will find it helpful to get started with tables.
https://www.myonlinetraininghub.com/excel-tables
For instance, in your Revised version, in the Running Total column where does the reference to 'Table4' in your formula come from? It appears to be exactly the same formula in every Row of that column, so how does it 'know' to add the Daily Amount to the Running Total from the previous day?
Oh, sorry. Should have explained better. "Table4 is just the name Excel gave the table when I formatted the data as a structured table. The formula itself indeed looks the same on every row.
=SUM(Table4[[#Headers],[Daily Amount]]:[@[Daily Amount]])
If you would write the same formula in traditional Excel 'language' you would use in F2:
=SUM($E$1:E2)
This formula will copy itself automatically. In F3 you will see =SUM($E$1:E3) etc.
So, Table4[[#Headers],[Daily Amount]] is a structured absolute reference to the cell in the header row of column "Daily Amount" of the table called "Table4". This code will be generated automatically when you click the cell while writing the formula. Add a colon and then click cell E2. That will be 'translated' to [@[Daily Amount]] where the @ sign indicates the cell on the same row in the "Daily Amount" column.
It may seem a bit alien with all the brackets, but Excel puts them in automatically. If you have not already done so, please check out the blog post I referred to earlier. It's explained in the video.
