Todays problem involves a complex data set with headings throughout the data. We want to add new columns to the dataset, but it is incredibly tedious to copy the new column headings throughout the report. Episode 546 shows you how to use a combination of AutoFilter, and Visible Cells Only to solve the problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Here's a question that came in during one of our power excel seminars, person said I get a data set from someone and it has four columns I go through all the time and add three new columns, so basically I'm adding three new columns out here New 1, New 2, and New 3, but he said what's interesting about this data set is that they repeat the headings throughout the data every time the group number changes there's a new set of headings, and now I would never set data up that way, but that's the way this is set up and he wanted to make sure that his headings got copied down throughout the data wherever there's a new set of headings, and of course in this 150 page document that would be very tedious to go through and copy and paste these headings to all of his different rows, so we took a look at the report the first thing that I saw was that there's headings in row two and a title in row 1 and my trick would not work with those two rows adjacent t each other, so I went to row 2 and I said we're going to insert a row basically to keep the data apart and then I'm going to turn on the auto filter, so we'll go to data filter, auto filter, and we'll get the little drop-down headings on each of ourselves.
I asked when I said well this heading is here in column B the customer heading does that appear anywhere else.
You don't have any values and customer be that would just say customer and he said no, so I now go to the heading and I say all right I want to see all the rows that have the word customer, basically this now it gets me just the headings from the whole data set so now I'll go over to my new headings new one, new two, new three, and we'll copy those to the clipboard and select all of these cells here next to our headings, now this won't quite do it we actually have to select the visible cells only, that's Edit, go to special visible cells only, ok or we just hold down the ALT key, ALT semicolon will select the visible cells only, now when I paste, CTRL V to paste, and those headings are added to just the visible cells which happen to be just the heading sales, now we'll go back and ask to see all values so I'll choose all from the customer drop down, turn off the auto filter, data filter, auto filter again and sure enough we've added the new headings all the way down through our data set fairly complicated this is something you need do of course you can click replay and watch that one again.
The real trick is the ALT semicolon to select the visible cells only great question a little bit tricky how to do with several different things including the important one of making sure there was a blank row between the title and the headings, otherwise the Auto filter wouldn't work.
Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Here's a question that came in during one of our power excel seminars, person said I get a data set from someone and it has four columns I go through all the time and add three new columns, so basically I'm adding three new columns out here New 1, New 2, and New 3, but he said what's interesting about this data set is that they repeat the headings throughout the data every time the group number changes there's a new set of headings, and now I would never set data up that way, but that's the way this is set up and he wanted to make sure that his headings got copied down throughout the data wherever there's a new set of headings, and of course in this 150 page document that would be very tedious to go through and copy and paste these headings to all of his different rows, so we took a look at the report the first thing that I saw was that there's headings in row two and a title in row 1 and my trick would not work with those two rows adjacent t each other, so I went to row 2 and I said we're going to insert a row basically to keep the data apart and then I'm going to turn on the auto filter, so we'll go to data filter, auto filter, and we'll get the little drop-down headings on each of ourselves.
I asked when I said well this heading is here in column B the customer heading does that appear anywhere else.
You don't have any values and customer be that would just say customer and he said no, so I now go to the heading and I say all right I want to see all the rows that have the word customer, basically this now it gets me just the headings from the whole data set so now I'll go over to my new headings new one, new two, new three, and we'll copy those to the clipboard and select all of these cells here next to our headings, now this won't quite do it we actually have to select the visible cells only, that's Edit, go to special visible cells only, ok or we just hold down the ALT key, ALT semicolon will select the visible cells only, now when I paste, CTRL V to paste, and those headings are added to just the visible cells which happen to be just the heading sales, now we'll go back and ask to see all values so I'll choose all from the customer drop down, turn off the auto filter, data filter, auto filter again and sure enough we've added the new headings all the way down through our data set fairly complicated this is something you need do of course you can click replay and watch that one again.
The real trick is the ALT semicolon to select the visible cells only great question a little bit tricky how to do with several different things including the important one of making sure there was a blank row between the title and the headings, otherwise the Auto filter wouldn't work.
Hey thanks for stopping by, we'll see you next time for another netcast from MrExcel.
