When I set format of a cell as %, it automatically multiply the value with 100 & show the value. nvm! You are both correct with expected inaccuracies. Funny enough, excel WILL apply the correct number format TEMPORARILY to the open document: but if you re-open the file, POOF! http://office.microsoft.com/en-au/excel-help/refresh-pivottable-data-HA101906071.aspx I found this clever looking hack but the percents don't seem to work properly: http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/. @James: could stop to make a test % and , behave in LibO differently. Firstly i would like to thank you for the solution you provided. @James: What I would do is using the link of chandoo and experiment. cgaedfkafgde, I think you have observed some very interesting details , appreciate it for the post. [closed], Number format code that multiplies by powers of 1000 [closed], http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10, Creative Commons Attribution Share Alike 3.0. Is it possible to multiply by a different number instead of 10? Alternatively you could make the custom format "#,K" which automatically suggests rounding. Reason ### displays a maximum of 3 digits after the decimal point. Unfortunately it seems this work-around has now been semi-disabled in Excel 2010. In a new cell, type "=" and click the first cell you want to multiply. Reason: I MIGHT have a kind of workaround if the % problem cannot be solved. However, this is leaving me with a result with a . A1. Typically the answer has been limited to “It can’t be done” or “It can only be done in multiples of 1000”. It is possible to Multiply or Divide any cell contents by any power of 10 using Custom Number Formats ! Kindly assist. Select the cells you want format. What do you mean with "trainling point"? I learn about how others think and put another tool or two in my Excel toolbox. By extension we can now use this technique to add multiple Lines of Text to a Custom Number Format, You can download a file containing all the above example here: Download Here, http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/, http://chandoo.org/wp/2011/11/02/a-technique-to-quickly-develop-custom-number-formats/, http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/, http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/, http://chandoo.org/wp/tag/custom-cell-formatting/, http://www.ozgrid.com/Excel/CustomFormats.htm, http://peltiertech.com/Excel/NumberFormats.html, Just a quick final Thank You to Kyle for highlighting this Custom Number Format feature/trick last week. my custom format looks like this: I have noticed that this method works well with .xls files in compatibilty mode. For division by powers of 1000, I can use commas, so I can use the format codes: Or I could even construct something more generic like this: (There seems to be a bug that prevents me from using more than 3 conditions, so for now G is omitted.). You may have to use a helper column to assist you. Calendars, invoices, trackers and much more. As I explained in a previous comment, the site crashed when I was trying to correct myself. 1662052 as 16.62 When I try to change to format to %, it automatically multiplies by 100 and I get 130%. Notify me of when new comments are posted via e-mail. @Matt But I think that is a poor idea. I don't have the proprietary alternative to actually try the test you suggest. Furthermore, Excel doesn't complain when the … I have a graph which shows the diff from 100, e.g. If you would like to have my test sheet, let me know. - as you want to display them I want to apply % format on a column but want to keep the values same as before. I just want % sign to be shown along with the value. However, since the field is linked to a percentage, it is formatting as 100%%. I want to display absolute figures in lacs - For example, you can read this shortcut roundup to get up to speed on the best formatting shortcuts.. And there are some formatting hints in our list of ways to Idiot-Proof Your Spreadsheets, too.. Thanks for this tip. http://answers.microsoft.com/en-us/office/forum/office_2003-excel/losing-formats-when-i-refresh-the-pivottable/372c5001-21ba-4ee1-b4f7-7c7758f8737f. In a simple or single sum the round command works well but when I start using those rounded sums to get to a bottom line (Profit/Loss) or on a balance sheet, they can veer out of balance from actual final numbers more often than I like to see. Try to apply: 0.0,"mV"%%% The custom format that we are using is : a "comma" to denote decimal point and a "dot or punkt" to denote thousands ('1000s) placeholder. [closed], Please refine "Search" in Calc - implement functions in Gnumeric [closed], Are there any new applications planned for Libre Office in the near future? The Number tab of the Format Cells dialog box. this is very clever. You will learn how to show the required number of decimal places, change alignment or font color, display a currency symbol, round numbers by thousands, show leading zeros, and much more. Learn & be awesome. (since that is what Excel is doing with the format, rounding to the nearest 1000) Thanks very much. It seems like my scenario runs into two competing issues within Excel. I hear you, but that all numbers tie is absolutely expected at my company. Hi Hui - I don't have the option to create another field as I am already using a calculated field to arrive at %. Sometimes, it’s straightforward, but sometimes the order doesn’t matter. 1. So 5.66 becomes 566 %. The QA team will be happy to help you triage your feature request in the bugtracker. Therefore, you have to define a custom number format (the numbers are corresponding with the picture): Select the cells which you want to display in thousands.