Cell Formatting Decimal Cutoff

Copper Contributor

Hello all, I've been absolutely stumped on this issue.  As you can see, this cell has .006 but Excel always rounds it to .01.  I've set the default decimal places to 3 in Windows per a separate suggestion somewhere else to no avail.  Since this is a table going into some xlookups elsewhere, I've left it in general format.  Text could work but this is an issue that I don't typically have on other workbooks and I need this for work.  Has anyone encountered this before?  Typically I add the ' to the front and it fixes it but I know I shouldn't have to do that each and every time.Screenshot 2024-05-09 100340.png

 

Thanks

5 Replies

@User_4289 

You can set the number format to Number with 3 decimal places.

The number format does not matter for XLOOKUP.

Thanks Hans, technically it's not a number so I don't want it to set to number.

@User_4289 

In that case, you'll have to either format the cells as Text before entering the data, or prefix the values with an apostrophe.

I've been doing some experimenting on my personal Excel (vs. work) and I happened upon perhaps some explanation.  I accidentally missed a digit when I typed it, and as general, it displayed the full .006.  It must be that Excel has a hard limit on the # of characters for general before it starts to "round up" on its own.

@User_4289 

That is correct.