Determining If a Cell is Bold

Written by Allen Wyatt (last updated September 20, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


6

Ken wonders if there is a worksheet function that will indicate whether the contents of a cell are bold. He can find other informational functions, such as ISBLANK, but cannot find one that will indicate if the cell is bold.

There is no ISBOLD function built into Excel. There is a very arcane way to do this without resorting to a macro, but it only works with some versions of Excel. Apparently, for example, this approach won't work with Office 365, as it appears that Microsoft has finally removed support for it. This old Excel 4 function, called GET.CELL, will work with some older versions of Excel. Here is how you would use it in a formula:

=IF(GET.CELL(20,A1), "Bold", "Not Bold")

The GET.CELL function returns True if at least the first character in the cell is bold.

A better approach would be to create a User-Defined Function in VBA that could be called from your worksheet. Here's a simple version of such a UDF:

Function CheckBold(cell As Range) As Boolean
    Application.Volatile
    CheckBold = cell.Font.Bold
End Function

In order to use it in your worksheet, you would do so in this manner:

=IF(CheckBold(A1), "Bold", "Not Bold")

The CheckBold function will only update when your worksheet is recalculated, not if you simply apply bold formatting to or remove it from cell A1.

This approach can work for most instances but understand that the Bold property can actually have three possible settings—True, False, and Null. The property is set to False if none of the characters in the cell are bold. It is set to True if they are all bold. Finally, it is set to Null if only some of the characters in the cell are bold. If you think you might run into this situation, then you'll need to modify the CheckBold function:

Function CheckBold(cell As Range) As Integer
    Dim iBold As Integer

    Application.Volatile

    iBold = 0
    If IsNull(cell.Font.Bold) Then
        iBold = 2
    Else
        If cell.Font.Bold Then iBold = 1
    End If

    CheckBold = iBold
End Function

Note that the function now returns a value, 0 through 2. If it returns 0, there is no bold in the cell. If it returns 1, then the entire cell is bold. If it returns 2, then there is partial bold in the cell.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13733) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating a Table of Contents from TOC Fields

If you inserted a bunch of TOC fields in your document, you can create your table of contents quite easily based on those ...

Discover More

Selecting Text in Linked Text Boxes

Text boxes are often used as design elements in a document layout. If you have linked text boxes, you may have noticed ...

Discover More

Too Many Edits Prevent Pasting

Sometimes Word can be rather cryptic in the error messages it provides. One such cryptic message warns about "too many ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Professional Looking Fractions

Professional typesetting has, in many ways, spoiled us. One way this is evident is in the preference we show for making ...

Discover More

Accurate Font Sizes

Need to use some bizarre font size in your worksheet? Not a problem, provided it is a full or half point size.

Discover More

Converting Dates to Text

Need to use a date as part of a larger string of text? Here are some handy ways to go about the process.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 9?

2020-10-11 12:04:18

Kell Vagtholm

Hi
Is it possible to create a cell formulat like =SUM(A2:A8), including some kind of check so that only cell values in Bold will be summed?
Best regards,
Kell Vagtholm


2020-04-14 09:26:58

R Monat

Correction :
...The "$" before "B" prevents the column from indexing...
Addition:
Cell B8 is evaluated for whether it is bold or not.


2020-04-14 09:23:32

R Monat

It works in Office365 by setting the get.cell function to the range definition (make sure you have selected the correct cell to start with). This image shows the function being used when cell D8 is selected on sheet "ProjectSchedule". The "$" before "D" prevents the column from indexing and the absence of the "$" before the "8" allows the row to index. (see Figure 1 below)

Figure 1. Named Range Definition


2020-02-04 13:45:54

J. Woolley

@Roy
Here is an interesting reference regarding your point about the use of Excel 4 Macro Functions: https://exceloffthegrid.com/using-excel-4-macro-functions/


2020-02-03 16:03:20

Roy

It works just fine in Office 365 (Version 1912, Build 12325.20344 Click-to-Run).

To use Excel 4's macro language functions, you must put the actual formula that uses them in a Named Range. Usually you'd put just the portion that uses one of those functions and use the Named Range appropriately in the spreadsheet-side formula (what you enter in the cells themselves), but this one does only the one thing in the above formula, so why not put the whole thing into the Named Range?

In any case, perhaps your Named Range is "horsey". If soi, you'd put Mr. Wyatt's formula (which works nicely done properly here) into the Named Range, then in the cell you wish to have the result, place "=horsey" and you're done.

Naturally, if you don't use $A$1, but rather the A1 (no dollar signs) used above, you had best choose carefully what cell you have selected when you create the Named Range. Relative referenceing is awesome, but a stonecold ***** when you forget about using it...

Remember, the key is that the ACTUAL Excel 4 macro functions HAVE TO BE in a Named Range. They WILL NOT WORK cell-side. You put them in Named Ranges, then use the Named Ranges cell-side.

EVALUATE() gets the most press, but all the GET.xxx() functions can be hideously useful. There are at least a half dozen others of interest. Even those that seem in the modern set of functions sometimes behave differently in meaningful ways. Which tweaks my mind to check about a problem I currently have with a "modern" function.

Some evidence suggests that MS can NEVER ditch these functions no matter how much they suggest it could happen anytime (which they have been saying for 25 years...). A fairly complete rewrite of Excel would be one instance of how they could do so someday but unless the mobile version of Exxcel is a complete rewrite, hence it big differences from the desktop program, maybe the MOBILE version cannot use these anymore. Maybe. My bet is it still can and will continue to be able to for decades. 'Cause MS doesn't seem... into... the idea of a complete, modernizing, rewrite.


2020-02-01 15:49:26

John Mann

I tried the GET.CELL function in Excel 10, and it didn't work, got "That function is not valid"


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.