# How to Handle Data Formatting in Retool

- Tool: Retool
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Format data in Retool tables by setting the column type (Currency, Date, Percent, Tag) in the Table Inspector. For custom formatting, use moment(currentRow.date).format('MMM D, YYYY') for dates and Number(value).toLocaleString('en-US', {style:'currency', currency:'USD'}) for money. Apply consistent formatting across the app in a transformer before binding.

## Display Raw Data in Human-Readable Formats

Raw data from databases and APIs is rarely display-ready. Timestamps come as ISO strings, prices as raw numbers, booleans as 0/1, and statuses as lowercase codes. Retool provides several layers for formatting: built-in column type settings in the Table Inspector, {{ }} expressions using JavaScript formatting functions, and transformer-based pre-processing.

This tutorial covers the most common formatting scenarios: dates, currencies, percentages, phone numbers, and status labels. You'll learn which layer to use for each case and how to keep formatting consistent across your app.

## Before you start

- A Retool app with a Table component connected to a query
- Query data that includes dates, numbers, or status fields needing formatting
- Basic familiarity with JavaScript string methods and toLocaleString()
- Optional: moment.js knowledge (bundled in Retool)

## Step-by-step guide

### 1. Use Built-in Column Types for Common Formats

Select your Table component and open the Columns section in the Inspector. Click the gear icon on any column to expand its settings. The 'Column type' dropdown provides built-in formatters: String, Number, Currency, Date, Percent, Boolean, Tag, Button, Image, Link.

For a price column, set type to 'Currency' and configure the currency code (USD, EUR, etc.) and number of decimal places. For a created_at column, set type to 'Date' and configure the display format.

**Expected result:** Columns display with appropriate formatting without any custom code.

### 2. Format Dates with moment.js

Retool bundles moment.js globally as moment. Use it in any {{ }} expression to parse and format date strings or timestamps. LDML format tokens are also supported via Retool's built-in date column type, but moment gives you more flexibility for custom formats.

Common moment format strings: 'MMM D, YYYY' → Nov 15, 2024; 'MM/DD/YYYY' → 11/15/2024; 'YYYY-MM-DD' → 2024-11-15; 'h:mm A' → 3:45 PM; 'MMM D, YYYY h:mm A' → Nov 15, 2024 3:45 PM.

```
// In a table column Value expression or a text component:
{{ moment(currentRow.created_at).format('MMM D, YYYY') }}

// Relative time (e.g. '3 days ago'):
{{ moment(currentRow.updated_at).fromNow() }}

// With timezone handling:
{{ moment(currentRow.created_at).utc().local().format('MMM D, YYYY h:mm A') }}

// ISO date to readable:
{{ moment('2024-11-15T15:45:00Z').format('MMMM D, YYYY') }}

// In a transformer:
return data.map(row => ({
  ...row,
  createdFormatted: moment(row.created_at).format('MMM D, YYYY'),
  updatedAgo: moment(row.updated_at).fromNow()
}));
```

**Expected result:** Date columns display in readable formats like 'Nov 15, 2024' instead of raw ISO strings.

### 3. Format Currencies and Numbers with toLocaleString()

For currency and number formatting in text components or table column expressions, JavaScript's native toLocaleString() is clean and versatile. It respects locale-specific formatting (e.g. comma separators in the US, period separators in Europe).

For a built-in currency column in a table, use the Currency column type instead — it is simpler and consistent. Use toLocaleString() for text components and transformer pre-processing.

```
// Currency formatting in a text component or column expression:
{{ Number(currentRow.price).toLocaleString('en-US', {
  style: 'currency',
  currency: 'USD',
  minimumFractionDigits: 2
}) }}
// Output: $1,234.56

// Large number with commas:
{{ Number(currentRow.revenue).toLocaleString('en-US') }}
// Output: 1,234,567

// Percentage:
{{ (currentRow.conversion_rate * 100).toFixed(1) + '%' }}
// Output: 12.5%

// In a transformer for consistent app-wide formatting:
return data.map(row => ({
  ...row,
  priceFormatted: Number(row.price).toLocaleString('en-US', {
    style: 'currency', currency: 'USD'
  }),
  growthFormatted: (row.growth_rate >= 0 ? '+' : '') +
    (row.growth_rate * 100).toFixed(1) + '%'
}));
```

