Using Excel

Posted on 2013-06-28
Last Modified: 2014-05-23
I want to display a number such as 0.0000004 as 400 in the cell, but maintain the value of the number.
Question by:nikitav8
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 39285580
You can do this but it's tricky. Use a custom number format. Enter the format as follows:


Actually press CTRL+J versus entering the text "<CTRL+J>".

Format the cell with word wrap.

LVL 20

Expert Comment

ID: 39285592
You can format the cell with the custom value of:  #,<Ctrl-J>%%%%%%

Zorvek has it right, and sooner than mine, so please award him all the pointer.

Expert Comment

ID: 39285932
WHY does this work? It seems like the most random command ever! What's the effect of Ctrl-J and what's up with WordWrap?
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39285938
Each comma divides the number by 1,000. Each percent sign multiplies the number by 100. The commas are hidden but the percent signs are displayed. By entering CTRL+J a line feed is placed in the formatted string. Setting the cell to word wrap puts the percent signs on a second line that is not displayed unless the row is expanded.


Expert Comment

ID: 39285948
That is a sweet solution to something MS haven't bothered offering us :)
However, to be able to replicate your result with a Norwegian locale I had to replace the comma with a space.

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question