Generating Worksheet Stats

Terri would like to generate some statistics about a worksheet. In particular, she would like to know how many cells contain text, how many contain numbers, and how many contain formulas. Terri wonders if there is a built-in way to determine this information.

The answer to this can get a bit tricky. For instance, let's say that you want the number of cells containing numeric values. Traditionally, you can use one of these formulas:

=COUNT(A1:Z99)
=SUMPRODUCT(--ISNUMBER(A1:Z99))

What you get back is the number of cells containing numeric values in the range A1:Z99. (You can change the range as desired.) The problem is, both formulas return any cell that contains numbers regardless of how those numbers got there. For instance, if you have a formula that returns a numeric value, that formula is included in what is returned. However, Terri indicated that she would like to have a count of the number of cells that contain formulas, implying that she doesn't want the numeric result of formulas included in the count of numbers.

The same problem occurs when counting cells containing text. The COUNTIF and ISTEXT functions seem perfect:

=COUNTIF(A1:Z99,"*")
=SUMPRODUCT(--ISTEXT(A1:Z99))

The count returns those cells containing text derived from a formula, as well.

That bring us to determining cells containing formulas. That can be ascertained using the following formula:

=SUMPRODUCT(--ISFORMULA(A1:Z99))

This actually returns just those cells containing formulas, but it is only available in Excel 2013 or later versions. You can combine it into the earlier formulas to return only cells containing numbers and text, ignoring what is returned by formulas:

=SUMPRODUCT(--NOT(ISFORMULA(A1:Z99)),--ISNUMBER(A1:Z99))
=SUMPRODUCT(--NOT(ISFORMULA(A1:Z99)),--ISTEXT(A1:Z99))

If you are using Excel 2021 or later, you can construct a LET statement that can return quite a few stats about a range of data. Here is a mega-formula that returns the number of columns, rows, cells, non-blanks, formulas, numbers, text, and blank cells:

=LET(rng,INDIRECT("A1:Z99"),rows,ROWS(rng),cols,COLUMNS(rng),
total,rows*cols,blanks,COUNTBLANK(rng),nonblank,total-blanks,
formulas,SUMPRODUCT(--ISFORMULA(rng)),
nums,SUMPRODUCT(--ISNUMBER(rng),--NOT(ISFORMULA(rng))),
txt,SUMPRODUCT(--ISTEXT(rng),--NOT(ISFORMULA(rng))),
logs,SUMPRODUCT(--ISLOGICAL(rng),--NOT(ISFORMULA(rng))),
errs,SUMPRODUCT(--ISERROR(rng)),
"Columns: "&TEXT(cols,"#,##0")&" | "&
"Rows: "&TEXT(rows,"#,##0")&" | "&
"Cells: "&TEXT(total,"#,##0")&" | "&
"Blank: "&TEXT(blanks,"#,##0")&" | "&
"Nonblank: "&TEXT(nonblank,"#,##0")&" | "&
"Numbers: "&TEXT(nums,"#,##0")&" | "&
"Text: "&TEXT(txt,"#,##0")&" | "&
"Formulas: "&TEXT(formulas,"#,##0")&" | "&
"Logical: "&TEXT(logs,"#,##0")&" | "&
"Errors: "&TEXT(errs,"#,##0"))

Remember that this is a single formula, even though I am showing it here on multiple rows. It results in this being displayed, with the numbers and percentages varying based on what is in the range A1:Z99:

Columns: 26 | Rows: 99 | Cells: 2,574 | Blank: 2,289 | Nonblank: 285 | Numbers: 135 | Text: 96 | Formulas: 54 | Logical: 0 | Errors: 0

If Terri prefers to simply discover some pre-defined stats for a worksheet, then pressing Ctrl+Shift+G will display the Workbook Statistics dialog box. (The dialog box is also available by clicking the Workbook Statistics tool on the Review tab of the ribbon.) The stats it provides are quite rudimentary and may not fit Terri's needs. To determine the number of cells containing the various types of data, she can use the Go To capabilities of the program.

1. Press Ctrl+G. Excel displays the Go To tab of the Find and Replace dialog box.
2. Click the Special button. Excel displays the Go To Special dialog box.
3. Click the Constants radio button. Excel makes the four check boxes under Formulas active. (Yeah, I know. It doesn't make much sense to have a radio button make options under a different radio button active. Go figure.)

At this point, you can use the check boxes—Numbers, Text, Logical, and Errors—to indicate what type of constants you want to include in your search. For instance, if you want to know how many cells contain numbers, make sure that only that check box is selected. When you click on OK, Excel selects those cells and displays in the status bar a count of how many cells were selected.

You can repeat this process for each of the other types of content you want. If you prefer, in step 3 you can click Formulas in order to determine how many formulas there are in the worksheet.

Finally, if you have a version of Microsoft 365 in an enterprise plan, then you can get detailed stats on an entire workbook by using Workbook Analysis. This is part of the Spreadsheet Inquire add-in, which you can find more information about here:

https://support.microsoft.com/en-us/excel/analyze-a-workbook-with-spreadsheet-inquire

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