**Expected result:** Numbers display with locale-appropriate separators and currency symbols.

### 4. Format Phone Numbers and Other Structured Strings

Raw phone numbers (e.g. '5551234567') need formatting for display. Use string manipulation in a calculated column or transformer to add formatting. The same approach works for credit card numbers, ZIP codes, or any structured identifier.

Note: only apply display formatting for presentation — always store the raw unformatted value in your database.

```
// Format US phone number (10 digits) in a column expression:
{{ currentRow.phone
  ? currentRow.phone.replace(/^(\d{3})(\d{3})(\d{4})$/, '($1) $2-$3')
  : 'N/A'
}}
// '5551234567' → '(555) 123-4567'

// Format ZIP code (ensure 5 digits with leading zero):
{{ String(currentRow.zip_code).padStart(5, '0') }}
// 1234 → '01234'

// Truncate long text:
{{ currentRow.description?.length > 100
  ? currentRow.description.slice(0, 100) + '...'
  : currentRow.description ?? ''
}}
```

**Expected result:** Structured fields like phone numbers display in standard human-readable formats.

### 5. Use Tag Column Type for Status Labels

For status fields with a small set of values (active/inactive, pending/approved/rejected), use the Tag column type in the Table Inspector. Configure the tag color mapping: select the column, set type to 'Tag', then in the 'Tag color' field use a conditional expression to map values to colors.

This creates colored badge-style indicators without any custom CSS.

```
// Tag column 'Tag color' expression:
{{
  currentRow.status === 'active' ? 'green' :
  currentRow.status === 'pending' ? 'yellow' :
  currentRow.status === 'rejected' ? 'red' : 'gray'
}}

// Available tag colors: red, orange, yellow, green,
// teal, blue, indigo, purple, pink, gray
```

**Expected result:** Status columns display as colored tag badges instead of plain text.

### 6. Apply Consistent Formatting in a Transformer

For consistent formatting across your entire app (not just one table), pre-format values in a transformer and bind all components to the transformer's output. This avoids duplicating format expressions across multiple tables, text components, and charts.

Create a transformer that formats all display fields and returns a new array. All components reference {{ formattedData.value }} instead of {{ query1.data }}.

```
// Transformer: formattedData
// Provides display-ready fields alongside raw values

return (query1.data || []).map(row => ({
  // Keep raw values for writes/calculations
  ...row,

  // Add formatted versions for display
  price_display: Number(row.price).toLocaleString('en-US', {
    style: 'currency', currency: 'USD'
  }),
  created_display: row.created_at
    ? moment(row.created_at).format('MMM D, YYYY')
    : 'Unknown',
  updated_display: row.updated_at
    ? moment(row.updated_at).fromNow()
    : 'Never',
  phone_display: row.phone
    ? row.phone.replace(/^(\d{3})(\d{3})(\d{4})$/, '($1) $2-$3')
    : 'N/A',
  status_display: row.status
    ? row.status.charAt(0).toUpperCase() + row.status.slice(1)
    : 'Unknown',
  growth_display: row.growth_rate != null
    ? (row.growth_rate >= 0 ? '+' : '') +
      (row.growth_rate * 100).toFixed(1) + '%'
    : 'N/A'
}));
```

**Expected result:** All components bound to formattedData.value display consistently formatted values without duplicating format logic.

### 7. Format Booleans as User-Friendly Labels

Boolean fields (true/false or 0/1) often need human-readable labels. In a table column expression, use a ternary to convert booleans to meaningful text. Use the Boolean column type for simple checkmark/cross display, or a Tag column type for colored labels.

```
// Calculated column Value expression:
{{ currentRow.is_verified ? 'Verified' : 'Unverified' }}

// With emoji indicators:
{{ currentRow.is_active ? '✓ Active' : '✗ Inactive' }}

// Boolean column type in Inspector:
// Set column type to 'Boolean' for a checkbox display
// This renders true as a checked checkbox, false as unchecked

// In a transformer for multiple booleans:
return data.map(row => ({
  ...row,
  verifiedLabel: row.is_verified ? 'Verified' : 'Pending',
  activeLabel: row.is_active ? 'Active' : 'Inactive',
  emailConfirmedLabel: row.email_confirmed ? 'Confirmed' : 'Unconfirmed'
}));
```

