This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi all,
I have a query which retrieves a dataset, and then follows a series of merged joins, to incorporate other related data. One of the original columns is a value field which is set to type 'number' and the excel table itself is also formatted to number (hopefully irrelevant that bit)
On some occasions when the data is refreshed, the table loads with all values rounded down to zero decimal places (so 1.25 would become 1.00 and 5.77 would become 6.00). This is causing a huge problem for me. The values at source are correct, and when I view the query, the values in that column show the correct (i.e. 1.25 and 5.77), but when loading to the table they suddenly round down. Different users are performing the refresh and I'm not able to yet identify a pattern.
There are no other formula or formatting that appear to be affecting the cells. Any troubleshooting advice would be appreciated.
Thanks
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 79%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
That setting rounds any number values in the workbook based on the displayed value. So if your column is set to not show decimal places, when you refresh the data, the actual values will be rounded to 0DP. It's not a setting I would ever recommend turning on.
Hi Rory, thanks for your quick reply. looking at the workbook it does have that setting turned on - so maybe we are getting somewhere. I'm not entirely clear how that works though, as the field in the table itself is set to number format with 2 decimal places
Thanks for your reply Pascal, the column has sufficient width to display the full value and I can see in the formula bar the actual value does represent that displayed in the cell
Could it be the Excel column is too narrow to display the decimals? Try widening it a bit.
Is the table in Excel set to display decimal places? The only option I can think of in Excel (other than code) that could cause what you are describing is the 'Set precision as displayed' option, but that is a workbook-level setting so someone would have to be turning it on.