Permanently formatting pivot tables?

English
, ,
#1

Hi.

I am new here so, please, if I do something wrong just let me know and I’ll try to improve.

A bit of background about the question. When working for a given enterprise for some years, I discovered how nice pivot tables are when it comes to analysing loads of numbers. They used some other spreadsheet program whose name I won’t mention but, that gave you the capability to format pivot tables. This changes where mostly retained when refreshing the pivot table.

I can apply format to pivot tables in localc as well, but, when I refresh the table, everything, even numbers format, is wiped, which is a nuisance because I need to constantly refresh it.

I don’t need anything complicated, just a way to visually mark lines so I can follow them from one side to the other of the screen. Just basic stripping and number format with two decimals for some columns.

But having to redo it every minute is a bit annoying.

Is there some silly thing I haven’t taken into account or is this as of now impossible to do in localc?

Thanks beforehand for any insight you can provide :slight_smile:

#11

my workaround :
Apply the wished formatting to the source data, not to the pivot table.

Example, I apply the formatting : two decimals and comma separator is , to my source data numbers (which is in another sheet within the same calc file), and the pivot table overtakes formatting.
For sure this workaround doesn’t help you if you want to format column headers of your pivot table.

#12

Thanks, I’m not allowed to upvote, but wanted to say this worked for me.

#13

thanks, that worked well for me - several different formats now applied to the source data depending on detail required in each column - appears to replicate in the pivot table each time I refresh - so far, so good!

#2

Default styles for PT are reapplied when it’s updated, what you can do is modify those styles. But they are for all PTs on the file.

Sample file

As I know the are no way to retain direct formats, but you can reference the PT data with and array large enough on another sheet, what you can format as you like without lost the format when the PT is updated.

#3

Nah, ignore the above, just learned that I am supposed to use CONTROL+SHIFT+ENTER to enter an array formula.

#4

However not I am finding that autoformat is not working for arrays, which in turn makes me wonder how am I supposed to stripe a table that’s many hundreds of thousands of lines lone. Some of my pivot tables can easily get over 60 thousand lines. I can’t believe there’s no easy way to just make it easy to follow a long line without fingering all over it in my screen. Just reformatting the pivot each time I update it is probably easier…

#5

Thanks everyone for your help. But I’ll look into some other open source solution. This is too complex and the best solution in the horizon won’t fulfil my needs.

#6

Thank you for your answer.

However, I have been trying to put it to good practice but either my tables are too big or I am doing something wrong.

No matter what I do I always get “Err. 512”.

I have been reading and as far as I can see, to reference a given pivot table all I have is to use a regular reference but do so inside curly brackets, is that true or am I missing something obvious?

So, let’s say, I have a pivot table that, right now, goes from STOCK.A1 TO STOCK.AH609. So, if I open a new page and put this formula in the first cell of that new page, I should get an array containing the referenced range, true?

={$STOCK.A1:AH609}

I have tried with bigger and smaller ranges, but I always get Err. 512, which seems to mean the formula is too large. The true meaning of that is a bit uncertain to me.

Sorry if I am missing something obvious, but never used arrays in localc before. :frowning:

#7

Use the Formula without curly-braces, and enter with ctrl + shift + enter

#8

Thank you. It works, however autoformating doesn’t work in arrays, it seems. Any other way to, at least, automatically strip lines on alternate colors for readability? Otherwise, autoformating the pivot table after each refresh might be easier than this whole array thingie.

#9

You should try with conditional formats.

#10

I don’'t find that to be scalable and/or convenient. I think I chose the wrong tool. I’ll check some other way. Pivot tables are not mature enough for serious use.

#14
#15

With the new coming version 25.8 is possible to apply format inside the pivot table results, as in Excel

Support importing and exporting OOXML pivot table (cell) format definitions, which allow direct formatting of the pivot table cells (which survive pivot table update). (Tomaž Vajngerl, Collabora)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.