Issue on number format

Question

Dear all,

I have a sample database which contains a table with decimal number I have added manually like below list

Price EUR
18.4
16.25
27.3
30.63
23.58
14.95
11.5
17.6
36.3
31.05
46.2
14.35
24.3
18.56
19.58
28.88

Numbers are decimal with one or two decimal places

The issue occurs when I export this table in excel I have not the original value as below screenshot:

How it can be possible?

Am I doing something wrong?

For your reference you can download the Sample database here

Thanks

Answer 1

HansV wrote:

Change the field type from Number to Currency.

---

Thanks, your suggestion works good but I'm having the same issue on the attached PowerPivot file for which I have the wrong number format

I know nothing about PowerPivot and transferring data from an Access DB.

But if you can apply HansV's suggestion to original example, why can't you apply it to the data the you imported and used with PowerPivot?

Alternatively, in both cases, what if you took Pakrashio's suggestion and checkmarked "Export data formatting and layout"?

---

Irrespective of that issue, you might have another issue to address.

The sum in L15 is incorrect. It appears to be the equivalent of SUM(L5:L13) instead of SUM(L5:L14). IOW, it omits the value in L14.

In contrast, the sum in M15 includes the value in M14.

Answer 2

I can't help you with that, sorry. I hope someone else can.

Answer 3

This is because the Price EUR field is a Number field with field size Single. Single cannot represent decimal numbers very accurately, hence the discrepancies you see when you export to Excel.

Change the field type from Number to Currency. Access will display a warning when you save the table design; you don't have to worry about that.

If you then export to Excel, the values will be accurate:

Thanks, your suggestion works good but I'm having the same issue on the attached PowerPivot file for which I have the wrong number format as you can see by downloading the file from here

Thanks for helping me!

Answer 4

This is because the Price EUR field is a Number field with field size Single. Single cannot represent decimal numbers very accurately, hence the discrepancies you see when you export to Excel.

Change the field type from Number to Currency. Access will display a warning when you save the table design; you don't have to worry about that.

If you then export to Excel, the values will be accurate:

Answer 5

Hello LucaTramontana,

When exporting, tick the option "Export data with formatting and layout". This option will preserve most of the formatting for your requirement.

See data exported to excel in the below.

Let us know if this works for you.

All the best