Locked lesson.
About this lesson
The video shows two different ways to get rid of blank lines in a dataset and how to sort data without unique identifiers back into its original order.
Quick reference
Importing data into Excel: get rid of blank lines in your data
Learning how to prepare data for use in Excel - get rid of blank lines in your dataset.
When to use
Blank lines cause Excel to think the dataset ends and results in misselection of a range, formula copying not being effective, and you may end with incorrect calculations or reports about the data without knowing.
Instructions
Getting rid of blank lines
- Deleting blank lines are the final issue to resolve in this sheet
- Excel regards a break in the data as an indication that the dataset has ended
- To get rid of blank lines may be as easy as selecting the line and delete
- If you have a larger dataset, that may become a bit tedious, so here are two methods:
- Sort the data by any of the fields so that the blank lines end up together and delete from there
- Select the data and add an auto filter, filter the blanks, select and delete
Sort
- If you sort and need to get data back in the original order, add a type of unique number in a column and sort with the data
- Select the entire range, on the data tab, sort & filter grouping, click on the sort button
- Remember to tick your “my data has headers” box if you include the headers in the range selection
- After deleting your blank lines, you can use the unique number sequence to sort the data in the original order again
Filter
- Select your data range, be sure to include the whole range (select to include the blank lines)
- On the data tab, sort & filter grouping, click on the auto filter button
- Excel adds auto filter buttons to the headers
- Click on an auto filter button, deselect all options, move to the bottom of the values available and choose blanks
- All the blanks cells should be visible (check the row numbers on the side, if some are blue, then your data is filtered
- Select the lines, delete
- Remove the filter
- Filtering does not change the order of your data like sorting does
- 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.