MrExcel.com - Your One Stop for Excel Tips & Solutions

Fill Series 1 to 100,000

July 10, 2017 - by

Fill Series 1 to 100,000

Use the Fill Series dialog in Excel to fill from 1 to 100,000 in a few clicks.

What if you have too many items to drag the Fill Handle? Follow these steps:

  1. Type the number 1 in a cell.
  2. Select that cell.
  3. On the Home tab, toward the right, in the Editing group, open the Fill dropdown and choose Series.
  4. Select Columns.
  5. Enter a Stop Value of 100000.
  6. Click OK.
Fill Series
Fill Series

What if you have to fill 100,000 cells of bagel flavors?

  1. Type the first bagel flavor in A1.
  2. Select A1.
  3. Type A100000 in the Name box and press Shift + Enter. This selects from the current cell to A100000.
  4. Home, Fill, Series... Click AutoFill in the Type box.

Thanks to the young lady in row 6 at the Meonske Conference in Kent, Ohio for suggesting this feature.

Watch Video

  • Use the Fill Series dialog in Excel to fill from 1 to 100,000 in a few clicks. Also in this episode:
  • It would take a long time to fill 1 to 100,000
  • Even if you use the Ctrl key while dragging the fill handle
  • Type a starting number in a cell
  • Home, Fill, Series...
  • Change from Rows to Columns
  • Click OK
  • You can Fill from a series
  • Cool trick with Shift + Enter in Name Box

Video Transcript

The MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, Excel cartoons, cocktails, tweets and jokes.

Learn Excel from MrExcel podcast, episode 1979 - Use Fill Series to Fill From 1 to 100,000!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. We're talking about the fill handle recently, and I showed that cool trick where if hold down the CTRL key, the 1 will automatically extend. But what if you have to do from 1 to a 100,000? It would take HOURS to get down to a 100K, there's no way that's ever going to work, I learned this great trick from my professor at the University of Western Michigan, I was doing a live power Excel seminar out there, and he showed me a trick:

Just put in the number 1 in any blank cell, select that, and then we're out of here, on the Home tab, go to fill, and then choose Series... You always have to change two columns, put the last number so a 100,000. Click OK. And BAM - it's done! Isn't that a beautiful, beautiful, beautiful, beautiful, trick!

Now what if you would have to feel like a hundred thousand flavors? So, two episodes ago, I created a custom list with Apple, Banana, Cherry, and so on. What if we had to fill a hundred thousand of those? Alright well, so I start here from the first one, and then up here I type A100002, and I press Shift+Enter. Shift+Enter says we're going to that cell, but we're selecting from the current cell - isn't THAT awesome? Fill, and then Fill Series, and we are going to Autofill, click OK. And it fills 100,000 names, what a cool, cool trick.

Alright so, recap of the concepts in the video: It would take a long time, to fill 1 to a 100,000, EVEN if you knew the CTRL key from a couple of episodes again. Just type the starting number in the cell, Home, Fill, Series, always change from rows to columns, click OK. You can also fill from a series. AND that cool trick using Shift+Enter in the name box!

Alright again, that's just one more tip from the book "MrExcel XL", click that little "i" up in the top-right hand corner right now, to be taken to a page where you can buy this book. Thanks to the Accounting Department of Western Michigan University for this great tip.

And thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!

Title Photo: rkit / pixabay