Ask Your Question

Calc - issue with loading .xlsx pivot table [closed]

asked 2019-09-13 09:59:03 +0200

Pollik gravatar image

updated 2020-08-30 23:31:21 +0200

Alex Kemp gravatar image

Running V on Win 10

I am trying to load this file, from the Ministry of Justice, which contains a pivot table:

I have screenshots from a colleague to show me that the file opens in, I assume, Excel, but it won't open in Calc.

I get the error message shown in the image.

I would be grateful for any ideas, please.


image description!image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-09-26 20:41:35.225142

1 Answer

Sort by » oldest newest most voted

answered 2019-09-13 10:33:37 +0200

erAck gravatar image

updated 2019-09-13 16:06:07 +0200

The sheets are stupidly formatted such that for all remaining unused columns (similar for rows on some sheets but that doesn't matter here) a custom style is applied for custom width. Excel uses 16384 columns, Calc only supports 1024 columns so far, hence the warning is displayed. In this case you can ignore it safely. The file is opened, it's just a warning displayed before the view.

Edit: 2019-09-13T16:04+02:00

Seeing the newly added screenshot now it seems there's nothing missing, but just that Excel presents things slightly different. If in Calc you click on the list button in B23 for the Offence page/filter you get the same items presented as in the Excel view for which in B23 it says (Multiple Items). You can have the same multiple selection in Calc just within the filter list, and if chosen the same 19[CDFGEH] items (and selected 02: Female in B17) you get the same numbers as in the screenshot (and B23 then even says - multiple -).

edit flag offensive delete link more


Thank you...I wondered it was something like that. And it is the MoJ, so yeah badly formatted.

Yes, I can open it, but part of the file is missing. And because of the missing data, I cannot interrogate the pivot table on the fourth tab. Essentially, it makes the pivot table unusable.

Interpreting your comments, am I right in assuming that any work around would involve finding someone to open it in Excel and reformat it?

I've edited my opening post to add a screenshot of how it should look in Excel. Unfortunately, the screenshot comes from my antagonist and it would be difficult to ask him to do my work for me. It is a tense exchange, as is so often the case on social media :/

Bottom line, then, is I need Excel?

Pollik gravatar imagePollik ( 2019-09-13 11:35:14 +0200 )edit

Glancing at the file structure it doesn't indicate there would be data in the excess columns. You didn't say what data is missing. However, the document file has 5 sheets. The last sheet is huge (uncompressed 94MB with data in A1:V137405) and is marked hidden and thus not displayed. It is loaded and visible in the Navigator (F5) as HO Principal Offence 2019 and can be activated using menu Sheet -> Show Sheet... I don't see data missing, just most rows on that sheet being hidden. I don't have Excel to compare.

erAck gravatar imageerAck ( 2019-09-13 11:55:53 +0200 )edit

Apologies, I thought I had added the image of how it appears in Excel. Done that now.

And...while have been playing with it, I have solved my problem. I wasn't seeing all the control boxes, but I have found a way in through the control boxes that I can see.

Thank you...talking it through with you has helped me find my own solutions. :)

Pollik gravatar imagePollik ( 2019-09-13 15:47:36 +0200 )edit

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-09-13 16:19:18 +0200 )edit

Yes, I finally saw that. Thank you again.

Pollik gravatar imagePollik ( 2019-09-13 20:07:03 +0200 )edit

Question Tools

1 follower


Asked: 2019-09-13 09:59:03 +0200

Seen: 259 times

Last updated: Sep 13 '19