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.

Practice

1. What does the Excel TEXT function do?

easy

A. It deletes all formatting from a cell.

B. It calculates the sum of numbers in a range.

C. It finds the largest number in a list.

D. It changes how numbers or dates look by turning them into styled text.

Solution

1. Step 1: Understand the purpose of TEXT function

   The TEXT function converts numbers or dates into text with a specific format you choose.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions unrelated to TEXT.

3. Final Answer:

   It changes how numbers or dates look by turning them into styled text. -> Option D

4. Quick Check:

   TEXT formats numbers/dates as text [OK]

Hint: TEXT formats numbers/dates as text with style [OK]

Common Mistakes:

• Thinking TEXT calculates or sums numbers
• Confusing TEXT with formatting removal
• Assuming TEXT changes the actual number value

2. Which of these is the correct syntax to format the number in cell A1 as currency with two decimals using TEXT?

easy

A. =TEXT(A1, "dd/mm/yyyy")

B. =TEXT(A1, "$#,##0.00")

C. =TEXT(A1, "0.00%")

D. =TEXT(A1, "General")

Solution

1. Step 1: Identify currency format pattern

   "$#,##0.00" formats numbers as currency with commas and two decimals.

2. Step 2: Check other formats

   A uses "dd/mm/yyyy" for date, B uses "0.00%" for percentage, D uses "General" for general text, so only C fits currency formatting.

3. Final Answer:

   =TEXT(A1, "$#,##0.00") -> Option B

4. Quick Check:

   Currency format uses "$#,##0.00" [OK]

Hint: Use "$#,##0.00" inside TEXT for currency [OK]

Common Mistakes:

• Using percentage or date formats for currency
• Forgetting the $ sign inside quotes
• Missing commas for thousands separator

3. Given cell A1 contains the date 2024-06-15, what will the formula =TEXT(A1, "dddd, mmmm dd, yyyy") display?

medium

A. Saturday, June 15, 2024

B. 15/06/2024

C. 06/15/2024

D. 2024-06-15

Solution

1. Step 1: Understand the format string

   "dddd" shows full weekday name, "mmmm" full month name, "dd" day with two digits, "yyyy" full year.

2. Step 2: Apply to date 2024-06-15

   June 15, 2024 is a Saturday, so the output is "Saturday, June 15, 2024".

3. Final Answer:

   Saturday, June 15, 2024 -> Option A

4. Quick Check:

   Format "dddd, mmmm dd, yyyy" = Saturday, June 15, 2024 [OK]

Hint: dddd = weekday, mmmm = full month name [OK]

Common Mistakes:

• Confusing day/month order
• Using short month/day codes
• Ignoring weekday name format

4. You want to format the number 0.85 in cell B2 as a percentage with one decimal place using TEXT. Which formula?

medium

A. =TEXT(B2*100, "0.0%")

B. =TEXT(B2, "0.0")

C. =TEXT(B2, "0.0%")

D. =TEXT(B2, "0.0%%")

Solution

1. Step 1: Understand percentage formatting in TEXT

   In TEXT, the format "0.0%" automatically multiplies the number by 100 and adds the % sign, so TEXT(0.85, "0.0%") displays "85.0%".

2. Step 2: Compare other options

   A multiplies by 100 first (85), then "0.0%" makes "8500.0%"; B shows "0.9"; D with "0.0%%" shows "0.9%" (%% is literal % without scaling).

3. Final Answer:

   =TEXT(B2, "0.0%") -> Option C

4. Quick Check:

   TEXT(0.85, "0.0%") = "85.0%" [OK]

Hint: Use "0.0%" to convert decimal to formatted percentage [OK]

Common Mistakes:

• Multiplying by 100 manually (over-formats to 8500%)
• Omitting % (shows plain decimal "0.9")
• Using %% (prevents scaling, shows "0.9%")

5. You have a list of prices in column A and want to create a new column B that shows prices formatted as dollars with commas and two decimals, but also adds the text " each" after the price. Which formula in B1 correctly does this?

hard

A. =TEXT(A1, "$#,##0.00") & " each"

B. =TEXT(A1 & " each", "$#,##0.00")

C. =TEXT(A1, "$#,##0.00 each")

D. =TEXT(A1, "$#,##0.00") + " each"

Solution

1. Step 1: Format price as currency text

   Use TEXT(A1, "$#,##0.00") to format number with $ sign, commas, and two decimals.

2. Step 2: Add text " each" after formatted price

   Concatenate with & operator: TEXT(...) & " each" adds the text after the formatted number.

3. Final Answer:

   =TEXT(A1, "$#,##0.00") & " each" -> Option A

4. Quick Check:

   Concatenate formatted text with & and string [OK]

Hint: Use & to join formatted text and extra words [OK]

Common Mistakes:

• Trying to format number and text together inside TEXT
• Using + instead of & for text concatenation
• Putting text inside format string incorrectly