Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
For some data tables in Excel, you may want to assign a record number to cells in a particular column. For instance, you might want record numbers for 20 different records, ranging between 1 and 20 or between 100 and 119. It doesn't really matter to Excel what range you select. How you go about setting up the record numbers depends on what you want to later do with them.
If you want the record numbers to be static—that is, they are always assigned to a particular record and never change—then you should use the AutoFill feature of Excel to assign the numbers. To do this, simply enter all your data except the record numbers. Then type in the first two or three record numbers, select them, and drag on the AutoFill handle (the black square at the bottom-right corner of the selection) to fill out the rest of the records.
Using this approach is fast and easy, but it does make the record numbers static. For instance, if you delete the record that has a record number of 107, then that particular record number is gone, and your numbers will show a gap, jumping from 106 to 108.
If you want dynamic record numbers—ones that will change as you make deletions—then you can use a formula to calculate the record numbers. You could put the first record number in, for instance, cell A5, and then in the next cell down you would use a formula such as =A5+1 to calculate the new record number.
This still presents a problem, however, because if you delete a record, all the record numbers below the one you deleted will show an error (#REF!). Why? Because you delete a cell on which the next cell down was dependent. A better solution is to use a record number formula that is dependent on the row in which the formula is located. For instance, let's assume your first record is in row 5. You could use this formula to generate a range of record numbers starting with 100:
=ROW()+95
Now, if you delete a record, the remaining record numbers readjust themselves and you don't end up with any errors.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2085) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates.