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

Posted on 2011-05-02
Last Modified: 2012-05-11
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?
Question by:Ruckafella
    LVL 33

    Assisted Solution

    Change the font size!
    LVL 22

    Accepted Solution

    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...
    LVL 22

    Expert Comment

    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.
    LVL 33

    Assisted Solution

    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.

    LVL 2

    Assisted Solution

    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.
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now