Software

Office challenge: How do you count the number of blank cells in an Excel range?

This week, learn how to change Word's envelope style and try your luck at identifying an easy -- but not well known -- Excel trick for counting blank cells.

What's the quickest and easiest way to count the number of blank cells in a range? The solution is a cinch if you know your functions... and that’s the only hint you’ll receive this week!

Last week we asked...

How do you change the font Word uses when printing envelopes? Word's built-in envelope utility uses predefined styles. Specifically, it uses a style named Envelope Address. There’s an Envelope Return style for the return address too. Both styles use the Arial font. You can change the font for the current envelope or you can update the style permanently.

To change the font for only the current envelope(s), do the following:

  1. Choose Letters And Mailings from the Tools menu. Then, Select Envelopes And Labels from the submenu. In Word 2007, click Envelopes in the Create group on the Mailings tab.
  2. On the Envelope tab, click the Options button.
  3. To change the font for the delivery address, click the Font button under Delivery Address; click Font under Return Address to change the return address.
  4. On the Font tab, select the font you want to use.
  5. Click OK.

Once you close the utility, it reverts to the style’s font. To permanently change an address font, do the following:

  1. Display the Task Pane (choose Task Pane from the View menu) and then choose Styles And Formatting from the Task Pane’s drop-down list. In Word 2007, the Styles group is on the Home tab.
  2. Choose All Styles from the Show control’s drop-down list (at the bottom of the pane). In Word 2007, click the Options link at the bottom of the pane. Then, choose All Styles from the Select Styles To Show control's drop-down list.

  3. From the Pick Formatting To Apply list, choose Modify from the Envelope Address item’s drop-down list.

  4. Choose Font from the Format button’s drop-down list (at the bottom of the Modify Style dialog box).

  5. On the Font tab, select the new font and click OK twice.

Three members, Kenone, WireTraveler, and Roywar, had the right steps for changing the font for the current envelope. Roywar added the tip that you can right-click on the Delivery Address and Return Address controls in the Envelopes And Labels dialog box to quickly select Font from the resulting context menu. (See step 2 in the first set of instructions.) MarciaAnn was the only one to mention changing a predefined style. As always, thanks for playing and be sure to try your hand at this week’s challenge.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
AAABDEL
AAABDEL

Use countif(range, "")

AtCollege
AtCollege

Use =COUNTIF(Range of the cells you want to count,"") Example: =COUNTIF(B3:B5,"") returns 1 List is in B3:B5 Cell B4 is blank "" (two double quotes with nothing in between

amasa
amasa

Use the Countblank function. Specify the range and it tells you the number of empty cells.

kevaburg
kevaburg

By using =COUNTBLANK(range) The same could also be achieved by using a COUNTIF function but that would be going all around the houses to get home!

blogs
blogs

=ROWS(range) * COLUMNS(range) - COUNTA(range) Why use a single function when you can use three functions at once?

AnswerMan
AnswerMan

Isn't there also something in an IF statement called IF(IsEmpty)? Maybe that was just in the VBA side of things..

Radar1751
Radar1751

Sorry, I meant to reply to the original post.

Radar1751
Radar1751

I think you meant cinch instead of synch.

rflii
rflii

COUNTBLANK() does not work when the range uses merged cells. Though visibly you have filled in the merged cell, the invisible cells will be counted as blank. This can really screw up when trying to format a worksheet but need stats.

AnswerMan
AnswerMan

Your assuming everyone knows SQL. And in Excel, that can be a tricky thing. I would guess your talking about something like: =ROWS(1:4)*COLUMNS(A:D)-COUNTA(A1:D4) Seems like a lot of work when you can just go =COUNTBLANK(A1:D4) Unless I'm missing something... which is entirely possible.

fledis
fledis

It's a function ISBLANK(value) however it responds only about single cell not range of cells.

ssharkins
ssharkins

Criminy.... thank you! ;) I'll take care of that.

Editor's Picks