Excel sheet count number different in 3 similar column

Hi Binayak Rath

Welcome to Microsoft Q&A Forum. Thank you for reaching out with your question and for sharing the Excel file.

After reviewing your Excel file, the difference in count between Columns A, B, and C is caused by cell B11. While it appears visually similar to the surrounding cells, it is actually completely empty.

When tested using function, =LEN(B11) returns 0, confirming that the cell contains no characters at all, neither visible nor hidden. As a result: 

• Columns A and C each have 85 non-blank entries. 
• Column B has only 84, because B11 is blank, whereas similar rows in Columns A and C contain data.

Use the LEN function to measure the character length:

=LEN(B1)

Drag this formula down the column. Any value greater than 0 indicates content is present, including numbers, text, or hidden characters.

As a general tip, if you suspect that a cell might contain invisible characters (such as line breaks or extra spaces), you can return a cleaned version of the content by using this formula:

=TRIM(CLEAN(B1))

This can help ensure accurate counts and comparisons when working with imported or manually edited data. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.