How to Count Cells with Bold Formatting in Excel

Excel users often struggle to isolate manually formatted data, particularly when trying to count cells with bold text. While standard funding sources of spreadsheet logic-such as COUNTIF or standard built-in functions-only recognize raw data values, they fail to detect visual formatting. Leveraging a custom User Defined Function (UDF) grants users the capability to bypass this limitation instantly.

The stipulation, however, is that your workbook must be saved in a macro-enabled format (.xlsm). This approach is highly effective in concrete scenarios like financial reporting audits, where key metrics are bolded manually for emphasis.

Below, we outline the exact VBA code and installation steps to implement this solution.

Excel is an incredibly powerful tool for data analysis, but it has a well-known limitation: its native formula engine is designed to analyze values, not formatting. Standard functions like COUNTIF, SUMIF, or AVERAGE cannot natively see whether a cell's text is bold, italicized, or colored.

However, users frequently use bold formatting to highlight key data points, totals, or pending actions. If you find yourself needing to count the number of cells with bold text formatting, you aren't out of luck. In this comprehensive guide, we will explore three distinct methods to achieve this: using a custom VBA User-Defined Function (UDF), utilizing Excel's legacy XLM macro 4.0 system, and leveraging the manual "Find and Select" feature for quick counts.

Method 1: The VBA User-Defined Function (The Most Robust Solution)

The most reliable and automated way to count bold cells in Excel is by creating a custom formula using Visual Basic for Applications (VBA). This allows you to build a custom function-such as =COUNTBOLD(A1:A10)-that behaves just like a native Excel formula.

Why Use VBA?

VBA can access the deep properties of a cell, specifically the Font.Bold attribute. Furthermore, by utilizing Excel's DisplayFormat property, VBA can detect bold formatting whether it was applied manually or dynamically generated via Conditional Formatting.

Step-by-Step Implementation

1. Open your Excel workbook.
2. Press ALT + F11 to open the VBA Editor.
3. In the menu bar, click Insert > Module. This will open a blank code window.
4. Copy and paste the following VBA code into the module window:

Function CountBold(WorkRng As Range) As Long
    Dim Rng As Range
    Dim Count As Long
    Count = 0
    
    ' Loop through each cell in the specified range
    For Each Rng In WorkRng
        ' DisplayFormat ensures we catch manually bolded cells AND conditionally formatted bold cells
        If Rng.DisplayFormat.Font.Bold = True Then
            Count = Count + 1
        End If
    Next Rng
    
    CountBold = Count
End Function

5. Close the VBA Editor and return to your Excel worksheet.

How to Use Your New Formula

Now that the code is installed, you can use it just like any other formula. Select an empty cell and type:

=CountBold(A1:A20)

This formula will scan the range A1:A20 and return the exact number of cells containing bold text formatting.

A Note on Recalculation

Excel does not consider changing a cell's formatting (like applying or removing bold) to be a "data change." Therefore, manual formatting changes will not instantly trigger your formula to recalculate. To force the formula to update after changing text formatting, press F9 to recalculate the sheet, or make a change to any value in your spreadsheet.

Method 2: The Legacy XLM Macro 4.0 Trick (No VBA Code Required)

If you cannot or do not want to write VBA modules, you can use a hidden, legacy feature of Excel known as XLM Macros. This method relies on Excel's old macro engine from the 1990s, which is still supported for backwards compatibility. We can use it to create a dynamic "Named Range" that reads formatting properties.

Step-by-Step Implementation

Assume your list of text is in column A, starting at A2, and you want to flag bold items in column B.

1. Select cell B2 (it is vital that you select the cell directly next to your first data point).
2. Navigate to the Formulas tab on the Ribbon.
3. Click on Define Name.
4. In the dialog box, configure the following settings:
   • Name: IsBold
   • Scope: Workbook
   • Refers to: =GET.CELL(20, A2)
5. Click OK.

Note on the formula: The number 20 is the specific code for the "font bold" attribute in XLM. Because we used a relative reference (A2) while selecting cell B2, the named range will always check the cell immediately to its left.

Applying the Trick to Count Bold Cells

1. In cell B2, enter the formula: =IsBold
2. Drag this formula down column B alongside your data.
3. The formula will return TRUE if the cell to the left is bold, and FALSE if it is not.
4. Now, to count the bold cells, use a simple COUNTIF formula at the bottom of your sheet:

   =COUNTIF(B2:B20, TRUE)

Just like standard VBA macro workbooks, files using the GET.CELL named range must be saved in the .xlsm format to work when reopened.

Method 3: The "Find & Select" Feature (No Formulas, Quick & Easy)

If you only need to count bold cells as a one-time task and do not require a dynamic formula that automatically updates, Excel's built-in Find & Select tool is the easiest and fastest way to get an instant count.

Step-by-Step Instructions

1. Select the range of cells you want to analyze (e.g., column A).
2. Press CTRL + F to open the Find and Replace dialog box.
3. Click the Options >> button on the right to expand the dialog box.
4. Next to the "Find what" search field, click on the Format... button. (Do not type anything into the text box).
5. In the Find Format dialog box, go to the Font tab.
6. Under Font style, select Bold. Click OK.
7. Click Find All.

Excel will populate a list of all bold cells within your range at the bottom of the dialog box. Look at the very bottom-left corner of the Find and Replace window; it will display a status message such as "14 cell(s) found". There is your total count!

Summary: Which Method Should You Choose?

A Best Practice Recommendation: Use Status Columns

While the solutions outlined above work perfectly, relying on visual formatting (like bold text) to carry data meaning can lead to mistakes. If you are designing a new spreadsheet, the best practice is to use a dedicated "Status" or "Flag" column.

For example, instead of bolding urgent tasks, create an "Urgency" column with values like "High" or "Low". You can then easily use standard Excel formulas like =COUNTIF(B2:B20, "High"), and use Conditional Formatting to automatically bold those rows based on the column value. This keeps your formulas clean, standardizes your data, and eliminates the need for macros entirely.