TLDR

In order to change % to pp in GoogleSheets do:

  • Start adding a custom number format.
  • Go to Developers tools to Console.
  • Paste:

Object.defineProperty($("[label='Custom number format']"), "value", {value: "0.00pp\n%"})

  • Use the newly defined format and set height of rows to 21 pixels.

Story

When I finished an analysis a couple of weeks ago I was told that I used wrong units. I checked the analysis again and couldn't find what I did wrong as there were actually no units in the analysis. What I was computing was a change of shares, e.g. the company had share 10 % on market before a change and 20 % after the change. I was using Python, but the results needed to be exported to GoogleSheets for managers, so I simply exported DataFrames and uploaded them to Google Sheets, where I set up conditional formatting and changed all decimal numbers to percentages including the difference between percentages of shares before and after the change.

And that is what I did wrong. For me, it was just a decimal number, because the % sign literally means This number was multiplied by 100. However, I was explained by a mathematician colleague that there exists an agreement that differences between percentages are not written with percentage sign, but they are written with percentage points sign pp instead. So change from 10 % to 20 % is 10pp not 10 %. It is still the same number without a unit from my point of view, but to have it correctly, I now needed to change about 120 columns in GoogleSheets for which I already set up conditional formatting.

There were couple of options:

  • Generate and upload DataFrames again, but change the type for columns to string ending with pp, but then the numbers would not be usable in GoogleSheets if somebody wanted to use them in a custom formula and conditional formatting would stop working.
  • Multiply all the percentage differences by 100 and then use #,##0pp formatting, but that is not really the same number and the conditional formatting would stop working.
  • Find better solution so that the number stays the same and just replace % with pp.

I was searching for the third option. It is a bit more complicated than it seems, because when you click on % sign in GoogleSheets it automatically multiplies decimal numbers by 100, but the underlying value is kept the same. There is no other way to show a decimal number multiplied by 100 while keeping the actual value not multiplied.

I started to search and found a hack. It is possible to create a custom format that appends pp to a number and writes the percentage sign on the next row. You then set the height of rows to 21 pixels so that only one row is visible and nobody will see the percentage sign, they will see only number with pp instead. The value of a number will stay the same, will not be multiplied by 100, so it can be used in custom formulas and already set up conditional formatting will not break up, while the shown value will be multiplied by 100.

However, there is one more problem. It is not possible to add newline character while creating a custom format, but if you search enough, you can find this answer on StackExchange, which shows how to set custom number format using Javascript and that is what I wrote to TLDR.