Join Dennis Taylor for an in-depth discussion in this video Use Sort to eliminate empty rows and columns, part of Excel 2016: Cleaning Up Your Data.
- [Narrator] In this worksheet called Extra Rows, we've got some valuable information here. Within each department, for example ADC we see a list of information about the people who work in that department. And scrolling up and down you can see over in column A how the data is indented and every time we have a new department, we've got a new list with a new heading. That heading by the way, does repeat the information that we see to the right. So if we wanted to print this and distribute it to people, we might hide column C, right click Hide.
That's a valuable looking list. But suppose we want to work with this list, we might want to do some filtering with it, we might want to sort the list we might want to create a Pivot Table, it's not in great shape for those kinds of features. Let's get rid of these. First of all, let me bring back the column that I hid, I'll just press Control Z to undo that last action. We actually want to get rid of these. Now if you are familiar with the feature called Subtotal, you would now how to create a similar list with similar kinds of headings. But how do we get rid of these without scrolling down to the next one, pressing Delete as we delete each row down here.
That's not very efficient and how many are there here? There could be hundreds of them. Let's actually sort the data and let's pick one of the columns where we've got empty information next to that. In other words, let's sort based on any column except column A. Maybe we just want to sort simply by Department, it looks like it's sort of that way now in a certain sense. but let's just click, grab in column C. And we can go to the Sort button on the Data tab up in the Ribbon, the larger sort button. And before sorting the data make sure we pick one of the columns here, it can be almost any column, except column A.
I'll just choose Department and we want to sort in Ascending order. Always be alert when you sort, my data has headers that usually does and Excel recognizes that. In this case, so that row 1 doesn't get treated like the others and thrown into the mix. We'll simply click OK and what happened to those titles? They're likely to be at the bottom. Double click the bottom edge of wherever the active cell is, that will take you down to the bottom and there we see them down here. Now let's say we decide we will never need these. If we don't, we'll simply get rid of them.
Now possibly if we think we're going to be needing them, maybe either drop them down here to get them out of the way, or better yet, let's just move them over to an empty sheet. Let's say we don't need them anymore, all we did really was to sort the data to pull these out. And now we can work with the data without concern about those headings that we saw there. Zip back up top here. Now we can sort the data, we can filter the data, we can create a Pivot Table off of this, we can perform other features as well without worrying about those embedded headings that we had.
In this list here, we've got empty rows. If we simply click in the top section of the data right here and start the sort process, we realize that in the background based on the data that's being highlighted, that we will only be sorting that data. So we need to pick up all this information here and keep in mind this could be thousands and thousands of rows. We need to go to the bottom and many of you are familiar with the key stroke shortcut, Control End, that's the END key, that's going to take us to the last cell on the worksheet, where there's any data in that column or row.
And it looks as if as we scroll left and right here, that there's no data in row 765. Here's the lower right-hand corner of our list and what do we do now? We want to go back to the upper left-hand corner while holding onto this position here. So we hold down the Shift key and now press the opposite of Control End, is Control Home. Remember the Shift key is held down, press Control Home. All this data is highlighted. Now let's sort the data and in effect, this will be putting all the empty rows at the bottom.
We want to sort based on any column, how about Employee Name column A, sort A to Z. We do have headers in our list Excel picks up on that, that's accurate. Make sure that's checked. Click OK. The data's been sorted. The empty rows are at the bottom and there's our contiguous list. We can click on any one of these cells here. Then double click the top edge and if it's contiguous, zip to the top as we did here. So cleaning up our data by getting rid of empty rows as we saw in this list or in the previous list or prior to the changes we had seen headings here.
We need to clean up that data for efficient use of lists in Excel, and possibly you might eventually convert these to tables, if you're familiar with that concept, we need to get rid of empty rows and also extra rows that contain headings that we don't ultimately need in our data.
- Moving and inserting rows and columns of data with a simple drag
- Replacing data at the character level
- Converting dates with text functions
- Converting text data to values
- Using the CONCAT and TEXTJOIN functions to combine data
- Splitting data into columns via the Text to Columns feature
- Using Flash Fill for faster combining and splitting
- Checking and correcting spelling mistakes