Skip to content

How to append data in excel using Python

append data in excel using python

To work with Ms Excel and Python seamlessly we should know how to append data in excel using Python . For this reason we will take two .xlsx file (named demo1.xlsx and demo2.xlsx) which will look like below –

Now we will append data of demo2.xlsx after data of demo1.xlsx . We will do this in two ways –

Append data in excel using Python by pd.concat() method

import pandas as pd

# Reading the demo1.xlsx

# reading the demo2.xlsx

# appending df2 after df1
# df3=df1.append(df2)  
# append method will be deprecated in future version
# so we will use concat method

# creating a new excel file and save the data

In the first way we will load both the file in the program as pandas dataframe as df1 and df2 . Then we will create df3 from df1 and df2 using pd.concat() method. After that we will create a new excel file demo3.xlsx and put all the data of df3 into that . Below is the image of demo3.xlsx


We can also use append() method to append df2 after df1 . But this method will be deprecated in future . So , we should always use pd.concat() method instead of append() method (Recommended by pandas) .

Suppose we have a master excel file in which we have to append data in daily basis . After appending data in daily basis the size of the master file becomes very big . So, it is not feasible to load that huge amount of data every time in pandas dataframe just to append some small amount of data . So we will solve this problem using openpyxl module of Python .

Append data in excel using Python openpyxl

import pandas as pd

# read the demo2.xlsx file

# appending the data of df after the data of demo1.xlsx
with pd.ExcelWriter("demo1.xlsx",mode="a",engine="openpyxl",if_sheet_exists="overlay") as writer:
    df.to_excel(writer, sheet_name="Sheet1",header=None, startrow=writer.sheets["Sheet1"].max_row,index=False)
  • In the above code first we have imported pandas library and read the demo2.xlsx file as df .
  • Then we need to create an ExcelWriter object which will open the demo1.xlsx file in append mode (mode="a") . We have used openpyxl as engine and overlay as if_sheet_exists . By default value of if_sheet_exists is error (which will give ValueError if the sheet exists) . Other values are new (will create a new sheet , name will be determined by the engine) , replace (will delete the old content before writing to the sheet) and overlay (will append the data in the existing sheet without removing the old data)
  • After that by to_excel() method we have appended data of demo2.xlsx after the data of demo1.xlsx in the demo1.xlsx file . Here we have used writer.sheets["Sheet1"].max_row as startrow which will automatically calculate the length of previous content .

We can also see the image of new demo1.xlsx for reference –


We also need to install openpyxl to use the above example .To install openpyxl open PowerShell window , type the below code and press Enter key –

pip install openpyxl

Version of pandas should be greater than or equal to 1.4.0 for the above example .


In the first way we need to load data of both the file in memory but in the second way we only need to load the data of that file which will be appended . So second method is more efficient as it takes less memory .

Thank you for reading this Article . If You enjoy it Please Share the article . If you want to say something Please Comment .

14 thoughts on “How to append data in excel using Python”

  1. When i run the code for Append data in excel using Python openpyxl, i got following error:

    “C:\Program Files\Python39\python.exe” “C:/Python/20220809/Excelcombine already existing excel sheet/”
    Traceback (most recent call last):
    File “C:\Python\20220809\Excelcombine already existing excel sheet\”, line 7, in
    with pd.ExcelWriter(“demo1.xlsx”,mode=”a”,engine=”openpyxl”,if_sheet_exists=”overlay”) as writer:
    File “C:\Users\emdzaka\AppData\Roaming\Python\Python39\site-packages\pandas\io\”, line 52, in init
    File “C:\Users\emdzaka\AppData\Roaming\Python\Python39\site-packages\pandas\io\”, line 943, in init
    raise ValueError(
    ValueError: ‘overlay’ is not valid for if_sheet_exists. Valid options are ‘error’, ‘new’ and ‘replace’.

    Process finished with exit code 1

  2. Pingback: Homepage

  3. Pingback: see here

  4. Thanks its helps a lot. I would like to ask another question, how can append data from one workbook to below of another workbook data with a table design?

  5. I would like to convey my admiration for your generosity in support of men and women that have the need for help with this particular concern. Your special dedication to getting the message all over had been wonderfully productive and have all the time made professionals much like me to attain their dreams. Your own invaluable tutorial means a great deal to me and additionally to my office workers. Thank you; from everyone of us.

  6. I’d like to thank you for the efforts you’ve put in writing this
    site. I am hoping to check out the same high-grade blog posts by you in the future as well.
    In truth, your creative writing abilities has inspired me
    to get my very own blog now 😉

  7. Wow that was strange. I just wrote an very long comment but after I clicked submit my comment didn’t appear.
    Grrrr… well I’m not writing all that over again. Regardless, just wanted to say superb blog!

Leave a Reply