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.

8 comments
guy
guy

The ARABIC function confused me a bit.  I understand the function but my question is why.  Can somebody please suggest some applications where Roman numerals would have been used in an Excel sheet.

Just curious.


Thanks

kabluii
kabluii

I think there is a mistake in the description of the XOR function. It returns TRUE if an odd number of arguments is TRUE. Check line 9 of the example.

jvp@CTFolk.com
jvp@CTFolk.com

Some of the new functions are somewhat abstruse and applicable only to a small percentage of users. There are two functions that I think would be much more valuable for the next version of Excel.
   One of the ways Excel has caused serious financial loss is in cashflow projections and budgets, where one amount is being added to the previous amount in a column. (Example: previous running total plus income minus expense equals new running total.) The problem occurs when the rows are rearranged into a different order, e.g. when dates change, which  can cause the formula in the running totals column to reference a value in a row other than the row just above it. A function such as sumup(A2:a100,n), where n is a number the default for which is -1, would always reference the cell just above the cell in which the formula is entered, even if the rows are completely rearranged. (A positive number for n would be equivalent to a sumdown function.) Right now I can do this using indirect("column letter"&row()-1), but that's kludgy and beyond many users.
   The other function is for generating a report by selecting some items from a list and not others based on whatever criteria, without requiring the user to manipulate the date filtering. The problem is that the eliminated items show up as blanks in the new list. There needs to be an easy array function that closes up the list so there are no blanks.   

TimMartin01
TimMartin01


Microsoft excel is one of the greatest office package software ever designed for business professional that has being a great tool. Ever since computers entered the business world MS excel has played a great versatile role but most of its functions are hidden and makes it a great difficulty for common people understand. One thing your post has done is definitely given guidance for fast and reliable experience to excel users thus making it a better tool for computation.A very big thank you for the post.

ssharkins
ssharkins

@TimMartin01 Thank you -- I'm glad you found the article helpful! Sometimes learning a new interface is so overwhelming that we forget all about the new functions! 

DAS01
DAS01

@ssharkins @TimMartin01 Belated comment... actually... Lotus 1-2-3 is the greatest piece of software, earlier and better than Excel... although not being the first spreadsheet it was Lotus 1-2-3 that drove the widespread adoption of personal computers in business.

However, Lotus 1-2-3 is dying out and I think Excel is a worthy successor.

Pierre Venter
Pierre Venter

I think that MS could have a look at the addin apps created specifically to address the shortfalls in functions. ASAP utilities is one. Include the additional depth of parameters and return values then it would develop the advanced options. These could be selected by setting the options to beginner, intermediate or advanced and the functions flagged according to the level. Then corrections for converting strings to valid values, ie dates could give the results, with warnings on accuracy

Editor's Picks