Elevate Efficiency: Automating Excel Tasks With Python

Excel is one of the most popular tools used worldwide. Irrespective of the company size, be it corporate bigwigs or local grocery shops, or even maintaining a personal vacation budget, excel is a common tool used by all. Since every basic tabulation of data is preferred in an excel format, it’s only logical to keep it that way. However, automating recurring Excel tasks can be challenging, but we need not give up such a useful and widely accepted format altogether. One would argue the use of Power Query to automate a few steps or maybe VBA as an alternate, however, these methods are not very portable.

Here are three basic and the most important Python code snippets for interacting with excel (The below codes incorporate reading and writing data into the excel workbook):

1. Create a Pivot Table

To create a pivot table, you require pandas and numpy modules. The first requires you to import these modules. Next, you need to read the data and ingest it into a variable. The .pivot_table() method creates the pivot table by providing necessary arguments such as index, values, columns and aggregate functions. Finally, you save the pivot output in another excel.

print(pivot_output)

Save the pivot on the excel sheet
pivot_output.to_excel(r'C:\Users\', sheet_name='Sheet1', startrow=5)

 
2. Refresh Pivot Table/Power Query

In this we use the *win32com* library to refresh the pivot table. First, create an object and open the excel application. Using the object, open the excel file containing the pivot table by providing the source path. *RefreshAll()* method used on the excel instance refreshes all the data connections.

3. Formatting excel - Unmerge cells: 

Openpyxl is the most popular library for formatting excel. Here, you use a ‘for’ loop to iterate over the merged cells everywhere in the active sheet and apply the *.unmerge_cells()* method to get the output.

**NOTE:** Important Note: install all the libraries beforehand. (example: run “pip install pandas” in command prompt to install pandas)

Conclusion

In the past, almost all my work, personal or professional, required me to create pivots or perform filtering data in excel. Excel is simple to use but the recurring tasks, that too on a tightly scheduled workday, seem a lot. Hence, I started looking for automation techniques. Python got the spotlight because of the huge library support it has, and I had completed a lot of projects during my learning phase. It is thrilling to see how by running a query can get repetitive tasks completed magically in seconds without opening a workbook. I hope you find the blog useful. Stay tuned for more on automation using Python.