Excel crashes in Table Properties

Anonymous
2025-01-06T00:22:10+00:00

I use PowerQuery quite a bit together with Excel (Microsoft 365).

Excel has started crashing whenever I access Table Design / Properties for a table created by PowerQuery.

This is the scenario:

    • Put cursor in the table
    • Click Table Design menu
    • click "Properties" (which is present in the "External Data Table" section of the ribbon). [Note: Properties will be greyed out for an ordinary table - AFAIK, it's only accessible for a Table created by PQ.]
    • make some change in the window that pops up

In all existing workbooks, and even a brand new one I create, following Step 3 (or sometimes Step 4) Excel crashes - and it usually restarts. So, the properties function under Table Design is inaccessible!

What could be causing this? How to fix it?

Microsoft 365 and Office | Install, redeem, activate | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

52 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2025-01-21T20:47:23+00:00

    I can confirm that this solution works!! Managing the query by right-clicking Queries & Connections and selecting Properties did the trick! I've been struggling with this for over a week now! Kudos Tristan, you are a lifesaver!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-01-23T12:25:28+00:00

    We tested the above, and it seems like it worked. It looks like MS have introduced a bug, as we starter experiencing this from Dec 2024

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-01-27T14:29:43+00:00

    I have the same scenario as Tristan: Microsoft Query pulling from SQL dB.

    However, the property that I am trying to change is "preserve column sort/filter/layout" which is not available when accessed via Data-Queries&Connections unfortunately.

    Even cancelling out of the Data-Properties box causes a crash. Very frustrating

    For me, this type of operation was common and no problem. Yes, I agree Microsoft has introduced a bug - it would be great if they would fix it.

    Has anyone tried changing the query properties through VBA?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2025-01-29T17:37:55+00:00

    Has anyone tried changing the query properties through VBA?

    Yes, it works. Set your parameters

    Sub QueryTable_Properties() 

    *With Selection.ListObject.QueryTable* 

    *.RowNumbers = False* 

    *.FillAdjacentFormulas = False* 

    *.PreserveFormatting = True* 

    *.RefreshOnFileOpen = False* 

    *.BackgroundQuery = True* 

    *.RefreshStyle = xlInsertDeleteCells* 

    *.SavePassword = False* 

    *.SaveData = True* 

    *.AdjustColumnWidth = True* 

    *.RefreshPeriod = 0* 

    *.PreserveColumnInfo = True* 

*End With*

    End Sub

    Was this answer helpful?

    7 people found this answer helpful.
    0 comments
  5. Anonymous
    2025-01-29T18:15:58+00:00

    Thanks okshef 👍😊

    It does indeed work: testing was a pain as just looking at the properties crashes the spreadsheet.

    Having this workaround is a lifesaver ... until the Excel team at Microsoft sort the bug out.

    I tried and failed - with my VBA (lack of) knowledge the bit that I was missing was the right object

    Selection.ListObject

    Thanks again

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments