I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the result cell.

I suspect this is not possible, but is it?

  • I tried but was only able to change the color of the text or the shading of the cell via conditional formatting – mhoran_psprep May 3 '12 at 12:47
  • I think the answer of pnuts could work out. Have you tried it? – Jacob Jan Tuinstra Mar 14 '14 at 8:26
  • 1
    It depends what you mean by work out. It doesn't address the core of the problem which is automatic unit-consciousness and division. – yarian Mar 14 '14 at 20:54
  • What would be really nice is a spreadsheet which also calculated units. So you would get an error if you tried to add meter and liter. And m * m giving m^2 – Lenne Sep 13 at 12:39

Does not manage the automatic division by 1000 for kilogrammes but perhaps of some use (better than nothing?)

In New Google Sheets, Format > Number > More Formats > Custom Number Format and Apply:

0,0 g
| improve this answer | |
  • I can't seem to add a unit of 2 or more characters, i.e. km is not valid. – Morgoth Oct 24 '17 at 10:57
  • 7
    @Morgoth It works if you double-quote the unit: 0,0 "km" – vieron Jan 3 '18 at 15:16

I found it.

Format > Number > More Formats > Custom Number Format
then enter: . "lbs"

If you want two decimal points then it would be: .00 "lbs"

Putting whatever you want in quotes allows you to create custom units of measurement.

| improve this answer | |

There is an add-on that does this called LegendSheet. However, you don't have the number and the unit in the same cell rather you have to put them side by side. But it handles all the unit conversions such that you could do something like this:

  | A   | B    | C     | D | E                     |
1 | 3.0 | [k]g | 200.0 | g | =LS_QADD(A1:B1,C1:D1) |

And the result in E1 will be 3.2 [k]g. Note that you have to use the brackets for unit prefixes. There is a help video on on YouTube that shows you can also do subtraction, multiplication and division and raise to a power numbers with units. It is a paid add-on, but you get a good amount of free uses per day that I find to be sufficient for my needs.

| improve this answer | |

you can add to any cell: & "unit"

| improve this answer | |
  • 3
    A description would be more helpful. – serenesat Jun 1 '17 at 17:41
  • 4
    The OP still wants to be able to add the cells up, though. – jonsca Jun 1 '17 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.