In this video, we look at a way to fill in missing data when the structure is not consistent, using formula created with IF and ISBLANK functions.
In this video, we're going to build on the technique we covered in an earlier video, where we add in missing data using formulas.
Here again we have music collection with some missing data.
The twist in this case is that the Artist isn't in the right column. Instead of column B, it appears in column C. You'll sometimes see this kind of pattern when data is exported from another system.
As before, we'll use formulas to fill in the missing data, but this time, we need to take care of the artist rows as a first step.
With all the cells in column B selected, enter an equal sign to start a new formula.
What we need to do in this case is check for a value in column E. If column E is blank, then we know we're in an artist row.
We can do this with the IF and ISBLANK functions. If the cell in column E is blank, get the value in column C.
If not, get the value in the cell above.
Control-enter to add the formula to all selected cells.
This nicely fills in the Artist column.
As before, this will create a chain of formulas. Most formulas simply look up. But in the Artist rows, the formula will point to column E.
Use paste special to replace all formulas with values.
Now we need to remove the extra rows for artists, since we don't need them anymore, and they juts clutter up the data.
Do this with Go To Special. First select all the values in a column where the artist rows are blank.
Next, use Go To Special > Blanks to select only the blank cells. Finally, use Control - for delete, and select Entire Row. That's the end of the Artist rows.
Now follow the same process we used earlier.
Select the entire table and use use Go To Special > Blanks.
Then enter a formula that refers to the cell above and use Control + enter.
Finally, use Paste Special > Values to replace all formulas.
You can use the same approach for all kinds of data, adapting the formulas to handle the specific structure of the data.
The Excel ISBLANK function returns TRUE when a cell contains is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
Quick, clean, and to the point.