Introduction
Mastering Excel TEXT skills is essential for anyone who wants to format dates, numbers, and text dynamically.While Excel has built-in formatting options, the TEXT function gives you precision and flexibility to present numbers exactly the way you need. Whether you are formatting dates, showing percentages, or applying accounting styles, this function makes your spreadsheets more professional, readable, and analysis-ready.
In this article, we’ll cover everything you need to know about the Excel TEXT function. You’ll get a clear explanation, practical use cases, and 20 problems with solutions using a real dataset. By the end, you’ll have hands-on Excel TEXT function practice to apply in your workplace or learning journey.
What is the Excel TEXT Function?
The TEXT function in Excel converts a numeric value into text and allows you to apply custom formatting.
Syntax:
=TEXT(value, format_text)
- value → The number, date, or reference to a cell you want to format.
- format_text → A custom format code wrapped in quotes.
Why Use the TEXT Function?
- Format dates into readable forms (e.g.,
01-01-2025→Jan 1, 2025) - Display percentages, fractions, or scientific notations
- Show currency in Accounting or Financial reports
- Create professional IDs, report codes, or labels
- Ensure numbers are formatted consistently across dashboards
Example Dataset
Here’s the dataset we’ll use for practice:
| Employee | Join_Date | Salary | Bonus | Performance_Score |
|---|---|---|---|---|
| John | 01-01-2020 | 55000 | 0.1 | 88.5 |
| Mary | 15-03-2019 | 72000 | 0.12 | 92.3 |
| Sam | 10-07-2021 | 48000 | 0.08 | 76.2 |
| Rita | 05-11-2020 | 64000 | 0.09 | 81.7 |
| Alex | 22-08-2018 | 85000 | 0.15 | 95.6 |
| Nina | 30-12-2019 | 51000 | 0.07 | 79.1 |
| Tom | 14-02-2022 | 46000 | 0.05 | 74.8 |
| Sara | 19-06-2021 | 69000 | 0.1 | 83.9 |
| David | 25-09-2018 | 88000 | 0.14 | 90.2 |
| Lily | 03-05-2020 | 53000 | 0.09 | 85.4 |
20 Practice Problems to Improve Your Excel TEXT Skills
1. Format Join Date as “January 01, 2020”
Formula:
=TEXT(B2,"mmmm dd, yyyy")
Output: January 01, 2020
2. Show Join Date in “DD-MMM-YY” format
Formula:
=TEXT(B2,"dd-mmm-yy")
Output: 01-Jan-20
3. Display Salary with Commas
Formula:
=TEXT(C2,"#,##0")
Output: 55,000
4. Show Salary as Accounting Style with Dollar Sign
Formula:
=TEXT(C2,"$#,##0.00")
Output: $55,000.00
5. Convert Bonus (decimal) to Percentage
Formula:
=TEXT(D2,"0%")
Output: 10%
6. Show Bonus as Percentage with Two Decimals
Formula:
=TEXT(D2,"0.00%")
Output: 10.00%
7. Round Performance Score to 1 Decimal Place
Formula:
=TEXT(E2,"0.0")
Output: 88.5
8. Create Sentence: “John joined on Jan 01, 2020”
Formula:
=A2 & " joined on " & TEXT(B2,"mmm dd, yyyy")
Output: John joined on Jan 01, 2020
9. Build String: “Salary: $55,000”
Formula:
="Salary: " & TEXT(C2,"$#,##0")
Output: Salary: $55,000
10. Show Salary in Indian Rupee Accounting Format
Formula:
=TEXT(C2,"₹#,##0.00")
Output: ₹55,000.00
11. Combine Name with Bonus % — “John (10%)”
Formula:
=A2 & " (" & TEXT(D2,"0%") & ")"
Output: John (10%)
12. Display Salary with Suffix “USD”
Formula:
=TEXT(C2,"#,##0") & " USD"
Output: 55,000 USD
13. Format Performance Score as Percentage
Formula:
=TEXT(E2/100,"0.0%")
Output: 88.5%
14. Join Salary and Bonus: “$55,000 with 10% bonus”
Formula:
=TEXT(C2,"$#,##0") & " with " & TEXT(D2,"0%") & " bonus"
Output: $55,000 with 10% bonus
15. Format Date as “Monday, January 01, 2020”
Formula:
=TEXT(B2,"dddd, mmmm dd, yyyy")
Output: Wednesday, January 01, 2020
16. Create Custom Code: “JOHN_2020”
Formula:
=UPPER(A2) & "_" & TEXT(B2,"yyyy")
Output: JOHN_2020
17. Show Annual Compensation (Salary + Bonus) Formatted
Formula:
=TEXT(C2*(1+D2),"$#,##0.00")
Output: $60,500.00
18. Combine Name, Salary, Country Style
Formula:
=A2 & ": " & TEXT(C2,"$#,##0.00")
Output: John: $55,000.00
19. Format Bonus as Words: “10 percent”
Formula:
=TEXT(D2,"0%") & " percent"
Output: 10 percent
20. Make Report Line: “[John] – Joined 2020 – Salary $55,000”
Formula:
="[" & A2 & "] - Joined " & TEXT(B2,"yyyy") & " - Salary " & TEXT(C2,"$#,##0")
Output: [John] – Joined 2020 – Salary $55,000
Benefits of Practicing with the TEXT Function
By practicing these 20 problems, you’ll build strong Excel formatting skills:
How to Improve Your Excel TEXT Skills with Practice
- Better report presentation for managers or clients.
- Easier data storytelling with custom sentences.
- More professional dashboards using cleanly formatted values.
Practice File
Conclusion
The Excel TEXT function practice shared here helps you master one of the most flexible tools for formatting data in spreadsheets. Instead of relying only on default cell formatting, you can combine data, create dynamic strings, and ensure consistent presentation across reports.
With these 20 exercises, your Excel TEXT skills will grow stronger and help you stand out in professional reporting.
