Lozzer provided great feedback in the small print: 'If I set the default editing language to Japanese, then the SHIFT-JIS csv file will open correctly.' I have spent hours looking for a solution on how to open Japanese CSV with one simple click We have a multi-language environment (En-Jp). Word and excel for mac. Even though Excel showed Japanese menus (based on the control panels language settings), the default editing setting in File > Options > Language > Choose Editing Language (Top area) remained English. Changing that to Japanese (obviously no issue for Japanese users) solved our problem.

It is a pity that Microsoft in 2016 does not have UTF-8 support in Excel 2016 for Mac. You should write 'Microsoft is unable to support UTF-8' outside the Office 365 box and the same in a big top banner on office365.com webpage.