J-Walk & Associates, Inc.

Dynamic Range Names?

Does anyone use dynamic range names anymore? In the past, this technique was the only way to create a chart that expands as you enter more data (here's an example) . But now you can easily create a self-expanding chart by using a table.

There must be other good uses for this technique, but I can't think of any.

Creative Charts

Check these out:  Creative and advanced chart design in Excel.

You can download the workbooks to see how these charts are made. If you're stumped, use Excel's Name Manger to examine the names. Then you'll be even more stumped.

Try This DatePicker Task Pane App

To get a feel for how Office 2013 apps work, I created a simple one: A DatePicker Task Pane app. This works with both Excel 2013 and Word 2013.

If you have a copy of the Office 2013 Preview, please give it a try. 

Here's what you need to do:

1. First you must specify a trusted app catalog. In this case, it's a Sharepoint app catalog where the app's XML manifest is stored.  In Excel,  choose File - Options - Trust Center, then click the Trust Center Settings button.
2. In the Trust Center dialog box, select Trusted App Catalogs.
3. In the Catalog Url field, type this: https://jwalk.sharepoint.com/sites/jw, and then click Add Catalog.
4. Make sure the Show in Menu option is checked, and then click OK to exit.
5. In Excel 2013 Preview, start with an empty workbook.
6. Choose Insert - Apps for Office.
7. In the Insert App box, click the SHARED FOLDER option.
8. You should see an item named DatePicker Task Pane App. Select that item, and then click Insert.

The DatePicker Task Pane app should appear. Note that the inserted app is stored with the workbook. In other words, if you activate another workbook, the Task Pane will not be available (unless you also insert it into that workbook).

At least that's how I think it works. Plus, Sharepoint has always been a confusing mystery to me, and I really don't understand the concept of trusted app catalogs. If anyone (besides me) can get this to work, I'll be satisfied (and a bit surprised).

By the way, only the XML manifest file is at my Sharepoint site, and that file points to the app's location on my server. The app is just a plain old HTML document, with lots of Javascript (the bulk of which is Unobtrusive Date-Picker Widget V5, from frequency-decoder). In fact, you can run this app in your browser rather than as an Office App: http://j-walk.com/datepicker/datepicker.htm. You won't be able to insert any dates when it's running in your browser, but you can see how it works and use your imagination.

Time will tell if Office Apps catch on. At some point, developers will be able to submit apps to Microsoft, and (if they meet their standards) they will be available at the Microsoft Office App Store. If the apps are sold, rather than given away for free, Microsoft will keep (I think) 20% of the proceeds.

To me, the biggest problem is the limited interaction with the Office programs. For example, an Office app can read data from a range and write data to a range -- but that's about it. In this DatePicker app, it would be nice to provide a list of date formats to apply to the date cell, but that's not possible.

My problem is that I'm still thinking in terms of add-ins. Office Apps are nothing like add-ins, and are intended to serve a much different purpose.

I Made A Simple Task Pane App

If you've read about Office 2013, you may have seen the terms Content App and Task Pane App. I tried my hand at creating a task pane app,  just to get a feel for how it works.

This task pane can be inserted into a worksheet. It displays my Excel Blogs list, which links to the most recent headlines at each blog. It looks like this:

It's really nothing more than a browser that displays this page. When you click one of the links, the Task Pane displays the recent headlines for that blog as links.  To read an article, click and it opens the article in a browser window. Actually, it's an Internet Explorer browser window. Apparently, task pane apps that have outside links ignore your default browser and always use IE.

This app, of course, offers no advantage over just viewing the site with a browser. Next, I think I'll try to make a flashy date picker app, using HTML5 and Javascript. That one might actually be useful. Documentation is pretty poor at this point, but from what I'm reading, a task pane app can read cells in a worksheet, and write data to cells in a worksheet. That's about it.

Contrary to what's implied in this headline, apps will not replace add-ins: Microsoft Office 2013- Goodbye to adds-in, bring on the apps.

The Research Behind Flash Fill

I predict that one of the most controversial new features in Excel 2013 will be Flash Fill. Here's a video that demonstrates how it works. It's an AI alternative to extracting specific  parts of text strings. For example, if you have  a column of names, you can type a few last names and Flash Fill will figure out what you're doing, and fill in the empty cells.

It's a great concept, but it can also lead to lots of bad data. I think many users will look at a few "flash filled" cells, and just assume that it worked. But my preliminary tests leads me to this conclusion: Be very careful.

Flash Fill works in two ways: (1) It can extract data by example, and it can (2) create data by example. Creating data seems to be much more reliable. But use caution when extracting data. For example, most of the extracted data will be fine. But there might be exceptions that you don't notice unless you examine the results very carefully.

For the technically-minded, here's a Microsoft Research report (PDF) that seems to be the basis for this feature:  Automating String Processing in Spreadsheets Using Input-Output Examples.

Excel 2013 Charting Notes

Today I discovered a new feature in Excel 2013 that I hadn't seen mentioned elsewhere: When you create a chart using Excel 2013, you can specify an arbitrary range of data to be used as data labels! Users have been requesting this feature for at least 10 years -- probably longer. Currently, you need to use a utility program (such as PUP or the utility from AppsPro).

Charting in Excel 2013 is vastly improved. The Format dialog box has been replaced with a task pane. Once you get over the initial shock of not being able to find the commands you need, you'll probably find that it's a significant improvement. In addition, when you activate a chart, you see three icons on the right. These icons are the express route to making common changes to your chart.

The third icon has some new filtering options. For example, you can easily hide (not delete) a series, or hide specific data points in a series.

Another improvement is creating combination charts. In the past, creating a combination chart required several manual changes to a normal chart. Now it's all done at the time you create the chart. And you even get a live preview.

Yet another new feature is "recommended charts." When you create a chart, you can choose to let Excel give you some recommendations. I can't say that I always agree with the recommended charts, but new users will appreciate the suggestions.

All in all, I'm impressed. Excel 2013 has come a long way in making charting easier to use for the masses.

I Don’t Like This

I've always hated that "Format" dialog box that's used to format shapes, pictures, and other inserted objects. It always seemed very poorly designed, and more difficult to use than it should be.

Microsoft made some major changes to it in Excel 2013. Unfortunately, it's even worse than it was. It now features an amazingly confusing mixture of text and icons. The old version was vastly superior.

Office 365 Preview

Get more information and download it here:  Office 365 Customer Preview.

I installed it on a system that also has Office 2003 and Office 2010. No problems, so far.  But the new "flat" look sure is ugly. And I've already discovered a major annoyance: Each workbook opens in a separate window.

But I've found some nice new features, and it has lots of new worksheet functions:  ACOT, ACOTH, ARABIC, BASE, BINOM.DIST.RANGE, BINOMDIST, BITAND, BITLSHIFT, BITOR, BITRSHIFT, BITXOR, CEILING.MATH (same as CEILING.PRECISE), COMBINA, COT, COTH. CSC, CSCH, DAYS, DECIMAL, ENCODEURL, FILTERXML, FLOOR.MATH  (same as FLOOR.PRECISE), FORMULATEXT, GAMMA, GAUSS, IFNA, IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH, IMTAN, ISFORMULA, ISOWEEKNUM, MUNIT, NUMBERVALUE, PDURATION, PERMUTATIONNA, PHI, RRI, SEC, SECH, SHEET, SHEETS,  SKEW.P, UNICHAR, UNICODE, WEBSERVICE, XOR. I probably missed a few (and misspelled others), so don't consider this a comprehensive list.

Now it's time to start revising my Excel books.

Musical Resignation Letter

A Microsoft employee calls it quits

So Bye, bye Excel and I
It's been 3 good years, oh how time has flown by
With devs in the hall drinkin' whiskey and rye
Singin' forever recalc or die, forever recalc or die

Another PUP Sale

By popular demand,  we're conducting another 24-hour sale for Power Utility Pak.

You'll save lots of money, on either version of PUP:

• PUP v7:  $7.77 (normally $40.00)
• PUP v6:  $6.66 (normally $39.95)
• The complete VBA source code is available for an additional $20.00

The sale begins at 11:00 am U.S. Eastern Time on Tuesday, March 27, and will last for 24 hours.

Click here for the 24-Hour Sale Order Form

Spread the word. This is a great opportunity to buy a PUP license for everyone in your office.

Excel 15 Features

Found here:  Exclusive- Microsoft Office 15 features and improvements

Microsoft is aiming to make Excel 15 easier for folks who need to visualize large data sets that they are working with. A new Quick Analysis Lens feature lets Excel 15 users quickly access ways to visually represent data, and Recommended Charts and PivotTables helps surface the best way to visualize certain information based on data patterns. Flash Fill, a new feature to Excel 15, will also simplify the reformatting and rearrangement of data in an Excel spreadsheet. Excel 15's Touch Mode feature is designed to let users browse through charts, graphs, and tables on tablet devices with their fingers and hands.

Excel 15 will also introduce some new chart formatting controls that use a fully interactive interface to fine tune charts quickly. Chart animations will also let chart users see and understand every different change in their charts as new data points are added or existing numbers adjusted along the way. Microsoft is also building in a new Start Experience for Excel 15 that provides access to a collection of professional designed templates, including ones for budgets, calendars, forms, and reports.

I haven't seen it, so I have no idea if this is accurate or comprehensive.

The Spreadsheet Page Is 16 Years Old

Today is the 16th anniversary of The Spreadsheet Page. It's now old enough to drive.

Here's how it looked in 1997, the earliest archived page:

Excel-related RSS Feeds

I keep a fairly comprehensive list of RSS feeds from Excel-related blogs: Excel Blog Headlines.  Today I added a new one, from Doug Glancy. His site has a clever name: yoursumbuddy.

I was going to clean up the list, and remove sites that haven't been updated in a while. But then I decided to leave them in -- just in case they come back to life.  Plus, it required too much effort.

Excel 15

I've been keeping my eye on the news for stories about the next version of Excel. Not much so far, just rumors. For example: Microsoft planning beta of next-generation Office 15 suite in January.

The software giant is currently preparing final milestone builds of Office 15 in preparation for a full beta in late January, according to sources familiar with the company’s plans. Microsoft Office 15, the codename for the next version of Microsoft’s popular productivity suite, is tentatively scheduled for a beta 1 milestone in late January. Microsoft will provide a Technology Preview of the software initially, expected at CES 2012 alongside the Windows 8 beta.

That didn't happen.

Office 15 will be designed with touch at the heart of the applications. Microsoft has redesigned the general look and feel of its popular Outlook email client to make it usable by touch, pen and mouse… Microsoft CEO Steve Ballmer confirmed in September that the company is preparing a Metro style version of Office. You ought to expect that we are rethinking and working hard on what it would mean to do Office Metro style,” said Ballmer

How To Be Employable

Here's some advice: You will always be employable if you know how to do this

One recruitment professional claims to have identified the secret of eternal employability in financial services: advanced Excel skills.

“There is always steady demand for people with advanced Excel,” says Dominic Connor at P&D Quant Recruitment. “It is an excellent safety net for when it hits the pan.”

Using Excel to ensure employability means familiarising yourself with pivot tables, VBA, importing and exporting data from SQL servers and more complex elements like DDE (dynamic data exchange strings).

And this:

The great joy of all this is that it’s easy to learn, argues Connor. “All you need to do is to buy a book by Walkenbach and work your way through from beginning to end,” he suggests, “The joy of Excel is that everyone knows how to do it, but not many people know how to do it properly.”