Software optimize

Office challenge: How quickly can you count the number of cells in a selection?

This week test your Excel and math skills by sharing a quick method for determining the number of cells in a selection.

This week, there's no harried users, just a fun quiz in the hopes of exposing an interesting Excel feature that lots of users never even notice. Now, let's suppose you need to know the number of cells in the current selection (not a range, but a selection). What's the quickest way to do so? You don't have to return a value in an Excel cell; you just need to determine the correct number of cells.

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.

7 comments
Zahra B.
Zahra B.

That would be my first choice also, assuming you can do simple arithmetic (or just get the calculator windows application out to do the multiplication).

nicefred60
nicefred60

Count works for cells with data in them but if cells blank count isnt accura Reselect the area and keep mouse button depressed. Name Box gives number of rows x number of columns just multiple those numbers

damiross
damiross

You can see the count of all active cells in a selection in the lower right corner. In versions 2003 and earlier, the default number shown is the sum. Clicking on this number gives you the opportunity to change it from sum to count.

cfeliz
cfeliz

In Excel 2010, you first make your selection and on the bottom left hand side of the screen it will read "Count: ##". Note: It will only count the cells that have information in them.

sk.tomar
sk.tomar

what about when multiple ranges are selected?

zimmerwoman
zimmerwoman

Excel only gives an accurate count on a range with all cells populated with some kind of data. I populated data into a 7-row, 5 column range, skipping cells and using 1-up numbering so I would have the count of populated cells by default - giving me 12 populated cells. Also, the arithmetic progression of integers 1 - 12 equals 78 and that is the sum of the numbers. The count Excel read was 12, the final number of the 1-up data. Then I populated the blank cells with "0" by doing a "paste special, add" to the whole range (something we learned in December 2010, if I am not mistaken). Excel now takes those cells into the count, which is now 35 (7 rows X 5 columns). The sum remains the arithmetic progression of 1 through 12, 78 because all the new values added were "0",

zimmerwoman
zimmerwoman

Copy a space into the clipboard. Select the ranges, "Go To" (F5), Special, Blanks: Paste Special, OK. Look at the status bar. If you have it set up to show the count, Presto-Chango, Magic!!! There it is! The number of blank cells. "CTL Z" to get rid of the spaces.