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.
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 makes it easy
- 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.
- Go to the
Editmenu and choose
Fill > Series. The Series window appears.
- 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 valueof 1. In the
Stop valuebox enter the value of the final number you need.
- 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.