Excel Top Tip #8 - Improve Excel with Python

Here is the email sent to all subscribers on 28 January 2021. To receive Excel top tips just like this, you can subscribe by clicking here.

Hello

Ever wondered how coding could help you? Learning to code with Python allows you and your team to automate Excel in an endless number of ways.

To get started, we advocate finding a problem early on. It works to give learning structure and pulling you towards a goal (it’s how StatementReader began!)

With Python, I started with cheatsheets for basic familiarity and then picked up the O’Reilly Python Cookbook, always with the aim in mind - that I would start my Python journey writing scripts that solve problems I see in Excel.

And here are some problems, with links to the full code, that I wrote to improve Excel (and learn Python):

“Combine multiple Excel files from a folder” [link] Sometimes you just want all the data in a single Excel Sheet.

Our Python code loops through each Excel file in a folder (including subfolders), collects the data in the first sheet, and adds a new column for reference back to the filename. The aggregated data is then output to a new Excel Workbook.

“Split Excel file into separate files” [link] Handy for managing the access to a dataset that splits easily into parts.

Using Python we can automatically read the first sheet from an Excel file, split the data by the first column, and save each part into separate Excel files, which are named after the value in the first column.

“List Excel file attributes in Excel” [link] File attributes of Excel files (the file properties accessible on right-click) are seldom used to their full potential.

Our script allows you to see the title, description, subject and other details (like date last modified) for multiple Excel files held in a single folder, all in one spreadsheet.

“Set the file attributes for multiple Excel files” [link] Setting file attributes for multiple files across multiple folders can be so time consuming and prone to error that they aren’t updated.

With Python we get the file attributes (the title, description and subject) from a file the user selects, and copy them to all Excel files within a folder.

“Track changes in Excel” [link] As standard, tracked changes in Excel are presented in a separate tab, making it difficult to audit the changes made.

Python works with Excel events to trigger custom operations, like noticing the changes made in Excel, we can then update the cell comments with a time and the cell’s previous value.

Tackling any repetitive task with code is a super-power; get started today by seeing how readable these ready-to-go scripts are, and find your own problems to solve. Any questions or requests, just get in touch.

Thank you,

The StatementReader Team