How to fill a series in Excel so it’s not a drag


How to fill a series in Excel so it’s not a drag
Quick Mac Tip, 04 March 2012

If a company has files in storage they may have a spreadsheet inventory of several hundred thousand items. Helen Hancox of the Flying Filing Squad then needs to make sure the first column displays a unique number for every row. In this guest Tip Helen explains how she saves a lot of hard work by using Microsoft Excel’s Fill Series command to add those unique numbers in a trice.

Helen Hancox. Since 1991, the Flying Filing Squad has been helping organisations with their record keeping. Helen Hancox is the expert to contact when you need practical advice on how to reduce your off site storage costs.

In my business, we use Excel to keep our lists (we are never listless). Formatting cells and filling down are important.

I now use Excel for Mac 2011 version 14.1.4.

I knew that it’s possible to “fill down” by selecting a cell and using the click and drag technique.

That’s easy peasy when you only need to do a few lines. I had done up to 1,000 rows in the past but it was such a drag!

Faced with 144,000 rows that needed a sequential number I knew I had to find an easier way. The answer lies in Excel’s Edit menu.

Fill Series from the Edit menu in Excel.

Fill Series from the Edit menu in Excel.

Fill series makes it easy

  1. Select a cell, perhaps A1 and enter a number, perhaps 1. You may need to press Return or the Tab key to ‘accept’ the value. Then select that cell again.
  2. Go to the Edit menu and choose Fill > Series. The Series window appears.
  3. Select the options you need for your series of numbers. In my case, I needed to fill down a column, so I selected Columns. I also wanted a linear series. I wanted the numbers to increase by 1 each time, so I set a Step value of 1. In the Stop value box enter the value of the final number you need.
  4. Click the OK button. Excel fills down the column (or across a row if you selected Rows) incrementing the number by the Step value you entered until it reaches the Stop value you chose.

Voila! There are the unique identifiers.

Miraz adds: you can use any starting value in the cell, any Step value and any Stop value you need. For example, your cell may contain a number such as 427.235 and you could increment it (Step value) by 1.732 until it reaches 4,000,000 (Stop value). And what about negative numbers? Well, give it a try and let us know what you find. :-)

Do you use Excel? Let us know in the Comments how this Tip helped you.

Related posts

[wpzon keywords="pressure cooker" sindex="PCHardware" snode="1232597011" sort="salesrank" listing="8"]

2 Comments

  1. Peter said:

    Thanks, that is very useful. Much easy than using a drag for a larhe range. the negative works as long as all options make sense. For example starting off at -5 and then using -1 increments to -10 works, but not (as expected) using -5 with -1 increments to get to 10.

    But how do you use the other options?

    There does not appear to be any Excel Help for this item.

  2. Carol said:

    Thanks so much for this information. I did not know this was possible and what a time saver too! I also tried playing with some of the other options with no results. So I guess I’ll have to contact the Excel help team to understand how they work, such as “growth”, etc.

Comments are closed.

Top