How to fill in missing data part 2

In this video, we're going to build on the technique we covered in an earlier video, where we added in missing data using formulas.

Here again we have a 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 the data has been 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 off 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 from 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 get a value from the cell above. But in the Artist rows, the formula will get a value from column C.

Use Paste Special to replace all the values with formulas.

Now we need to clean up the extra rows for Artists since we don't need them anymore, and they just 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 - (minus) 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 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 this same approach on all kinds of data; just adapt the formulas as needed.