Startseite » Excel EN » Count Filled Cells in Excel – Quick Methods for Windows, macOS & Ubuntu

Count Filled Cells in Excel – Quick Methods for Windows, macOS & Ubuntu

If you work with spreadsheets regularly, it’s crucial to know how to count filled cells in Excel—whether you’re on Windows, macOS, or Ubuntu. This skill is especially helpful for summarizing survey responses, analyzing inventory lists, or cleaning up large datasets. In this guide, we’ll cover keyboard shortcuts, formulas, and platform-specific tips to help you accurately count non-empty cells in your worksheet.


📊 Why Count Filled Cells in Excel?

Counting filled or non-blank cells helps you:

  • Understand how much of your data is complete
  • Calculate response rates
  • Filter usable data
  • Avoid errors in calculations that rely on complete entries

The best part? It’s super easy when you know the right tools and formulas.


✅ Method 1: Use the COUNTA Function

The COUNTA function counts all non-empty cells, regardless of data type (numbers, text, symbols).

Syntax:

=COUNTA(range)

Example:

=COUNTA(A2:A100)

This counts all filled cells in the range A2 to A100.

Works on Windows, macOS, and Ubuntu (LibreOffice Calc)


✅ Method 2: Use COUNTIF to Exclude Blanks (Optional)

You can also use:

=COUNTIF(A2:A100, "<>")

This counts only those cells that are not blank.

Both COUNTA and COUNTIF with “<>” will give the same result unless your range contains formulas returning empty strings (""), in which case COUNTIF is more accurate.


🪟 Windows Excel Shortcuts

  • Select Range: Ctrl + Shift + Arrow keys
  • Open Function Wizard: Shift + F3
  • AutoSum Dropdown (includes Count): Alt + =, then choose Count Numbers or More Functions

🍏 macOS Excel Shortcuts

  • Select Range: Cmd + Shift + Arrow keys
  • Function Wizard: Shift + F3
  • AutoSum Menu: Cmd + Shift + T, then use arrow keys to pick “Count”

🐧 Ubuntu (LibreOffice Calc)

LibreOffice supports both COUNTA() and COUNTIF() exactly like Excel.

  • Select Range: Ctrl + Shift + Arrow keys
  • Insert Function: Ctrl + F2
  • You can also use the Function Wizard via Insert > Function

🔎 Method 3: Count Filled Cells by Color (Advanced)

This method requires VBA (Windows) or macro scripting (LibreOffice):

Excel (Windows):
Use a custom VBA script to count cells with background color.
LibreOffice:
Use conditional formatting or script macros.

macOS Excel:
VBA can work, but support is more limited than Windows.


🧠 Pro Tips

  • COUNTA counts cells with formulas, even if the result is blank.
  • To exclude formulas returning blanks, use:
=COUNTIFS(range, "<>", range, "<>""")
  • Use filters to manually check and count filled cells by conditions.

📌 Keyboard Shortcut Table Summary

ActionWindows ShortcutmacOS ShortcutUbuntu (LibreOffice Calc)
Select RangeCtrl + Shift + ArrowsCmd + Shift + ArrowsCtrl + Shift + Arrows
Open Function WizardShift + F3Shift + F3Ctrl + F2
Insert Count FunctionAlt + = (then arrow keys)Cmd + Shift + TInsert > Function

❓ FAQ – Count Filled Cells in Excel

Can I count only cells with numbers?
Yes, use the COUNT() function instead of COUNTA().

Does COUNTA count spaces or formulas?
Yes. COUNTA includes any non-empty cell—even if it just contains a space or a formula.

What if I want to ignore hidden cells?
Use a helper column or filter view with SUBTOTAL:

=SUBTOTAL(103, A2:A100)

Can I count based on a condition?
Yes, use COUNTIF() or COUNTIFS() for more advanced criteria.


✅ Conclusion

Knowing how to count filled cells in Excel is essential for quality data management. Whether you’re on Windows, macOS, or Ubuntu, the built-in formulas and a few helpful shortcuts make this process quick and easy. From basic counts to conditional tracking, mastering this technique ensures your data analysis is accurate and efficient.

Complete List of Windows Keyboard Shortcuts

If you need help for Windows, you can find a whole list of all keyboard shortcuts here.

https://keyboard-shortcuts.org/

Advertisment