+ Reply to Thread
Results 1 to 4 of 4

Can't delete a table without deleting the data?

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Can't delete a table without deleting the data?

    I have Work book that is quite big and I was silly and put in tables, but this ultimately slows down excel so much so that I have to wait 20-30secs while it performs a single task (PC is i7 with 4g of ram).

    B/c I have been working on this Workbook for a long time it has multible sheets all interconnected and I can't go through all the sheets and redo the equations , as that wouyld just take too much time. Is there a way of leaving the data the way it is and deleting the table?

    This is an example below of the type of table I am talking about.
    Attached Files Attached Files
    Last edited by JapanDave; 07-08-2009 at 03:42 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Can't delete a table without deleting the data?

    You should find if you activate the Table and go to Design tab there is an option to "Convert to Range" -- formulae presently using Table[Field] syntax should update to traditional syntax reflecting the conversion from Table to Range.

    On an aside, are you sure the slowdown is caused by the Tables ... I'd save a Table version first then convert and save as different version - if performance does not improve you can revert back to Table version and then investigate further (ie look for Volatiles / Array bottlenecks)

  3. #3
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Can't delete a table without deleting the data?

    Quote Originally Posted by DonkeyOte View Post
    You should find if you activate the Table and go to Design tab there is an option to "Convert to Range" -- formulae presently using Table[Field] syntax should update to traditional syntax reflecting the conversion from Table to Range.

    On an aside, are you sure the slowdown is caused by the Tables ... I'd save a Table version first then convert and save as different version - if performance does not improve you can revert back to Table version and then investigate further (ie look for Volatiles / Array bottlenecks)
    Thanks, I coverted them back but it is only very slightly faster. I am thinking that I have too much data converging onto one sheet???

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Can't delete a table without deleting the data?

    Without seeing the thing in full swing it's hard to give advice but what I would say is generally avoid using Arrays in large quantities (includes Sumproduct), avoid using Volatile functions in large quantities (eg INDIRECT,OFFSET etc...)

    For more detailed info. on optimisation I would refer to Charles Williams' artcile on msdn:

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    and also his own site:

    http://www.decisionmodels.com/calcsecrets.htm

    but traditionally Volatiles & Arrays are the key contributors to poor performance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1