Importing Data into Excel: Get Rid of Blank Lines in Your Data

Quick reference

• 00:04 Blank lines and data sets are not unusual.
• 00:07 Removing them may be as easy as selecting the line, right click, and delete.
• 00:13 But if you have a larger data set, that method becomes ineffective and
• 00:17 you need another plan.
• 00:19 So I want to show you two ways to remove blanks, two options.
• 00:24 The first option involves sorting.
• 00:27 When you sort your data, the blank lines will gather or
• 00:29 accumulate at the bottom of the data set.
• 00:32 The other option uses an auto filter that filters the blank lines,
• 00:36 and then you can delete them from there.
• 00:39 So let's talk about option one.
• 00:42 If I'm going to sort this database, these pieces of data will end up out of order.
• 00:47 Very often, usually for accounting purposes,
• 00:49 you need to get that data back in its original order.
• 00:53 If this was a bank statement, and you had a column for
• 00:55 balances in here, well the balances wouldn't make
• 00:58 any sense unless you had the transactions in its original order.
• 01:02 Since these transactions have no unique identifier to indicate a sequence,
• 01:07 I'm going to add my own unique identifier.
• 01:11 I'm going to insert a temporary column at the beginning.
• 01:14 Let's call it Line.
• 01:16 And I'll add numbers to the lines.
• 01:20 I need at least three lines here and then I can double click the fill handle.
• 01:24 But it's only going to copy it down until it hits a break, and
• 01:27 then it's going to give me a problem.
• 01:29 That's the problem with blank lines, but I can drag it all the way down.
• 01:34 So now I have a set of unique identifiers.
• 01:37 To select this entire data range, I'm going to Ctrl and
• 01:41 Shift, I press my right arrow key to select this entire row.
• 01:48 And keep holding Ctrl and Shift.
• 01:50 Now I hit the down arrow key, and it's all selected.
• 01:54 So now let's go up to the Data tab, Sort and Filter.
• 01:57 Before I click on the Sort button, look at my selection.
• 02:00 I have included my headers in the selection.
• 02:04 But if we click the sort button,
• 02:06 the next screen has the option my data has headers and it's checked.
• 02:12 And because that box is checked, the selection of my headers disappears.
• 02:18 Next, I click on this Sort by drop down, the headers appear in there and
• 02:23 I could choose which header I want to sort by.
• 02:26 Let's say my data doesn't have headers or I didn't select it.
• 02:30 Excel is now going to sort the words line, date, detailed description,
• 02:35 and amount as if they were data.
• 02:37 And my Sort by is going to ask column A, B, or
• 02:40 C, which column do I want to sort by?
• 02:43 That is not going to work for this example because I do have headers and I have
• 02:47 selected them and I want to sort by well, let's choose amount, smallest to largest.
• 02:54 And we'll hit OK.
• 02:56 So you can see all my blank lines have accumulated at the bottom.
• 03:01 I can select them, right click in normally you'd see an option to delete
• 03:05 the selection right now that menu was a little bit off the screen.
• 03:08 So, let me show you another way.
• 03:10 Go to the Home tab up top, and on the Cells grouping, there's my Delete button.
• 03:17 Same option that you get from the right click.
• 03:20 There's the Delete drop down, Delete Sheet Rows and it gets rid of the blanks.
• 03:28 Okay, but now my data is out of order.
• 03:32 It's in amount order, but I want it back in the original order.
• 03:37 So let's select all the data again, we'll click on Line, Control and
• 03:41 Shift, right arrow, down arrow, and it's all selected.
• 03:47 Go back to the Data tab.
• 03:48 Sort.
• 03:49 Now I'm going to sort by line, smallest to largest, and
• 03:54 it's back in its original order.
• 03:58 Once you're happy and you're certain everything is correct, and
• 04:02 all the blank lines are gone, you select your line column, right click and delete.
• 04:06 We don't need it anymore.
• 04:08 And there's my data all in good order and ready to work with.
• 04:12 Now that was option one, sort and delete.
• 04:16 Let's go back to the original database, so
• 04:19 we can show you the second option, the auto filter.
• 04:23 We'll start once again by selecting the data.
• 04:26 Click on Date Ctrl Shift, right arrow key to select this whole row.
• 04:32 But watch what happens when I hit the down arrow key.
• 04:34 Excel stops at the first break.
• 04:36 Because of the blank line, Excel thinks the data set is complete.
• 04:40 But let's keep pressing that down arrow until we've got the whole entire data set
• 04:45 selected.
• 04:47 Make sure when you try this yourself that you select your entire data set.
• 04:51 Otherwise this auto filter will not filter everything.
• 04:55 You see if I only select this much, for example,
• 04:57 it's going to perform the filter on only the top part.
• 05:01 Because this break here indicates to Excel that that's the end of the database and
• 05:04 it's really not.
• 05:05 So make sure you get the entire database when you select it.
• 05:09 Okay, now go up to the Data tab, Sort & Filter and click on Filter.
• 05:16 Now I've got my column filter buttons.
• 05:19 Okay, I'm going to select detailed description so you can see how this works.
• 05:25 Click on Detailed description.
• 05:28 The filter button.
• 05:29 Deselect all then select only blanks and hit OK.
• 05:37 Now your data is not gone.
• 05:39 I know it looks blank but it's just hidden.
• 05:41 If you look on the side you can see the normal line numbers appear in black.
• 05:46 These line numbers are in blue with small indicators of something in between.
• 05:51 That means the other rows are hidden.
• 05:54 Now, I can select these rows that I want to delete.
• 05:58 Let's select them right click, Delete Row.
• 06:02 Now that's only going to get rid of these blank lines.
• 06:05 It's not going to delete the hidden ones in between.
• 06:09 But this does look a little scary.
• 06:12 Let's check anyway,
• 06:13 because it's always important to confirm your data is where it's supposed to be.
• 06:17 Clear the filter, and you see everything is back, ten lines of data, it's all good.
• 06:22 And the filter didn't move the data out of the original order.
• 06:26 It's still in the same order as it was before.
• 06:28 So, those are the two ways to remove blank lines from your data.
• 06:33 Number one, you can sort to get the blank lines to the bottom or
• 06:37 number two do an auto filter, hide the lines with data and delete the rest.
• 06:42 Thanks for watching.

Lesson notes are only available for subscribers.