+ Reply to Thread
Results 1 to 3 of 3

Excel 2016 crashes on modifying pivot based on query

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    UK
    MS-Off Ver
    Pro 2010 & 2016
    Posts
    6

    Excel 2016 crashes on modifying pivot based on query

    Hi All,

    I'm running Excel 2016 & have a reasonably large document. I have 4 data tables on separate sheets. Each table contains 79 columns & anything up to around 1500 rows. They are all the same format; I just needed to split the data since there is quite a bit of calculation going on within the table which can be too slow when we get into the 1000s of rows. File size is around 7MB.

    To analyse the data, I generate a query from each table & then a further append query to join them all together. I then use pivot tables to analyse the data. The queries are saved as connection only.

    The problem is that Excel crashes. I'm generally OK with one pivot. A soon as I add a second, Excel can crash at any time. It can be quite predictable. Just as an example, I create the pivot & get the data, set the filter & then set a sort. The sort seems to work & then Excel crashes. If I restart & go through exactly the same procedure, if it crashed last time when I set the sort, it will crash at exactly the same time.

    Any ideas where to start? In times past, I generated a pivot from each table & then joined those. But, to make it dynamic is complex & I'd like to simplify.

    Tnx Dave.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Excel 2016 crashes on modifying pivot based on query

    1. Update your profile with your Excel version, please.

    2. How much of your calculation needs to happen on the worksheet, and how much could be done in Power Query?

    3. Do you really need 79 columns? I bet you don't. I bet you can eliminate lots of them, by modelling your data differently.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-07-2018
    Location
    UK
    MS-Off Ver
    Pro 2010 & 2016
    Posts
    6

    Re: Excel 2016 crashes on modifying pivot based on query

    Thanks Olly,

    1. Would do if I knew where to. Profile only shows which MS Office version & that's already filled in.
    2. Calculation on the sheet is not analysis type calculation but more like automation: data validation, conditional formatting etc.
    3. Unfortunately, I really do need 79 columns.

    All of that said, I'd still like to know if others are experiencing similar Excel crash problems when attempting to use Get Data from tables in the same document. I'm using this for analysis. I've also looked at doing the same from external tables & that crashes too - different documents but similar amounts of data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Persistent crashes in Excel 2016
    By cliftonvilla in forum Excel General
    Replies: 2
    Last Post: 02-19-2019, 10:03 AM
  2. Macro built in Excel 2011 crashes workbook in Excel 2016
    By NCRB_10 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2018, 12:31 AM
  3. [SOLVED] Unalterable Excel 2016 spreadsheet always crashes
    By Zarathrustra in forum Excel General
    Replies: 7
    Last Post: 02-13-2018, 07:13 AM
  4. excel 2016 stops running(crashes) after running VBA that was working fine on previous vers
    By lauriejerome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2017, 01:51 PM
  5. Update Query Url: Excel 2016 Query
    By igoodable in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2017, 12:45 PM
  6. [SOLVED] Using ADO/DAO in Excel 2013 to query an .accdb database hard crashes Excel
    By 1aaaaaaaaaaaaaaa in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-25-2015, 07:15 PM
  7. Excel 2004 crashes on update/insert/load query
    By jolly in forum Excel General
    Replies: 0
    Last Post: 11-17-2005, 02:10 AM

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