After Hours

10 new Excel 2013 functions that can save you time

Excel 2013 includes a number of new functions designed to help you work more efficiently.

You might think that Excel couldn't possibly come up with any new functions — but with every new version, we get a few more. Most of them are specialized and of use to specific users, such as analysts or engineers. But Excel 2013 has at least 10 new functions that will appeal to a wide range of users.

1: DAYS()

I can't help wondering what took Microsoft so long to add the DAYS() function, which returns the number of days between two dates. Perhaps they believed it wasn't necessary, since the simple expression EndDate - StartDate will return the same result. Figure A compares this new function to the enddate - startdate expression.

Figure A

DAYS() returns an error in row 7 because the date in column A is a string that Excel can't interpret as a valid date.

2: IFNA()

Excel 2007 added the IFERROR() function, which combines the IF() and ISERROR() functions. The new IFNA() function works similarly by returning a value you specify if your formula returns the #N/A error. This function uses the form

IFNA(value, NAvalue)

where value references the formula you're checking for errors and NAvalue is the value you want returned if value returns #N/A.

If an argument references an empty cell, IFNA() treats it as an empty string (""). If value is an array formula, IFNA() returns an array. This function isn't superior to IFERROR(), but it is more specific about the type of error, which can lead to quicker troubleshooting. You can often get the same results using IFERROR(), but that function evaluates all error values. In contrast, IFNA() considers only the #N/A error, which allows you to narrow things down.

Figure B shows a row of VLOOKUP() functions that return #N/A when the lookup value doesn't exist. This function can return several error values; using IFNA(), you can match the #N/A error to an invalid lookup value.

Figure B

IFNA() lets you share specific information about errors.

3: SHEETS()

VBA's Count property returns the number of sheets in the current workbook (Worksheets.Count). Now there's a function that will do the same thing, and a bit more. Using the following form, SHEETS() returns the number of sheets in reference, where reference identifies a 3D range:

SHEETS([reference])

This function counts all types of visible, hidden, and very hidden sheets. For instance, if the current workbook contains five visible sheets, one chart sheet, and two very hidden sheets, this function would return 8:

4: SHEET()

Excel's new SHEET() function returns the sheet number of the referenced (by name) sheet. This new function has only one argument:

SHEET([reference])

This optional argument must be the name of a sheet. When you omit reference, the function returns the number for the current sheet. If reference isn't a valid sheet name, the function returns the #REF! error. It returns #N/A if reference is a valid name construct, but no sheet by that name exists. (A good use for IFNA()!)

5: XOR()

Excel's OR() function evaluates expressions and returns FALSE only when all expressions are false. If only one expression is true, the function returns TRUE. XOR() is what's known as an Exclusive OR, which returns TRUE if only one option is true (or false). Figure C shows this distinction.

Figure C

Use XOR() to find logical expressions where only one differs.

6: ISOWEEKNUM()

The WEEKNUM() function returns the week number in which a date occurs. It's usually adequate, but it won't work for organizations that use the ISO scheme, which assumes the first week of the year begins on the Monday of the week in which the first Thursday in January occurs. That means one of the last days of December could be the beginning of the first week of the next year!

Excel 2013's new ISOWEEKNUM() returns the ISO week for a date. Excel 2010 users can use the WEEKNUM() function's returntype argument (21). ISO is a European standard, so if you work with European entities, either function should help. If you're using Excel 2007 or earlier, you'll need a rather complex formula to return an ISO week. Figure D shows the difference between WEEKNUM() and ISOWEEKNUM().

Figure D

If you need ISO week numbers, use ISOWEEKNUM().

7: ISFORMULA()

This function is a new informational tool. Reference any cell and this function returns TRUE if the cell contains a formula and FALSE if not. Be careful though: You and Excel might not have the same definition of formula, as shown in Figure E. There's also a new FORMULATEXT() function, but I believe it will be of limited interest to most users.

Figure E

Quickly discern whether a cell contains a formula using ISFORMULA().

8: ARABIC()

Use ARABIC() to convert Roman numbers to Arabic, as shown in Figure F. It's easy to use and returns an error when the referenced cell or value isn't a valid Roman number. It does have its limits; its argument can be only 255 characters, so you can't convert beyond 255,000. The Roman component doesn't have to be uppercase, and although negative Roman numbers aren't standard, this function will handle the possibility.

Figure F

It's easy to convert Roman numbers to Arabic using ARABIC().

9: ENCODEURL()

This new function's more specialized than the first eight, but as more users expand into the world of Web applications and services, they''ll be specializing too — at least a bit. The ENCODEURL() function encodes text. URLs sometimes contain non-ASCII characters, which must be converted to ASCII characters before transmitting over the Internet. Encoding (converting) does that — it converts those non-ASCII characters into valid ASCII format.

10: WEBSERVICE()

Speaking of Web services, WEBSERVICE() returns data from a Web service across the Internet or an intranet. (A Web service is a type of communication using specialized software that allows electronic devices to connect and share data over the Web.) Right now, you might not be accommodating Web services, but many users are being asked to expand their skills in that direction.

Automatically sign up for TechRepublic's 10 Things newsletter!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox