While building a telephone directory, you need to fill a column in Excel with the numbers 7900 to 7999. This episode shows you four different methods.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1937.
Excel Fill a Column with Sequential Numbers.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This question came in via Twitter: I need help in Excel, I need a column of extension numbers from 7900-7999.
How do I do this automatically?
And I tweeted back with the answer, but it's one of those things that is hard to explain in 140 characters, and I said, you know, a video would really show this off.
And there's a lot of different ways to do this.
So, first thing: put in the number 7900, you know, so you type 7900, press Enter and of course it goes to the next spot.
So, come back up to choose that cell with the 7900, on the HOME tab we're going to come out here to this little icon, it's called “Fill”.
Open the Fill drop-down and choose Series… Now, a couple of things you have to do here.
First, change this from Rows to Columns.
The Step value is going to be 1, you can leave that alone.
And the Stop value is going to be your last number, it was 7999, and click OK.
And Excel fills that data down.
Now, this isn't Friday, it's not a Dueling Excel podcast, I'm not going to throw it over the wall to Mike Garvin, but there are a lot of different ways to do this.
You could put in 7900 like that, and then a little formula: =B2+1 (previous cell +1).
And then, actually here I could just double click, but in real life you would grab the fill handle and drag, and go down till you get to enough rows, and everly you're going to go too far, all right.
So that's okay, once you find where it should end, just do Ctrl+Shift+Down arrow from there, press the Delete key, all right.
Now, those are formulas and of course there's some chance that someday we're going to delete a row, because an extension went away and when we delete a row, it'll do it all EDR, everything else is going to change to a reference error #REF!.
So, that's… the method there in column B, I'll undo with Ctrl+Z, is a little bit less good, because of those formulas.
Unless, of course we choose column B and then Copy and Paste / Paste Values.
All right, now, in the future it'd be fine to delete a row and everything doesn't change to a reference error.
The other way to go, that would do this automatically, is a little formula called =ROW.
=ROW(), see, it returns 2, because we're in row 2.
So, in your head you have to do a little bit of math and do +7898, all right, which gives us the 7900.
Again, drag that down until you do 7999 and then Copy and Paste / Paste Values.
Some other ways: you can put in the first number 7900 and normally the fill handle, you know, will fill January-February-March, but of course, with numbers it does not.
Want to fill….
It just copies, but this is great trick.
if you hold down the Ctrl key, hold down the Ctrl key and drag the fill handle, it will automatically extend.
You even see the little tool tip there, can you see that?
Shows you where you're going to end.
So I'm still holding down the Ctrl key, let go of the mouse and it fills those numbers.
So lots of different ways to fill sequence.
The method I suggested in the tweet was using Fill, Series.
You can see there's might be faster, shorter way to get on.
Hey, I want to thank Tracy for sending the question via Twitter, you can always send your questions in via Twitter, just include @MrExcel.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1937.
Excel Fill a Column with Sequential Numbers.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This question came in via Twitter: I need help in Excel, I need a column of extension numbers from 7900-7999.
How do I do this automatically?
And I tweeted back with the answer, but it's one of those things that is hard to explain in 140 characters, and I said, you know, a video would really show this off.
And there's a lot of different ways to do this.
So, first thing: put in the number 7900, you know, so you type 7900, press Enter and of course it goes to the next spot.
So, come back up to choose that cell with the 7900, on the HOME tab we're going to come out here to this little icon, it's called “Fill”.
Open the Fill drop-down and choose Series… Now, a couple of things you have to do here.
First, change this from Rows to Columns.
The Step value is going to be 1, you can leave that alone.
And the Stop value is going to be your last number, it was 7999, and click OK.
And Excel fills that data down.
Now, this isn't Friday, it's not a Dueling Excel podcast, I'm not going to throw it over the wall to Mike Garvin, but there are a lot of different ways to do this.
You could put in 7900 like that, and then a little formula: =B2+1 (previous cell +1).
And then, actually here I could just double click, but in real life you would grab the fill handle and drag, and go down till you get to enough rows, and everly you're going to go too far, all right.
So that's okay, once you find where it should end, just do Ctrl+Shift+Down arrow from there, press the Delete key, all right.
Now, those are formulas and of course there's some chance that someday we're going to delete a row, because an extension went away and when we delete a row, it'll do it all EDR, everything else is going to change to a reference error #REF!.
So, that's… the method there in column B, I'll undo with Ctrl+Z, is a little bit less good, because of those formulas.
Unless, of course we choose column B and then Copy and Paste / Paste Values.
All right, now, in the future it'd be fine to delete a row and everything doesn't change to a reference error.
The other way to go, that would do this automatically, is a little formula called =ROW.
=ROW(), see, it returns 2, because we're in row 2.
So, in your head you have to do a little bit of math and do +7898, all right, which gives us the 7900.
Again, drag that down until you do 7999 and then Copy and Paste / Paste Values.
Some other ways: you can put in the first number 7900 and normally the fill handle, you know, will fill January-February-March, but of course, with numbers it does not.
Want to fill….
It just copies, but this is great trick.
if you hold down the Ctrl key, hold down the Ctrl key and drag the fill handle, it will automatically extend.
You even see the little tool tip there, can you see that?
Shows you where you're going to end.
So I'm still holding down the Ctrl key, let go of the mouse and it fills those numbers.
So lots of different ways to fill sequence.
The method I suggested in the tweet was using Fill, Series.
You can see there's might be faster, shorter way to get on.
Hey, I want to thank Tracy for sending the question via Twitter, you can always send your questions in via Twitter, just include @MrExcel.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
