Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Automatically Add Serial Number in Excel

    How to Automatically Add Serial Number in Excel

    AsmiBy AsmiFebruary 7, 2023 Excel 4 Mins Read

    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

    use flash fill to fill 123

    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.

    1. Select cells A2 and A3.
    2. Place your cursor on the bottom-right corner.
      flash fill 123
    3. Drag the fill handle to cell A21 and release it.
      fill 123

    Excel will automatically fill the selected cells with a serial number.

    Example 2

    flash fill serial number3

    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.

    flash fill serial number1

    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)

    SEQUENCE function

    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)

    Sequence formula Excel

    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

    1. Launch Excel.
    2. From the home page, head to Options.
      Excel Options
    3. Select Advanced on the sidebar.
    4. Scroll down to the General section and click on the Edit Custom Lists button.
      Edit Custom Lists Excel
    5. Either create a new list under the List entries section or, click Import to create a custom list from the grid.
      Import data for custom fill
    6.  Click OK.

    Step 2: Insert the Fill

    Flash Fill Excel

    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.

    Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Using Flash Fill
        • Example 1
        • Example 2
      • Add Serial Numbers Dynamically
      • Create Custom Fill
        • Step1: Create the List
        • Step 2: Insert the Fill
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.