Excel 2013–Real World Examples of New Functions

Editor’s note: The following post was written by Excel MVP Ben Currier

Excel 2013 – Real World Examples of New Functions

Microsoft included over 50 new functions with Excel 2013, and I wanted to walk you through a few real world examples of these new additions. You’ll soon be able to see how handy these extra tools are in your ‘Excel toolbox’. I’ll only be going over just a select few of the new functions, but feel free to leave a comment with some of your other favorite new functions or features!

FORMULATEXT()

Until Excel 2013, there has been a gap in our ability to see and use formulas in any given cell. There has been the option of going into Formula Auditing mode (either via the ribbon or Ctrl + `) which allows you to view all of the formulas in a spreadsheet. However, this is an all-or-nothing type feature and only applies to the visualization of the spreadsheet, allowing for switching between all formulas being displayed, or having all results/values show instead. Sometimes there are instances where you want to be able to track or assess a formula in another cell without having to select it directly or change viewing modes entirely. The FORMULATEXT function allows for this ability.

For example, you could very quickly check if two formulas are identical across different spreadsheets, even if their resulting values are not the same. This might be useful if you have a spreadsheet template that you’ve setup and you want to make sure that two versions of it have identical formulas. By comparing the formula text from each of the sheets, you could quickly see if any changes had been made.

Let’s say that you keep a weekly record of daily sales for your company, with Monday through Sunday sales being tracked in cells B6:E12 of multiple spreadsheets, as seen here:

If you were using a third ‘Sum’ spreadsheet to add up the Monday-Sunday totals from multiple sheets, you might want to make sure that the formulas in the cells are the same. Depending on how you’ve designed your workbook, knowing that the formulas haven’t changed could help ensure that the template is working as intended and that any associated formulas would reflect the correct information. In this example I use a ‘Check’ worksheet with formulas to see whether the formula text in each of the order total columns is the same. Here I’ve used the new FORMULATEXT function to compare whether the formulas are the same in column E, and I’ve also put additional FORMULATEXT functions in column G to help illustrate the formulas I’m using to compare the two sheets:

I’m using a direct comparison by putting the = sign between the two sheet/cell references. This comparison results in a TRUE because the formula text in the Week 1’s cells is the exact same as in Week 2’s. If I were to go in and delete row 3 from above the table in one of the sheets, the function would give me a FALSE on all of them, showing me that the formulas no longer match and something has changed with my underlying data sheets. I could go further and put a conditional format to alert me visually when any of my values turn false by using a COUNTIF statement like this =COUNTIF(E6:E13,FALSE) which would always show 0 unless a FALSE sprung up in my ‘Check’ sheet. Overall, the addition of the FORMULATEXT function certainly adds to your ability to track formulas in cells and understand changes in your workbooks.

ISFORMULA()

The new ISFORMULA function joins it’s brethren of ISBLANK, ISNONTEXT, ISNUMBER and the other information functions related to checking what kind of value the output is. In this case, however, the function allows for us to see whether or not the referenced cell contains a formula. Before this addition, it would require a lot more effort and a much more complicated formula to check whether a cell has a formula in it. Using the previous example above, if we wanted to track whether or not the weekly totals were a formula vs. something else (like a hard-coded value), we could use the ISFORMULA function to check. First, here’s a glimpse of the sheet for Week 1 sales once I’ve changed cell E7 to be a value only. I’ve included the Formula Text in column G which comes up with an #N/A value for the total that I changed:

Now instead of checking out the formula text in column G, I’ll change it to an ISFORMULA function, so that you can see the result. I’ve conditional formatted the FALSE result so that it stands out. This would be another good way of keeping track of which components of your spreadsheet are formulas vs. hard-coded values (and can help you identify issues with your calculations):

Especially when using the conditional formatting, the changed cell really jumps out at you, whereas before it might have been a lot more difficult to realize that E7 has been changed to a value. If you had gone into Tuesday’s sales to update from 3 to 4, the order total would not appropriately update. Unfortunately, without some kind of formula/error tracking, it would likely go unnoticed. If you find yourself having trouble with how errors work in Excel, or ideas for how to track changes in your data, see my tutorial on Error Checking & Data Monitoring.

XOR()

When dealing with logical functions in Excel, they require a statement that results in a TRUE or a FALSE (aka Boolean) value. The AND function will assess whether all of the logical statements passed to the function are TRUE, and the OR function will check to see whether any of the values show up as TRUE. In Excel 2013, Microsoft has added the XOR function which is an ‘Exclusive OR’ function. In the simplest version of 2 logical statements, XOR will return TRUE if that the result is TRUE for either of the two results, but not both and not neither. When more logical statements are added, the XOR works so that it will result in a TRUE if the total number of TRUE inputs is odd, and FALSE is the number of TRUE inputs is even. Here is an example which shows all of the possibilities for 3 logical statements being fed to the XOR function (I’ve highlighted the TRUE/FALSE values to make it a bit easier to understand):

It may be a bit difficult to grasp the concept at first, or how it could be applied to a real-life scenario. The term is of ‘Exclusive Or’ is more frequently heard when related to programming or computer science in general, but here’s an example of how the XOR function could be used to assess whether an employee is working a half-day using only two logical statements:

As you can see, it only shows up as TRUE for working a half day is one of the two statements in columns C & D are true. There are much more complicated ways you can use XOR, especially related to mathematics, but hopefully this gives you a taste of how the function works.

Brush up on your Functions!

I’ve only gone through only three of the new functions here, but if any of these piqued your interest, I’d suggest reviewing all of the new functions that have been added with Excel 2013, and certainly would suggest taking some time to review the 400+ functions that you already had at your disposal before these new functions were added. Since there are so many aspects of Excel, you might find a hidden gem that you didn’t know already existed! For a listing of pre-existing functions, shortcuts, and examples of how to use them, feel free to check out my Master Workbook. Hope you’ve enjoyed the lesson and feel free to leave any comments or questions below.

About the author

Ben Currier has been working in Financial Planning and Analysis over the past 10 years. He also teaches an on-going free online Excel course at Excel Exposure with video tutorials and lessons, which aims to help people to improve their Excel skills.  He is excited about the emerging trends in online education and loves the thought of quality, free, and accessible educational information available for anyone who wants to improve their knowledge and abilities.

About MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.