TEXT function for formatting in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that shows sales dates and amounts in a clear, easy-to-read format for a presentation.

📊 Data: You have raw sales data with dates in default Excel date format and sales amounts as numbers with many decimals.

🎯 Deliverable: Create a new table that formats the sales dates as 'Month Day, Year' (e.g., January 5, 2024) and sales amounts as currency with two decimals (e.g., $1,234.56).

Sample Data

Sale ID	Sale Date	Sale Amount
101	44561	1234.567
102	44562	987.4
103	44563	1500
104	44564	234.5
105	44565	765.89
106	44566	4321.1
107	44567	678.234
108	44568	890.5

1

Step 1: Insert a new column next to 'Sale Date' and label it 'Formatted Date'.

Expected Result

A new empty column ready for formatted dates.

2

Step 2: In the first cell under 'Formatted Date', enter the formula to convert the date number to a readable date format 'Month Day, Year'.

=TEXT(B2, "mmmm d, yyyy")

Expected Result

For Sale Date 44561, the result is 'January 1, 2022'.

3

Step 3: Copy the formula down the 'Formatted Date' column for all rows.

Drag the fill handle from the first formula cell down to the last row.

Expected Result

All sale dates are shown as 'Month Day, Year' format.

4

Step 4: Insert a new column next to 'Sale Amount' and label it 'Formatted Amount'.

Expected Result

A new empty column ready for formatted amounts.

5

Step 5: In the first cell under 'Formatted Amount', enter the formula to format the number as currency with two decimals.

=TEXT(C2, "$#,##0.00")

Expected Result

For Sale Amount 1234.567, the result is '$1,234.57'.

6

Step 6: Copy the formula down the 'Formatted Amount' column for all rows.

Drag the fill handle from the first formula cell down to the last row.

Expected Result

All sale amounts are shown as currency with two decimals.

Final Result

Sale ID | Sale Date | Formatted Date    | Sale Amount | Formatted Amount
--------|-----------|-------------------|-------------|-----------------
101     | 44561     | January 1, 2022   | 1234.567    | $1,234.57       
102     | 44562     | January 2, 2022   | 987.4       | $987.40         
103     | 44563     | January 3, 2022   | 1500        | $1,500.00       
104     | 44564     | January 4, 2022   | 234.5       | $234.50         
105     | 44565     | January 5, 2022   | 765.89      | $765.89         
106     | 44566     | January 6, 2022   | 4321.1      | $4,321.10       
107     | 44567     | January 7, 2022   | 678.234     | $678.23         
108     | 44568     | January 8, 2022   | 890.5       | $890.50         

✓Dates are now easy to read as full month names with day and year.

✓Sales amounts are clearly shown as currency with two decimals.

✓The report is ready for presentation with professional formatting.

Bonus Challenge

Format the 'Sale Amount' to show negative amounts in red color with parentheses, e.g., ($123.45).

Show Hint

Use the TEXT function with a format code like "$#,##0.00;[Red](\$#,##0.00)" to handle positive and negative numbers.

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