Office 365 migrates to Office 2016

Office 2016 is the recommended version of Office 365 ProPlus and includes all the latest upgrades and new features. As we announced in September 2015, when we released Office 2016, beginning March 1, 2017, the Office 2013 version of Office 365 ProPlus will no longer be available for installation from the Office 365 portal.


How does this affect me?

Beginning March 1, 2017, your users will no longer see Office 2013 as an option for download through the Office 365 portal, and admins will no longer have the option under Software download settings in the admin portal to choose to enable Office 2013. In addition, we will no longer provide feature updates for this version, nor provide support.


What do I need to do to prepare for this change?

We recommend you install Office 2016 as soon as possible to have the latest and greatest features and support.

Power View

POWER VIEW has been deprecated. Now you must use Power BI Desktop

Presentation of Power View

Power View was a tool in Excel 2013 Pro Plus to create interactive dashboard.

Dashboard generated with Power View

Each element (chart, map, table of value) can interacted with the others.

For instance, if you select a slice of the pie chart, this will filter all the others element according to the selection

Technology of Power View

Power View has been developed with the technology Silverlight. Unfortunately, this technology has been depreciated by Microsoft in October 2012. So it was obvious that Power View in its original state couldn’t evolve.

In fact, in the same time, HTML5 was much more robust, quick and light than Silverlight. So it was necessary for Microsoft to develop a similar tool but base on HTML5.

Power View with Excel 2016

Power View is not visible by default in Excel 2016. Nowhere in the ribbon, you can find it.

For compatibility reason, Power View could be installed by activating the tool from the menu Options>Custom Ribbon

But, that was at the beginning of Excel 2016. It seems in 2018 that none of the Power View dashboard couldn’t opened.

What is the future of Power View?

In fact, now, the best way to create interactive dashboards is to use Power BI Desktop. And there is many reason for that:

  • The tool is free
  • You can connect to many different sources
  • Manipulate easily your data (Power Query)
  • Create your own measures (Power Pivot)
  • You have tons of visuals to present your data
  • Share easily your dashboards to a specific audience

New functions of Excel 2016

With the new release of Excel 2016, six new very interesting functions have been added in three libraries.

Library Text

Function CONCAT

  • Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or ignore empty arguments
  • This function depreciates CONCATENATE

Function TEXTJOIN

  • Better than the CONCAT function (it’s strange that Microsoft has introduced these 2 functions in the same release)
  • TEXTJOIN combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.
  • If the delimiter is an empty text string, this function will effectively concatenate the ranges
Function TEXTJOIN
TEXTJOIN with space delimiter

Library Logical

Function IFS

  • Can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. 
  • The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition

Function SWITCH

  • The SWITCH function evaluates one expression (a logical test) against a list of values, and returns the result corresponding to the first matching value.
  • This function is similar to the function SWITCH in Python, DAX, …
  • If there is no match, an optional default value may be returned.

Library Statistical

Function MAXIFS

MAXIFS returns the maximum value among cells specified by a given set of conditions or criteria

Function MAXIFS
Return the maximum in function of the product name

Function MINIFS

MINIFS returns the minimum value among cells specified by a given set of conditions or criteria

Move between workbooks

SDI is the abbreviation for Single Document Interface. SDI means that each workbook opens in its own window and has its own interface. This provides a lot of useful functionalities.

Benefits of dual screen with SDI

When you work with two monitors in a dual screen setup, showing two workbooks in the same Excel instance was not easy to do. Now with SDI, you can have a workbook on each screen.

Two workbooks on 2 different instances

Move or Copy between worksbook

With the SDI, moving a worksheet from one workbook to another is very easy; it is like moving a file from a folder to another one.

To move one worksheet to another workbook

  1. Put your 2 workbooks side by side
  2. Drag and drop a worksheet from the first workbook to the other one.

If you want to copy your worksheet, press the Ctrl key while you drag and drop.

Major update

Recently Microsoft has announced a new public update to Excel 2013 that includes major stability and usability improvements in Self Service BI scenarios – especially when Power Query, Power Pivot and Power View are involved. For this release we eliminated over 120 bugs, crashes and hangs and introduced two new important usability improvements that aim to make the analysts’ work smoother.

