How to Determine the Number of Rows in an Excel Table – 3 Methods

It is not uncommon for an Excel user to have to deal with determining how many rows a table contains. To do this, you can use some methods. There are a lot of them, so the choice of a specific one depends entirely on the goal that the user wants to achieve. Today we will describe some of them. They can be used for articles with varying degrees of content, as well as in some other situations.

Why determine the number of rows in Excel

First of all, why determine the number of rows in Excel at all? There may be many options. For example, it is necessary to estimate the number of goods, each of which is located in a separate line and at the same time the specific number does not correspond to the line number in the document itself. Or you need to determine the number of rows that match a certain criterion. Let’s proceed directly to the consideration of methods on how to understand how many rows an Excel table contains.

Determining the number of rows in an Excel table

So, there are several basic methods for determining the number of rows:

1. View the information contained in the status bar.
2. Using a special function that can either determine the number of rows on its own or be used for other calculations.
3. Using the conditional formatting tool and filters.

These methods should be considered in more detail.

Conditional Formatting and Filtering

In what situation is this method suitable? First of all, if we need to determine the number of rows in a certain range, which also corresponds to a certain attribute. That is, in this case, only those lines that fall under the user-specified criterion will be considered. How does it work in practice?

1. We select the range of data that will be used for the calculation.
2. After that we find on the “Home” tab the “Styles” group. There is a tool called Conditional Formatting.
3. After we click on the appropriate button, a pop-up menu will appear with the inscription “Cell Selection Rules”.
4. Next, a new window appears in which we need to select the “Text contains” item. But this is specific to our example, because the set of cells used in our case contains only text values. You also need to choose the item that is right for your situation. We describe only the mechanics.
5. After that, a window will appear in which we directly set the formatting rules. First of all, we need to specify the values ​​at which the cells will be painted with a certain color. In the left field, for example, we will write the letter A, and in the right field we select the formatting that is set by default. Again, you can change these settings to your liking. For example, choose a different color scheme. We need to select all the cells that contain the letter A and make them red. After we enter these settings, click the OK button.
6. Next, we perform a check. If all the cells that meet this criterion were colored red, then this indicates that we did everything right.
7. Next, we need to apply the Filter tool, which will help determine the number of lines that are red. To do this, again select the range that we need. Then go to the “Home” tab and open the “Filter” icon there. You can see what it looks like in this screenshot. We click on it.
8. A symbol will appear at the top of the column that has been selected, indicating an inactive filter. It looks like a down arrow. We click on it.
9. After that, we search for the item “Filter by color” and click on the color that we used before.
10. After the filter is applied to the table, only those rows that contain cells that are red will be visible in it. After that, it is enough to select them in order to understand the final number. How to do it? This is done through the status bar. It will indicate the total number of rows that we had to calculate in the problem that we set at the beginning of this section.

Using the LINE function

This feature has one huge advantage. It makes it possible not only to understand how many lines are filled, but also to display this value in a cell. However, you can control which rows to include in the count using other functions. Both those that contain values ​​and those that do not contain data will be taken into account.

The general syntax for this function is as follows: = STRING(array). Now let’s think about how to use this function in practice. To do this, we need to open a tool called the Function Wizard.

1. Select any cell that does not contain values. It is recommended that you first make sure that it does not contain non-printable characters or other formulas that give an empty value. This cell will display the result of the function.
2. After that, we click on the “Insert Function” button, which is located a little to the left of the formula bar.
3. Now we have a dialog box in which we can select the category of the function and the function itself. To make it easier to search, we need to select the category “Full alphabetical list”. There we select the function CHSTROK, set the data array and confirm our actions with the OK button.

By default, all lines are considered both those that contain information and those that do not. But if combined with other operators, then you can configure more flexibly.

Information in the status bar

And finally, the easiest way to see the number of selected lines here and now is to use the status bar. To do this, you need to select the desired range or individual cells, and then see the amount in the status bar (highlighted with a red rectangle in the screenshot).

Thus, there is nothing difficult in seeing the number of lines.