How to customize name of months in Pivot Table when grouping dates without changing „Format” in Region (Control Panel) in Windows - OR- How to prevent Excel from blocking sorting, autofilling and custom lists when I do change that „Format”
I use Excel within Office 365 Personal on Windows 8.1 Pro on Lenovo ThinkPad X1 Carbon (from 2013 or 2014...).
Main, initial problem:
When I group dates by month in pivot table, as names of months I get abbreviations of Polish names of months, while I need English abbreviations.
Problems that I encountered trying different solutions:
[I list them so maybe somebody can use them as un/satisfactory solutions, and also you know what I’ve already done]
The solution to that problem is changing „Format” in Region (Control Panel) in Windows to English. But this disables sorting, autofilling and editing custom lists in Excel (sorting works in pivot table, but not in the source data or other data in Excel). Why? What to do to conserve these functionalities?
I was informed by Microsoft Support team member that Excel and system should be in line regarding language, formats and region, so I installed Office 365 once again, with this new format already set up in the system, and I installed English version of Office 365. Still these functionalities didn’t work. How and where I can align the format, language, region? Even with the changes and new installation it seems that Excel is confused, from somewhere it gets the conflicting data on the format/location/custom lists and refuses to sort, do autofilling and editing custom lists.
Adding additional column to the dataset using “text” function gives name of the month in Polish once again, so it doesn’t help.
Adding additional column to the dataset using “if” and “month” functions does give names that I want, but pivot table doesn’t “accept” that column at all, it’s not listed in “pivot table fields”. Why…? How to add it to pivot table as category?
Adding additional column to the dataset using “month” function gives number of the month (for example “6” for June) (the column has to be formatted as “number” not as date!!!). This column is listed in “pivot table fields” and can be used for grouping (when I disabled automatical grouping by month). It is OK, but still I would prefer to have English names and not simple numbers.
In the custom lists (Options->Advanced), Polish abbreviations are the first in-built list for months (there is another one with full names, but this one with abbreviations come as the first one). These default lists can’t be changed. I don’t know how to make Excel use different custom lists as the basis for pivot tables.
These names of months are texts, no matter how do I format the source data, whatever format or language, the grouping in pivot table uses these Polish abbreviations.
So - what to do…? How to have English abbreviations of months when grouping dates in pivot tables? Of course it has to be automatic, easy to use on every new pivot table. Alternatively, how to maintain sorting and autofilling and editing custom lists when changing regional settings in Windows? And is there anything I can do to solve these problems, anything related with my system, settings...?
At least tell me that it’s impossible, so I stop trying ;)
Today the problem was fixed by Microsoft Support technician who deleted old settings using “Registry editor”. From what I understood, there were some remainings from previous Excel/ Office versions which made confusion regarding language and related settings. It seems not everything is removed at deinstallation and sometimes it has to be removed manually. I don’t know exactly what was removed/changed, but it’s good to know where to search for a solution when standard solutions that I tried at the beginning don’t help. I only hope nothing else was damaged on the way, but for now all seems in order.