Back
Excel

6 new Excel functions that simplify your formula editing experience

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

The Excel team is happy to announce six new functions that simplify some of your common calculations and help you avoid the tedious work of building custom functions to accomplish these tasks. We picked these six functions based on your feedback and will continue to provide more improvements in the future as part of your Office 365 subscription. We added TEXTJOIN and CONCAT to combine a list or range of text strings, MAXIFS and MINIFS to find the maximum or minimum in a range given one or more conditions and IFS and SWITCH to avoid messy nested IF functions. Read on for details on each function.

Combine text strings using TEXTJOIN and CONCAT

A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting.

The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)

The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)

Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:

6 new Excel functions 1a

The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.

6 new Excel functions 2a

If you want to know more, see the online help for TEXTJOIN and CONCAT.

IFS and SWITCH functions help specify a series of conditions

The new IFS and SWITCH functions give you an alternative to using a series of nested IF functions, like “IF(IF(IF()))”, when you have more than one condition that you want to test to find a corresponding result. The IF function is one of the most commonly used functions in Excel, and using IF inside IF (nested IF functions) has been a common practice in Excel, but it can be challenging or confusing at times.

The advantage of using the new IFS functions is that you can specify a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true—making it very straightforward to create and read the formula afterward. For example, let’s say you want to get the grade letter for a given score on a test. Using the IFS function, it might be something like this:

=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1<60, “Fail”)

6 new Excel functions 3a

This can be read as, if the grade in C1 is greater than or equal to 90, it’s an A. Otherwise, if it’s greater than or equal to 80, it’s a B. Otherwise, if it’s greater than or equal to 70, it’s a C and so on. It’s pretty easy to write it this way and it’s also straightforward to read and understand what’s going on.

The SWITCH function also handles multiple conditions. What makes it different is that rather than specifying a series of conditional statements, you specify an expression and a series of values and results. The values are compared to the expression, and when the first exact match is found, the corresponding result is applied to the cell. You can also specify a “default” result that will be returned if none of the values are an exact match for the expression. The advantage of the SWITCH function is that you can avoid repeating the expression over and over, which sometimes happens in nested IF formulas.

In the example below, the first part of the formula extracts the size code (i.e. XS, M and G) from the middle of the item in column B. It’s rather long, so it’s nice that SWITCH only needs it to be written once and it can be compared to a list of values.

The example below can be explained as:
Extract the size code from the item in column B. If it equals “XS”, the result is “Extra Small.” Otherwise, if it equals “S”, the result is “Small” and so on. If there’s no match, the result is “Not Specified.”

6 new Excel functions 4a

The same result could be calculated using nested IF functions, but it would be significantly longer, as shown below.

6 new Excel functions 5a

If you want to know more, go to the online help for IFS and SWITCH.

Filter and calculate data with MAXIFS and MINIFS

If you’re familiar with COUNTIFS, SUMIFS and AVERAGEIFS, then MAXIFS and MINIFS don’t need much explanation. The classic MAX and MIN functions calculate the maximum or minimum value in a range, but what if you need to apply conditions to filter your data? This is precisely what MAXIFS and MINIFS allow. You can specify one or more conditions that filter the data before calculating the max or min. The conditions can be applied to adjacent ranges or the range that contains the values. For example, let’s say a retailer has a table containing sales data for all their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales figure for a specified item in stores located in a specified region.

In the example shown below, MINIFS and MAXIFS are used to calculate the min and max sales figures from the table, but it only includes values from the Sales column if the value in the Retailer column is “BigMart,” the value in the Brand column is “Longlast” and the value in the Sales column is greater than zero.

6 new Excel functions 6a

If you want to know more, go to the online help for MAXIFS and MINIFS.

Try them yourself!

Try the new functions and see how they can help you simplify your formulas. Do you have ideas on other functions and improvements that you’d like to see in Excel? Click here to tell us about them!

