Many people believe that they have mastered all the secrets of Excel, the popular Microsoft Office spreadsheet program, but they are unaware of some interesting tricks. One of them is the one we are going to discuss and explain in this post: How to count cells with text in Excel.
If you are one of those users, what we tell you in the following paragraphs will interest you. Because, although Excel can overwhelm us with so many formulas and options available, the truth is that once we learn to use it, it becomes one of our best tools.
The content of cells in Excel spreadsheets can vary greatly. Typically, these are numerical values, but they can also contain formulas and functions, as well as images, hyperlinks, and more. texts of different kinds (notes, comments, etc.).
What is the purpose of counting cells with text in Microsoft Excel?
Although at first this function may seem a bit superfluous, the truth is that it can be useful to us. for many tasks and objectives.This is a small list of them:
- Data analysis, compiled from the information contained in the cells in text form.
- Classification and segmentation, grouping the cells and creating categories based on the type of content reflected in the text.
- Inventory management, when the text refers to product descriptions and the like.
- Form response tracking.
- Data verification, identifying cells that should contain numbers instead of text, for example.
In summary, we can say that counting cells with text in Excel is a resource that we can use for a correct organization of information. One more tool for get more out of Microsoft's spreadsheet tool.
The user has several ways to count cells with text in Excel. The most practical and intelligent way is to use some formula to achieve this goal. And we already know that in Excel you can configure formulas or functions for almost anything you want to achieve. Let's see which ones we can use:
COUNTIF Function
This is the function that almost everyone uses to count cells with text in Excel. It is preferred by those who use the program with some frequency, due to its simplicity and effectiveness. COUNTIF function can be used for many purposes, as it is designed to count cells that meet a specific criterion. For the case at hand, this is how we can use it:
If we want to count cells with text in a certain column (for example, column A, from cell A1 to cell A10), we must write the following formula:
=COUNTIF(A1:A10, "*").
In the space where we have placed the asterisk, the word or text that must be included in the cell to be counted goes. For example, to count the cells within the range A1:A10 in which the word "sale" appears, we will write it in quotation marks in the position where the asterisk goes.
COUNTA function
Another formula that will help us count cells with text in Excel is the following: COUNTA function. Thanks to her, we will be able to detect and count all cells in a given range that are not empty. That is, those that contain any type of content, be it text, number or any other data.
Using the same range as in the previous example, to use it we would have to enter the following formula:
=COUNTA(A1:A10)
In the case of working with large volumes of data, to be even more precise in our work of counting cells with text, it is worth using some kind of combined functionExcel experts know how to do it. For beginners, we recommend reading our previous post How to use the IF function in Excel.
Filter text in Excel
Finally, we indicate one more resource that we can use to count cells with text in Excel: the cell filteringIn this case, it is not a formula or a function as we have seen so far. The truth is that it is a much simpler trick, especially for those users who get confused when applying formulas and are looking for a more accessible resource. This is what you should do:
- First of all, you have to Select with the mouse the column, row or cells you want to filter.
- Once the cells are selected, we go to the instrument appointment and click on the tab "Data".
- Then we select the option "Filter".
- Next, we choose «Text filters».
- Alli We choose “Contains” or “Does not contain”, depending on what our objective is.
- Finally, we press the button "Apply".
After applying the filter, only those cells that contain text or not will be visible in the document, depending on our previous choice. As you can see, it is a much simpler way to achieve the same thing: counting cells with text in Excel.