How to stop excel table from rounding?

Sometimes I think we need to be more precise in our language. What you show there is a number format code, not a formula. Unless you have "precision as displayed" checked in Excel options, number formatting should not change the actual value you enter into a cell. Check you "precision as displayed" option and make sure it is unchecked.

If that does not help, then I suggest that you upload a sample workbook to illustrate the issue.

Originally Posted by Coinpie

I'm using a custom formula to display thousands with a K, but when I enter a cell value in between thousands (3,500) it rounds it up to 4K. How can I stop this, I believe it's a formula issue?? This is the formula I am using [>=1000]$###0,"K";0$.

First, the format rounds; it does not (always) round up. For example, it will display 3K if the value is 3499.

Second, it is unclear whether you want to change the underlying value, or you just want to change its appearance.

Excel always rounds (again, not necessarily rounds up) when it formats values with less precision.

The only way to change its appearance to always round down is to change the result of the formula by using the TRUNC or ROUNDDOWN function, for example.

If you want to leave the formula result unchanged for other purposes, I suggest that you use one (hidden?) cell for the unrounded calculation and a separate cell to display the result in rounded-down "K". For example:

=TRUNC(A1,-3)
or
=ROUNDDOWN(A1,-3)

Dependent formulas should reference A1 if they need the unrounded value.

So the problem is specifically related to the "pup up" thing that shows when mousing over a data point in a chart. It is not that Excel is really seeing the value as 4000.

As near as I can tell, the format of the value displayed in these popup windows is determined by the format of the data in Excel. The easiest solution I can see is to have your cells in Excel formatted with one decimal place [>=1000]$###0.0,"K";0$

I have never tried to modify these popups (if they even can be modified), so I am unaware of any way to make the formatting of values in the popup different from the source range formatting.