**Expected result:** Boolean fields display as clear, readable labels rather than 'true'/'false' or 0/1.

## Complete code example

File: `Transformer: formattedUsers`

```javascript
// Transformer: formattedUsers
// Pre-formats all display fields for consistent rendering
// across tables, text components, and charts.
// Bind table: {{ formattedUsers.value }}

const raw = query1.data || [];

return raw.map(row => ({
  // Preserve all raw fields for write operations
  ...row,

  // Dates
  created_display: row.created_at
    ? moment(row.created_at).format('MMM D, YYYY')
    : '—',
  updated_display: row.updated_at
    ? moment(row.updated_at).fromNow()
    : 'Never updated',

  // Numbers and currency
  revenue_display: Number(row.revenue || 0).toLocaleString('en-US', {
    style: 'currency', currency: 'USD', minimumFractionDigits: 0
  }),
  growth_display: row.growth_rate != null
    ? (row.growth_rate >= 0 ? '+' : '') +
      (row.growth_rate * 100).toFixed(1) + '%'
    : '—',

  // Status (capitalize + tag color logic handled in Inspector)
  status_display: row.status
    ? row.status.charAt(0).toUpperCase() + row.status.slice(1).toLowerCase()
    : 'Unknown',

  // Phone
  phone_display: row.phone
    ? String(row.phone).replace(/^(\d{3})(\d{3})(\d{4})$/, '($1) $2-$3')
    : '—',

  // Boolean
  verified_display: row.is_verified ? 'Verified' : 'Unverified',
  active_display: row.is_active ? 'Active' : 'Inactive'
}));
```

## Common mistakes

- **Formatting values in the database query instead of the transformer, making them hard to change later** — undefined Fix: Keep SQL focused on data retrieval and filtering. Do formatting in Retool's transformer layer so you can update display formats without changing database queries.
- **Using moment() directly in a calculated column causing performance issues on large tables** — undefined Fix: Pre-compute formatted date strings in a transformer. Column expressions run for every rendered row — moment() parsing in a 1000-row table means 1000 moment() calls per render.
- **Displaying NaN when a number field is null or undefined** — undefined Fix: Always use Number(value || 0) or parseFloat(value ?? 0) before calling toLocaleString() or toFixed(). Null values passed to Number() return 0, preventing NaN display.
- **Overwriting raw field values with formatted strings in the transformer** — undefined Fix: Add formatted fields alongside raw fields using spread: { ...row, price_display: formatted }. Never replace raw numeric fields with string-formatted versions — you'll lose the ability to use them in calculations or write queries.

## Best practices

- Use built-in column types (Currency, Date, Percent, Tag) for standard formatting — they handle edge cases and are faster to configure than custom expressions.
- Pre-format data in a transformer for app-wide consistency instead of duplicating format expressions across individual table columns and text components.
- Keep raw values in your data alongside formatted display versions — you need the raw value for write operations, comparisons, and calculations.
- Use moment().format() for date formatting but pre-compute it in a transformer rather than calling it per-row in column expressions to avoid repeated parsing overhead.
- Always null-guard format expressions: Number(value || 0), row.field ?? '—', and similar patterns prevent displaying 'NaN' or blank cells.
- For status labels with colors, use the Tag column type and a conditional color expression rather than encoding colors in text.
- Store monetary values as integers (cents) in your database and divide by 100 when displaying to avoid floating-point precision issues.

## Frequently asked questions

### How do I format a date column in a Retool table without writing code?

Click the gear icon on the column in the Table Inspector's Columns section, then set the Column type to 'Date'. A date format field appears where you can enter LDML format strings like 'MMM D, YYYY' or choose from presets. This requires no JavaScript and applies automatically.

### Is moment.js available in Retool without importing it?

Yes. Retool bundles moment.js and it is available globally in all {{ }} expressions and JavaScript code (transformers, JS queries). You do not need an import statement. Use it as moment(value).format('MMM D, YYYY') directly.

### How do I display a number as currency in a Retool text component?

Use JavaScript's toLocaleString() method: {{ Number(query1.data[0].price).toLocaleString('en-US', { style: 'currency', currency: 'USD' }) }}. For tables, use the built-in Currency column type which handles the formatting automatically without code.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-handle-data-formatting-in-retool
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-handle-data-formatting-in-retool