Excel Percentage of Cells with Text: 2 Formulas

Formulas & functions · Updated November 22, 2026

The quickest way to find the percentage of cells containing text in Excel is to use =COUNTIF(range,"*")/COUNTA(range) for non-empty cells, or =COUNTIF(range,"*")/ROWS(range) for all cells in the range. Format the result as a percentage. This counts any cell that contains at least one character, including spaces, numbers stored as text, and formula results that return text.

What Counts as “Text” in Excel

Excel treats data types differently. A cell contains text if its value is a string of characters, such as “apple”, “John Doe”, or “ABC-123”. Numbers, dates, and times are numeric values, not text, even if they look like words (e.g., “1-Jan” is a date). However, cells that contain a number formatted with a text format (e.g., a zip code like “90210” entered as text) are considered text by Excel. Also, a cell with a formula that returns a text string (e.g., =IF(A1>0,"Yes","No")) counts as text. Blank cells and cells with only spaces are tricky: a cell with a single space is technically text, but it’s often treated as empty by functions like COUNTA (which ignores truly empty cells but includes spaces). For most practical purposes, you’ll want to exclude those.

The Two Main Formulas and When to Use Each

Choose the denominator based on what you’re measuring. If you want the percentage of text cells among all non-empty cells (ignoring blanks), use COUNTA. If you want the percentage of text cells out of the entire range, including blanks, use ROWS or COUNTA plus COUNTBLANK.

  • Percentage of non-empty cells that contain text: =COUNTIF(A2:A100,"*")/COUNTA(A2:A100). Example: If A2:A100 has 50 non-empty cells, and 30 of them contain text, the formula returns 60%.
  • Percentage of all cells (including blanks) that contain text: =COUNTIF(A2:A100,"*")/ROWS(A2:A100). With 100 cells total and 30 text cells, this returns 30%.

Note: COUNTIF with the wildcard "*" counts any cell that contains at least one character. It does not count truly empty cells, but it does count cells with spaces. To exclude spaces, use =COUNTIF(A2:A100,"?*")/COUNTA(A2:A100) – the ?* pattern requires at least one character before any others, effectively ignoring cells that are only spaces.

Step-by-Step: Applying the Formula in Excel

  1. Select the cell where you want the percentage to appear, e.g., B1.
  2. Type =COUNTIF(A2:A100,"*")/COUNTA(A2:A100) (adjust the range to your data).
  3. Press Enter. The result will be a decimal like 0.6.
  4. Go to the Home tab, and in the Number group, click the Percent Style button (%) or press Ctrl+Shift+% (Windows) / Cmd+Shift+% (Mac).
  5. Increase or decrease decimal places using the Increase Decimal / Decrease Decimal buttons if needed.

On Excel for the web, the steps are identical. On Mac, the keyboard shortcut is Cmd+Shift+%.

Common Problems and How to Fix Them

Three issues trip up most users:

  • Getting 0% or 100% unexpectedly: Check if your range includes header rows or totals. If you include a header like “Name”, it counts as text. Also, ensure the cells you think are text are not actually numbers formatted to look like text (e.g., a number with a leading apostrophe).
  • Counting cells with formulas that return empty strings: A formula like =IF(A1="","","Text") returns an empty string, which COUNTIF with "*" does NOT count (it treats it as empty). But COUNTA DOES count it as non-empty, so your percentage could be skewed. To exclude such cells, use =COUNTIF(A2:A100,"?*")/SUMPRODUCT((A2:A100<>"")*1) – but that’s complex. Simpler: use =COUNTIF(A2:A100,"?*")/COUNTA(A2:A100) if you know no empty strings exist.
  • Spaces in cells: A cell with a space looks empty but is counted by COUNTIF with "*". Use ?* instead, or clean data with =TRIM(A2) in a helper column.

Comparing with Other Counting Methods

Excel has several counting functions. COUNT counts numeric cells, COUNTA counts non-empty cells (including text, numbers, errors, and logical values), COUNTBLANK counts empty cells, and COUNTIF counts cells meeting a condition. To count only text, COUNTIF(range,"*") is the standard approach. A more robust alternative is =SUMPRODUCT(--ISTEXT(A2:A100)) which counts cells that are text type, ignoring numbers and spaces. But ISTEXT returns TRUE for cells with spaces, so it’s similar. For excluding spaces, you’d need an array formula like =SUMPRODUCT((ISTEXT(A2:A100))*(TRIM(A2:A100)<>"")).

Why the Wildcard “*” Works

In Excel, the asterisk (*) is a wildcard that matches any sequence of characters. In COUNTIF, using "*" as the criteria matches any cell that contains at least one character. The question mark (?) matches any single character, so "?*" matches any cell with at least two characters? No, actually ?* means: first character must be any single character, then any number of characters after that. So a single space (one character) would not match because it needs at least one character after the first. A cell with “a” matches (first char ‘a’, then zero or more). A cell with ” ” (space) does not match because after the space there is nothing. This is a neat trick to ignore cells that are only spaces.

Frequently Asked Questions

How do I count cells that contain any text but not numbers? Use =COUNTIF(range,"*") – it counts text cells, but also counts numbers stored as text. If you want to exclude numbers stored as text, you’d need a more complex array formula with ISTEXT and ISNUMBER checks, but that’s rarely necessary.

Why does my formula return a decimal like 0.45 instead of 45%? Because the cell is not formatted as a percentage. Select the cell, go to Home > Number > Percent Style, or press Ctrl+Shift+% (Windows). The underlying value is still 0.45, but it displays as 45%.

Can I count text cells across multiple columns? Yes, but you need to sum the counts. For example, to count text in A2:A10 and B2:B10, use =COUNTIF(A2:A10,"*")+COUNTIF(B2:B10,"*") and divide by the total non-empty cells in both columns. Or use =SUMPRODUCT(COUNTIF(INDIRECT({"A2:A10","B2:B10"}),"*")) – but that’s advanced.

Does this work in Google Sheets? Yes, the same formulas work in Google Sheets with identical syntax. The only difference is the menu path for percentage formatting (Format > Number > Percent).

What if my range includes errors like #N/A? COUNTIF with "*" ignores errors, but COUNTA counts them as non-empty. So if you have errors, your percentage could be off. To exclude errors, use =COUNTIF(range,"*")/SUMPRODUCT(--(range<>"")) but that still counts errors. Better to clean errors first.

How do I count cells that contain specific text like “urgent”? Use =COUNTIF(range,"*urgent*") to count cells containing that substring. To get a percentage, divide by the total non-empty cells.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.