Continuing in the tradition of past versions, the new Excel 2013 comes with a set of some very interesting COM add-ins that greatly enhance the capabilities of standard Excel. Add-ons are connected on the tab developer (Developer) buttons Add-ons (Add-ins) и COM add-ins (COM Add-Ins). Some of the add-ins described below are only available in the full version of Office 2013 Professional.
Inquire
Powerful diagnostic and debugging tool. After connecting this add-in, a new tab on the ribbon appears in the Excel 2013 interface:
The add-in can carry out a detailed analysis of your books (Workbook Analysis) and issue a detailed report on more than three dozen parameters:
The add-in can visually display links between books in the form of a diagram (the command Workbook Relationship):
It is also possible to create a similar diagram for formulaic relationships between sheets and between cells within the same sheet using the commands Worksheet Relationship и Cell Relationship:
Such functionality allows you to quickly track and correct broken links in formulas and visualize logic in complex files.
The function deserves special attention. Compare Files. Finally there is a tool to compare two files in Excel! You specify two files (for example, the original book and its copy after making changes) and clearly see what, where and how has changed compared to the original:
Separately, with the help of different colors, changes in the contents of cells, formulas, formatting, etc. are highlighted. In Word, a similar function has been available since the 2007 version, and in Excel it was very lacking for many.
Well, to fight against those who like to fill all the rows or columns in the table with color, the function Clean Excess Cell Formatting. It removes formatting from unused worksheet cells outside of your tables, greatly reducing the size of the book and speeding up the processing, recalculation and saving of heavy slow files.
PowerPivot
This add-in was introduced for the previous version of Excel 2010. Previously, it had to be downloaded separately from www.powerpivot.com and specifically installed. Now (in a slightly modified form) it is included in the standard Excel 2013 package and is connected with a single checkmark in the add-ons window. The Power Pivot tab looks like this:
In fact, this add-in is an Excel-like user interface to a full-fledged SQL database, which is installed on your computer and is a powerful tool for processing huge amounts of data that opens in a separate window at the click of a button. Management (Manage):
Power Pivot is almost omnivorous – you can load information into it from several different sources at once: text files, databases, online cloud storage, other Excel or Access files, etc. – the full list includes almost twenty options and is available through the command Getting external data (Get External Data):
The size of the loaded tables is not limited. And since all processing takes place in RAM, the speed is very impressive. How about sorting 12 million rows in less than a second? Or building a pivot table for the same number of records in less than a couple of seconds?
Power Pivot tools allow you to link imported tables to each other by key columns, filter and sort them, perform mathematical and logical operations on them using more than 150 functions of the built-in DAX language (tab Constructor – Function Injection). Many of the tools of this add-in are now also present in the standard Excel 2013. In particular, the ability to build pivot tables from several source tables at once from different sources using the Data Model.
For those who are hungry for details, I recommend going to http://www.powerpivot.com/ and http://www.powerpivotpro.com/ – these sites contain a ton of information on this topic.
power view
This add-in was introduced to Excel 2013 from SharePoint, where it was called the Microsoft SQL Server 2012 Reporting Services Add-in. Its main purpose is to provide the user with tools for quickly creating visual “live” reports using pivot tables and database-based charts (what is now called the buzzword Business Intelligence = BI).
You can insert a Power View report sheet into the book using the button of the same name on the tab Insert (Insert):
Power View reports are based on the Silverlight engine. If you don’t have it, the program will download and install it by itself (about 11 MB).
Power View automatically “clings” to all data loaded into RAM, including the PivotTable cache and data previously imported into the Power Pivot add-in. You can add totals to the report in the form of a simple table, a pivot table, various types of charts. Here is an example of an interactive report I made in less than 5 minutes (without touching the keyboard):
Impressive, isn’t it?
It is quite remarkable that Power View allows you to link data from tables even to Bing geographic maps:
An absolutely fantastic tool. You can’t tell in a nutshell and even articles about it – I refer those who are interested to the relevant sites (one, two, three). If you have five minutes, watch this video demonstrating the main features and techniques of working in Power View.
Apps for Office
For Office 2013 and for Excel in particular, there is now an online software store – Office Store, where the user can buy or download additional modules for the package programs for free. You can do this using the command Apps for Office (Apps for Office) tab Insert (Insert):
True, there is no version of the store yet, so you are transferred to your native US store. The choice is quite large:
So, for example, at the moment, from there you can install an application for creating an interactive calendar on an Excel sheet, displaying Bing geographical maps, an online translation module, builders of various non-standard charts (waterfall, gantt), etc. The selected applications are inserted into the Excel sheet as separate objects and are easily linked to data from the cells of the sheet. I think the developer community will not keep you waiting and very soon we will see a large number of useful extensions and applications for Excel on this platform.