For more information on the content being included in this update, please read the official announcement on the Power BI blog.

 

If you have automatic updates enabled for your Office 365 subscription, you should receive the latest Excel update soon. If you don’t have automatic updates enabled in Excel, go to File >Account > Update Options > Update Now.

 

If you are running a perpetual Office edition then you will need to install the following updates through Windows Update or Microsoft Download Center – KB2910929KB2899498KB2899505and KB2920734.

Discontinued features in Excel 2013

In Excel 2013, 13 features have been discontinued or significantly changed.

Indeed, these features were not really popular, so it’s not a big issue if they have been removed.

Power BI with Excel 2013

In this video, look everything you can do with Excel 2013

Every tools used in this video are from the new application Power BI

  • Power Query
  • Power Pivot
  • Power View
  • Power Map

The logic of Power Pivot

Working with Power Pivot it isn’t hard. In fact it’s easier than Excel but the logic is totally different. But if you respect the next 3 rules, you will be able to build measures for your model

Rule 1: Structure of the data

When you want to create a measure, your data must be stored like in a database ; all the same data in only 1 column.

For instance, here is 2 presentation of the same data.

2 dimensional table
All the value in a single column

Even if the first presentation looks better because you can read easily your data, the second is the only one to present your data if you want to use Power Pivot.

With the second presentation, all the data for the calculation are in only one column and not 12 compared to the first presentation.

Rule 2 : Basic formulas are the best

When you work with Excel, COUNTIFS and SUMIFS are common functions to calculate with criteria. But when you work with Power Pivot, you must think more simple.

When you ask to an agregator to calculate your data, it’s the nature of the calculation that is the most important like SUM, COUNT, MIN, MAX, AVERAGE, …

In fact, this principle is already visible in Excel when you change the type of calculation in a Pivot Table.

With an agregator like Pivot Table, the basic calculations are the most important

Rule 3: Filters starts the measures

With Power Pivot, the name of  your formulas is a measure.

Measure SUM in Power Pivot

Once your have created your measures, you just have to simply drag it into a field in a pivot table to visualize the result.

Here is an example of the behavior of a measure in a pivot table. Each time you select a value in a slicer, this action give the order to the measure to be recalculated.

Presentation of Power Pivot

Power Pivot is a new tool of Excel 2013 that allows you to perform calculations on an unlimited number of rows. The calculation can be done with calculated columns, like in Excel, or with measures.

It all starts with SQL Server

Before to explain how to use Power Pivot, it is important to know that this tool has been developed by the engineers of Sql Server.

SQL Server is a relational database management system developed by Microsoft. It’s interface is SQL Server Management Studio (SSMS). When you use SSMS for the first time and when you are familiar with worksheet, you will be surprised to not visualize your data like you can do with Excel.

In fact, your data are stored, somewhere, but to visualize your data, you have to create a sql query.

Result of a query In SQL Server Management Studio (SSMS)

So, to visualize the data like in Excel, the Microsoft’s engineers have developed a tool call SQL Server Analysis Services (SSAS)

Data in SQL Server Analysis Services (SSAS)

When you link SSAS to a database, you can see easily the contain of each table like in a spreadsheet. But the data are in View only mode

SSAS = Power Pivot

At this step, Microsoft has created a tool to connect to database without limitation of rows. That’s the perfect tool to avoid the limitation of Excel.

So, in 2011, Microsoft has integrated SSAS to Office. And because the only way to return the results is in an agregator, and the only agregator of Excel is Pivot Table, SSAS becomes Power Pivot in Office (logic).

Installation of Power Pivot

In the version of Excel 2010, Power Pivot was an add-in but with Excel 2013, Power Pivot was integrated in the native Pro Plus version.

Update: Since May the 22nd 2018, Power Pivot is available for all the versions of Excel (Family, Student, Pro, …) and for Excel 2013 or Excel 2016.

But by default, Power Pivot is not activated. To activate Power Pivot, you must go to

  1. Files > Options > Add-ins
  2. Select COM Add-ins
  3. Press GO
  4. Finally, select the option Power Pivot

Now, you can see the tab Power Pivot in your ribbon

Power Pivot Ribbon

50 new functions in Excel 2013

