eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Microsoft has new updates in store this week for Excel 2016 power users, including performance enhancements and more flexible ways of transforming and mashing up data.
After applying the update, available now to Office 365 subscribers, Excel 2016 will allow users to append multiple tables using a single Append operation. In the past, combining more than two tables required users to select the first two (primary and additional table) and subsequently build upon them one table at a time. Now, a new “Three or more tables” option in the Append tables dialog box allows users to select and add multiple tables in one shot.
Excel 2016 also does a better job of handling jagged CSV (comma-separated values) files. Jagged CSV files contain an unequal number of columns across its rows, hence the term.
“With this update, the detection logic for data columns in CSV files has been dramatically improved to recognize these extra columns,” wrote the Microsoft Excel team in a blog post. “There’s no additional user action required to benefit from this improvement (besides importing the CSV file again using the latest product version).”
Query Editor data previews now display more data, allowing users more freedom in exploring a table. Previously, previews imposed 3,000-cell and 100-column limits. Now, using a new virtualized view, users scan scroll within a table to see all of its contents.
In addition, the April 2016 update introduces a new one-click percentage transform feature. After selecting a column, users can pick the new Query Editor option to quickly create a new column based on a percentage calculation of their choosing.
Users may also notice that routine Query Editor transformation operations, like renaming, removing and reordering columns, can be accomplished faster. Excel no longer requires a reload of the data from a remote data source and instead executes these actions locally, improving performance.
Another performance-enhancing tweak, the ability to switch off Navigation Columns when using database connectors, helps provide a snappier, more responsive user experience.
While helpful in many instances, “it turns out that the cost of retrieving Navigation Columns for a given table is high enough that it degrades the performance of retrieving data previews, thus degrading the overall user experience for simple scenarios,” explained the Microsoft staffers. Practically all relational database connectors support the option to disable Navigation Columns, except for Azure SQL Database, SQL Server and SQL Server Analysis Services.
Users can eke out further performance gains by disabling background downloads of data previews on Excel workbooks. The feature appears under Query Options while creating a new query in the Data menu item.
Rounding out the new functionality is the ability to sort columns alphabetically using the Choose Columns transformation builder, improved invalid URL handling in the SharePoint List connector and OAuth support on the Exchange Server connector. When connecting to Web data sources, Excel now enables users to specify where their credentials apply, from the domain level in a URL to a specific page.