FIXED

Text Functions

(4.6/5)

Formats a number as text with a fixed number of decimal places, with or without thousands separators. Essential for formatting numbers for display, reports, and consistent number formatting in Excel.

Interactive Formula Tester

Number:

Formula:

=FIXED("1234.567")

Complete Theory & Understanding

Master the fundamentals of Excel FIXED function

Core Concept

The FIXED function formats numbers as text with a fixed number of decimal places and optional thousands separators. It's essential for consistent number formatting in reports, displays, and when you need text representation of numbers with specific formatting.

Why Use FIXED?

Format numbers consistently in reports
Ensure consistent number display
Combine formatted numbers with text
Format numbers for presentations

Key Characteristics

Text Output

Returns formatted text, not a number

FIXED(1234.56) returns "1,234.56"

Fixed Decimals

Controls decimal places precisely

FIXED(1234.567, 2) = "1,234.57"

Thousands Separator

Includes comma separator by default

FIXED(1234) = "1,234.00"

Rounding

Rounds to specified decimal places

FIXED(1234.567, 2) rounds to 1234.57

Function Anatomy

=FIXED(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Report Formatting

Format numbers consistently in reports

Display Consistency

Ensure consistent number display

Text Concatenation

Combine formatted numbers with text

Data Presentation

Format numbers for presentations

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=FIXED(number, decimals, no_commas)

Required

number:

The number to format as text

Optional

decimals:

The number of digits to the right of the decimal point (default: 2)

Optional

no_commas:

TRUE to omit thousands separators, FALSE to include them (default: FALSE)

Returns

Return Value:

Text string formatted with fixed decimals

Description: Formats a number as text with fixed decimal places and optional thousands separators

Interactive Examples

Basic Fixed Format

Format number with fixed decimals

Input Text:

"1234.567"

Formula:

=FIXED(1234.567)

Result:

1,234.57

Formats with 2 decimal places (default) and thousands separator

VBA Implementation & Automation

Basic FIXED in VBA

Simple VBA implementation of FIXED function

' Basic FIXED in VBA
Range("B1").Value = Application.WorksheetFunction.Fixed(Range("A1").Value)
' Formats number in A1 with 2 decimals

' Using VBA Fixed function
Dim numValue As Double
numValue = 1234.567
Range("B2").Value = Application.WorksheetFunction.Fixed(numValue)
' Returns: "1,234.57"

' Format with zero decimals
Sub FormatFixedZero()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If IsNumeric(cell.Value) Then
            cell.Offset(0, 1).Value = Application.WorksheetFunction.Fixed(cell.Value, 0)
        End If
    Next cell
End Sub

' Format without commas
Sub FormatFixedNoCommas()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If IsNumeric(cell.Value) Then
            cell.Offset(0, 1).Value = Application.WorksheetFunction.Fixed(cell.Value, 2, True)
        End If
    Next cell
End Sub

Advanced Number Formatting

Custom formatting and validation

' Format with validation
Function FormatFixedSafe(value As Variant, Optional decimals As Integer = 2, Optional noCommas As Boolean = False) As String
    If IsNumeric(value) Then
        FormatFixedSafe = Application.WorksheetFunction.Fixed(CDbl(value), decimals, noCommas)
    Else
        FormatFixedSafe = "0.00"
    End If
End Function

' Format range with consistent decimals
Sub FormatRangeFixed()
    Dim cell As Range
    Dim decimals As Integer
    decimals = 2
    For Each cell In Range("A1:A100")
        If IsNumeric(cell.Value) And cell.Value <> "" Then
            cell.Offset(0, 1).Value = Application.WorksheetFunction.Fixed(cell.Value, decimals)
        End If
    Next cell
End Sub

' Compare FIXED vs TEXT formatting
Sub CompareFormatting()
    Range("A1").Value = 1234.567
    Range("B1").Formula = "=FIXED(A1, 2)"
    Range("C1").Formula = "=TEXT(A1, "#,##0.00")"
    ' Both format similarly, but FIXED is simpler
End Sub