How to create a report in Excel using QXL spreadsheet data
I want to refresh my spreadsheet automatically from QXL exported spreadsheet. So, my spreadsheet data gets updated automatically using QXL exported spreadsheet.
How to create MS Excel spreadsheet which gets updated data automatically from QXL exported Excel files?
Please follow below steps to create MS Excel spreadsheet which gets updated data automatically from QXL exported spreadsheet data:
1. Create New MS Excel file.
2. Navigate to "Data" tab & click on "Connections" for adding QXL exported spreadsheet connection.
3. Add connection by clicking on "Add".
Click on "Browse for More.."
Navigate to QXL Exported sheet folder.
Default QXL Export sheet folder path is "This PC" --> "Documents" --> "QXL" --> YourQuickBooksCompanyName
In this example, QXL export sheet folder path is: "This PC" --> "Documents" --> "QXL" --> Rock Castle Construction
Select QXL exported Excel file which you want to use as a source & click on "Open".
Connection added. Click "close" button.
4. Import Data from the added connection by clicking on "Existing Connection".
Select connection & click on "Open".
Configure Imported Data property & click "OK".
5. Data Imported.
Now we are hiding column which we don't want to use. You can hide column/columns by selecting column/columns right click on it & click on "Hide".
In this example, I want to see only Unpaid Invoice. So, I have filtered IsPaid column.
I have added new column Due Date which will get data from DueDate field. We are converting this DueDate data to Date using DATEVALUE function.
By converting it to DateTime data type, the value displayed on the Excel is in numeric and correct value is shown only after formatting the cell.
Now we will hide Original DueDate column & add the Aging column which we use for count Aging Days of Invoice payment due.
We can count Aging using the DATEDIF function.
Now, let us sort data according to Customer name & use conditional formatting to show aging data.
After applying all the formatting finally, data looks as below.
To refresh your Excel sheet report, Use QXL Application and Click "Export" button. Once your source is updated you can refresh your Excel sheet.
To refresh Excel sheet report, click "Refresh All" button from the menu Data-->Refresh All menu.