What’s new for Excel 2013 – Personal observations

The customer preview of Office 2013 is finally available, and there is a lot of new things to look at, be it for users or for developers. Here are a few personal observations based on my first look at the product. PowerPivot for Office 2013  now comes bundled with Excel, making Office On Demand, the evolution of the Click-To-Run technology, much more viable. The PowerPoint add-in is no longer required to build simple data models and pivot tables based on several data tables. Measures, KPIs and some other features will require the add-in to be activated. The message “To use multiple table in your analysis, a new PivotTable needs to be created based on the Data Model.” hints at the fact that old-school pivot tables are not dead yet and still are the default PivotTable type. New table objects in your workbook will automatically be added to your data model. You can create a pivot table based on a simple range and then transform the pivot table into a “PowerPivot model”. The initial range will not be transformed into a table object. Further ranges can be added through the Connections manager. (What for?) The data model can now be accessed from VBA. The corresponding object is called Model (not DataModel), despite what the MSDN documentation mentions about it. The PowerPivot add-in will not recognize tables that were added directly to the data model from Excel as linked tables. However, a new connection will be created in Excel, so that refactoring can be done from the Connections manager. A corresponding object called WorksheetDataConnection is available in VBA. You can create two worksheet data connections for the same range. Duplicating ranges for scenarios where the same data must be used twice (for example, when a dimension must play different roles) is no longer required. The function FILTERXML allows you to query an XML document with XPath. The WEBSERVICE function returns an XML text from a web service. ENCODEURL is a helper function to encode a string into a valid URL. All three functions are available in VBA through the WorksheetFunction object. The function FILTERXML returns an array. You can use it in array formulas, or in conjunction with the INDEX function, for example. New functions like SHEET, and SHEETS are available, although it is still unclear to me in which scenarios they will be useful, since there are still no specific functions a sheet index can re-used. ISFORMULA and FORMULATEXT are also new in Excel 2013. Examples in the help system are provided as embedded Excel Web App workbooks. This may solve some translation issues that have occurred in the past for non-English Office versions. Internet Explorer is used to navigate the VBA help. You can now alt-tab between the VBA environment and the documentation. Like this: Be the first to like this.