How to Automatically Number Columns in Excel 2016: A Step-by-Step Guide

Are you tired of manually numbering columns in Excel 2016 each time you create a spreadsheet? Well, you’re in luck! Simply by using the “Fill Series” function or a quick formula, you can number your columns automatically in just a few clicks. This not only saves time but ensures consistency across your data. Let’s dive into how you can make your columns number themselves like magic!

Step by Step Tutorial: Automatically Number Columns in Excel 2016

Before we get into the nitty-gritty, let’s briefly understand what we will be achieving with these steps. We will be using Excel 2016 to automatically number columns, eliminating the need to input numbers manually. This can be particularly useful when dealing with large datasets or when you need to add rows and want the numbers to update automatically.

Step 1: Select the First Cell in Your Column

Click on the first cell where you want your numbering to start.

Selecting the first cell is crucial as it tells Excel where your numbering sequence should begin. If you have headers, make sure to start in the cell directly below them.

Step 2: Enter the Starting Number

Type the number you want to start with, usually “1”, into the selected cell.

Entering the starting number initializes the sequence. This number will tell Excel the base value from which to begin the numbering.

Step 3: Drag the Fill Handle Down

Click and hold the fill handle at the bottom right corner of the cell, then drag it down to the last cell you want to number.

Dragging the fill handle down the column allows Excel to understand that you want to continue the sequence. If done correctly, Excel will automatically fill in the numbers in sequence.

Step 4: Use the Fill Series Option

Alternatively, you can use the “Fill Series” option by going to the ‘Home’ tab, clicking on ‘Fill’ and then ‘Series’, and selecting the appropriate options.

The “Fill Series” function is a more advanced feature that gives you additional control over the numbering. You can specify the step value and stop value for your series, making it a versatile option for more complex numbering needs.

After completing these steps, your column should be numbered automatically from your starting number down to the last cell you selected. If you need to add more rows later, simply drag the fill handle from the last numbered cell, and Excel will continue the sequence for you.

Tips: Enhancing Your Experience with Automatically Numbered Columns in Excel 2016

  • To ensure your numbering updates automatically when rows are added or deleted, consider using an Excel formula such as =ROW(A1) where A1 is the starting cell.
  • If you want to start your sequence with a different number, simply change the starting number in Step 2 to your desired value.
  • You can also number your columns backwards by using a negative step value in the “Fill Series” dialog box.
  • If your sequence needs to skip numbers (e.g., only even numbers), adjust the step value accordingly when using the “Fill Series” option.
  • Keep in mind that dragging the fill handle will only work for contiguous cells. If you have gaps, use the “Fill Series” function or a formula.

Frequently Asked Questions

Can I automatically number columns in Excel without dragging?

Yes, you can use the “Fill Series” function or a formula to number the columns without dragging.

What if I want to start numbering from a number other than one?

Simply enter that number as the starting number in Step 2.

How do I get Excel to continue numbering automatically when I add new rows?

Use the fill handle to drag from the last numbered cell down to the new cells, or use a formula that references the row number.

Can I number columns in a non-sequential order?

Yes, adjust the step value in the “Fill Series” dialog box or use a more complex formula to achieve this.

What should I do if the numbers don’t fill in automatically?

Make sure you’re dragging the fill handle correctly, and if you’re using the “Fill Series” function, ensure the correct options are selected.


  1. Select the first cell in the column.
  2. Enter the starting number.
  3. Drag the fill handle down or use the “Fill Series” option.
  4. Enjoy your automatically numbered columns!


Automatically numbering columns in Excel 2016 is a breeze once you know how to do it. With just a few clicks, you can save time and avoid the monotony of manual input. Whether you’re a seasoned Excel user or a newcomer, mastering this simple yet powerful trick will enhance your spreadsheet skills and improve your workflow efficiency. So, the next time you find yourself starting to type out numbers one by one, remember this guide, and let Excel do the heavy lifting for you. Happy numbering!

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time.
Read our Privacy Policy