Dashboard Mode - TEXT function for formatting
Dashboard Goal
Show how to use the TEXT function in Excel to format numbers, dates, and times for clear reports.
0
0
TEXT function for formatting in Excel - Dashboard Guide
Sample Data
| Item | Price | Sale Date | Sale Time |
|---|---|---|---|
| Notebook | 12.5 | 2024-06-01 | 9:15 |
| Pen | 1.2 | 2024-06-02 | 14:30 |
| Backpack | 45.99 | 2024-06-03 | 16:45 |
| Calculator | 23 | 2024-06-04 | 11:00 |
| Desk Lamp | 18.75 | 2024-06-05 | 19:20 |
Dashboard Components
- Formatted Price (Column E): Use
=TEXT(B2,"$0.00")to show price with dollar sign and two decimals.
Example: 12.5 becomes $12.50 - Formatted Sale Date (Column F): Use
=TEXT(C2,"dddd, mmm dd, yyyy")to show full weekday, short month, day, and year.
Example: 2024-06-01 becomes Saturday, Jun 01, 2024 - Formatted Sale Time (Column G): Use
=TEXT(D2,"h:mm AM/PM")to show time in 12-hour format with AM/PM.
Example: 9:15 becomes 9:15 AM - Total Sales (Cell B8): Use
=SUM(B2:B6)to add all prices.
Result: 101.44 - Total Sales Formatted (Cell C8): Use
=TEXT(B8,"$0.00")to show total with dollar sign and two decimals.
Result: $101.44
Dashboard Layout
+-----------------------------+ | Sample Data | | +-------------------------+ | | | Item | Price | Date |Time| | | |-------------------------| | | | ... (5 rows) | | | +-------------------------+ | | | | Formatted Columns: | | E: Price ($0.00) | | F: Date (dddd, mmm dd, yyyy)| | G: Time (h:mm AM/PM) | | | | Total Sales: B8 | | Total Sales Formatted: C8 | +-----------------------------+
Interactivity
Add a filter on Item to select specific products. When you pick an item, the formatted price, date, and time update to show only that item's data. The total sales and formatted total recalculate for the filtered items.
Self Check
If you filter the dashboard to show only "Pen" and "Calculator", what is the Formatted Total Sales value?
Answer: Pen price = 1.2, Calculator price = 23, total = 24.2, formatted as $24.20.
Key Result
Dashboard demonstrating how to use the TEXT function to format prices, dates, and times in Excel.