Welcome to Excel Avon
Extract Non-blank cells with formula in Excel
DOWNLOAD THE USED EXCEL FILE FROM HERE>>
In today’s post we will show you How to Extract non-blank cells with formula in Excel, although it is quite simple, In Excel, sometimes we don’t even want blank cells to disturb the formula. Most of the time we don’t want to work with a blank cell as it creates errors using the function.
Suppose you have a lot of data in which some cells are blank, now if you are told that you have to extract non-blank cells in this data, then you will think that you can do it manually, but it will take a lot of time to do it manually. You will think that what is the option other than this, then we will use such a formula in this story, so that we will be able to extract such non blank cells easily.
By the way, our Extract non-blank cells are going to be like this as you can see.
Extract non-blank cells with formula in Excel
First write the index formula then select whole range (Select the range where the data B2:B15) then lock the range.
then we will use If parameter in small function. after If parameter use Lenth Function select whole range and lock (absolute) the range. The condition is with the small function, if the Length of any cell in this range is greater than zero, then extract it.
Then subtract the row No of B2 over the entire range. When you write the range you also absolute the range(press F2 to do it). If B2 Cell subtracted by B2 cell then zero will be appear and cell will appear blank so do +1 inB2 Range.
Press Ctrl+Shift+Enter after writing whole formula (because this is an array formula)
Now we will using Row (1:1) it will check one by one cell. Then completely close the formula bracket.
Now drag the corner of the cell, you will get the formula and result. After drag we will get some error in the result.
Use Iferror function for Error, If there is an error in the result, then by using Iferror Function, the cell will be blank.
Again, drag the corner of the cell, you will get the formula and result. Now you can see where error cell was it is now blank.
Therefore, I hope that you have understood How to Extract non-blank cells with formula in Excel, maybe if you do not understand anything, then you can comment us with the question, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
DOWNLOAD THE USED EXCEL FILE FROM HERE>>
LEARN MORE DASHBORAD AND CHART TOPIC HERE
You can also see well-explained video here about How to Extract non-blank cells with formula in Excel
