4.To make the Add-In a permanent part of your Excel, press Alt+T followed by I to display the Add-Ins dialog. Click Browse... and find your newly saved workbook. Click OK.

From this point forward, any time you have Excel open on your computer, you should be able to use

=SPELLNUMBER.

Gotcha: When you send the workbook to others, they will need to have the add-in as well.

RETURN DATA FROM A WEBSERVICE IN EXCEL 2013

Microsoft introduced three new functions in Excel 2013 that will return data from a webservice.

Figure 569 This data is not coming from a web query.

You will use at least two of the functions in conjunction with each other.

The first thing to do is to find a URL that returns data from a webservice. In this example, I am using http://feeds.feedburner.com/MrexcelExcelForumTop10Posts. This will return the five hot topics from the MrExcel Message board.

Many times, a web service will accept parameters that will return different data. For example, Yahoo

Weather will accept a location code. If you ask for http://xml.weather/yahoo.com/forecastrss/100001_f. xml, you will get the forecast for New York. If instead you ask for http://xml.weather/yahoo.com/ forecastrss/100009_f.xml, you will get the weather forecast for Los Angeles. Whoever publishes documentation on the webservice should provide you a list of codes.

In my example, I enter (or use formulas to build) the webservice address in cell A8.

In cell A9, I use the formula =WEBSERVICE(A8). This formula reaches out to the web and returns a mas- sively long result to cell A9. In my case, it returns 5009 characters of XML.

I hate to characterize the stuff in A9 as gobbledegoo. It is certainly machine readable and harder for humans to understand. But, if you copy A9 and paste to Notepad or to a text box, you will start to see some patterns. For me, I can tell that I want a field called “title” and another field called “link”.

Figure 570 Wade through the XML to find the fields that you want to return.

Because I am already familiar with this data source, I know there are generally five topics returned. However, there are some headers, so there are 7 occurrences each of title and link in the 5009 characters returned by the =WEBSERVICE function.

To extract the titles to a range in Excel, follow these steps:

1.Select seven vertical cells. In my case, A12:A18.

2.Type =FILTERXML(A9,”//title”). This is something called XPATH. I won’t claim to know anything about XPATH, but putting two slashes and the field name seems to do the trick.

3.Because you want this one formula to return 7 values, do not press Enter to accept the formula. In- stead, hold down Ctrl+Shift and then press Enter. Excel will return all 7 titles to A12:18.

Those steps required some trial and error. I initially only selected five cells, then noticed the two headers were being returned. So, I cleared those formulas and tried again with 7. I might have tried again with 8, but then the 8th value would have retuned #N/A.

Over in B12:B18, the array formula is =FILTERXML(A9,”//link”).

To put it all back together in a nice report, I use =HYPERLINK(B14,A14) in cell A2 and copy down five cells.

There is one more function that you might use - it is called ENCODEURL. Imagine that you are allow- ing someone to select a city from a dropdown list. They select the city. You use VLOOKUP to return the correct city code and then concatenate the whole thing together into a URL. If there is any chance that the result will contain an illegal character, you can wrap the formula in =ENCODEURL to convert those illegal characters into a valid URL.

ADD NEW FUNCTIONS TO EXCEL WITH FAST EXCEL SPEEDTOOLS EXTRAS

Check out these tools at http://tinyurl.com/fastexcel.

=SUM(B1:B5) IS BETTER THAN =B1+B2+B3+B4+B5

Problem: You have an intern working in the Accounting department who likes to add up short columns of numbers using =B1+B2+B3+B4+B5. You try to convince him that the correct formula is =SUM(B1:B5). He looks at you like you are insane, pointing out that his formula returns the exact same answer.

Strategy: Explain the story of the ad agency for Microsoft who plastered millions of dollars of billboards across America with ads for the new Surface tablet. The person who created the spreadsheet in the ad used a formula of =B1+B2+B3+B4+B5+B6 instead of =SUM(B1:B6) and ended up with a calculation error in all of the ads.

Figure 571 The real total is $9500, not $9000.

Here is what likely happened.