Excel Cell Formatting: How to Test It

How-to basics · Updated November 18, 2026

If A2 is formatted as 0.00%, enter =CELL("format",A2)="P2" in another cell to test its number format; Excel returns TRUE when the format matches. The CELL function can identify some common number-format categories, but it cannot reliably test every detail of a cell’s appearance, such as its font, fill color, borders, or conditional formatting.

What “testing cell formatting” can mean

Formatting controls how Excel displays a value, not necessarily what the value is. A cell containing the number 0.25 might display as 0.25, 25%, or $0.25 depending on its number format. A test can therefore mean checking the number format, checking a visual property such as bold text, or checking what a conditional formatting rule makes the cell look like.

Those are different questions, and Excel offers different ways to answer them. A worksheet formula can test some number-format categories. Excel’s Format Cells dialog lets you inspect the underlying format directly. For properties such as fill color or bold, Visual Basic for Applications (VBA) can read formatting values. Conditional formatting needs extra care because the displayed appearance can differ from the cell’s ordinary, underlying format.

Use CELL for common number formats

The CELL function returns information about a referenced cell. Its format option reports a short code for certain built-in number formats. For example, with a percentage in A2 formatted to show two decimal places as 0.00%, the formula below returns TRUE:

With A2 formatted as 0.00%, this formula tests for the two-decimal percentage category:

=CELL("format",A2)="P2"

The formula compares CELL’s result with the code P2. If the cell’s format is changed to a different category, the comparison returns FALSE. This tests the format category Excel reports; it does not test the value in A2 or prove that A2 contains a meaningful percentage.

Some other familiar CELL format codes include:

  • G for General.
  • F0 for a number displayed with zero decimal places, such as 12.
  • F2 for a number displayed with two decimal places, such as 12.50.
  • P0 and P2 for percentage formats with zero and two decimal places.
  • C0 and C2 for certain currency formats with zero and two decimal places.
  • Date and time formats may return codes beginning with D, such as D1 or D4.

Codes describe recognized format categories, not every character in a custom format. The exact code can depend on the particular format Excel recognizes, so check the result in your workbook rather than assuming every custom format has a predictable code.

See the code before building a test

If you are unsure which code a cell returns, display it directly instead of comparing it straight away. With the cell you want to inspect in A2, enter:

=CELL("format",A2)

The result is a short text code, such as P2 for a recognized two-decimal percentage format. Once you know the returned text, compare it with that code in a separate formula. This two-step approach helps distinguish a mistaken assumption about the format code from an error in the comparison.

For example, if a cell displays 25.00%, the underlying value might be 0.25, but the display alone does not establish the exact format. CELL can identify a recognized percentage category; it does not tell you whether a custom format adds a label, uses unusual separators, or changes the display for negative values.

Where CELL’s formatting test stops

CELL’s format result is deliberately limited. It does not provide a complete description of the cell’s formatting, so it is not a general-purpose “is this cell formatted exactly like that one?” test. It cannot report all font settings, border styles, alignment choices, fill patterns, or custom number-format strings.

It also does not reliably tell you what a conditional formatting rule currently displays. For instance, a cell may have a normal number format but turn red when a rule is true. That red appearance comes from the conditional formatting rule; it is not the same as the cell’s ordinary fill format. A formula using CELL should not be treated as a test of that visual result.

Finally, formatting and data type are separate. A value formatted as a date is still stored as a number in Excel. If the real question is whether a cell contains a number, date-like value, or text, use a value or data-type check rather than relying on its number format. A format test answers how Excel is set to display a value, not what kind of information the value represents.

Inspect the exact format in Excel

For a one-off check in Excel for Microsoft 365 on Windows, select the cell and choose Home > Number, then open the Number Format list. For more detail, open the Format Cells dialog using Home > Number and its dialog box launcher, or press Ctrl+1. The Number tab shows the category and, for custom formats, the format code.

To see whether a cell is bold, what font it uses, or how its borders and fill are set, use the Font, Border, and Fill tabs in Format Cells. You can also examine the corresponding controls on the Home tab. The dialog is usually the clearest option when you need to identify a specific setting or edit it.

