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.