Excel 2013

New Features of Excel 2013

By Sean Vincent

Dual Screen Use

One of the simplest updates to Excel in the latest version is the ability to now have 2 separate Workbooks open on shared screens. This allows much greater flexibility in the work environment. Rather than having to Tab between workbooks (potentially causing a headache from slipping screens over and over) in order to (e.g.) compare data, providing you have a dual screen set up, you can now have both Workbooks open simultaneously.

Along with the new dual screen feature, Excel 2013 allows the user to open two separate Workbook files and copy and paste formulae and formats between them.

Excel 2013 dual screen(1)

 

SkyDrive

Microsoft has developed their own Excel “Cloud” server which a user can save their Excel (and other related Office programs) into the Cloud. This allows for greater flexibility in the uploading and syncing of documents to a cloud storage and then access them from a Web browser or their local drive.

Excel SkyDrive

 

Flash Fill

This new feature will predict your next step in a given set of data, and auto-fill the proceeding empty cells. Below are 2 basic examples of how it works.
This new feature removes potentially long and data sapping formulas typically required for such solutions in previous Excel versions. Not only that, the Flash Fill feature can save a lot of time for the user to perform other, more value adding functionality to an excel model.

Excel 2013 Flash Fill

 

Quick Analysis Option

The Quick Analysis Option allows the user to automatically add or produce a number of different outputs from a series of data without having to go through the Ribbon menu. Each option (see below) gives the user the option to test a potential add-in to the data set without having to commit, somewhat like a draft. Once the user finds what they are after, they can select and customise it on the active screen.

 

Excel 2013 Quick Analysis

 

Recommended Charts

This new function adds to Excel’s growing business intelligence functionality by giving the user a recommendation on chart type based on a series of data highlighted.
The user will be able to cycle through the recommended chart options, as well as view all charts which Excel 2013 offer.

Excel 2013 Recommended Charts

 

New Functions

A total of 66 new functions have been added to the latest version of Excel. Below are some highlighted examples of what will probably most used by excel users.

FORMULATEXT()

This formula will show the exact formula used a cell selected.

Excel 2013 FormulaText

IFNA()

Similar to the IFERROR() function which would allow the user to encode a response to an #ERROR, this performs the same function for #N/A.

 

ISFORMULA()

Brings back a TRUE/FALSE result is the cell is a formula (TRUE), or just a hardcoded cell (FALSE).

 

ISOWEEKNUM()

Returns the week number of a given date in relation to the year, e.g. 01/01/2013 = 1, 15/02/2013 = 7.

 

NUMBERVALUE()

The user can use this formula to remove delimitors (e.g. /,

[,… etc) effecting the numeric data within a cell, turning it into a useable number for further calculations, e.g. 400[300/555 at cell D9 becomes 400,300,555 with =NUMBERVALUE(D9,”[“,”/”).

 

SHEET()

Will return a value relating to the number order of a Sheet in a series of Sheets within a Workbook.

 

SHEETS()

Returns a number value for how many Sheets lie between 2 selected Sheets within a Workbook.

 

Slicers on a Table

You can now insert a Slicer into a regular data set, rather than pivot tables as in the previous version of Excel. The option is located in the Table Design section of the Ribbon, and allows for more user friendly filtering with the added Slicer options.

Excel 2013 Slicers on a Table

 

Data Model

In the latest version of Excel, Vlookups have been made much easier through some updated features added to the Pivottable Data Analyse (Analyze for all those used to American English) option.
One of Pivottable’s greatest new features is that it allows for 2 or more data sets which are related, potentially containing different data, to be merged into a single pivot table without having to use multiple VLookup functions. Not only does this save time, but also the potential pain of creating a formula (many people shy away from them) and the large consumption of data typically required for many cell formulae.

Excel 2013 Data Model 1

Edit Relationships

Pivottablefields

 

Apps Store

Provided you have a Microsoft account, you are able to download and/or purchase Excel 2013 specific apps (add-ins) which were not previously available in previous editions.

Apps Store
Below is an example of the FREE Bing Maps app. The user can input location names and statistics into cells and then select them to be included in the Map output.

MappsApp