Automatic Numbering in Excel - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Thursday, January 23, 2020

Automatic Numbering in Excel


Automatic Numbering in Excel

Numbering means giving serial numbers or numbers to a list or a data. In excel there is no special button provided which gives numbering for our data. As we already know excel does not provide a method or tool or a button to give the sequential number to a list of data, which means we need to do this by ourselves.

 

How to Auto Number in Excel?

To do auto numbering in excel we need to remember that we have our autofill function is turned on. By default, it is turned on but in any case, if we do not have enabled or by mistakenly disabled autofill here is how we can re-enable it.
Now we have checked the autofill is enabled, there are three methods for excel auto-numbering,
  1. Fill a column with a series of numbers.
  2. Using row() function.
  3. Using offset() function.
#1 – In the file, tab go to options.
auto numbering example 1
#2 – In the advanced section, under the editing options check the Enable fill handle and cell drag and drop.
auto numbering example 1-1
 

Top 3 ways to get Auto Numbering in Excel

There are various ways to get automatic numbering in excel.
You can download this Auto Numbering Excel Template here – Auto Numbering Excel Template
  1. Fill a column with a series of numbers.
  2. Use Row() function
  3. Use Offset() Function
Let us discuss all the above methods by examples.

#1 – Fill the Column with Series of Numbers

We have the following data,
Fill the Series with Number Example 1
We will try to insert automatic numbers in excel Column A.
  • Select the cell in which we want to fill. In this example cell A2.
  • Write the number we want to start with letting it be 1 and fill the next cell in the same column with another number let it be 2.
Fill the Series with Number Example 1-1
  • We did the numbering 1 in cell A2 and 2 in cell A3 to start a pattern. Now select the starting values i.e. cell A2 & A3.
Fill the Series with Number Example 1-2
  • The pointer ( dot) in the selected cell shown by the arrow, click on it and drag it to the desired range i.e. cell A11.
Fill the Series with Number Example 1-3
Now we have sequential numbering for our data.

#2 – Use ROW() Function

We will use the same data to demonstrate the sequential numbering by row() function.
  • Below is our data,
Use Row Function Example 1
  • In cell A2 or where we want to start our automatic numbering in excel, we select the specific cell.
Use Row Function Example 1-1
  • Type =ROW() in cell A2 and press enter.
Use Row Function Example 1-2
It gave us the numbering from number 2 because row function throws the number for the current row.
  • To Avoid the above situation we can give the reference row to row function.
Use Row Function Example 1-3
  • The pointer or the dot in the selected cell click on it and drag it to the desired range for the current scenario to cell A11.
Use Row Function Example 1-4
  • Now we have our automatic numbering in excel for the data using the row() function.
Use Row Function Example 1-5

#3 – Using Offset() Function

We can do auto numbering in excel by using Offset() function also.
Again we use the same data to demonstrate the offset function. Below is the data,
Using Offset() Function Example 1
As we can see that I have removed the text written in cell A1 “Serial Number” as while using offset function the reference needs to be blank.
Using Offset() Function Example 1-1
The above screenshot shows the function arguments used in the offset function.
  • In Cell A2 type =offset(A2,-1,0)+1 for the automatic numbering in excel.
Using Offset() Function Example 1-2
A2 is the current cell address which is the reference.
  • Press Enter and the first number is inserted.
Using Offset() Function Example 1-3
  • Select Cell A2, Drag it down to cell A11.
Using Offset() Function Example 1-4
  • Now we have sequential numbering using the offset function.
Using Offset() Function Example 1-5
 

Things to Remember about Auto Numbering in Excel

  1. Excel does not auto provide auto-numbering.
  2. Check for Auto Fill option checked enabled.
  3. When we fill a column with a series of numbers we make a pattern, we can either use the starting values as 2, 4 to make an even sequential numbering.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment