TEXT function for formatting in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TEXT function do in Excel?

The TEXT function changes a number or date into text in a specific format you choose. It helps show numbers or dates exactly how you want them to look.

beginner

Write the formula to show the date in cell A1 as "March 15, 2024" using the TEXT function.

Use =TEXT(A1, "mmmm d, yyyy") to format the date as full month name, day, and full year.

beginner

How do you format a number with two decimal places and a dollar sign using TEXT?

Use =TEXT(number, "$0.00") to show the number with a dollar sign and exactly two decimals.

intermediate

Why would you use the TEXT function instead of just changing cell format?

TEXT converts the value to text with the format you want, so you can combine it with other text or formulas. Changing cell format only changes how it looks, not the actual value.

intermediate

What happens if you use TEXT on a number but give an invalid format code?

Excel will show an error like #VALUE! because the format code must be correct for TEXT to work.

Which formula correctly formats the number 1234.567 as "$1,234.57"?

A =TEXT(1234.567, "#,##0")

B =TEXT(1234.567, "0.00")

C =TEXT(1234.567, "$0")

D =TEXT(1234.567, "$#,##0.00")

Option D uses the correct format with dollar sign, comma for thousands, and two decimals.

What does =TEXT(TODAY(), "dd/mm/yyyy") show?

A The date in American format month/day/year

B The current date as day/month/year

C An error because TODAY() is not a number

D The current time

TEXT formats the date from TODAY() as day/month/year as specified.

If you want to show a number with a leading zero (e.g., 007), which format code should you use?

A "000"

B "#"

C "0.00"

D "@"

Using "000" forces the number to show three digits with leading zeros.

What type of value does the TEXT function return?

A Text

B Date

C Number

D Boolean

TEXT always returns a text string, even if the input is a number or date.

Which of these is NOT a valid format code for the TEXT function?

A "mm/dd/yyyy"

B "$#,##0.00"

C "#-#-#"

D "0.00"

Option C is not a standard format code and will cause an error.

Explain how the TEXT function can be used to format dates and numbers in Excel.

Think about how you want numbers or dates to look in your spreadsheet.

Describe a situation where using TEXT is better than just changing the cell format.

When you want to join formatted numbers with words in one cell.