Excel 2013: Real World Examples of New Functions

Real World Examples of Excel 2013’s New Functions

[This is the second article I’ve written for the Microsoft MVP blog, but figured I would post it here as well for those who might find it handy]

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:

3823-image_5371c0bd

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:

1643-image_3c633f81

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:

8561-image_15b4b334

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):

5758-image_5fcfbe19

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):

1145-image_0631a165

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:

7345-image_2c9384b0

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.

Excel 2013 In-Depth Preview

What’s new in Excel 2013!

Microsoft has made it possible for anyone who’d like to check out Excel 2013 (included with the full Office 2013) through their Excel 2013 pre-release trial here.

UPDATE 2/20: Now they have the Office 365 full version for a one month trial!

I’d recommend downloading the Excel 2013 example file I go over in the video, but the majority of the information is included in these two links (if you are unable to download the Excel trial):

Excel 2013 New Function Examples // Excel 2013 All New Functions Listing

The video lesson shows some of the great new features in Excel 2013 including:

– Quick overview of appearance, template, and file menu changes [00:13]

– The new Flash Fill functionality [02:01]

– Quick-Analysis features including charts, tables, totals, formatting and sparklines [03:06]

– New charting features like Recommended Charts, and chart editing options  [05:02]

– In-depth examples of the following new functions [08:10]:

DAYS – ENCODEURL – FORMULATEXT – IFNA – ISFORMULA

ISOWEEKNUM – NUMBERVALUE – SHEET – SHEETS

– Example workbook includes Pivot data and tables already created and setup for trying out the new Excel Data Model that allows for multi-table Pivot Tables.

If you like what you’ve seen so far in Excel 2013, you should check out my Excel Data Model functionality tutorial.

Excel Video Lesson: Advanced VLOOKUP Examples

Advanced Vlookup Examples

The VLOOKUP function is one of the most frequently used functions in all of Excel.  In this video lesson I go over some advanced usages of the VLOOKUP function including:

– In-depth overview of the VLOOKUP syntax:

= VLOOKUP ( lookup_value, table_array, col_index_num, range_lookup ).

– Examples of how to set up intelligent formulas which pull information based on other cells rather than typed in (aka hard-coded) values.

– How to set up an intelligent order query which pulls a full row of information about different orders using one formula that gets copied and pasted.

Download the Advanced VLOOKUP Examples workbook to follow along with the video.

Video Lesson: Information Functions in Excel

In the following lesson I go through the Experience Level 1 Information Functions.  These functions are used to obtain information about formatting & referencing about specific cell or range references.  In this lesson I review the following functions:

ISBLANK – ISERROR – ISTEXT – ISNONTEXT – ISNUMBER

as well as the following info_types of the CELL() function:

“address” – “row” – “col” – “contents” – “width” – “filename”

“color” – “prefix” – “protect” – “type” – “format”

The CELL(“format”) info_type has many code results, so I’ve summarized them here:

Make sure to download the latest Master Workbook to follow along with the video lesson.

Video Lesson: Math and Statistical Functions

This Excel video lesson is about the Experience Level 1 Math and Statistical Functions in Excel.  Here is the level 1 function list extract from the Master Workbook that goes along with the video lesson.

The formulas discussed in the video are as follows:

MIN – MAX – AVERAGE – MEDIAN – SUM – PRODUCT – SUMPRODUCT

COUNT – COUNTA – COUNTBLANK – ABS – COUNTIF – COUNTIFS

SUMIF – SUMIFS – AVERAGEIF – RAND – RANDBETWEEN

ROUND – ROUNDDOWN – ROUNDUP – SUBTOTAL

As always, make sure to download the latest Master Workbook to follow along.

EDIT: The Gross Profit formula should not be including the ‘Retail Price’ in it’s SUM function.  I’ve left this mistake in there so that the numbers remain the same in the video & file, but I just wanted to point out that the calculation is incorrect.

Next up if you’re following the Lesson Guide is learning about Date & Time Functions (e.g. TODAY, MONTH)

Video Lesson: Advanced Lookup – Index & Match

The versatility and usefulness of the Index and Match functions are cherished by Excel veterans, but many less experienced users have either not encountered them, or are a bit confused by the usage of the two.

This Excel Video Lesson shows you how you can use the Index & Match functions to do advanced lookups which can be more reliable and versatile than just VLOOKUP or HLOOKUP alone.  In the video I go over the following uses of Index & Match:

– Two-Way Lookup (using both a row header and column header to find the intersecting value).  This can be very helpful when columns and rows may be added or deleted to your ranges.

– Averaging entire row/column information based on a lookup value.

– Summing entire row/column information based on a lookup value.

Make sure to download the most recent Master Workbook so that you can follow along at home!

Video Lesson: Logical Functions and Formulas

This Excel video lesson is about Logical Functions and Formulas in Excel.  Here are the formulas that I go through in the video lesson:

Logical formulas are extremely useful in more advanced Excel spreadsheets/methods and they are a great tool to have at your disposal.

Make sure to download the most recent Master Excel Workbook so that you can follow along. The spreadsheet I’m reviewing is in the Function Examples sheet in that Excel file.

The formulas covered in this video lesson are:

IF – IFERROR – AND – OR – TRUE – FALSE – NOT

Thanks for watching and feel free to leave any questions or comments in the comment box below!  I will respond via email if you include it with your comment.

Video Lesson: Lookup / Reference Functions

As listed in the Master Workbook (see link at the top of the page), there are functions grouped into categories. This video is a demonstration of the Experience Level 1 – Lookup & Reference functions.

The functions covered in this video lesson are:

ADDRESS  — COLUMN — COLUMNS — HLOOKUP  — INDEX — INDIRECT

LOOKUP — MATCH — OFFSET  —  ROW  —  ROWS  —  VLOOKUP

Extract of Level 1 – Lookup and Reference Functions from the Master Workbook:

A good follow-up lesson to check out would be either the Advanced Vlookup Examples or Advanced Lookup – Index & Match lessons.

Excel Video Lesson: Text Functions

This video tutorial is a demonstration of the Experience Level 1 Text functions.

Make sure to download the newest version of the Master Workbook that I updated earlier today to follow along.

The functions covered in this lesson are:

CONCATENATE — EXACT — FIND — LEFT — LEN — LOWER — MID — PROPER
REPLACE — RIGHT — SEARCH — TEXT — TRIM — UPPER

Extract of Level 1 – Text Functions from the Master Workbook:

Text Functions marked as Level 1

Next video lesson is Logical Functions and Formulas.

Any feedback is appreciated.

Excel Video Lesson: Date & Time Functions

As listed in the Master Workbook, there are functions grouped into categories. This video is a demonstration of the Experience Level 1 Date & Time functions.

The functions covered in this video lesson are:

DATE — DATEVALUE — TODAY  — DAY  — MONTH — YEAR
TIME — TIMEVALUE — NOW — HOUR — MINUTE — SECOND

CORRECTION: I mention in the video that Excel views dates as the number of days since January 1, 1900 but it is actually January 0, 1900 (a bit confusing since January 0 doesn’t make intuitive sense).

Now that you’re an Excel ninja when it comes to Date & Time functions, try mastering Text Functions (e.g. LEN, CONCATENATE)