Top
57 comments
  1. Very cool. I saw a blurb about this last week. SWITCH() should come in very handy. I cannot find the link, but it mentioned this would be coming to iOS and Android soon as well, but no mention of Mac. Please try to keep the Mac version somewhat consistent with the Windows version, especially in available formulas. I switch between Windows and Mac with Office 365 depending on which device I have – Surface or Macbook.

    • They could have managed my excitement/expectations better if this announcement opened with, “The Excel for Windows team is happy to announce …”. Even the introduction video says, “Office 365 Subscribers can now …”, which is obviously a half truth. I now have to include “(windows only)” in my OneDrive filenames so people know the workbook uses features the Mac does/will not have.

      • Hi Lance – Thanks for pointing this out. We do plan to add the new functions to Excel on iOS and Mac, but unfortunately I don’t have the target date for when that will happen.

  2. I have always used CHOOSE() combined with MATCH() to simulate SWITCH(), with the addition of IFERROR() if I need a default value.

    =CHOOSE(IFERROR(MATCH(MID(B2,SEARCH(“-“,$B2)+1,SEARCH(“-“,$B2,(SEARCH(“-“,$B2)+1))-SEARCH(“-“,$B2)-1),{“XS”,”S”,”M”,”G”,”XL”,”ZX”},0),”Not Specified”),”Extra Small”,”Small”,”Medium”,”Large”,”Extra Large”,”Double Excel”)

    • There’s a mistake in my example. It should be:

      =IFERROR(CHOOSE(MATCH(MID(B2,SEARCH(“-“,$B2)+1,SEARCH(“-“,$B2,(SEARCH(“-“,$B2)+1))-SEARCH(“-“,$B2)-1),{“XS”,”S”,”M”,”G”,”XL”,”ZX”},0),”Extra Small”,”Small”,”Medium”,”Large”,”Extra Large”,”Double Excel”),”Not Specified”)

  3. The function I’d like to see is DISTINCTCOUNT().

    Also, IFNA().

  4. While I have been using alternate methods to achieve the results these new functions provide, it will be very beneficial to use these new formulas moving forward, especially due to the fact that they will be easier to audit and understand by my coworkers.

    MINIFS and MAXIFS are a welcome addition, but I’d also love to see a MEDIANIF() function as well.

  5. How about we fix Excel and make it usable again?

    Microsoft has ignored this issue for months. The original post, located at https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/10196106-fix-broken-scrolling-in-excel-for-mac, has received tons of replies but no attention from Microsoft.

    This suggestion is #3 under hot ideas and it is the 4th most-voted change of all time. It isn’t even a new idea – it’s a basic bug fix. This issue has plagued us since the beta releases. Microsoft has done nothing but ignore a real issue. Very sad to see what happens when there is no competition.

    It’s insane that MS has ignored this. It needs to be addressed. It has plagued the OS X release since the early betas over a year ago. It’s all over their dev forums, all over Google, and all over this site. MS has done NOTHING to rectify the issue.

    I’ve posted (and may others have posted) about this actress all of Microsoft’s forms for months. This issue has existed since the first betas.

    Here are some links:

    https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/9194508-erratic-mouse-scrolling-in-excel-15-2-3

    https://answers.microsoft.com/en-us/mac/forum/macofficepreview-macexcel/excel-2016-on-mac-jumpy-scrolling/391412e8-6c18-4796-b757-3a6613995306?auth=1

    https://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/excel-2016-mac-jumpy-scrolling/22583584-7b3a-4817-b401-f7267868adb2

    This is an isane issue that many people run into on a daily basis. Microsoft needs to stop ignoring it.

  6. Very useful functions. Thank you.
    Now, I just need 2016 🙂

  7. The are very useful indeed . MaxIFS and MinIFS long term overdue.

    For me the killer functions to make me the upgrade to Excel 2016 are:
    – PERCENTILEIFS
    – DISTINCTCOUNT
    – DISTINCTCOUNTIFS

    Without these I will stay put on Excel 2010.
    With all honestly, I do no understand why the excel team is taking years to implement them, surely they are not that difficult to implement.

    PLEASE PLEASE PLEASE

    • Francisco – thank you for the comments and suggestions. It would be best if you can put your suggestions on https://excel.uservoice.com/, because then others will have the opportunity to vote for them. The voting helps us to know what is most important to Excel users.

  8. Some people analyze data which comes from statistical surveys. In these datasets are often used so-called “analytic weights” utilizing to change the structure of examined research samples. Unfortunately, there is not easy way to use mentioned “analytic weights” in standard Excel functions.
    It is an especially complicated problem when there is a need to count such statistical measures as percentiles, regression or other measures which must be counted by using individual, non-aggreaged elements of datasets.
    Maybe, You (dear Microsoft Excel Team) can create a set of formula which will be able to include these “weights” in calculations ???

    • Hi Rafal – thank you for the comments. We will certainly consider your suggestions, but it would be best if you can post them to https://excel.uservoice.com/, where others are able to vote to show support for the request. This helps us to prioritize all the requests that come in.

  9. Not sure if this is the correct place to report an issue or not, however I could not find any other so posting.

    Excel 2013 – Click to run installation – verion 15.0.4797.1003

    Insert a Table
    Create a Name Range referring to one of the columns in the table

    Create a In cell data validation and choose List – put in the named range and click ok.

    Now you can see the table column contents in the in cell drop down.

    Previously this method used to work with Form Controls ComboBox as well as ActiveX controls.

    After the latest udpate – this has stopped working.

    So if you put in a Named Range in the Form Control properties, put in the Linked Cell referrence and use TAB key to navigate in the dialog box; you will see the table selected when you highlight the
    INPUT RANGE, however the drop down will not show after you have come out of the box by clicking on OK.

    All other setting will be saved just the Input range is not getting saved – only when using a Named Range.

    Hopefully i have tried to explain the issue and will be happy to provide any sample files also if needed.

    Vijay

    • Hi Vijay–this is Andrew Stegmaier from the Excel engineering team.

      Thanks for pointing out this issue. I was able to see the same thing in Excel 2013 and 2016, but it appears to work fine in Excel 2010. We take feedback like this seriously when we prioritize our engineering investments for future versions.

      In the meantime, I wanted to suggest a workaround if you’re blocked by this–if you first point your named range at some _other_ range in the workbook that’s not a table column (e.g. =A1), then you should be able to set the input cell property of your dropdown control to this named range. Then, after the property is set, you can re-direct the range back to the table column, and everything will work fine.

  10. I read through everyone’s comment as much as possible to make sure this wasn’t already asked, but is this a part of an update to Excel 2013 via 365, too? Alternatively, do we have to install Excel 2016 via 365? Many of our in-house application add-ins do not support 2016 (very well, at least) just yet.

    Will I need to wait? I’m terrible at waiting. :'(

    • I have one PC running O365 ProPlus and enjoy some of the new functions.
      I too would like to have them on the other PC running Office 2016 Professional Pro.
      Why the delay?

      • Hi Bernard – By saying “Office 2016 Professional Pro”, I think you mean that you made a one-time purchase of Office 2016. If this is the case, then the new functions will not be available for that installation. As part of a subscription for O365, you are entitled to have the most up-to-date version of Excel, including any new features. On the other hand, if you made a one-time purchase (perpetual license rather than subscription), then you will have only the features that existed at the time that Office 2016 was initially available for purchase.

        • I have O365, and installed 2016 from O365, the above mentioned functions are available in the online Excel but not in my installed 2016 version, which is fully updated Version 16.0.6001.1061

          • Hi Mike – It sounds like you don’t have the latest updates installed. To check for updates in Excel, go to File>Account, then make sure it says “Subscription Product” near the top. If so, then click Update Options and choose Update Now. Once you get the latest updates, your version number should be 16.0.6528 or greater, which includes the new functions. If it doesn’t say Subscription Product, then you won’t see the new functions.

        • Hi Steve,

          I have just updated my stand-alone copy of Office Professional Plus 2016 (italian). Current version is 16.0.6568.2025, product activated. But I’m not logged into my Office Account.
          I still can’t see and use the 6 new functions. Why?

          While on the online version everything works as expected…

          Is this a limit to the stand-alone version of Excel? IFSo it’s a nonsense!!

          • Hi Marcello – it sounds like you purchased Office as a one-time purchase rather than as a subscription, since it says “product activated”. If you had a subscription, it would say “subscription product.” If you have a subscription, then you’ll need to log into your account so that it can be verified. Once your subscription has been verified, you should be able to see and use the new functions.

            For more information about the advantages of a subscription vs. a one-time purchase, please see https://support.office.com/en-us/article/How-do-I-get-the-newest-features-in-Office-2016-for-Office-365-da36192c-58b9-4bc9-8d51-bb6eed468516.

          • Thanx Steve for your answer. But I still don’t get the point. As you said mine is a one-time purchase with no subscription plan. Though I did get the latest update to the newest version for free through Windows Update. I do indeed have all the new features such as the black theme or the Funnel Graph in Excel. All I’m missing are the new 6 Excel functions!

            You are mean that there are two type of features in the update MS releases to Office:
            1) free for everybody who owns a valid licence, either with or without a subscription plan
            2) subscription plan reserved new features??

            Is that correct?

          • Hi Marcelllo – New features are reserved for those who are on a subscription plan, so if you don’t have a subscription, you should not be seeing any of these new features. Please describe what you see if you click File > Account and then look under where it says “Product Information” with the Office logo. For example, on mine it says “Subscription Product Microsoft Office 365 ProPlus”. Also, do you have “Update Options”, “About Excel” and “What’s New” buttons?

        • Hi Steve

          We have Office 365 ProPlus Subscription Product but still havent received this update..
          We are still on 16.0.6001.1068.

          I am from Norway. Are updated delayed in Norway or could there be som kind of administrator setting which we have to change?

    • Hi Bret – the new functions will not be added to Excel 2013. You will need to install Excel 2016 via O365. The new functions are also available in Excel Online, Excel Mobile for Windows 10, and Excel for Android. At some time, they will be available on Excel for iOS and Mac, but I don’t have the target date for that.

  11. IFS (Cool !!!!!)
    IFS evaluating all conditions, even after finding a true condition. Subsequent conditions need not to be evaluated once true found.

  12. I have O365 installed and I don’t have the new functions.
    I checked my version and it is: 15. 0.4797.1003 (which, as I understand from the comments here, is not the latest).
    However, when I clicked “Update Now” I get “You’re Up To Date” message.

    Any help would be appreciated.

      • After updating to Office 2016, my version is 16.0.6001.1061 and up to date, and still no IFS or SWITCH functions available…

        • Hi Or – It looks like your organization has chosen to receive updates at a slower pace (called the “deferred channel”), which means that you get new features less frequently than the monthly updates. You can read detailed information here – https://technet.microsoft.com/en-us/library/mt592918.aspx, and here’s some general information about how new features are released – https://support.office.com/en-us/article/How-do-I-get-the-newest-features-in-Office-2016-for-Office-365-da36192c-58b9-4bc9-8d51-bb6eed468516.

          • So??? I bought Home and Business 2016 (for home) ONE MONTH AGO specifically for the new Excel functions and I don’t have them. What’s the go? It’s such a convoluted process compared to earlier versions that one wonders why it needs to be so difficult.

          • Hi Brett – By saying “Home and Business 2016”, I think you mean that you made a one-time purchase of Office 2016. If this is the case, then the new functions will not be available for that installation. A one-time purchase does not include ongoing upgrades, which is of the key benefits of an O365 subscription plan. With a one-time purchase, you will have only the features that existed at the time that Office 2016 was initially available.

            For more information about the advantages of a subscription vs. a one-time purchase, please see https://support.office.com/en-us/article/How-do-I-get-the-newest-features-in-Office-2016-for-Office-365-da36192c-58b9-4bc9-8d51-bb6eed468516.

          • I note I can’t Reply To…..probably because Microsoft would know I’d be disgruntled at this news because I was misled. It’s really unhelpful and has left me wondering how to determine which new functions are available in which 2016 edition of Office.

            How do I go about exchanging my Home & Office for Office 365?? I expressly wanted what used to be Office Professional, i.e. not the Student version and thought the Professional version would have what I require. Can I purchase the new functions separately (although I don’t see why I need to). Very misleading, Microsoft..

  13. When will these new functions be added to Office 365 ProPlus?

  14. Why were the dropdown menus next to the Undo and Redo buttons on the Quick Access Toolbar removed? I hope they come back. Undo/Redo of multiple levels with this build of Excel are much more tedious than before.

  15. I have Office 365 E3 for Nonprofits. I’m using Office 2016. We are on standard release but I am one of the First Release subscribers for our org. Is there something i have to do to get the updates? Or are they still rolling out? Are staff will be excited to use these new features

    Lisa

    • Hi Lisa – Here’s some information to let you know how the updates are rolled out – https://technet.microsoft.com/library/mt455210.aspx. This latest update has been rolled out to the “Current Channel”, which receives monthly updates including new features. The new functions will hopefully be rolled out to the “First Release for Deferred Channel” subscribers (this is your channel) within the next few weeks, and will be rolled out to the “Deferred Channel” subscribers (the rest of your organization) a few months later.

  16. Love the new IFS!

    The TEXTJOIN will come in handy too in some cases (although I stopped using “CONCATENATE” ages ago as you can just put an & between text/cell refs to achieve the same effect)

    Overall it’s great to see that Excel is still being developed and I’ve already started voting for the next features on the uservoice forum!

  17. Hi,
    it is scheduled the release of these new features to Office Professional Plus?

    purchasing Office365 also provides for the use of stand-alone x64 exe version?
    of course, with these features it included ..

    thank’s in advance

  18. Thank you so much for sharing this great stuff! I`ve been working with Excel for a few years alraedy. I make reports with the help of Excel Add-in Zebra ( https://zebra.bi/ ). Does anybody of you guys use this tool for work?

  19. What will happen if I use one of these new functions and then someone with an older version of Excel who does not have them opens my spreadsheet?

    • Hi Michael – If you use one of the new functions and then someone with an older version of Excel opens the spreadsheet, they will see the value that was last calculated when you had the spreadsheet open. If they make a change that would cause a recalculation, then they’ll start to see #NAME? errors, since the older version of Excel doesn’t recognize the name of the new functions. If they look at the formula for these cells, they’ll see your formula, but the function will be prefixed with “_xlfn.”, which signifies that the function is available in a future version of Excel. Please see this article for more information – https://support.office.com/en-us/article/Issue-An-xlfn-prefix-is-displayed-in-front-of-a-formula-882F1EF7-68FB-4FCD-8D54-9FBB77FD5025.

  20. My version of EXCEL 2016 (16.0.6001.1068) part of Office 365 ProPlus does not have MAXIFS or MINIFS.

    Any reason why?

Comments are closed.