Automate Excel with Python Tutorial (2020)

24 Comments



In this video, I will show you some ways through which you can automate your excel workbooks. Python is known to be an excellent programming language for automation. It has many libraries that can help you manipulate data very easily. Two of my favorites are openpyxl and pandas. I will show you how we can effectively use them to retrieve information, merge, search and filter data, and compute operations in excel. Let me know if you’d like to see any automation ideas in the next videos.

Github repository: https://github.com/DavideMerlin/Excel_Automation

Automate the boring stuff with Python: https://automatetheboringstuff.com/chapter12/

More about Pandas Dataframes: https://pandas.pydata.org/pandas-docs/stable/user_guide/dsintro.html#dataframe

Automating Life in Python: https://www.youtube.com/watch?v=eWBXqjG9X0w&t=126s

#Python #Automation

00:00 Intro
00:57 Get values from different workbooks
2:52 Merge workbooks
4:39 Search and filter data in workbooks
6:02 Apply formulas across workbooks
7:00 Outro

Useful Links:
▶ Github: https://github.com/DavideMerlin
▶ Instagram: https://www.instagram.com/davidemerlin97

source

24 Comments
    • blank
      Michael Ibanez
      May 06, 2022 13:03 pm Reply

      In this video, it assumes people know the cell value. But what if you don’t know the cell values and you have appending queries running. I’m currently trying to update cell value as new data gets added to excel table. Any thoughts on how that can be accomplished or have any videos on iterating through rows of data to find keywords and update cell values that contain keywords in the cell?

    • blank
      United DingDong
      May 06, 2022 13:03 pm Reply

      Very good for someone who is already familiar with Py but not for someone who wants to learn.

    • blank
      Awais Vlogs
      May 06, 2022 13:03 pm Reply

      U need to slow down bro hahah

    • blank
      Dipankar Panda
      May 06, 2022 13:03 pm Reply

      Are you in a hurry sir.. its very difficult to follow.

    • blank
      Ron Natalia
      May 06, 2022 13:03 pm Reply

      Hi Merlin thanks for your post this is exactly what I am looking for to begin to learn Pyhton after completing a very basic tutorial.

    • blank
      Erick Gutiérrez
      May 06, 2022 13:03 pm Reply

      Thanks dude I am just starting learning Python

    • blank
      Afra Al-Zadjali
      May 06, 2022 13:03 pm Reply

      thank you brother

    • blank
      Khashti Shri
      May 06, 2022 13:03 pm Reply

      excel_files = ["C:UsersshreePycharmProjectsBook1.xlsx","C:UsersshreePycharmProjectsBook2.xlsx"]
      ^

      SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated UXXXXXXXX escape

      Can you tell why?

    • blank
      Jörg Bockius
      May 06, 2022 13:03 pm Reply

      Hey thank you for your video! I am struggling to print a whole workbook (send it to my printer) but i can't save in Excel to keep printing the whole Workbook or set the value in python to print all workbooks
      How can i solve this?

    • blank
      Abc Xyz
      May 06, 2022 13:03 pm Reply

      It seems very static. What if table has different row numbers and file and tab name changes?
      Or Output location changes since table size may change?

    • blank
      Bottom Barrel Budget Films
      May 06, 2022 13:03 pm Reply

      What IDE do you use? I've been using PyCharm

    • blank
      mhaddadi
      May 06, 2022 13:03 pm Reply

      Can python read cells from open sheet?

    • blank
      I M
      May 06, 2022 13:03 pm Reply

      But you can do all this in Power Query… No?

    • blank
      nagendra vishwamitra
      May 06, 2022 13:03 pm Reply

      Sir could you please help to make a video n how to perform vlookup function between 2 excel files

    • blank
      houseplayer1000
      May 06, 2022 13:03 pm Reply

      PowerQuery alongside PowerPivot is a much better solution for this type of tasks

    • blank
      mjtr1970
      May 06, 2022 13:03 pm Reply

      Excellent tutorial but little fast for a beginner. Meanwhile how python use diff from using powerquery as it will also enables automation.

    • blank
      Jonathan Rivera
      May 06, 2022 13:03 pm Reply

      Why a mac?

    • blank
      Marcos Silva
      May 06, 2022 13:03 pm Reply

      thanks

    • blank
      boooring learning
      May 06, 2022 13:03 pm Reply

      brilliant video, thanks!

    • blank
      Tyler Southcott
      May 06, 2022 13:03 pm Reply

      I know these is an introductory example of uses but most of these I can use built in Excel features like query, pivot, merge options, etc and that seems easier than going into python and writing up code for it?

    • blank
      arquimedes castillo
      May 06, 2022 13:03 pm Reply

      What IDE do you use as a text editor?

    • blank
      Syrian Eagle
      May 06, 2022 13:03 pm Reply

      I don’t understand it’s so complicated

    • blank
      jg
      May 06, 2022 13:03 pm Reply

      And here is that other video I promised https://www.youtube.com/watch?v=uEgJLLHBlZU

      This one automatically organizes music album files into their respective and automatically created folder. And it does this for all the alubms. So amazing to see it execute and do it all automatically. Hopefully I have this walk through video for other users and anyone who is interested in Windows Automation. Let me know if you get any questinos that I can help with.

    • blank
      qqiangg
      May 06, 2022 13:03 pm Reply

      Can you give a link for us to view your codes clearly? Your screen is too small to view.

Leave us a comment