Excel Files
- Authors:
- Created:
01/02/2020
- Modified:
12/15/2024
Purpose
To be filled in.
Frequent Questions
Does Ops Insights build formulas into the Excel Files it exports?
Why do my file numbers look correct in Ops Insights, but look like exponential numbers in Excel?
Does Ops Insights build formulas into the Excel Files it exports?
One of the purposes of Ops Insights is save people the time of having to do repetitive, time wasting tasks in Excel. All of the calculations that would be performed in an Excel function have already been performed by the Ops Insights system. Ops Insights just dumps those final calculated results into an Excel spreadsheet for convenience in sharing that information with others. Our goal is to save you the time of needing to deal with any Excel functions, so there is no need for us to build them into the Excel files that are exported or emailed out from Ops Insights.
Why do my file numbers look correct in Ops Insights, but look like exponential numbers in Excel?
When numerical column data is exported from the Ops Insights application and imported into an Excel Spreadsheet, the application automatically sets the formatting of each cell as a General Number. This is done because several numerical formats can be stored in a Title Production System.
General Number formatting does not typically present an issue when displaying numerical data in a cell but Excel defaults to use the exponential notation (replacing part of the number with E+n) for large numbers (12 or more digits), which typically causes an issue with loan numbers, tracking numbers, etc.
There are a few ways to solve the exponential formatting issue:
1. View the Report in the Ops Insights Application
Ops Insights Reports are designed to be used in a web browser for optimal user experience. If the formatting of an Excel export does not match what’s displayed in the Ops Insights application, you can execute the report in the browser. If you would like to share the report with a user inside your organization, a new user must be created and the report must be assigned to his/her access role. If you would like to share the report with a user outside of your organization, a new user must be created (external users are free) and the report must be assigned to his/her external access role.
2. Export the Results to PDF
The Ops Insights application does offer other exporting options. One option is to export the results to a PDF document. The formatting of a numerical column in a PDF export should appear exactly as it does in the Ops Insights application. This is a great solution for reports that do not return large data sets and only have a limited amount of columns.
3. Update the Number Formatting in the Excel Document
If the data must be exported to Excel, you can simply highlight the numerical cell(s), right click and select to format the cell(s) to a Number/Number format. If the number does not contain any decimal places, be sure to update the number of decimal places to 0.
Note
Leading Zeros - when formatting to a Number/Number format, if the number has leading zero(s) then those leading zero(s) could be removed from the formatting number to leave the first number that’s greater than zero as the leading number.
Numbers that Contain Text - Some loan numbers and tracking numbers can contain text along with numbers, this can cause complications with number formatting. There is a possibility of justification issues if a number leads with text. In that scenario the numbers that lead with text will be justified to the left and numbers that lead with numbers will be justified to the right.
The steps to update the number formatting in the Excel document are listed below:
This is what it will look like when exported to Excel along with the steps that are needed to solve the formatting issue.
Can Ops Insights create a multi-tabbed Excel workbook with results from numerous reports, each in its own tab?
In short the answer is that Ops Insights cannot create a multiple tabbed Excel workbook comprised of results from multiple reports. To provide some context on why that is the case, below are some bullet points of our thoughts when designing Ops Insights.
A report is essentially just a way to answer one business question, thus there is a one to one relationship. Allowing only the results of one report into an exported Excel maintains focus on answering that one business question that needs answering.
If a more robust or collective view is needed from multiple reports, that is what we intended to be used when designing and creating the user dashboard inside Ops Insights. Someone can login and quickly discern many aspects of how the business is doing. From orders to revenue to workflow and beyond - many business questions can quickly be answered from viewing and interacting with the dashboard inside of Ops Insights.
Each exported Excel report needs additional information with it to provide context. For instance, if someone were looking at an exported report of Orders Opened, they would not know what time frame was used for this exported data. Also, they would not know if it was further filtered by Region, Branch, etc. In essence, they would not know what filters (criteria) were applied to that result set. Also, they might make assumptions about the purpose or other information was intended when extracting that data. That is why our Excel exports also include information about the reports purpose and how it works (Report Description) in addition to the filters (Criteria) applied when exporting the data. To try and provide that information for an exported Excel workbook with results for multiple reports would quickly become too cumbersome and bulky.
Upon monitoring and refining Ops Insights we noticed that most often the people that would like everything packed into one very large, multi-tabbed Excel workbook would also like a large dataset over a large time frame. This presents challenges beyond the points listed above in that the file size of these reports can very quickly exceed 25 MB in size. Usually 25 MB is the maximum file size that email servers will let through for attachments (oftentimes it is 10MB). If we allowed for these types of reports, we would spend more time explaining and re-explaining to end users that the reason they did not get their multi-tabbed / multi-report Excel workbook emailed to them was because their email server rejected it and not something to do with Ops Insights.
We want people to spend more time in Op Insights and within the application analyzing data rather than in an Excel workbook. We built Ops Insights to mainly be used in the application itself, and to quickly and easy get answers to business questions without having to spend hours or even days pivoting data in Excel. Time is money and one of the purposes of Ops Insights is to cut down on repetitive tasks like exporting data into Excel and then pivoting it various different ways. If there is a business question that needs to be answered by pivoting data to make some comparison then we can help create a custom report in Ops Insights to make that comparison fast, easy and systematic so that time and money are not wasted.
Summary data does exist in Ops Insights and is what powers each visualization in the system. These can be sent out of the system by setting up and Automated Delivery (emailed report) or just viewing the data on the dashboard (the visualization is supposed to make it even easier to consume the summary data).
There are some additional points as well, but the above bullet points are the main ones.