Tuesday, July 16, 2013

Playing with Excel 2013

In a recent discussion with +Bill Jelen  (Mr. Excel himself), we drew the impression that Excel 2013 is not flooding the market place like Excel 2007 and even more dominantly Excel 2010. I've had Excel 2013 for a long while but haven't used it in production because my clients are running either Excel 2007 or Excel 2010. So tonight, testing out some training material (written for Excel 2010), I came across these quirks in Excel.

  • Excel 2013 uses a Single Document Interface (SDI). Each workbook opens in a separate window. This is long overdue because a fair number of power users are on two screens and Excel pre 2013 loads all the workbooks into the same window unless you run Excel empty as another instance and then use File . Open to load the next workbook you want to use. So SDI is good. However, it is said to give macros that switch workbooks some issues.
  • Excel 2013 has an Quick Analyze pop up icon at the bottom right corner of a dense block of cells. This offers options to carry out charting, creation of tables, pivot tables, conditional formatting, add subtotal row. However, it seems to automatically choose the columns in your block of cells to carry out this operation. I can't yet see the usefulness in that.
  • Excel 2013 seems to have lost the tin stubs at the end of the scroll bars. I like using them to split a window into panes. The Command Ribbon . Views Tab still has the Split Window icon.
  • For the new generation Excel, 2007 and later, creation of a table in the workbook blocks the use of the Views . Custom Views feature.
  • You can use Custom Lists for Advanced Sorting in Excel 2007 and later. But Custom Lists are stored in the specific Windows registry of the machine (MSKBID 213796). You can produce lists on a worksheet, then run a macro to take those lists and push them into the registry of another machine.
       Application.AddCustomList ListArray:=Sheets("Sheet1").Range("A1:A50")

More as I encounter


Bill said...

Great tip with Application.AddCustomList

Ananda Sim said...

Thanks Bill. Both in Excel and Word, there are infrastructure details that started with product design in the 1980s, 90s and 2000 but by 2010, the user cases have evolved markedly. The location of templates in Microsoft Office products in some obscure bowels of the PC user/roaming/ folder is now way out of fashion. People just move around whether they are using USB sticks, Office 365 cloud or Google Apps/Drive cloud. Things like templates, the new .thmx files, Word autocorrect data, personal dictionary and Excel custom lists need to be liberated from obscure places. And we haven't mentioned .xla files.