• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to delete Data Tables (the what if analysis kind)

O

Oxidised

Member
So i previously created some "data tables" using the what if analysis tools in my spread sheet. Now I don't need them anymore, so I selected them and hit delete. However, when I recalculate my rather large spreadsheet, it still says updating "data table 1", "data table 2", etc in the information bar in the bottom right of the screen.
It seems it still has those tables hidden in the file somewhere?

Is there a way of of getting rid of them completely as it is slowing down my spreadsheet a lot!

Thanks
 
Hi, Oxidised!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Data Tables are Array Tables and as such you have to simply select the whole table area, not the Row and Column headers and simply press Delete
You Can select the Row and Column headers as well, but you can't select on part of the table
 
hmmm... intesesting,
Attached is a screen shot of the worksheet where I deleted all the tables. I have highlighed the bottom right where you can see it is updating the tables after I press F9 to recalculate.

Sorry, it would be a bit hard to upload the file as it is massive and I can't really cut it down easily... I would have to build a new one...

datatables.jpg
 
Last edited by a moderator:
Hi, Oxidised!
Any external data sources or any connections? Data tab, Connections group.
Regards!
 
Thanks for the replies and ideas. Just checked and there are no hidden sheets and no external data connections!
I have tried just deleting that whole worksheet... and it sends my rest spreadsheet into a #ref hernia throughout the other sheets in the book... I'll have to chase up where the reference is... sigh.

I'll keep chasing it myself and if I'm stuck I'll see if I can upload the file
 
OK, I found the reference, fixed it and now the sheet deletes OK... and the calculating data tables goes away!!
So it seems it holds those data tables hidden in that sheet somehow... very weird!
I'll just have to make sure all data tables are on their very own sheet from now on that can be deleted.
 
The way I picked up on it was the spreadsheet was taking a long time to recalculate (manually using F9). I know there were only data tables in the one worksheet which were no longer needed. It seemed selecting them and deleting them did not entirely remove them. (although I selected the entire sheet, maybe could try selecting each one individually and deleting??)
Then delete the worksheet seemed to remove them completely. What stuffed me up is I had a named variable referencing to that worksheet, so when I initially tried to delete the sheet, the named variable created a "#ref" error that found it's way through the spread sheet. So I reloaded a saved copy, fixed this reference first, then deleted the sheet and all OK now. the spread sheet calculates much quick as it should now.
 
I know this is an old thread, but I found a way to quickly find the what-if data tables that may be spread throughout your workbook. Open up the find dialog (Ctrl+F) and click the Options button. Search within the entire workbook, and look in Formulas. In the Find What box, type =TABLE and click Find All. Note this only works on visible sheets, so if you have any hidden sheets, unhide them all first.
 
You must log in or register to reply here.
Back
Top