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

Ask Your Question

Permanently formatting pivot tables? [closed]

asked 2017-06-03 09:04:04 +0200

i92guboj gravatar image


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 :)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-14 21:42:34.021600

3 Answers

Sort by » oldest newest most voted

answered 2017-09-06 16:37:15 +0200

Wololo gravatar image

updated 2017-09-06 16:37:42 +0200

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.

edit flag offensive delete link more


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

Cerin gravatar imageCerin ( 2018-06-20 01:03:00 +0200 )edit

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!

DonnaC gravatar imageDonnaC ( 2018-10-07 20:52:55 +0200 )edit

answered 2017-06-03 12:52:56 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more


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

i92guboj gravatar imagei92guboj ( 2017-06-04 10:11:44 +0200 )edit

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...

i92guboj gravatar imagei92guboj ( 2017-06-04 10:30:00 +0200 )edit

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.

i92guboj gravatar imagei92guboj ( 2017-06-04 16:43:28 +0200 )edit

answered 2017-06-04 10:04:56 +0200

i92guboj gravatar image

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?


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. :(

edit flag offensive delete link more


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

karolus gravatar imagekarolus ( 2017-06-04 10:42:20 +0200 )edit

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.

i92guboj gravatar imagei92guboj ( 2017-06-04 12:24:23 +0200 )edit

You should try with conditional formats.

m.a.riosv gravatar imagem.a.riosv ( 2017-06-04 14:44:31 +0200 )edit

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.

i92guboj gravatar imagei92guboj ( 2017-06-04 16:42:10 +0200 )edit

Question Tools

1 follower


Asked: 2017-06-03 09:04:04 +0200

Seen: 3,073 times

Last updated: Sep 06 '17