Convert All Excel Formulas into Values using Python
Introduction
In this post, I will guide you through the process of converting Excel formulas into values using Python. This is particularly useful when you have a worksheet filled with formulas and want to simplify it by replacing those formulas with their calculated values. Let’s dive into the steps involved.
Understanding the Process
First, let me show you how to handle multiple Excel files in a folder. I have a collection of Excel files, and I will demonstrate how to convert the formulas present in these files to values.
As observed in the first worksheet, there are formulas used to calculate profit. The overview sheet also contains grey-shaded cells with formulas. The objective is to convert all such formulas into values.
Setting Up Your Python Environment
To begin, open a new Python file. The first step is to import the necessary modules. I will be using Path from the pathlib module to manage file paths and openpyxl for manipulating Excel files. If you haven’t installed openpyxl, you can do so by running the following command in your terminal:
pip install openpyxl
Creating Input and Output Paths
Next, I will get the path to the current working directory of my Python file. This is essential for locating the folder containing the Excel files.
In my case, the folder with all the Excel files is named Input. I will also create an output folder where the converted files will be saved. Using the pathlib module makes this process straightforward.
Processing Excel Files
Now that the paths are set up, I will retrieve all the Excel files in the input folder. I will loop through these files, opening each workbook with the data_only parameter set to true. This ensures that I read only the values, not the formulas.
Once the values are read, I will save the workbook in the output directory, appending _valuecopy to the original filename. This way, the original files remain unchanged.
Validating the Results
To check if the conversion was successful, I will navigate to the output folder and open one of the workbooks. You will notice that all the formulas have been converted to values.
Converting Specific Sheets
Sometimes, you may want to convert only specific sheets within a workbook rather than the entire workbook. For this, I will demonstrate using xlwings, another third-party package. Ensure you have it installed with:
pip install xlwings
This time, I will define a list of sheets that I want to convert. In this example, I will only convert the sales sheet. The code structure remains similar, but this time I will open Excel in the background and iterate through the specified sheets.
Within each specified sheet, I will convert the used range to values, save the workbook, and append _valuecopy_only_sht to the filename to avoid overwriting previous results.
Execution Time
Keep in mind that this process may take some time, especially when using the Excel instance for conversion. In my case, it took around 8 seconds to complete.
Once done, you will find the converted files in the output folder. If you open one, you will see that only the formulas in the sales sheet have been converted, while the overview sheet retains its formulas.
Conclusion
In summary, converting Excel formulas into values using Python can streamline your data and make it easier to manage. I hope this guide helps you understand the process and apply it to your own Excel files.
