# Excel question for the experts

**URL:** <https://boards.straightdope.com/t/excel-question-for-the-experts/523763>\
**Category:** Factual Questions\
**Created:** [January 5, 2010, 4:45pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763 "2010-01-05T16:45:26Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Parthol](https://avatars.discourse-cdn.com/v4/letter/p/dec6dc/32.png) [@Parthol](https://boards.straightdope.com/u/Parthol)\
**Post date:** [January 5, 2010, 4:45pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/1 "2010-01-05T16:45:26Z")

</div>

Hi there!

Does anyone here know if it’s possible to do the following in Excel?

I have a column (or row) of numbers, some of which are formatted in **bold.**

I want to count the number of cells in that row (or column) that are so formatted. (As opposed to the sum of those numbers).

In other words, I want a formula that returns: “how many cells within this range are formatted in bold?”

Thanks,

-P

---

<div class="post-metadata">

**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\
**Post date:** [January 5, 2010, 4:55pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/2 "2010-01-05T16:55:09Z")

</div>

I think it depends on what version of Excel you are using. On the computer that I’m on at present is Excel 2003, and I think you can’t do that sort of thing. However, I believe that you might be able to do in in Excel 2007: I’ve been told that you can do tests of cell format with that version.

---

<div class="post-metadata">

**Author:** ![Parthol](https://avatars.discourse-cdn.com/v4/letter/p/dec6dc/32.png) [@Parthol](https://boards.straightdope.com/u/Parthol)\
**Post date:** [January 5, 2010, 5:04pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/3 "2010-01-05T17:04:20Z")

</div>

I’m using the 2007 version.

---

<div class="post-metadata">

**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\
**Post date:** [January 5, 2010, 5:14pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/4 "2010-01-05T17:14:15Z")

</div>

> [@Parthol](#):
>
> In other words, I want a formula that returns: “how many cells within this range are formatted in bold?”

I believe you have to use VBA code (Visual Basic Applications) to test whether the cell’s properties are **bold**. You use VBA to write a UDF (User Defined Function) that specifically tests for bold. I’m sure this type of function has be re-invented a 1000 times by different people so a google search should find some example code to paste into your worksheet.

**Giles** might be thinking of a function in Excell called [CELL()](http://office.microsoft.com/en-us/excel/HP100623921033.aspx) but that only returns the formatting string such as “$0.00” or if the cell is protected/unprotected – the CELL() doesn’t provide color, bold, etc.  
(Tangent thought… I’ve always wondered why people ask Excel questions here instead of dedicated forums such as [http://www.excelforum.com](http://www.excelforum.com) where there are more experts and better answers.)

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [January 5, 2010, 5:23pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/5 "2010-01-05T17:23:50Z")

</div>

I was unable to find this feature in Excel 2007. Doesn’t mean it isn’t there.

This function works in Excel 2007.

```auto

Function CountBold(r As Range) As Long
   CountBold = 0
   
   Dim c As Range
   For Each c In r
      If c.Font.Bold Then
         CountBold = CountBold + 1
      End If
   Next c
End Function

```

However, a change to formatting is not considered a change to the cell so will not trigger an automatic recalculation of this function. You could declare it as Volatile, but even that won’t guarantee it will be recalculated when formatting changes.

> [@Ruminator](#):
>
> (Tangent thought… I’ve always wondered why people ask Excel questions here instead of dedicated forums such as [http://www.excelforum.com](http://www.excelforum.com) where there are more experts and better answers.)

Bite your tongue 😉

Actually, people come here because it’s handy and quick. That’s the tradeoff of a general-purpose board. Big audience who know a little about everything. Specialized boards know everything about a little. I happen to like [www.ozgrid.com](http://www.ozgrid.com) for Excel help, and I guess there are others that are very good too.

---

<div class="post-metadata">

**Author:** ![tomndebb](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@tomndebb](https://boards.straightdope.com/u/tomndebb)\
**Post date:** [January 5, 2010, 8:18pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/6 "2010-01-05T20:18:39Z")

</div>

Also, many of us talk something closer to English than the denizens of tech centered boards. If you are newly fighting your way into a situation, it helps if your guide speaks a language you understand. There are multi-lengual tech geeks and some of us only speak tech, but the odds of getting an answer in English, here, are mildly higher.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [January 5, 2010, 8:58pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/7 "2010-01-05T20:58:19Z")

</div>

> [@tomndebb](#):
>
> Also, many of us talk something closer to English than the denizens of tech centered boards. If you are newly fighting your way into a situation, it helps if your guide speaks a language you understand. There are multi-lengual tech geeks and some of us only speak tech, but the odds of getting an answer in English, here, are mildly higher.

That is true, but there is a reason for that. I have found that over half of the Excel questions I try to answer are so poorly worded that I have to ask a lot of questions to find out what the person is trying to do, and what problem they are having. Sometimes just reading the question shows that it isn’t worth it to try to answer it.

Note that **Parthol** did an excellent job in formulating a precise, brief, clear question!

---

<div class="post-metadata">

**Author:** ![Parthol](https://avatars.discourse-cdn.com/v4/letter/p/dec6dc/32.png) [@Parthol](https://boards.straightdope.com/u/Parthol)\
**Post date:** [January 6, 2010, 4:29pm UTC](https://boards.straightdope.com/t/excel-question-for-the-experts/523763/8 "2010-01-06T16:29:39Z")

</div>

Just wanted to pop back in to say ‘thanks’ for the responses!

Also, yeah, I asked the Dope because it’s quick, familiar, and the answers are typically just as good as those from tech forums.

-P