In today's podcast, a simple process to delete the blank rows by sorting the data first. Episode 704 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Well we're on day three of trying to fix up this data set.
We've solved the model and region problem. We filled in all the regions in column B.
Now we need to go through and get rid of the blank cells.
So column D Is completely blank. We're going to delete that.
Ultimately, we're not going to do the total anymore, so I'm going to delete that and now we need to go through and get rid of all the rows that are not detail rows.
Well as I look down through this data, it looks like basically anywhere where there is a market in column C is a row that I want.
So we have regions over here in column B.
You can see the blanks out here is where the model heading used to be.
We don't need that anymore now that we've moved the models over to column A.
We also have blank cells in column C.
That's where we have the east total.
Now depending on your data set you might have to you know do some tweaks here.
Maybe they have the word total here, and that would be a problem, but basically this one is pretty simple.
I'm going to choose the market heading and I'm going to sort ascending and basically what we'll see is down below the data.
Once we start to get to the first blank cells everything from here on down is one of the rows that I don't need. There's no numbers, but either is one of the headings or one of the totals or blank cells in between. So I'm simply going to choose those cells. Back at home will choose delete.
Delete sheet rows and we're good. So now we have almost the perfect data set however, because we have time going across.
This is a bad situation when you're trying to create a pivot table.
In tomorrow's podcast we'll take a look at why pivot tables created with time going across are problematic and what we can do about it.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
I'm Bill Jelen. Well we're on day three of trying to fix up this data set.
We've solved the model and region problem. We filled in all the regions in column B.
Now we need to go through and get rid of the blank cells.
So column D Is completely blank. We're going to delete that.
Ultimately, we're not going to do the total anymore, so I'm going to delete that and now we need to go through and get rid of all the rows that are not detail rows.
Well as I look down through this data, it looks like basically anywhere where there is a market in column C is a row that I want.
So we have regions over here in column B.
You can see the blanks out here is where the model heading used to be.
We don't need that anymore now that we've moved the models over to column A.
We also have blank cells in column C.
That's where we have the east total.
Now depending on your data set you might have to you know do some tweaks here.
Maybe they have the word total here, and that would be a problem, but basically this one is pretty simple.
I'm going to choose the market heading and I'm going to sort ascending and basically what we'll see is down below the data.
Once we start to get to the first blank cells everything from here on down is one of the rows that I don't need. There's no numbers, but either is one of the headings or one of the totals or blank cells in between. So I'm simply going to choose those cells. Back at home will choose delete.
Delete sheet rows and we're good. So now we have almost the perfect data set however, because we have time going across.
This is a bad situation when you're trying to create a pivot table.
In tomorrow's podcast we'll take a look at why pivot tables created with time going across are problematic and what we can do about it.
Well, thanks for stopping by. Will see you next time for another netcast from MrExcel.
