This video comes from our online video training for Excel.
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 IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
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.
View the discussion thread.
Quick, clean, and to the point.