Want to highlight a helpful answer? Upvote!

Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: JagManXK8
JagManXK8 Author
User level: Level 1
5 points

Excel accounting format displays the wrong currency when viewed on iPhone/iPad

One of my developers has created an Excel document for distributing within the office and to our clients


Numeric values have had the Accounting format applied


In Excel, $100 will correctly be displayed as USD 100.00, and any subsequest values are nicely aligned by currency and then by decimal point


When the Excel document is viewed on the iPhone or iPad by the default viewer, the currency is miraculously changes to £, and the value appears as £ 100.00


Any other currency is also changed to the £ symbol.


Apple's initial suggestion was to change the location setting of the device to the US, not particulary useful when living in the UK!!!!


The Excel currency format appears to work fine, but we wish to display the document in the Accounting format


One of our Directors who is anti Apple, gleefully advised that his Windows Nokia phone displayed the value fine. With our fleet renewal coming up, such a glitch could be a deal breaker and we could move to another device


If the document is viewed using Documents To Go, the correct value is displayed


Whilst we could update our own iPhone fleet with this other software, forget the idea of asking all of our clients to do the same


Has anyone else come across a similar problem and have any suggestions on how to fix it


Many thanks

iOS 7.0.4

Posted on Jan 31, 2014 12:23 PM

Reply
9 replies
User profile for user: silvergc
silvergc
User level: Level 4
1,002 points

Jan 31, 2014 2:26 PM in response to JagManXK8

JagManXK8,


(Pardon if this ends up being a duplicate post, my browser is having problems).


I ran a test on a US iPhone just now. I created an Excel spreadsheet in Office 2010 (version shouldn't make a difference as long as the file is saved as .xlsx type. Used the same accounting format for some numbers as you did (USD 100, for example). I emailed the file to my email account on my iPhone and was able to see this:


USD] 100


for the cell. Of course the "]" is odd, but not a problem like the GBP symbol your are seeing.


But, in response the James, I can confirm that there is a default "viewer" for xml files in iOS 7. I don't have Numbers, or any other spreadsheet app on this iPhone. I was able to open the .xlsx file (xml format) without any difficulty.

Reply
User profile for user: JagManXK8
JagManXK8 Author
User level: Level 1
5 points

Jan 31, 2014 3:27 PM in response to silvergc

silvergc


Thanks for your comments


Several of our iPhone users in the UK reported the problem this morning, and as the Apple fan in the office I've got the job of resolving the issue


The original Excel was an old .xls file, but I have saved it as a .xlsx and the problem still appears


I haven't spoken with our developer yet, but I assume he has created the Excel file using some programming tools rather that directly using Excel


In the meantime I have created a simply spreadsheet with 8 cells contain values


In the first 4 cells, I have formatted them using Currency, and have used the currencies USD, DEM, GBP and CHF


In the remaining cells I have formatted them using the Accounting option, using the same currencies


When I review the spreadsheet on the iPhone (or iPad), the first 4 remain the same yet the last four all show the £ symbol


I have now changed the Region Format of the iPhone to United States, and when viewing on the iPhone the bottom 4 values are now all showing the $ sign!


If I had created the spreadsheet I would have put the Currency code in a seperate cell and purely used a numeric format for the number, and there would be no issue. That might be the only solution


Yes, the Director has the full MS Excel app on his Windows 8 device, but I can't expect all of our clients to go and do the same, in business many of them will have the iPhone


We may have to change the way we produce the report because of Apple's failings here, and it's not the first time that we have had to change something because Apple devices can't display them properly


In business, submitting a potential job contract only for the recipient to get the wrong idea of the costs is a major concern

Reply
User profile for user: ChrisJ4203
ChrisJ4203
User level: Level 10
230,293 points

Jan 31, 2014 3:40 PM in response to JagManXK8

Just to add a little something as someone that uses Excel. The error here is not in the phone. The phone does have a default reader for information and when confronted with displaying date/time and other regional specific information, such as currency, it is responding as it should, which is to change the display to pounds. This is what your computer would do as well. If you look at your original .xls file, does it also display the regional currency sign? Without the full version of Excel, I don't think you can force it to display in USD. As a matter of fact, you may find that displays in other regions would be region specific as well.


I'm actually checking to see if there would be a way to force the USD display, but I don't know if that would be possible since it has to do with the default reader. If you were to change the region format of your computer, you would probably see a similar behavior there when using the format, since Excel only allows for the changing of the display from regular numbers to currency. It receives it's direction based on the computer's region format. Is this spreadsheet designed for them to enter information into it, or is it for display only? What if you Protect the workbook from changes, does that change it? If it is not something that is to be used for entering information, then you could always distribute it as a pdf file. I'll let you know if I can find a different answer in Excel.

Reply
User profile for user: JagManXK8
JagManXK8 Author
User level: Level 1
5 points

Feb 1, 2014 3:31 AM in response to ChrisJ4203

Hi Chris


Thanks for coming back to me


The currency that might be used in the spreadsheet has nothing to do with a region format


In one Excel cell type an amount, choose the Accounting format and indicate that the currency for this amount is say DEM. When you then view the file on the iPhone the currency has been changed to £ (or whatever region setting you have set the iPhone to)


This does not happen if you use the Currency format, which is the potential solution, although my colleague was quite keen to get all the 3 character values lined up, which is what you get with the Accounting format


He also produced a PDF version as you suggested, which was fine. Not sure whether our clients might want to add in their own calculations, which is why Excel would be a better format


Just to let you know that I have now sent my simple spreadsheet to my wife's Blackberry, and that views the file perfectly - I'll try my friend's Android later on, but to me that categorically indicates that the fault is with the iPhone

Reply
User profile for user: petra221
petra221
User level: Level 1
4 points

Nov 27, 2016 4:28 PM in response to JagManXK8

Hello.


I believe that Android is definitely better solution for you, especially when it comes to excel reports. And apple should recognise this weakness and allow their clients to set at least the third party default app if they are unable to display format correctly, especially when it comes to currencies.


I have experienced similar issue.


And then below worked:

I have also had issue with too many decimal places for which i used ROUND formula

And then to display £ on iphone I went to Custom -> [$£-cy-GB]#,##0.00

This would display in 2 decimal places

with no decimal places just set formatting to [$£-cy-GB]#,##0


I hope that helps

Reply

Excel accounting format displays the wrong currency when viewed on iPhone/iPad

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up