• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

How to get excel to display value of formula instead of pound # symbols

Hey EE Community,

I'm looking for a way to get my co-worker's Microsoft Excel 2007 to display the cell value instead of ##### when the value of the formula is longer than the cell width.

Here is the scenario... She is an accountant and has already set up most of her reports in Excel 2003.  With the move to 2007, many of the formulas that she has set up display as ##### instead of the contents that she is used to.  She doesnt want to expand the column width as this will mess up the page formatting.  She also doesnt want to format all the rows as "General" as she will have to do this for all of her spreadsheets.  In addition formatting as General displays all negative dollar amounts as -1,255 instead of (1,2555), which she prefers.  

So is there a way to give her what she wants in Excel?
4 Solutions
Change the font size!
Basically, the only ways are to make the data fit:

1) resize the column (top, between the column letters, double-click) - IGNORE since you don't want this
2) change the font size down to make the data fit
3) clear the cell(s) to the right so that the data jumps across columns
4) turn on cell word wrap (you may need to adjust the row height to see all the data)

maybe a few other options...
Also, depending on the data, maybe you can truncate it with something like this:

=Left( {currentformula}, 25)

If there's no formula there now, add a new column (and hide the one with the data) and add a formula like that:

(assuming the data to truncate is in A1)
You may need to adjust the number 25 depending on the font style and size.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

If you check, there are fonts that with the same height, are narrow that others. For instance, you can see on the attached image, the same information displayed with the same font size but with different fonts. You can see that some use more space than others.

Change the size of the row. This can be easily done for the whole row by highlighting it and then double clicking on the left side of the row.
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now