When working with dates in Excel, it’s common to encounter data inconsistencies such as #N/A, 1/0/1900, or blank values in the ‘date’ column. Converting such columns to datetime format can be challenging, and it may lead to errors or unexpected results. For example, in the code provided below, an error occurs when attempting to convertContinue reading “Python Pandas Date Conversion: How to Handle #N/A, 1/0/1900, and Blank Dates in Excel”
Tag Archives: excel
Rename Excel Sheet Name Using openpyxl in Python
Imagine you have multiple Excel files in a folder and you want to change the sheet name of all Excel files that contain “Documentation*” to just “Documentation”. As an example, let’s say you have two Excel files in a folder, and each Excel file has two sheets. One of the sheet names contains “Documentation”, andContinue reading “Rename Excel Sheet Name Using openpyxl in Python”
Create a chart from Excel data in Python with matplotlib
In this post, you will learn how to use Pandas, Matplotlib, and BytesIO to visualize the data from an Excel file. The step we need is below: Read Excel file with Pandas. In this part, you can use CReadExcel or (CExcelFile & CSheet) classes from the previous post “How to read excel (xls, xlsx) fileContinue reading “Create a chart from Excel data in Python with matplotlib”
Filter Excel Pivot Table with VBA
You can toggle the filter of PivotTable on and off from VBA code and change the value to whatever you like and “Refresh” the PivotTable. Here is how you can do it: Create a Pivot Table in VBA Let’s say you have below data sample: From excel data above create a pivot table “Insert ->Continue reading “Filter Excel Pivot Table with VBA”
Software Technology 