Excel crashes in Table Properties

Question

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:

1. • Put cursor in the table
2. • Click Table Design menu
3. • 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.]
4. • 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?

Answer 1

I have the same problem with Excel crashing. I am using Microsoft Query to connect to a SQL database. I did find another solution that worked for me: instead of selecting a cell inside my data table, then selecting 'Table Design' and 'Properties' (which crashes Excel every time), instead go straight to the main 'Data' ribbon tab, then 'Queries & Connections' which opens up the side menu. Then from there you can select 'Connections', right click and open up the familiar Properties window! Bit long-winded but it works! There clearly is a bug that we have to hope Microsoft will fix soon! Hope someone else finds this useful.

Answer 2

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

Answer 3

I started noticing this issue about a couple of months ago, and saw this thread about a month ago. I thought it would have been fixed by now. Can Microsoft get their act together and just fix this thing? It's actually pathetic.

Answer 4

Hi, Hannah.Z,

I'm sorry, but this is indeed an Excel problem. The 'Properties [Data Range Properties]' command/dialog is completely broken, and that functionality just happens to apply specifically to Query Tables. This is a big enough problem that for the first time I taught myself how to write a macro and assign it to a button in the Quick Access toolbar, and this is my first post ever on one of these forums.

The solution that has worked for some but not others, involving right-clicking in Queries & Connections pane, to me seems to work through 'Connection Properties [Connection Properties]' dialog, which does not access many of the properties. (I'm using Excel; Queries but no Connections; and my primary use is to turn off the column-width adjustment.)

The response below from okshef on Jan 29, 2025 on using VBA to change QueryTable properties is the only solution I've found that seems to work for everyone's needs. From looking at the list of properties in his code, 'Connection Properties' can be used to set a limited number of QueryTable properties. I'm trying to create templates that my less-technical colleagues can use to create their own queries/query tables, and I think it's asking too much for average users to have to deal with macros to handle basic table functionality.

It's also clear from all the related posts that this bug was introduced with one of the updates that started rolling out late last year.

I hope and beg that Microsoft will prioritize fixing this bug.

Thanks very much,

Earle

Hello, rau1.

Thank you for visiting the Microsoft Community.

We understand the problem you are having. Your current issue appears to be a Power Query-related issue, we suggest you ask the following questions to ask questions and research:

Power Query - Microsoft Fabric Community

(Users who have the same problems as you in this area will prefer to ask questions and share their ideas.)

The experts here will provide you with more specialized solutions to your Power Query problems.

Sincere greetings

Hannah.Z- MSFT |Microsoft Community Support Specialist

 

Answer 5

I tried a lot of things:

• scf /scannow- DISM /Online /Cleanup-Image /RestoreHealth
• Disabling Excel add-ins.
• Removing any files (xlsb) in Excel temp.
• Disabling GPU acceleration/multi-threaded processes.

Event Viewer says: Faulting module name: ntdll.dll

ntdll.dll is responsible for a few things like memory management, error handling, etc.I got the exception error code c0000374 which indicates a heap corruption error, meaning memory corruption, bad drivers, or software conflicts.

Heap corruption errors can be caused by many system-level things like corrupt updates, but running both mentioned commands would have solved this if so.

What actually worked: running Excel in Win 8 compatibility mode.

I found when it crashed it was trying to find:

HKCU\Software\Classes\Wow6432Node\CLSID\{7B8A2D94-0AC9-11D1-896C-00C04FB6BFC4}

So, perhaps Win 8 compat. mode looked elsewhere for this (HKLM), and the CLSID existed there.

Merging this across to HKCU alone didn't fix the issue if ran w/o Win8 compat. mode so I guess there is a lot more to this; maybe there are other missing reg paths, or the compatibility mode bypasses security checks, maybe older DLLs in use also.