Microsoft Office UK/US language confusion

I've been having a nightmare with dates and spelling showing as US format. Here are my Windows settings:

and, here are my Office settings:

I wondered which sort of English that might be and so clicked on the “Add a Language” button to add “English (United Kingdom)”. This resulted in a changed display where “English” was replaced by “English (United Kingdom)”. 

I think most systems will be set to “Match Microsoft Windows [English]”. In spite of saying it matches Microsoft Windows, it doesn’t actually match the UK bit of the Windows setting and, instead, uses US English! 

Now that it reads “Match Microsoft Windows [English (United Kingdom)]”, I'm getting what I want. How confusing is that?

Excel High CPU Usage

My laptop sounded like it was working hard so I opened up Task Manager in Windows to see what was going on and, to my surprise, I found that Excel was reporting greater than 50% CPU usage. Permanently it would seem. After a lot of blind alleys I eventually discovered that the zoom setting on the tabs seemed to be the critical factor. At 100% zoom on all 12 tabs the CPU problem persisted. Setting them all to 90% zoom seemed to make the problem marginally worse (approaching 60% CPU usage). Setting them all to 80% zoom seemed to make the problem go away. 

Searching for what might be the issue, I noted that freeze panes was active on one of the sheets such that the scrolling part of the screen was only just visible at 80% zoom. Trial and error determined that the scrolling part of the screen disappeared at 82% zoom and that was the exact zoom level that the CPU usage problem occurred. I removed freeze panes from the sheet and set the ScrollArea in worksheet properties to achieve the same sort of "locked area" effect that freeze panes was used to create and had a usable workbook again.

Trouble with Time (Text ODBC driver)

I have a text file with Date and Time fields containing the values 01/01/2020 and, for the time field, 13:00:22

In Microsoft Query the values show as 2020-01-01 00:00:00 and 1899-12-30 13:00:22

When returned from MS Query to appropriately formatted columns in a Query Table they show as 01/01/2020 and 00:00:00

However, if I create a DateTime field as Date+Time it shows as 01/01/2020 13:00:22

How to get the Time to show correctly in the Query Table?

Time + 2 as [Time]

This shows as 13:00:22 in the Query Table. Took me ages to figure this out. I spent ages playing around with the DateTimeFormat option in the Schema.ini to no avail. It seems that if MS Query returns anything prior to 01/01/1900 00:00:00 the time portion will always show as 00:00:00 in Excel

Excel 2016 version 1612 change

Following the release of the Microsoft Office 365 update to Excel 2016 version 1612 (January 25, 2017), there has been a change in Excel’s default behaviour in relation to PivotTable subtotals when a new field is created following a Group operation. Until now, the new field has been created with the subtotals set to “None”. Now, the new field will be created with the subtotals set to “Automatic”.

I haven’t checked whether the same change occurs with subscription versions of Excel 2013.

Windows 10 Anniversary Update (1607) and problems with Excel

Some Microsoft Windows 10 users have recently been reporting that their Microsoft Excel either crashes or hangs regularly, particularly after the Windows 10 Anniversary Update to version 1607 in August 2016.

The issue can often be traced to printer driver incompatibilities with Windows 10 which affect the correct operation of Excel. Correct operation of Excel can usually be restored by temporarily setting your "Default" printer driver to either "Microsoft Print to PDF" or "Microsoft XPS Document Writer" until you can obtain a Windows 10 compatible printer driver from your printer manufacturer.


HP Laserjet users can find the "Recommended Solution" for their printer model at http://h20564.www2.hp.com/hpsc/doc/public/display?docId=emr_na-c04675396.

[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1

I had a complex query that returned the above error intermittently. It turned out that one of the fields was DATE (upper case) as specified in the schema.ini file for the text file. The query referred to Date (proper case). When the query was changed to refer to DATE (upper case) throughout, the intermittent failures stopped.

What does PivotCache.OptimizeCache do?

After much searching in vain I finally came across this nugget of an explanation:

PivotCache::OptimizeCache Optimize storage for fields with less than or equal to 255 items

It is buried in the innards of a file explaining changes introduced in Excel 97. Here's the link to the file if you want to see it for yourself: Excel 97 Product Enhancements Guide‎

I presume this means that a different internal indexing mechanism is used in the cache if a byte is big enough to reference all the unique data occurences in a field.