When we are working with Excel, numbering the rows is an issue as manually adding row numbers to a large dataset is time-consuming and prone to errors. Row numbers are necessary as helps in easy identification and referencing different rows of data. What if i say i can make this take super easy for you?

Python offers a convenient way to automate this task, making it easier to manage your Excel data.

Using Python, you can read an Excel file, add a new column for row numbers, and then save the updated data back to the Excel file. This automation can save you time and effort, especially when dealing with large datasets.

To add row number automatically to an existing excel sheet. First create a file in your editor and save it with .py extension with the below code in it

Original Excel Sheet:

import pandas as pd

# Load the Excel file
file_path = 'employee_names.xlsx'
df = pd.read_excel(file_path)

# Remove the existing 'Row Number' column if it exists
if 'Row Number' in df.columns:
    df.drop('Row Number', axis=1, inplace=True)

# Add a new column for row numbers
df.insert(0, 'Row Number', range(1, 1 + len(df)))

# Save the updated data back to the Excel file
df.to_excel('employee_names.xlsx', index=False)

Output:

Explanation of the whole code above:

1. import pandas as pd: This line imports the pandas library and gives it an alias ‘pd’, which makes it easier to refer to pandas functions later in the code.
2. file_path = 'employee_names.xlsx': This line defines a variable file_path and assigns it the path of the Excel file (’employee_names.xlsx’) that we want to work with.
3. df = pd.read_excel(file_path): This line reads the Excel file specified by file_path into a pandas DataFrame called df. A DataFrame is like a table in a database or a spreadsheet, with rows and columns of data.
4. if 'Row Number' in df.columns:: This line checks if the DataFrame df already has a column named ‘Row Number’. The df.columns attribute returns a list of column names in the DataFrame.
5. df.drop('Row Number', axis=1, inplace=True): If the ‘Row Number’ column exists, this line removes it from the DataFrame. The drop method is used to remove columns (or rows) from a DataFrame. The axis=1 argument specifies that we are removing a column (1 for columns, 0 for rows), and inplace=True specifies that the operation should be done on the DataFrame itself, without creating a new DataFrame.
6. df.insert(0, 'Row Number', range(1, 1 + len(df))): This line adds a new column named ‘Row Number’ to the DataFrame df. The insert method is used to insert a new column at a specific position (index 0 in this case). The range(1, 1 + len(df)) generates a sequence of numbers starting from 1 to the length of the DataFrame (len(df)), which corresponds to the row numbers.
7. df.to_excel('employee_names.xlsx', index=False): Finally, this line saves the updated DataFrame df back to the Excel file ’employee_names.xlsx’. The index=False argument specifies that we don’t want to write the row indices to the Excel file.

Conclusion:
Automatically numbering rows in Excel using Python can greatly improve efficiency and accuracy when working with Excel data. By automating this task, you can quickly add row numbers to your dataset, making it easier to organize and reference your data. Whether you’re working with a small or large dataset, Python provides a convenient way to streamline your workflow and improve productivity.