Decimal place custom format

Hi Hayley,

I think the only things missing from your custom format the are the selected item in the menu below, and the "Number of Digits" item in that menu.

After dragging the decimal token to the bar, click the v to open the menu, and set as shown.

Caught this omission just after posting. Cells E2:E5 contain a formula that copies the value in that row of column D. The formula wasn't filled to rows below row 5, so B6 is empty.

Here's the table with the formula placed in all body rows of column E, each copying the value in its row of column D. Custom format is as shown above.

Regards,

Barry

I'm not sure you can with a custom format if you also want to show 1, 3, 4, or 5 decimals for other numbers. With custom formats, you are specifying the exact format to use and it will format the number as you specified. Your format will display up to 5 decimals but will drop trailing zeros.

You can make your format "#,###.00000" which will always show all five decimal places. Click on the disclosure triangle next to ".#####" and choose "show trailing zeros". The number 1.40 will display as 1.40000. If you want it to be 1.40 you need to reduce the number of decimal places in your format to two, but then all numbers you enter will be 2 decimals.

The only format that will adjust to what you type is "automatic". If you type 2 decimals, it will format the cell for 2 decimals. Type 3 decimals and is sets it to 3 decimals. But, as you already know, it has no way to make a zero invisible.

An alternative to your format would be to use a conditional highlighting rule to set the text opacity to zero when the number is zero. It will make it invisible.

1. Select the cells that should get the highlighting rule
2. Set the cell format to automatic
3. Click on "conditional highlight"
4. Click to add a rule
5. Choose "equal to"
6. Type a zero in the box
7. For the format, choose "custom style" (scroll down to the end to see that one)
8. Click on the color wheel next to the text color
9. On the "wheel" settings, set the opacity to zero.
10. Back in the format sidebar click "done"

If the information in the cells is data (not formulas), you can copy/paste the format to other cells by copy/pasting one of the formatted cells. If the cell to receive the format has a formula, you'll have to use Copy Style and Paste Style.

Hi Hayley,

You are welcome. thanks for the green checkmark.

Barry

PS: You're not the only one who sometimes 'misses things'.

My most recent occasion was in writing the reply above.

Noticed just after sending, the oddity in E6 which was blank despite the 1 in that row of column D.

Discovered I hadn't put the formula in rows 6 and 7 of column E, and spent a bit of time correcting that and editing my reply.

B.