On Excel for Mac, use Format > Cells to open the formatting dialog; the ribbon and keyboard shortcuts can differ from Windows. Excel for the web includes common formatting controls, but some detailed inspection and editing options may be more limited than in the desktop app. If you cannot find a specific setting in the web interface, open the workbook in desktop Excel.

Test formatting across a range

A CELL formula can be filled down to check a column one row at a time. For example, if A2:A20 contains cells whose formats you want to inspect, place a formula that refers to A2 in an adjacent column and copy it down. Each row then compares its own cell with the chosen category. This is useful for spotting an outlier in a column, but it only flags the categories CELL can report.

For a quick visual audit, select the range and inspect its number format using the ribbon or Format Cells dialog. Keep in mind that a mixed selection may contain more than one format; Excel may show a blank or a mixed state for some controls rather than a single setting. Inspect individual cells when the distinction matters.

Another option is to copy a correctly formatted cell and use Home > Paste > Paste Special > Formats to apply its formatting elsewhere. This is a repair method, not a reliable test: it can overwrite formatting you intended to keep. For a deliberate audit, compare settings first and apply changes only after confirming the target range.

Inspect font, fill, and displayed formatting with VBA

When you need to inspect properties that CELL cannot report, a short VBA macro can show the underlying number format, font, and fill for a selected address. This example reports the base settings and the formatting displayed after conditional formatting is applied. It reads A1 on the active worksheet; change the address if needed.

Before running any macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z.

Sub ReportCellFormatting()
    Dim target As Range
    Dim details As String

    ' Inspect A1 on the active worksheet.
    Set target = ActiveSheet.Range("A1")

    details = "Base number format: " & target.NumberFormat & vbCrLf _
        & "Displayed number format: " & target.DisplayFormat.NumberFormat & vbCrLf _
        & "Base font bold: " & CStr(target.Font.Bold) & vbCrLf _
        & "Displayed font bold: " & CStr(target.DisplayFormat.Font.Bold) & vbCrLf _
        & "Base fill color value: " & CStr(target.Interior.Color) & vbCrLf _
        & "Displayed fill color value: " & CStr(target.DisplayFormat.Interior.Color)

    MsgBox details, vbInformation, "Cell formatting"
End Sub

In VBA, NumberFormat, Font, and Interior describe ordinary cell formatting. DisplayFormat reflects formatting as displayed, including applicable conditional formatting. DisplayFormat is intended for use in a macro, not as a way to create a worksheet user-defined function that recalculates from formatting changes.

Common reasons a format test surprises you

  • The code is not the one you expected. CELL reports a category code, not necessarily the custom format string you see in Format Cells. Display the code first, then compare it.
  • The cell looks like a date but is not formatted as one. Excel stores dates as serial numbers. A plain number format can show the serial value, while a date format displays a calendar date.
  • The cell looks red, but the base fill is not red. A conditional formatting rule may supply the visible color. Inspect the rule or use VBA’s DisplayFormat when you need the displayed result.
  • A formula result seems stale. Recalculate the workbook with Formulas > Calculate Now or press F9 on Windows. If calculation is set to Manual, formula results may not update until recalculation.
  • The test returns TRUE but the data is still wrong. Matching a number format does not validate the cell’s contents. Check the value separately if the goal is data validation.

Frequently Asked Questions

Can an Excel formula check whether a cell is bold?
No standard worksheet function directly tests a cell’s bold setting. Use the Format Cells dialog for a manual check or VBA to read the cell’s Font.Bold property.

Can CELL check a cell’s background color?
No. CELL’s format option reports certain number-format categories, not fill color. Inspect the Fill tab in Format Cells or use VBA’s Interior.Color for the base fill.

Does CELL detect conditional formatting?
Do not use CELL as a dependable test of the formatting produced by a conditional formatting rule. Check the rule under Home > Conditional Formatting > Manage Rules, or use VBA’s DisplayFormat property in a macro to inspect the displayed result.

How do I check a custom number format exactly?
Select the cell and open Format Cells with Ctrl+1 on Windows, then inspect the Number tab’s Custom category and format code. CELL does not return a complete custom-format string.

Can formatting reveal whether a value is a date?
Not by itself. A date display is controlled by number formatting, while Excel stores dates as numbers. Check both the underlying value and the cell’s number format when the distinction matters.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.