We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc pivot table does not preserve word wrap format on refresh

asked 2020-03-14 23:27:10 +0200

LKeithJordan gravatar image

updated 2020-03-17 23:43:47 +0200

Lupp gravatar image

I have noticed this effect for some time. You can word wrap individual Pivot table cells or entire spreadsheet rows. You can do this via the format menu, the ribbon, or the properties window. Regardless of how you do it, a refresh resets to disable word wrap for the Pivot table cells (but not the cells outside the Pivot table -- if you word wrapped entire spreadsheet rows). I could perhaps change the Styles, but this might create unintended consequences in other Pivot tables.

Right now I'm using LibreOffice Version, but I have noticed this effect as far back as Version 3.x -- in both Windows and Linux editions. To the best of my memory, I have seen this effect on more than one of my laptops, so neither hardware nor OS seem to play a role.

Is this a bug, or am I missing something?

(search word "format" inserted into the subject by @Lupp)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-03-15 10:43:19 +0200

Lupp gravatar image

Pivot tables are -when created and when refreshed as well- formatted using preset cell styles. The borders are set as direct attributes.
I feel sure you cannot change that. Even if you delete the used special cell styles completely they will be recreated with hard-coded default attributes the next time a PT is created or refreshed.
You can modify the mentioned styles.The modifications will afflict alle the PT in the document.

If you insist on getting it another way, you need to do the refresh via a "macro" instead of using the 'Refresh' item from the special context menu. Such a macro might first use the .refresh method of the DataPilotTable (as PT are called in the API) and then rework the formatting.

But why? PT are a technical tool collecting/consolidating/... information.
Where you are concerned about presentation/prettyprint you should anyway create your own formatting framework, apply it fix or/and using CF to dedicated ranges, and pull in the PT results by formulas.
Working-sheets of spreadsheet documents should anyway not be seen with the "paper eye".

edit flag offensive delete link more


I appreciate your response, Lupp, but I do not believe we are communicating.

As a Certified Public Accountant, I have been using Pivot tables since Microsoft first introduced them so I am thoroughly familiar with their use as both a work paper and as a live, formal report.

I am also no stranger to creating macros when necessary to meet my needs. However, macros should not be the immediate response to every problem.

In this case, creating and running a macro to retain manual PT formatting across refreshes is cumbersome and should be unnecessary.

In this PT, the column headings are somewhat long but further abbreviation is unacceptable. With word-wrap, I can manually size the columns to fit the content and see all of the PT on one video page. Without word-wrap, the columns are far to wide for the content involved so the PT spans video pages; zoom is ...(more)

LKeithJordan gravatar imageLKeithJordan ( 2020-03-18 14:57:01 +0200 )edit

Lupp, I just figured out the answer to my own question -- HOWEVER, I am giving you the check mark. Please let me explain.

I just took a moment to experiment with modifying the styles for "Pivot Table Category" and "Pivot Table Title." In both cases, I enabled word wrap. I can now refresh the Pivot Table and word wrap is not removed.

I returned here to update my post and re-read your earlier revised post. Your comment: "You can modify the mentioned styles.The modifications will afflict [sic] alle [sic] the PT in the document." was not clear to me when I first read it, but made perfect sense when I read it in context with my findings. I am therefore giving you the check mark.

I still believe we have a difference of opinion as to the need for such changes, but I appreciate the time you took to ...(more)

LKeithJordan gravatar imageLKeithJordan ( 2020-05-07 21:19:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-14 23:27:10 +0200

Seen: 391 times

Last updated: Mar 17 '20