With Excel 2013, 50 new functions have been added. Here they are and for some of them, few extra explanations.

Most of them are dedicated for top level engineers or statisticians. But there is also some very useful functions for a daily practice of Excel.

Date and time

2 new Date and time functions. ISOWEEKNUM is a relief to convert a date with the correct week number for most of the country in the world.

  • DAYS: Returns the number of days between two dates
  • ISOWEEKNUM: Returns the number of the ISO week number of the year for a given date. You will find an example of this function in this article

Remark: DAYS is useless because you can return the number of days between 2 dates with a simple subtraction

Lookup and reference

This new function is brilliant to present to your audience the writing of your formula

  • FORMULATEXT: Returns the formula at the given reference as text
Example of the function FORMULATEXT

Text

3 new Text functions

  • NUMBERVALUE: Converts text to number in a locale-independent manner
  • UNICHAR: Returns the Unicode character that is references by the given numeric value
  • UNICODE: Returns the number (code point) that corresponds to the first character of the text

Logical

2 new logical functions

Financial

2 new financial functions

  • PDURATION: Returns the number of periods required by an investment to reach a specified value
  • RRI: Returns an equivalent interest rate for the growth of an investment

Information

3 new function is this section. SHEETS is interesting to count the number of worksheet in your workbook.

  • ISFORMULA: Returns TRUE if there is a reference to a cell that contains a formula
  • SHEET: Returns the sheet number of the referenced sheet
  • SHEETS: Returns the number of sheets in a reference

Web

3 new Web functions. In fact, the web function category is new. You can now directly return the content of a website in a cell.

  • ENCODEURL: Returns a URL-encoded string
  • FILTERXML: Returns specific data from the XML content by using the specified XPath
  • WEBSERVICE: Returns data from a web service

Math 

7 new math functions. CEILING.MATH and FLOOR.MATH are really interesting

  • ARABIC: Converts a Roman number to Arabic, as a number
  • BASE: Converts a number into a text representation with the given radix (base)
  • CEILING.MATH: Rounds a number up, to the nearest integer or to the nearest multiple of significance
  • COMBINA: Returns the number of combinations with repetitions for a given number of items
  • DECIMAL: Converts a text representation of a number in a given base into a decimal number
  • FLOOR.MATH: Rounds a number down, to the nearest integer or to the nearest multiple of significance
  • MUNIT: Returns the unit matrix or the specified dimension

Statistical

6 new Statistical functions

  • BINOM.DIST.RANGE: Returns the probability of a trial result using a binomial distribution
  • GAMMA: Returns the Gamma value
  • GAUSS: Returns 0.5 less than the standard normal cumulative distribution
  • PERMUTATIONA: Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
  • PHI: Returns the value of the density for a standard normal distribution
  • SKEW.P: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean

Engineering

13 new functions in this section. They are only useful for top level engineer. For a basic use of Excel, they are not useful.

  • BITAND: Returns a ‘Bitwise And’ of two numbers
  • BITLSHIFT: Returns a value number shifted left by shift_amount bits
  • BITOR: Returns a bitwise OR of 2 numbers
  • BITRSHIFT: Returns a value number shifted right by shift_amount bits
  • BITXOR: Returns a bitwise ‘Exclusive Or’ of two numbers
  • IMCOSH: Returns the hyperbolic cosine of a complex number
  • IMCOT: Returns the cotangent of a complex number
  • IMCSC: Returns the cosecant of a complex number
  • IMCSCH: Returns the hyperbolic cosecant of a complex number
  • IMSEC: Returns the secant of a complex number
  • IMSECH: Returns the hyperbolic secant of a complex number
  • IMSINH: Returns the hyperbolic sine of a complex number
  • IMTAN: Returns the tangent of a complex number

Trigonometry

8 new trigonometric functions

  • ACOT: Returns the arccotangent of a number
  • ACOTH: Returns the hyperbolic arccotangent of a number
  • COT: Returns the hyperbolic cosine of a number
  • COTH: Returns the cotangent of an angle
  • CSC: Returns the cosecant of an angle
  • CSCH: Returns the hyperbolic cosecant of an angle
  • SEC: Returns the secant of an angle
  • SECH: Returns the hyperbolic secant of an angle

Load more