Many useful changes in Excel 2016 for Windows in the January 2016 update.
We have extensive coverage of the overall changes that apply to Word, Excel and PowerPoint. Check out separate articles on the specific new features in Excel, PowerPoint and Outlook.
Like to know more? Office 2016: the real startup guide has already been updated with comprehensive coverage of all the new features. That includes the ones Microsoft isn’t talking about!
Funnel charts
The new Funnel chart option is for situations where there’s a dropping (or growing) set of values from the stages of a process.
Funnel charts will appear in the Recommended Chart list or choose from the new ‘Funnel’ option for all chart choices.
Funnel charts are also available in Excel Mobile. Only the Windows 10 and Android apps at the time of writing, presumably Excel for iOS will catch up soon.
Alternatives to nested IF’s
Nested IF statements have been a necessary evil for as long as there’s been worksheets. Over time, Microsoft has added functions to replace ‘IF with IF’, most notably VLOOKLUP and HLOOKUP.
Excel 2016 for Windows gets some more functions that should work better and be easier to understand/debug.
IFS
You can almost hear the sigh of relief from Excel users as they discover IFS(). It lets you create a series of conditions and act upon the first condition that’s true.
Here’s a straightforward example that any teacher will recognize. It tests a student’s % grade and returns a phrase equivalent.
=IFS(A2>89,"Excellent",A2>79,"Good",A2>69,"OK",A2>59,"Pass",TRUE,"Failed")
- IFS is a series of paired parameters … first a test, then the result or action to take if that condition is true.
- The order of IFS tests is vital. The first condition that evaluates as True will be used and the rest of the function ignored.
- Notice the last condition. This sets a default ‘True’ if none of the previous tests are triggered. If none of the tests return True you’ll get a #N/A! error so a default is a good idea.
- Each test has to return either TRUE or FALSE. Integer zero will return FALSE, any other value will be considered TRUE. Anything else will give you a #VALUE! error,
- The result of a pair can be empty, in other words no result is returned.
- Only the first test/result pair is required.
The formal syntax is
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],...)
So far IFS seems like a new way to do versions of VLOOKUP/HLOOKUP but it goes beyond that. The conditions or results do NOT have to be related (ie not testing the same cell value) and you can have up to 127 pairs of conditions and results.
The type of results can differ. Some can return a text label, others a cell reference or another formula.
Switch
Switch is similar to IFS except that it matches a single expression against a series of values to find a match.
SWITCH(expression, value1, result1, [default or value2, result2],...[default or value3, result3])
The first parameter is the expression or cell value you want to test.
Then follow pairs of values to test against the expression with a result to return if true.
The final parameter is an optional default result if none of the previous tests apply. If there’s no default and no test works you’ll get the #N/A! error.
Microsoft’s example is straight forward:
=SWITCH(DayofWeekNumber,1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday","No match")
MAXIFS
As the function name suggests, this combines MAX() with IFS(). Excel already has SUMIFS to add up values that meet certain tests, now we have a similar function which gives the highest value (and there’s also MINIFS)
Let’s say you have a range of values, you want to find the highest value in that range but only when some other criteria is applied.
Here’s the formula to find the best red-headed student in a class.
=MAXIFS(A2:A7,B2:B7,"Red")
The formula looks only at the scores that match the criteria (Red hair color) then selects the highest only from those scores (96).
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The first parameter is the range of values you want to return the highest value from.
The next pair of parameters are a range of values plus a value to match against.
- The criteria range of values must be the same size (number of cells) as the max_range but doesn’t necessarily have to be adjoining.
=MAXIFS(A2:A7,B11:B16,"Red")
works because both ranges span 6 cells. - Empty cells are treated as having the value zero.
- If the combined criteria eliminate all the possibilities, MAXIFS will return 0.
MINIFS
As the function name suggests, this combines MIN() with IFS(). It complements MAXIFS and the existing SUMIFS formula.
You want to find the lowest value in that range but only when some other criteria are applied.
MINIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The first parameter is the range of values you want to return the lowest value from.
The next pair of parameters are a range of values plus a value to match against.
- The criteria range of values must be the same size (number of cells) as the max_range but doesn’t necessarily have to be adjoining.
=MINIFS(A2:A7,B11:B16,"Red")
works because both ranges span 6 cells. - Empty cells are treated as having the value zero.
- If the combined criteria eliminate all the possibilities, MINIFS will return 0.
CONCAT
CONCAT isn’t really new at all, it’s merely a new name for the old function CONCATENATE.
CONCAT or CONCATENATE joins a series of strings together.
CONCAT(text1, [text2],...)
Of course, you can also use the & symbol to join strings.
CONCATENATE is still available in Excel but may be removed from a future version.
TEXTJOIN
TextJoin creates a text list from a series of values. It’s similar to CONCAT but inserts a character between each value and there’s an option to ignore empty values or not.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Join the text in each cell with a comma separating each one. Any empty cells are included.
=TEXTJOIN(",",FALSE,B2,C2,D2)
Improved Autocomplete
Until now, typing into the formula bar started autocomplete but it only showed formulas that started with what you typed. From January 2016, Excel will look at the whole function name.
It will look for matches in the entire formula name. Type in ‘sum’ and functions like ‘Dsum’ and ‘SeriesSum’ also appear.
Send As options from Share pane
An earlier update added ‘Send as attachment’ to the Share pane in Word and PowerPoint.
It’s now also available in Excel.