Serial numbers are a great element to add to bigger spreadsheets to uniquely identify a record.
Although Excel has row and column headings to act as serial numbers, Excel ignores this interface while printing your worksheet. Similarly, serial numbers assigned by organizations generally have leading zeros and dashes, which, obviously are very different from Excel’s rows and columns.
We have a few Excel tricks up our sleeves to help you fasten the process of adding serial numbers in Excel. Keep reading as we discuss both, the static and dynamic process of adding serial numbers in Excel!
Using Flash Fill
Flash Fill is personally my favorite part of Excel! If you’re looking to add serial numbers in a numeric format, look nowhere else. You can add Flash Fill to automatically add serial numbers even if they contain dashes and leading zeros.
To use Flash Fill, enter your data in the first cell of the range. With the cell selected, place your cursor on the bottom-right corner of the cell. After your cursor changes to a black plus sign, drag it and release it to the cell you wish to automatically add the serial number to.
Let’s discuss its application using two different examples.
Example 1
In the first example, we’re adding serial numbers the classic way. In cells A2 and A3, we entered “1” and “2” respectively. We’ll be using these two cells to help Excel identify a pattern and fill the remaining cells. As we’re looking to add 10 entries, we’ll be dragging the fill handle and releasing it in cell A11.
- Select cells A2 and A3.
- Place your cursor on the bottom-right corner.
- Drag the fill handle to cell A21 and release it.
Excel will automatically fill the selected cells with a serial number.
Example 2
Although this example is different, the process to automate the serial numbers is the same. For this example, we’ve used a serial key with leading zeros and dashes. We entered a different serial key, “000-001” in cell A2. What’s interesting about this is that, unlike the first example, you do not have to fill cell A3 with “000-002” to help Excel identify a pattern. This is because Excel automatically identifies such numbers as serial keys.
Using the same steps as example number 1, we dragged the fill handle to cell A11 and, Voila! Flash Fill has filled our cells with a serial number.
Add Serial Numbers Dynamically
If you’re dealing with a changing set number of records, using Flash Fill would be a bit time-consuming. This is because you will have to use the utility each time you make a unique entry. To overcome this, you can nest the COUNTA and SEQUENCE functions to add serial numbers dynamically.
The SEQUENCE() function is a new Excel function available only in Excel 365 and the web version of Excel. Similarly, this method is pretty limited if you’re looking to add serial numbers with leading zeros and dashes.
Enter your formula in the following format:
=SEQUENCE(COUNTA(range)-1)
In this example, we’re creating a record of the favorite subjects of each student in a university. In cell A2, we entered the following formula:
=SEQUENCE(COUNTA(B:B)-1)
The COUNTA function is used to count cells that are not empty. Each time you make an entry in column B, the SEQUENCE function will enter the corresponding serial number.
Create Custom Fill
We recommend this method if you have to enter a unique set of serial numbers frequently. You can either create a new list from the cells on your grid. However, if you’re importing ranges from the grid, make sure they’re text values. If you wish to import numeric cell ranges, format the values as text before you proceed.
Step1: Create the List
- Launch Excel.
- From the home page, head to Options.
- Select Advanced on the sidebar.
- Scroll down to the General section and click on the Edit Custom Lists button.
- Either create a new list under the List entries section or, click Import to create a custom list from the grid.
- Click OK.
Step 2: Insert the Fill
After you’ve created the list, it’s actually pretty simple to insert your serial number into the grid. Simply, enter two of the serial numbers in cells adjacent to each other, then drag the fill handle. Excel will automatically fill the selected cells with your custom list of serial numbers.