How to Use an Excel Function to Format Data

Save

Microsoft Excel comes with a TEXT function which allows you to change the number format of a cell. Its syntax is "TEXT(value, format)," where "value" can be a formula, name or cell reference. The TEXT function supports a wide variety of text formats. It allows you to create dynamic strings and sentences for better data presentation. What the TEXT function cannot do, however, is change colors or add borders. This is possible only through the use of conditional formatting.

Basic Usage

  • Open your workbook and highlight the cell that you'd like to edit.

  • Go to the Formula bar and enter the formula below. Replace "value" with a number, a cell reference or a formula. To replace it with a cell reference, highlight "value" in your Formula bar, and click the cell to which you'd like the function to point.

    =TEXT(value,format)

  • Replace "format" in your formula with the number format you'd like to apply to your data (see Resources for a list of acceptable format strings). Be sure to enclose it in quotation marks.

More Ways to Use the TEXT Function

  • Prefix your values with appropriate currency symbols. To do this, enter your preferred currency symbol at the beginning of the format string. For example, the following formula outputs "$25.00."

    =TEXT(25, "$0.00")

  • Enclose a comma sign within pound symbols to display the thousands separator. The following example outputs "12,200,000."

    =TEXT(12200000, "#,#")

  • Show decimal places and round off values using the "0" (zero) placeholder. The following examples will output "25.567," "25.6" and "26" respectively.

    =TEXT(25.567, "0.000")

    =TEXT(25.567, "0.0")

    =TEXT(25.567, "0")

  • Convert long numbers to shorthand format by using the comma symbol. The following examples will output "12.5k" and "12.6m" respectively.

    =TEXT(12500, "#.0,"&"k")

    =TEXT(12500000, "#.0,"&"\m")

  • Show fractions and pound signs using the forward slash ("/"). For example, the following formula will show "2 3/5" and "13/5" respectively.

    =TEXT(2.6, "# #/#")

    =TEXT(2.6, "#/#")

Tips & Warnings

  • Precede reserved letters and symbols with the backslash character ("\") in order to explicitly show them.
  • Combine the TEXT function with words and strings to create dynamic sentences. Assuming that "A1" contains your date of birth, the following formula will tell you how old you are:
  • ="You are now " & TEXT(TODAY()-A1, "y") & " years old"

References

  • Photo Credit Jupiterimages/Photos.com/Getty Images
Promoted By Zergnet

Comments

Resources

You May Also Like

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!