Show parts per million in Excel

Topic

New Reply

I would like to show parts per million in my cells. I don’t mind entering it in using exponents, such as 6.02E-6. Easier ways are always nice, but doing it this way is not that much trouble.

The important thing is that I want to be able to have the units show as ppm, not standard scientific notation. It makes it a LOT easier for people to see that 10 ppm is greater than 9 ppm. It loses impact to compare 1E-5 with 9E-6.

I discovered in another post to set up the formatting as ######.00E+00 which works fine as long as the PPM value is greater than 1. But if I put in, say, 0.1E-6, I get 100000.00E-12.

Any suggestions? Just to make things a little more difficult, I must use the actual PPM value in further calculations.

Reply | Quote

Replies

Excel has no intrinsic way to accomplish your requirement. However I think you are already on the best track.

If you use ppb as your “standard” and use the custom format ######### E+00 I believe your users may easily get used to how that looks and what it means – especially if you modify the custom format to ######### E+00 “ppb” which provides a constant visual reminder of what they are looking at.

Reply | Quote

I’d do it this way. It’s often useful to have a ‘hidden’ intermediate cell to make entry simpler and output clearer.
1. In the entry cell (say A1), use a custom format like ##0 “ppm”
You’d enter 10 ppm as 10 and it would show as 10 ppm
2. In an intermediate cell (say A2), have an intermediate calculation like =A1/1000000 to get it to its ‘real’ value
3. If you don’t want to show the intermediate value on the sheet, you could hide the whole row.
4. In the output cell (say A3), convert the value back to it’s ‘displayable’ form with a formula like =A2*1000000 and a custom format like ##0 “ppm”
This will turn it back into an output like 10 ppm

That’s how I’d do it.
It would be nice if Excel had a way to harness what it does with percentages (I think this is really what you’re after, but with ppm rather than percentages). If a cell is formatted as a percent format, you type in ’10’ but it actually stores it as 0.1

Oh, also to make it easier to show that 4ppm is bigger that 1 ppm, you could use Excel 2010’s new sparklines or a formula like =REPT(“*”, A2) to create a repeat of a star.

Peter

Reply | Quote

Peter: Your suggestion is my “fall-back” position. However, I was sort of hoping that there was a one-step way of doing it. But thanks to both you and MartinM for the help.

Reply | Quote

I agree that its maybe best to restructure the sheet to have a “display” cell and a “for calculations” cell. Messy but clearer for the users.

Over the years there have been many regrets that the percentage style of displaying something other than what you type cannot be adapted for other uses, but unfortunately it is in inaccessible code. There are similar issues with the entry of dates, as documented elsewhere in this forum. All of which is just meant to confirm that a workaround is the best you can hope for 🙁

Obviously Microsoft’s Excel team has more accountants and mathematicians than chemists !

Reply | Quote

I agree with Martin to have a display section. As a matter of course I desgn spreadsheets with a data section, a calculation section and a display section. each section if offset from the other so if I need to add more columns/rows then the other sections are not affected. HTH

Reply | Quote

each section if offset from the other so if I need to add more columns/rows then the other sections are not affected.

I used to do this in the days of Lotus123 with only 1 sheet / book. With multiple sheets possible I suggest putting each section in a different sheet.

Steve

Reply | Quote